Univ DB ERD and DDL

Use MYSQL Workbench to create EER diagram for a database (namely yournetid_univ_db) that stores information of the Students, Professor , Course Enrollment and more and their rela- tionships.

Consider the following information and provide the appropriate DDL statements to create each table. Identify and define Primary keys and Foreign keys in all the table and add necessary con- straints.

STUDENT Table contains List of all the students admitted in the university Columns of Student table are :

• First name and Last Name of Student
• Student Identification Number
• Address of the Student
• Department number of major
• Year admitted in university
• Birthday of Student
• For each record, Fname, Lname, SIN, have to be specified, and each student has to be assigned to a department

PROFESSOR Table contains List of Professor's in the university Columns of PROFESSOR table are :

• Professor Identification Number
• Professor First name and Last Name
• Office Room number
• Start time of office hours
• Duration of office hours
• Department number

DEPARTMENT Table contains list of the departments in university Columns of DEPARTMENT table are :

• Department number
• Department name

COURSES Table contains Provides list of all the courses offered by universities Columns of COURSES table are :

• Course number
• Course name

CLASSES Table contains List of courses offered in a semester Columns of CLASSES table are :

• Class Number
• Section of class which is number
• Semester (Fall, Spring...)
• Year (2018, 2019, ...)
• Professor
• Day (Monday, Wednesday.. etc)
• Duration of the class which is whole number
• Class hours (Starting time of class)

In Enrollment,

• Course number
• Student number
• Semester (Fall Spring...)
• Section (01, 02..)

In Final grade,

• Course Number
• Student Number
• Semester (Fall, Spring..)
• Year (2018, 2019..)
• Grade (A, B, C, D)
• Section