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.
298
426
Ответы на вопрос:
Популярно: Другие предметы
-
Dimka62115.12.2022 13:53
-
Анна284918.04.2022 23:19
-
99999767565613.10.2021 12:57
-
OlyaMonokova22.06.2023 17:31
-
yuliyamusatova130.01.2021 16:30
-
cheri22823.10.2021 11:15
-
Тимуркупцов12327.01.2023 17:17
-
АНТО1414.09.2020 10:17
-
никита344703.12.2020 07:14
-
Kata8i7786612.07.2022 01:45