Kg4sCktx

1USE COLLEGE;
2DROP TABLE IF EXISTS ADDRESS;
3CREATE TABLE ADDRESS(
4    AddressId   BIGINT UNSIGNED,
5    StreetName  VARCHAR(255) NOT NULL,
6    StreetNum   INT,
7    Zip         VARCHAR(5)  NOT NULL,
8    City        VARCHAR(255) NOT NULL,
9    State       VARCHAR(2) NOT NULL,
10    ApartmentNum VARCHAR(3),
11   CONSTRAINT ADDRESS_PK PRIMARY KEY (AddressId),
12    CONSTRAINT ADDRESS_SK
13        UNIQUE (StreetName, StreetNum, Zip, City, State, ApartmentNum),
14    CONSTRAINT ADDRESS_STREETNAME_LEN
15        CHECK ( LENGTH(StreetName) > 1 ),
16    CONSTRAINT ADDRESS_ZIP_LEN CHECK ( LENGTH(Zip) = 5 ),
17    CONSTRAINT ADDRESS_STATE_LEN CHECK ( LENGTH(State) = 2 ),
18    CONSTRAINT ADDRESS_APARTMENT_NUM
19        CHECK ( LENGTH(ApartmentNum) = 3 )
20);
21
22DROP TABLE IF EXISTS PERSON;
23CREATE TABLE PERSON(
24    PersonId    VARCHAR(40) NOT NULL,
25    Email       VARCHAR(320) NOT NULL,
26    Fname       VARCHAR(55) NOT NULL,
27    Lname       VARCHAR(55) NOT NULL,
28    Birthdate   DATE        NOT NULL,
29    AddressId   BIGINT,
30    CONSTRAINT PERSON_PK PRIMARY KEY (PersonId),
31    CONSTRAINT PERSON_SK UNIQUE (Email),
32    CONSTRAINT PERSON_EMAIL_LEN_MIN CHECK ( LENGTH(Email) > 6 ),
33    CONSTRAINT PERSON_ADDRESS_FK
34        FOREIGN KEY (AddressId) REFERENCES ADDRESS(AddressId)
35                   ON DELETE SET NULL ON UPDATE CASCADE
36);
37
38DROP TABLE IF EXISTS BUILDING;
39CREATE TABLE IF NOT EXISTS BUILDING(
40  BuildingId    BIGINT  NOT NULL,
41  Prefix        VARCHAR(2) NOT NULL CHECK ( LENGTH(Prefix) > 0 ),
42  Name          VARCHAR(255)  NOT NULL CHECK ( LENGTH(Name) > 2 ),
43  AddressId     BIGINT UNSIGNED NOT NULL,
44  CONSTRAINT BUILDING_PK PRIMARY KEY (BuildingId),
45  CONSTRAINT BUILDING_SK UNIQUE (Name, Prefix),
46  CONSTRAINT BUILDING_ADDRESS_FK
47      FOREIGN KEY (AddressId) REFERENCES ADDRESS(AddressId)
48                                   ON UPDATE CASCADE
49                                   ON DELETE CASCADE
50);
51
52DROP TABLE IF EXISTS ROOM;
53CREATE TABLE IF NOT EXISTS ROOM(
54    RoomId  BIGINT UNSIGNED NOT NULL,
55    BuildingId  BIGINT UNSIGNED NOT NULL,
56    RoomNum     VARCHAR(3)  NOT NULL CHECK ( LENGTH(RoomNum) = 3 ),
57    CONSTRAINT ROOM_PK PRIMARY KEY (RoomId),
58    CONSTRAINT ROOM_BUILDING_FK FOREIGN KEY (BuildingId)
59        REFERENCES BUILDING(BuildingId)
60                               ON DELETE CASCADE ON UPDATE CASCADE,
61    CONSTRAINT ROOM_SK UNIQUE (BuildingId, RoomNum)
62);
63
64DROP TABLE IF EXISTS DEPARTMENT;
65CREATE TABLE IF NOT EXISTS DEPARTMENT(
66    DeptId      BIGINT UNSIGNED NOT NULL,
67    DeptName    VARCHAR(255)    NOT NULL UNIQUE,
68    RoomId      BIGINT UNSIGNED,
69    PhoneNum    VARCHAR(10)     NOT NULL,
70    CONSTRAINT DEPARTMENT_PK PRIMARY KEY (DeptId),
71    CONSTRAINT DEPARTMENT_FK FOREIGN KEY (RoomId)
72        REFERENCES ROOM(RoomId)
73        ON UPDATE CASCADE
74        ON DELETE SET NULL,
75    CONSTRAINT DEPARTMENT_PHONENUM_LEN
76        CHECK ( LENGTH(PhoneNum) = 10 ),
77    CONSTRAINT DEPARTMENT_NAME_LENGTH
78        CHECK ( LENGTH(DeptName) > 2 )
79);
80
81DROP TABLE IF EXISTS STAFF;
82CREATE TABLE IF NOT EXISTS STAFF(
83    StaffId     VARCHAR(32) NOT NULL,
84    PersonId    VARCHAR(40) NOT NULL,
85    DeptId      BIGINT UNSIGNED,
86    OfficeId    BIGINT UNSIGNED,
87    PhoneNum    VARCHAR(10),
88    CONSTRAINT STAFF_PK PRIMARY KEY (StaffId),
89    CONSTRAINT STAFF_SK UNIQUE (PersonId),
90    CONSTRAINT STAFF_PHONENUM_LEN CHECK ( LENGTH(PhoneNum) = 10 ),
91    CONSTRAINT STAFF_PERSON_FK
92        FOREIGN KEY (PersonId) REFERENCES PERSON(PersonId)
93            ON UPDATE CASCADE ON DELETE CASCADE,
94    CONSTRAINT STAFF_OFFICE_FK
95        FOREIGN KEY (OfficeId) REFERENCES ROOM(RoomId)
96            ON UPDATE CASCADE ON DELETE SET NULL,
97    CONSTRAINT STAFF_DEPT_FK
98        FOREIGN KEY (DeptId) REFERENCES DEPARTMENT(DeptId)
99            ON UPDATE CASCADE ON DELETE SET NULL
100);
101
102DROP TABLE IF EXISTS COURSE;
103CREATE TABLE IF NOT EXISTS COURSE(
104    CourseId    BIGINT UNSIGNED NOT NULL,
105    CourseNum   INT UNSIGNED   NOT NULL,
106    CourseName  VARCHAR(255)   NOT NULL UNIQUE,
107    CourseDesc  VARCHAR(10000),
108    DeptId      BIGINT UNSIGNED NOT NULL,
109    Credits     INT UNSIGNED NOT NULL DEFAULT (0),
110    CONSTRAINT COURSE_PK PRIMARY KEY (CourseId),
111    CONSTRAINT COURSE_SK UNIQUE (CourseNum, DeptId),
112    CONSTRAINT COURSE_DEPT_FK
113        FOREIGN KEY (DeptId) REFERENCES DEPARTMENT(DeptId)
114            ON UPDATE CASCADE ON DELETE CASCADE,
115    CONSTRAINT CHECK ( LENGTH(CourseName) > 2 ),
116    CONSTRAINT COURSE_VALID_CREDIT CHECK ( Credits <= 30 )
117);
118
119DROP TABLE IF EXISTS PROGRAM;
120CREATE TABLE IF NOT EXISTS PROGRAM(
121    ProgramId   BIGINT UNSIGNED NOT NULL,
122    ProgramName VARCHAR(255) NOT NULL CHECK ( LENGTH(ProgramName) > 2 ),
123    DeptId      BIGINT UNSIGNED NOT NULL,
124    CONSTRAINT PROGRAM_PK PRIMARY KEY (ProgramId),
125    CONSTRAINT PROGRAM_SK UNIQUE (ProgramName),
126    CONSTRAINT PROGRAM_FK
127        FOREIGN KEY (DeptId) REFERENCES DEPARTMENT(DeptId)
128            ON UPDATE CASCADE ON DELETE CASCADE
129);
130
131DROP TABLE IF EXISTS
132CREATE TABLE IF NOT EXISTS PROGRAM_REQUIRES_COURSE(
133    ProgramId   BIGINT UNSIGNED NOT NULL,
134    CourseId    BIGINT UNSIGNED NOT NULL,
135    Grade       FLOAT,
136    CONSTRAINT COURSE_PROGRAM_REQUIRES_PK PRIMARY KEY (ProgramId, CourseId),
137    CONSTRAINT COURSE_PROGRAM_REQUIRES_PROGRAM_FK
138        FOREIGN KEY (ProgramId) REFERENCES PROGRAM(ProgramId)
139            ON UPDATE CASCADE ON DELETE CASCADE,
140    CONSTRAINT COURSE_PROGRAM_REQUIRES_COURSE_FK
141        FOREIGN KEY (CourseId)  REFERENCES COURSE(CourseId)
142            ON UPDATE CASCADE ON DELETE CASCADE,
143    CONSTRAINT VALID_COURSE_GRADE_RANGE CHECK ( 100 >= Grade AND Grade >= 0)
144);
145
146CREATE TABLE IF NOT EXISTS SECTION(
147    SectionId   BIGINT UNSIGNED NOT NULL,
148    CourseId    BIGINT UNSIGNED NOT NULL,
149    TermNum     INT UNSIGNED NOT NULL CHECK ( TermNum > 0 ),
150    InstructorId VARCHAR(32) NOT NULL,
151    RoomId      BIGINT UNSIGNED,
152    MaxStudents INT UNSIGNED CHECK ( MaxStudents > 0 ),
153    CONSTRAINT SECTION_PK PRIMARY KEY (SectionId),
154    CONSTRAINT SECTION_COURSE_FK
155        FOREIGN KEY (CourseId) REFERENCES COURSE(CourseId)
156            ON UPDATE CASCADE ON DELETE NO ACTION,
157    CONSTRAINT SECTION_INSTRUCTOR_FK
158        FOREIGN KEY (InstructorId) REFERENCES STAFF(StaffId)
159            ON UPDATE CASCADE ON DELETE SET NULL,
160    CONSTRAINT SECTION_ROOM_FK
161        FOREIGN KEY (RoomId) REFERENCES ROOM(RoomId)
162            ON UPDATE CASCADE ON DELETE SET NULL
163);
164
165CREATE TABLE IF NOT EXISTS STUDENT(
166    StudentId   VARCHAR(32) NOT NULL,
167    PersonId    VARCHAR(40) NOT NULL,
168    AdvisorId   VARCHAR(32),
169    CONSTRAINT STUDENT_PK PRIMARY KEY (StudentId),
170    CONSTRAINT STUDENT_SK UNIQUE (PersonId),
171    CONSTRAINT STUDENT_PERSON_FK
172        FOREIGN KEY (PersonId) REFERENCES PERSON(PersonId)
173            ON UPDATE CASCADE ON DELETE CASCADE,
174    CONSTRAINT STUDENT_ADVISOR_FK
175        FOREIGN KEY (AdvisorId) REFERENCES STAFF(StaffId)
176            ON UPDATE CASCADE ON DELETE SET NULL
177);
178
179CREATE TABLE IF NOT EXISTS COURSES_TAKEN(
180    StudentId   VARCHAR(32) NOT NULL,
181    SectionId   BIGINT UNSIGNED NOT NULL,
182    Grade       FLOAT,
183    CONSTRAINT COURSES_TAKEN_PK PRIMARY KEY (StudentId, SectionId),
184    CONSTRAINT COURSES_TAKEN_STUDENT_FK
185        FOREIGN KEY (StudentId) REFERENCES STUDENT(StudentId)
186            ON UPDATE CASCADE ON DELETE NO ACTION,
187    CONSTRAINT COURSES_TAKEN_SECTION_FK
188        FOREIGN KEY (SectionId) REFERENCES SECTION(SectionId)
189            ON UPDATE CASCADE ON DELETE NO ACTION,  # block the section from getting deleted, don't want to lose info
190    CONSTRAINT COURSES_TAKEN_GRADE_VALIDITY CHECK ( 100 >= GRADE AND Grade >= 0 )
191);
192
193CREATE TABLE IF NOT EXISTS PROGRAM_ENROLLMENT(
194    ProgramId   BIGINT UNSIGNED NOT NULL,
195    StudentId   VARCHAR(32) NOT NULL,
196    CONSTRAINT PROGRAM_ENROLLMENT_PK PRIMARY KEY (ProgramId, StudentId),
197    CONSTRAINT ENROLLMENT_PROGRAM_FK
198        FOREIGN KEY (ProgramId) REFERENCES PROGRAM(ProgramId)
199            ON UPDATE CASCADE ON DELETE CASCADE,
200    CONSTRAINT ENROLLMENT_STUDENT_FK
201        FOREIGN KEY (StudentId) REFERENCES STUDENT(StudentId)
202            ON UPDATE CASCADE ON DELETE CASCADE
203);