Given the following relational database schema: Student = (SSN, Name, Major) Cou

Question

Given the following relational database schema:

Student = (SSN, Name, Major)

Course = ( CourseNumber, Quarter, CourseTitle, NumberOfUnits, RoomNumber, DayTime), where DayTime is of the form MW 1:0-2:00.

Enrollment = (SSN,CourseNumber, Grade)

Write DDL statements to create the above tables and use appropriate data types for each attribute. The DDL statement must include at least the following constraints:

Every Primary Key;

Every Foreign Key;

For every Foreign Key constraint, specify an appropriate referential integrity constraints as follows:

ON DELETE SET NULL or SET DEFAULT ;

ON UPDATE CASCADE or SET NULL.

0 < NumberOfUnits < 5;

Grade is either A, B, C, D, or F;

The Name and Quarter can not be null;

The Major and Grade may assume the null value.

Explanation / Answer

CREATE TABLE Student(SSN bigint PRIMARY KEY SET DEFAULT (78926458), Name string NOT NULL, Major string);

CREATE TABLE Course(CourseNumber int PRIMARY KEY SET DEFAULT (101), Quarter int NOT NULL, CourseTitle string, NumberOfUnits int CHECK (NumberOfUnits>0 AND NumberOfUnits<5), RoomNumber int, DayTime timestamp);

CREATE TABLE Enrollment(SSN bigint FOREIGN KEY , CourseNumber int FOREIGN KEY,

Grade char CHECK (Grade==''A OR Grade=='B' OR Grade=='C' OR Grade=='D' OR Grade=='E' OR Grade=='F'),

CONSTRAINT course_table FOREIGN KEY (SSN) REFERENCES Course(SSN) ON UPDATE CASCADE {SET NULL} AND ON DELETE {SET NULL | SET DEFAULT},

CONSTRAINT student_table FOREIGN KEY (CourseNumber) REFERENCES Student(CourseNumber) ON UPDATE CASCADE {SET NULL} AND ON DELETE {SET NULL | SET DEFAULT});

All the 3 tables are created according to your needs. If any problem comes then try changing AND to &, OR to | or ||.

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

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