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;
Copy

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 );

Copy

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)
Copy

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"));

Copy

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 ;

Copy

Please prove the three rules union, decomposition, pseudotransitivity (Lecture 9 Page 14) using Armstrong’s Axioms (Page 11). (12 pt)

• For union:
  If then

2. According to augmentation
6. According to transitivity

• For augmentation:
  if , then and

2. according to reflexivity
3. according to reflexivity
4. according to transitivity

• For pseudotransitivity
  if and then

2. according to augmentation
3. according to transitivity

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: violates BCNF
Decomposition R into

and satisfy BCNF

c. The decomposition in b) is dependency preserving.

it 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.