Write a SQL script file to create tables, insert data and issue queries. 1. (5 p

Question

Write a SQL script file to create tables, insert data and issue queries. 1. (5 pts) Create tables CoursesDescription and CoursesSpring2018. All the data records should conform to the following constraints. In table CoursesDescription, (a) Every course has either one or three credit hours. (b) If a course has a prerequisite, the prerequisite must be an existing course. (c) When deleting a record for course c1 that is a prerequisite of another course c2, the prerequisite field of c2 should be set to null. In table CoursesSpring2018 (a) Every course offered in Spring 2018 has a unique course reference number (crn) (b) By default, the seatCapacity of a course is 24. (c) For every course, the value of seatTaken (number of seats that have been taken) should never exceed the value of seatCapactiy (d) Every course (its cno) should be an existing course in table CourseDescription. (e) When deleting a record for course c from table CoursesDescription, all sections of course cl should also be removed from table CoursesSpring2018 All constraints must be declared before any actual data is inserted into tables. ourse Credits uisite no 110 307 185 190 191 195 185 190 190 CouresesSpring2018 Cno 190 191 110 185 190 seatCapacit 24 24 24 24 24 Tn seatTaken 12222 13333 13 10 15555 2

Explanation / Answer

CREATE TABLE CourseDescription (

Cno int NOT NULL PRIMARY KEY,

Credits int CHECK (Credits=1 OR Credits=3),

Prerequisite int FOREIGN KEY REFERENCES CourseDescription(Cno)

);

CREATE TABLE CourseSpring2018 (

Crn int NOT NULL PRIMARY KEY,

Cno int FOREIGN KEY REFERENCES CourseDescription(Cno),

seatCapacity int DEFAULT 24,

seatTaken int CHECK (seatTaken<=seatCapacity)

);

For deletion you have to use cascade delete otherwise it won't let you delete because of foreign key constraint.

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

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