Question

QUESTION 1

1. How do we know the following relation with the following dependencies is BCNF?

   course ( course_id , title , dept_name , credits )

   Functional Dependencies

   course_id → title , dept_name , credits

   building , room_number → capacity

   course_id , sec_id , semester , year → building , room_number , time_slot_id

   		course_id, dept_name is a superkey
   		course_id is a candidate key
   		dept_name is a superkey
   		course_id is a superkey

QUESTION 2

1. In the following decomposition of R, what can we say about the decomposition if at least one of the following dependencies is in F + :

   • R 1 ∩ R 2 → R 1

   • R 1 ∩ R 2 → R 2

   		it is 3NF
   		it is lossy
   		it is lossless
   		it is BCNF

QUESTION 3

1. Which of the following are hallmarks of good database design?

   		avoids necessity of nulls
   		decomposition is lossless
   		all of the above
   		avoids repetition of data

Answer 1

1.BCNF Decomposition:

• course_id→ title, dept_name, credits holds.
• We replace class by:
  • course(course_id, title, dept_name, credits)
  • class-1 (course_id, sec_id, semester, year, building, room_number, capacity, time_slot_id)

building, room_number→capacity holds on class-1

− but {building, room_number} is not a superkey for class-1.

− We replace class-1 by:

• classroom (building, room_number, capacity)
• section (course_id, sec_id, semester, year, building, room_number, time_slot_id)

classroom and section are in BCNF.

2.Decomposition {R1, R2} is a lossless-join decomposition if R1 ∩ R2 → R1 or R1 ∩ R2 → R2.

Let R1 = (A, B, C), R2 = (A, D, E), and R1 ∩ R2 = A. Since A is a candidate key.Therefore R1 ∩ R2 → R1.

3.All of the above