Amity University, Noida
Uttar Pradesh
Amity Institute
of Information Technology
Advanced Database Management Systems
IT-601
Submitted to: Submitted by:
Dr. Komal Saxena Ananya Agrawal
A010145023084
MCA (1-B)
Q1) Develop a database application to demonstrate the representation of
multivalued attributes and the use of nested tables to represent complex
objects. Write suitable queries to demonstrate their use.
Q2) Develop a database application to demonstrate storing and retrieving of
BLOB and CLOB objects
Q3) Design and develop a suitable Student Database application. One of the
attributes to be maintained is the attendance of a student in each subject for
which he/she has enrolled. Using TRIGGERS, write active rules to do the
following: Whenever the attendance is updated, check if the attendance is less
than 85%; if so, notify the Head of the Department concerned. Whenever, the
marks in an Internal Assessment Test are entered, check if the marks are less
than 40%; if so, notify the Head of the Department concerned.
CREATE TRIGGER CheckAssessmentMarks
AFTER INSERT OR UPDATE ON AssessmentMarks
FOR EACH ROW
BEGIN
DECLARE marks_threshold FLOAT;
DECLARE notification_message VARCHAR(255); -- Variable to store the notification
message
SET marks_threshold = 40; -- 40% marks threshold
IF [Link] < marks_threshold THEN
SET notification_message = CONCAT('Low marks for Student ID: ', [Link], ' in
Subject ID: ', [Link]);
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = notification_message;
END IF;
END //
ER Digagram of a Private Nursing Home
ER Diagram of Library Management System
Q6) Implement the following based on above mention business process:
a) Apply all possible integrity constraints into the database to maintain the
integrity and consistency of data.
Primary Key Constraint:
Ensure that the b_id is unique for each book in the Books table.
ALTER TABLE Books
ADD CONSTRAINT PK_Books PRIMARY KEY (b_id);
Unique Constraint:
Ensure that no two authors have the same name within the Authors table.
ALTER TABLE Authors
ADD CONSTRAINT UQ_author_name UNIQUE (b_author);
Foreign Key Constraint:
Establish a relationship between the Authors and Books tables to ensure that an author in the
Authors table must exist in the authors_nested_table of the Books table.
ALTER TABLE Books
ADD CONSTRAINT FK_b_author FOREIGN KEY (b_author_table.author_id)
REFERENCES Authors (author_id);
Not Null Constraint:
Ensure that essential fields like title and author_name cannot be null.
ALTER TABLE Books
MODIFY title VARCHAR(255) NOT NULL;
ALTER TABLE Authors
MODIFY author_name VARCHAR(255) NOT NULL;
b) Perform various types of SQL queries to retrieve data from multiple tables
(Two or Three)
INNER JOIN to Retrieve Books and Their Authors:
SELECT [Link], a.author_name
FROM Books b
INNER JOIN Authors a ON b.authors_nested_table.author_id = a.author_id;
LEFT JOIN to Retrieve All Books and Their Authors:
SELECT [Link], COALESCE(a.author_name, 'No Author') AS author_name
FROM Books b
LEFT JOIN Authors a ON b.authors_nested_table.author_id = a.author_id;
CROSS JOIN to Combine All Authors with All Books:
SELECT [Link], a.author_name
FROM Books b
CROSS JOIN Authors a;
SELF JOIN to Find Co-authored Books:
SELECT [Link], [Link] AS coauthored_with
FROM Books b1
INNER JOIN Books b2 ON [Link] < [Link] -- Ensure no duplica tes
WHERE b1.authors_nested_table.author_id <> b2.authors_nested_table.author_id;
UNION to Combine Results:
SELECT title FROM Books
UNION
SELECT title FROM Magazines;
c) Suggest and create some suitable views based on the database from one or
more Tables.
d) Perform some select command on
view created from one or more
Tables