TEXTBOOK: Databases Illuminated, Catherine M. Ricardo, 3rd edition, Jones and Ba

Question

TEXTBOOK: Databases Illuminated, Catherine M. Ricardo, 3rd edition, Jones and Bartlett.

Consider the following relation that stores information about students living in dormitories at a college:

c. 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.

d. What tables would you actually implement? Explain any denormalization, omissions, or additions of attributes.

College (lastName. stuld. homeAdd. home Phone dorm Room. roommateName dorm Add. status. me Plan. room Charge. mea l Plan Charge) Assume: ach student is assigned to one dormitory r and may have several r oom oom mates Names of students are not unique. The college has several dorms. dormRoom contains a code for the dorm and the number of the particular r assigned to the student. For example, A221 means Adams Hall, room 221. Dorm names are unique. 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. status tells the student's status: Freshman, Sophomore, Junior, Senior, or Graduate Student. meal Plan 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.

Explanation / Answer

C.) yes, the resulting set of relation falls in BCNF and 3NF.

                   Since the attribute stuId-> lastName violates the second normal form, it automatically falls in BCNF and in 3NF

All the components relation in bcnf ensures dependancy preserving decomposition resuls.

The functional dependency was not preserved with Roommate attribute.

D.) De-normalization:

       Looking dormroom it contains two components dorm code and room number

       The functional dependancy here is dormCode -> dormAdd

     So we are breaking the dorm room into {dorm code, room number} and after de-normalization it will look like.

Initially

Stu(st.id, l_name, H_add, H_phone, D_code, D_roomnumber, M_plan, status).

Further stuid

{stid -> L_name, H_add, H_phone, D_code, D_roomnumber, M_plan, status}

On Dorm

Dorm(D_code, D_name, D_add, Roomcharge)

{D_code, -> D_name, D_add, Roomcharge: D_name->D_code, D_add -> D_code}

On Meal

Meal(MealPlan, MealPlancharge)

{Mealplan-> Mealplancharge}

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.