CPSC 343 Database Theory and Practice Fall 2017

# Homework 2: ER→Relational Mapping and Normalization

## due Mon Sep 25 in class

1. Map the ER schema below into a relational schema. (Click for a larger version.) For any case where you have a choice of ways to map a particular construct, choose the mapping that you think is best for this situation and explain your decision. Also be sure to specify all primary key and referential integrity (foreign key) constraints.

2. Map the ER schema below into a relational schema. (Click for a larger version.) For any case where you have a choice of ways to map a particular construct, choose the mapping that you think is best for this situation and explain your decision. Also be sure to specify all primary key and referential integrity (foreign key) constraints.

3. Consider the following relation about students and courses:

`      COURSE(dept,number,studentID,firstname,lastname,instructor,time,room,grade)`

The functional dependencies:

• dept,number → instructor [a course has one instructor]
• dept,number → time,room [a course is taught at a particular time and in a particular place]
• studentID → firstname,lastname [studentID identifies a student]
• dept,number,studentID → grade [a student has a single grade in a course]

2. Apply normalization until you cannot decompose the relations further. State the reasons behind each decomposition.

4. Consider the following relation about students:

`      STUDENT(studentID,firstname,lastname,major,credits,status)`

The functional dependencies:

• studentID → firstname,lastname,major,credits [studentID identifies a student]
• credits → status [a student's status is based on how many credits they have]

2. Apply normalization until you cannot decompose the relations further. State the reasons behind each decomposition.

5. Consider the following relation containing information about student room and board arrangements:

```      ROOM_BOARD(firstname,lastname,studentID,homeAddr,homePhone,dorm,room,
• Each student is assigned to one dorm room. Several students may be assigned to the same room.
• Names of students are not unique.
• The college has several dorms. dorm contains the name of the dorm and room contains the number of the particular room assigned to the student. Dorm names are unique.
• status tells the student's status: first year, sophomore, junior, senior.
• mealPlan tells how many meals per week the student has chosen as part of his or her meal plan. Each meal plan has a single mealPlanCharge associated with it.
• The roomCharge is different for different dorms, but all students in the same dorm pay the same amount.