HOMEWORK DESCRIPTION

To implement a database, an ER model for database (last page) has been provided to you along with some additional information (below).

Your task is to submit a SQL script for implementing the database that complies with the requirements.

ER Model Explanation:

- Entity and attributes are to be named as shown.

- Data types are clearly shown

For each entity:

- Attributes that are bolded are required to have values (not null)

- Primary Key is labeled as PK. For composite primary keys, multiple attributes are labeled as PK

- Foreign Key(s), if present, are labeled as FK1, FK2, etc. In case of composite foreign key, multiple attributes are designated identically.

o NOTE: foreign key attribute(s) may not be named identically to the referenced primary key column(s). In such case, the name used is reasonably indicative.

For example: In [Faculty Appointment] table, attribute [Faculty G-Number] refers to attribute [G-Number] in [Faculty] table

Additional Requirements:

1. TABLE: STUDENT

a. G-Number always begins with G and is followed by mix of up to 8 alphabets and digits.

b. Last Name, and First Name can only include alphabets spaces and hyphens

c. Zip can only be 5 digits

d. Major Code default value is UNDC, otherwise it is either 3 or 4 alphabets

e. No two students can have the same SSN

2. TABLE: REGISTRATION

a. regDateTime: if data is not provided, it is default set to datetime at the time of INSERT

b. Status can be only one of: E, W, A

c. Grade:

i. Only for Status E can there be grade; otherwise it should be NULL

ii. Grade, if present, can be only one of: A+, A, A-, B+, B, B-, C+, C, D, F 3.

TABLE: COURSE

a. Course Name can only include alphabets, spaces, digits and hyphens

b. Credit Hours allowed values are 0, 1, 1.5, 2, 3, 6 4.

TABLE: [COURSE OFFERING]

a. SemYear can be any year between 1990s to 2999 (Hint: SemYear is implemented as char(4), not a number)

b. Section number is always 3 digits

c. CRN is a surrogate key introduced to replace previous primary key which was composite of (Subject Code, Course Number, Semester, SemYear, Section)

5. TABLE: [COURSE TIMINGS]

6. TABLE: CLASSROOMS

7. TABLE: BUILDINGS

8. TABLE: DEPARTMENT

9. TABLE [FACULTY APPOINTMENT]

10. TABLE: FACULTY

a. G-Number always begins with G and is followed by mix of up to 8 alphabets and digits.

b. Last Name, and First Name can only include alphabets spaces and hyphens

x 2:170146 x G ile numbers Bangla Son X Van HW vad + Asignats - MS-410-001 AX Vsio-Hwyd C G G45 7271A-2. BCPAP 4, TCPAP. - O X DO Other bemes 715 0 1921. Free Delire "culu.. wprs. . .. Login FASC. 42*270. F # 1999 >> Vlaic-11w1.usd Faculty Faculty Appointment PK,FK1 DEPT CODE char(4) od PK,FK2 Faculty G-Number chario) PK G.Number char(9) Department PK DEPT CODE char(4) Dept Name varchar(30) Date Since datetime Last Name First Name Position varchar(20) varchar(20) varchar(20) Course Mering CRN char(5) PK Course char(2) Subiect Code Course Number char(4) char(3) char(4) varchar(3) O Registration PK.FK1 Student G-Number PK.FK2 CRN Semester Sem Year Section Max Students FK1 Faculty O-Number F K2 Subject Code Course Number Course Name Description Credit Hours DEPT CODE char(9) char(4) char(3) varchar(30) varchar(250) decimal(2,1) char(4) char19) O char(5) regDate Time Classrooms char2) char(1) Status HPK,FK1 Room Number Building Code char(4) chan(3) int Seating Capacity Computer Projector Laptop Hookup Student PK Schedule Code char(10) PK G Number char(9) char(1) Last Name First Nana Street Num Street Name Day of Week 1 Begin Time 1 End Time 1 Day of Week 2 Begin Time 2 End Time 2 varchar(20) varchar(20) varchar(8) varchar(50) varchar(20) char(2) char') char(9) varchar(4) char(1) Buildings Building Code PK char(3) datetime State char(3) SSN Major Code FK1 Building Code FK2 CRN Building Name varchar(30) Street Number varchar(4) Street Name varchar(50) Mail Stop Num varchar(3) City varchar(20) char(2) Zip chars) - AA 2030-09-13 x 2:170146 x G ile numbers Bangla Son X Van HW vad + Asignats - MS-410-001 AX Vsio-Hwyd C G G45 7271A-2. BCPAP 4, TCPAP. - O X DO Other bemes 715 0 1921. Free Delire "culu.. wprs. . .. Login FASC. 42*270. F # 1999 >> Vlaic-11w1.usd Faculty Faculty Appointment PK,FK1 DEPT CODE char(4) od PK,FK2 Faculty G-Number chario) PK G.Number char(9) Department PK DEPT CODE char(4) Dept Name varchar(30) Date Since datetime Last Name First Name Position varchar(20) varchar(20) varchar(20) Course Mering CRN char(5) PK Course char(2) Subiect Code Course Number char(4) char(3) char(4) varchar(3) O Registration PK.FK1 Student G-Number PK.FK2 CRN Semester Sem Year Section Max Students FK1 Faculty O-Number F K2 Subject Code Course Number Course Name Description Credit Hours DEPT CODE char(9) char(4) char(3) varchar(30) varchar(250) decimal(2,1) char(4) char19) O char(5) regDate Time Classrooms char2) char(1) Status HPK,FK1 Room Number Building Code char(4) chan(3) int Seating Capacity Computer Projector Laptop Hookup Student PK Schedule Code char(10) PK G Number char(9) char(1) Last Name First Nana Street Num Street Name Day of Week 1 Begin Time 1 End Time 1 Day of Week 2 Begin Time 2 End Time 2 varchar(20) varchar(20) varchar(8) varchar(50) varchar(20) char(2) char') char(9) varchar(4) char(1) Buildings Building Code PK char(3) datetime State char(3) SSN Major Code FK1 Building Code FK2 CRN Building Name varchar(30) Street Number varchar(4) Street Name varchar(50) Mail Stop Num varchar(3) City varchar(20) char(2) Zip chars) - AA 2030-09-13