a. (7 pts) Formulate a SQL query: In the course table, remove all courses that d

Question

a. (7 pts) Formulate a SQL query: In the course table, remove all courses that do not have any corresponding course section. No screenshot is needed.

b. (6 pts) After the statement in a is executed, what will happen to rows in other tables (e.g., prereq) that has a foreign key to course.course_id and depend on the rows that were removed in a? why (be very specific)?

file link : https://mega.nz/#!t3hhkKSY

The enclosed.sql file defines the following university database where primary key for each table is underlined department.dept-name is primary key (i.., unique) course.title is not unique instructor.name is not globally unique but is unique within the department student.name is not globally unique but is unique within the department student takes ID ID name dept name tot cred section course department advisor title dept name credits s id i id semester building budget time slot ilding room 10 time slot id end time instructor classroom ID ame dept name teaches ID sec id ear capacity salary

Explanation / Answer

a) DELETE * FROM COURSE WHERE COURSE.COURSEID NOT IN (SELECT SECTION.COURSEID FROM SECTION)

The query SELECT SECTION.COURSEID FROM SECTION returns all COURSEID from SECTION table.

The query DELETE * FROM COURSE WHERE COURSE.COURSEID NOT IN () deletes tuples from table COURSE whose COURSEID not in SELECT SECTION.COURSEID FROM SECTION .

b)

Referential Integrity Constraints: states that a value that appears in one table for a given a set of attributes also appears for a certain set of attributes in another relation.

Foreign keys are specified as part of CREATE Table statement.

There are 3 options when stating foreign key.

1) NULL: When corresponding primary key is deleted in the referenced relation respective entries in foreign key of referencing relation is set as null. Here course_id and prereq_id is primary key in that table, this violates primary key constraint..

3)CASCADING DELETE: When corresponding primary key is deleted in the referenced relation respective entries in foreign key of referencing relation is also deleted. Here course_id and prereq_id is refering course id in COURSE table. When course_id is deleted in COURSE table is deleted course_id and prereq_id are deleted form PREREQ table.

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.