deURQZ78

1DROP TABLE IF EXISTS Course;
2DROP TABLE IF EXISTS CourseSchedule;
3DROP TABLE IF EXISTS Instructor;
4DROP TABLE IF EXISTS InstructorCourse;
5DROP TABLE IF EXISTS Student;
6DROP TABLE IF EXISTS StudentCourse;
7
8CREATE TABLE Course (
9    CourseName CHAR (8) PRIMARY KEY
10                                             UNIQUE,
11    Credits    INT                  NOT NULL
12);
13
14CREATE TABLE CourseSchedule (
15    CourseName CHAR (8) REFERENCES Course (CourseName) 
16                                             NOT NULL,
17    Room       CHAR (8)       NOT NULL,
18    WeekDay    CHAR (3)    CHECK (WeekDay IN ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun') ) 
19                                             NOT NULL,
20    StartTime  INT               NOT NULL,
21    EndTime    INT               NOT NULL
22                                             CHECK (EndTime > StartTime) 
23);
24CREATE TABLE Instructor (
25    ID      INT                   PRIMARY KEY AUTOINCREMENT,
26    Name    CHAR (20)  NOT NULL,
27    Address CHAR (80) NOT NULL,
28    Salary  INT                NOT NULL
29);
30
31CREATE TABLE InstructorCourse (
32    CourseName   CHAR (8) REFERENCES Course (CourseName) 
33                                               NOT NULL,
34    InstructorID INT              REFERENCES Instructor (ID) 
35                                               NOT NULL
36);
37
38CREATE TABLE Student (
39    ID      INT                   PRIMARY KEY AUTOINCREMENT,
40    Name    CHAR (20)  NOT NULL,
41    Address CHAR (80) NOT NULL,
42    GPA     INT
43);
44
45CREATE TABLE StudentCourse (
46    StudentID  INT               REFERENCES Student (ID) 
47                                             NOT NULL,
48    CourseName CHAR (8) NOT NULL
49                                             REFERENCES Course (CourseName),
50    Score      INT                   NOT NULL
51);
52
53Write the SQL (DML) Statements that will violate the above integrity constraints and the SQL statements that will succeed.
54
55INSERT INTO Course (CourseName, Credits) values (‘COMP7855’, 4);
56INSERT INTO Course (CourseName, Credits) values (‘COMP7855’, 4);
57
58Violates Unique constraint on CourseName
59
60INSERT INTO Course (CourseName, Credits) values (‘COMP7855’, 4);
61INSERT INTO Course (CourseName, Credits) values (‘LIBS7007’, 4);
62
63Will work and not violate the unique constraint.
64INSERT INTO Student (ID, Name, Address, GPA) values (920439, ‘Nick’, ‘Surrey’, 100);
65INSERT INTO Student (ID, Name, Address, GPA) values (920439, ‘Bob’, ‘Delta’, 100);
66
67Violates Primary Key on ID
68
69INSERT INTO Student (ID, Name, Address, GPA) values (920439, ‘Nick’, ‘Surrey’, 100);
70INSERT INTO Student (Name, Address, GPA) values (‘Bob’, ‘Delta’, 100);
71
72Will work as the Non-specified ID will be generated from an AUTOINCREMENT
73
74INSERT INTO CourseSchedule (CourseName, Room, WeekDay, StartTime, EndTime) values (‘ELEX7005’, ‘SW11105’, ‘Fri’, 223737, 230000);
75
76Violates foreign key constraint on CourseName, as ELEX7005 is not in my Course table
77
78INSERT INTO CourseSchedule (CourseName, Room, WeekDay, StartTime, EndTime) values (‘COMP7855’, ‘SW11105’, ‘Fri’, 223737, 230000);
79
80Will work as COMP7855 was previously inserted into the Course table.
81
82INSERT INTO CourseSchedule (CourseName, Room, WeekDay, StartTime, EndTime) values (‘COMP7855’, ‘SW11105’, ‘Fri’, 230000, 223737);
83
84The above, however, will fail as the check constraint that EndTime > StartTime is violated
85
86INSERT INTO StudentCourse (StudentID, CourseName, Score) values (920438, ‘COMP7855’, 100);
87
88Will fail because I mistyped the student ID as 920438 instead of 920439, which does not exist In the student table
89
90INSERT INTO StudentCourse (StudentID, CourseName, Score) values ((SELECT ID FROM Student WHERE Name=’Nick’), ‘COMP7855’, 100);
91
92Would work and is a better way of entering the ID so that the foreign key constraint isn’t violated.