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

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)