Now, add the required foreign key constraints for each table. Do not add any records yet. Spool your statements and results to the CH4LAB1B.LST file and print it.

Question

What will be an ideal response?

Answer 1

ALTER TABLE student
ADD CONSTRAINT student_facultyid_fk FOREIGN KEY (FacultyId)
REFERENCES faculty (FacultyId);

ALTER TABLE student
ADD CONSTRAINT student_majorid_fk FOREIGN KEY (MajorId)
REFERENCES major (MajorId);

ALTER TABLE student
ADD CONSTRAINT student_startterm_fk FOREIGN KEY (StartTerm)
REFERENCES term (TermId);

ALTER TABLE faculty
ADD CONSTRAINT faculty_roomid_fk FOREIGN KEY (RoomId)
REFERENCES location (RoomId);

ALTER TABLE faculty
ADD CONSTRAINT faculty_deptid_fk FOREIGN KEY (DeptId)
REFERENCES department (DeptId);

ALTER TABLE crssection
ADD CONSTRAINT crssection_courseid_fk FOREIGN KEY (CourseId)
REFERENCES course (CourseId);

ALTER TABLE crssection
ADD CONSTRAINT crssection_termid_fk FOREIGN KEY (TermId)
REFERENCES term (TermId);

ALTER TABLE crssection
ADD CONSTRAINT crssection_facultyid_fk FOREIGN KEY (FacultyId)
REFERENCES faculty (FacultyId);

ALTER TABLE crssection
ADD CONSTRAINT crssection_roomid_fk FOREIGN KEY (RoomId)
REFERENCES location (RoomId);

ALTER TABLE registration
ADD CONSTRAINT registration_studentid_fk FOREIGN KEY (StudentId)
REFERENCES student (StudentId);

ALTER TABLE registration
ADD CONSTRAINT registration_csid_fk FOREIGN KEY (CsId)
REFERENCES crssection (CsId);

ALTER TABLE location
ADD CONSTRAINT location_roomtype_fk FOREIGN KEY (RoomType)
REFERENCES ROOM (RoomType);