date: 2023-12-27
title: RDBMS-As-3
status: DONE
author:
- AllenYGY
tags:
- DBM
- Assignment
created: 2023-12-27T17:15
updated: 2024-06-11T01:15
publish: True
RDBMS-Assignment-3
a) Find the students who have more enrollments than other students. Display your answer by sID and sname.
SELECT sID, sname
FROM student JOIN enroll USING (sID)
GROUP BY cID
ORDER BY COUNT(DISTINCT cID) DESC
LIMIT 1;
b) Find the students who have enrolled all courses. Display your answer by sID and sname.
SELECT sID, sname
FROM student
WHERE sID IN ( SELECT sID
FROM enroll
GROUP BY enroll.sID
HAVING COUNT(DISTINCT enroll.cID)
= (SELECT COUNT(DISTINCT cID)
FROM course );
c) Find the students who have taken all courses instructed by Goliath (instructor’s name and assuming that there is no other instructor who is called Goliath). Display your answer by sID and sname.
SELECT s.sID, s.sname
FROM student s
JOIN course c ON 1=1
LEFT JOIN enroll e ON s.sID = e.sID AND c.cID = e.cID
WHERE NOT EXISTS (
SELECT *
FROM teach t
JOIN instructor i ON t.iID = i.iID
WHERE i.iname = 'Goliath' AND t.cID = c.cID
AND NOT EXISTS (
SELECT *
FROM enroll e2
WHERE e2.sID = s.sID AND e2.cID = t.cID
)
)
GROUP BY s.sID, s.sname
HAVING COUNT(DISTINCT c.cID) = COUNT(DISTINCT e.cID)
d) Implement a constraint to guarantee that the position of an instructor is one of “lecturer”, “assistant professor”, “associated professor”, and “professor”.
ALTER TABLE instructor
ADD CONSTRAINT position_domain
CHECK (position IN
("lecturer", "assistant professor", "associated professor","professor"));
e) Implement a constraint to guarantee that “no student is enrolled to a course which is not taught by any instructor”.
ALTER TABLE student
ADD CONSTRAINT teach_course
FOREIGN KEY (cID) REFERENCES course(cID)
ADD CONSTRIANT teach_instructor
FOREIGN KEY (iID) REFERENCES course(iID)
ON DELETE CASCADE
ON UPDATE CASCADE;
DELIMITER |
CREATE TRIGGER guarantee_student
AFTER INSERT ON student
FOR EACH ROW
BEGIN
IF new.id NOT IN (
SELECT iID FROM instructor)
THEN
DELETE FROM student WHERE student.sID = new.sID;
END IF;
END;|
DELIMITER ;
Please prove the three rules union, decomposition, pseudotransitivity (Lecture 9 Page 14) using Armstrong’s Axioms (Page 11). (12 pt)
a. Find all condidate keys
L: A
R: BCH
LR:DEFG
N:
There is only one candidate key AF
b. Decompose the schema in BCNF
Remove redundant functional dependencies and extraneous Attributes
Iteration 1:
Decomposition R into
c. The decomposition in b) is dependency preserving.
d. What is the canonical cover of the functional dependencies?
e. There are no redundant dependencies or extraneous attributes in any one of the dependencies.
It has been satisfy BCNF, so it's also 3NF.