Normalization to BCNF - Example

Normalization to BCNF - Example - or non-key dependency •...

Info iconThis preview shows pages 1–7. Sign up to view the full content.

View Full Document Right Arrow Icon
Video Library Example Consider the case of a simple video library. Each video has a title, director, and serial number. Customers have a name, address, and membership number. Assume only one copy of each video exists in the library. We are given:
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Video Library Example video(title ,director,serial) customer(name,addr,memberno ) hire(memberno,serial,date ) title->director,serial serial->title serial->director name,addr -> memberno memberno -> name,addr serial,date -> memberno
Background image of page 2
What NF is this? No repeating groups therefore at least 1NF 2NF – A Composite key exists: hire(memberno,serial,date ) Can memberno be found with just serial or date? NO, therefore the relations are already in 2NF. 3NF?
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Test for 3NF video(title ,director,serial) title->director,serial serial->director Director can be derived using serial, and serial and director are both non keys, so therefore this is a transitive
Background image of page 4
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Background image of page 6
Background image of page 7
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: or non-key dependency. • Rewrite video… Rewrite for 3NF • video(title ,director,serial) – title->director,serial – serial->director • Becomes: • video(title ,serial) • serial(serial ,director) Check BCNF • Is every determinant a candidate key? • video(title ,serial) - Determinants are: – title->director,serial Candidate key – serial->title Candidate key – video in BCNF • serial(serial ,director) Determinants are: – serial->director Candidate key – serial in BCNF • customer(name,addr,memberno ) Determinants are: – name,addr -> memberno Candidate key – memberno -> name,addr Candidate key – customer in BCNF • hire(memberno,serial,date ) Determinants are: – serial,date -> memberno Candidate key – hire in BCNF • Therefore the relations are also now in...
View Full Document

{[ snackBarMessage ]}

Page1 / 7

Normalization to BCNF - Example - or non-key dependency •...

This preview shows document pages 1 - 7. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online