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:

    Answer the following questions:

    1. What normal form is this relation in? Explain your answer.

    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:

    Answer the following questions:

    1. What normal form is this relation in? Explain your answer.

    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,
                     dormAddr,status,mealPlan,roomCharge,mealPlanCharge)

    Answer the following questions:

    1. Using these assumptions and stating any others you need to make, list all the non-trivial functional dependencies for this relation.

    2. What are the candidate keys for this relation? Identify the primary key. (If there is more than one candidate key, explain why you picked the primary key you did.)

    3. What normal form is this relation in? Explain your answer.

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


Valid HTML 4.01!