Part 1 (20 points): Database Design
You are asked to design a database schema for a university registrar's office to handle student and course registration information.
The database keeps track of each student's name, WIN, current address and phone, permanent address and phone, birthdate, sex, level (freshman, sophomore, ......, master, phd), major (a 4-char code), and
home department. For address, some applications need to refer individually to the city, state (2-char abbrv.) and zip code (some use 5-digit, some use 9). Some applications need to refer to students' first and last names separately. WIN is unique to the student.
Each department has a name, a 4-char code, location (main building and room number), office phone, college, and a list of instructors. Department codes are unique within the university.
Each instructor has his/her WIN, first name and last name.
The course catalog contains the list of courses which WMU offers. Each course has a number (e.g. 4430), name, description, credit hours & department. Course numbers are unique within a department, but not unique across the university (so taken together, they are unique).
Each section has a single instructor (no team-taught class), semester, year (4-digit), course number and section number. The section number is unique within the university.
A student enrolls in a course and gets a letter grade (A, BA, B, ..., E, X, I).
Note: If the above requirements leave out any important detail, assume that it is handled the way WMU does things in the real world.
You are asked to:
Draw an E-R diagram of the above information.
Convert the E-R diagram to a relational schema. Express the schema using SQL CREATE TABLE statements, with proper primary keys and foreign keys identified.
Part 2 (10 points): Relational schema normalization
You are given a relation R with 4 attributes ABCD, R(A,B,C,D). The following are five sets of FDs on R:
C→D,C→A,B→C
B→C,D→A
ABC→D,D→A
A→B,BC→D,A→C
AB→C,AB→D,C→A,D→B
For each of the above sets of FDs, assuming that those FDs are the only dependencies that hold for R, and do the following:
Identify the key(s) for R.
Identify the highest normal form that R satisfies (1NF, 2NF, 3NF, or BCNF).
If R is not in BCNF, decompose it into a set of BCNF relations.
Prepare your submission in a PDF file and submit to e-learning.
