Database In-class Assignment Normalization Name, For the following table, list t

Question

Database In-class Assignment Normalization Name, For the following table, list the functional dependencies and create a database from the table that is in BCNF and 4NF. Rating Showtime Theater Movie The SpongeBob Movie The SpongeBob Movie Focus Focus Focus Kingsman: The Secret Service 4:00 7:00 4:00 3,4 7:00 3,4,5 10:00 PG PG 1,2 R7:006 Kingsman: The Secret Service R 10:00 6 3:00 7:00 7:30 7:00 9:00 7:00 PG McFarland, USA McFarland, USA McFarland, USA Hot Tub Time Machine 2 Hot Tub Time Machine 2 Jupiter Ascending 7,8 PG PG-13 12

Explanation / Answer

Boyce Codd normal form (BCNF)

It is an advance version of 3NF that’s why it is also referred as 3.5NF. BCNF is stricter than 3NF. A table complies with BCNF if it is in 3NF and for every functional dependency X->Y, X should be the super key of the table.

Example: Suppose there is a company wherein employees work in more than one department. They store the data like this:

Functional dependencies in the table above:
emp_id -> emp_nationality
emp_dept -> {dept_type, dept_no_of_emp}

Candidate key: {emp_id, emp_dept}

The table is not in BCNF as neither emp_id nor emp_dept alone are keys.

To make the table comply with BCNF we can break the table in three tables like this:
emp_nationality table:

emp_dept table:

emp_dept_mapping table:

Functional dependencies:
emp_id -> emp_nationality
emp_dept -> {dept_type, dept_no_of_emp}

Candidate keys:
For first table: emp_id
For second table: emp_dept
For third table: {emp_id, emp_dept}

emp_id emp_nationality emp_dept dept_type dept_no_of_emp 1001 Austrian Production and planning D001 200 1001 Austrian stores D001 250 1002 American design and technical support D134 100 1002 American Purchasing department D134 600

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

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