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 DocumentRight 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 DocumentRight 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 DocumentRight 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

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