Consider the following relation that stores information about students living in dormitories at a college: College (lastName, stuId, homeAdd, homePhone, dormRoom, roommateName, dormAdd, status, mealPlan, roomCharge, mealPlanCharge)

Assume:

1) Each student is assigned to one dormitory room and may have several roommates.

2) Names of students are not unique.

3) The college has several dorms. dormRoom contains a code for

the dorm and the number of the particular room assigned to the student. For example, A221 means Adams Hall, room 221. Dorm names are unique.

3) The dormAdd is the address of the dorm building. Each building has its own unique address. For example, Adams Hall may be 123 Main Street, Anytown, NY 10001.

4) status tells the student’s status: Freshman, Sophomore, Junior, Senior, or Graduate Student.

5) 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.

6) The roomCharge is different for different dorms, but all students in the same dorm pay the same amount.

Answer the following questions:

a. Using these assumptions and stating any others you need to make, list all the nontrivial functional dependencies for this relation.

b. What are the candidate keys for this relation? Identify the primary key.

c. Is the relation in third normal form? If not, find a 3NF lossless join decomposition of Books that preserves dependencies.

d. Is the relation or resulting set of relations in Boyce-Codd Normal Form? If not, find a lossless join decomposition that is in BCNF. Identify any functional dependencies that are not preserved.

almiradanil    1   09.11.2020 15:36    2