Purpose of Normalization - Database Design - Lecture Slides, Slides of Database Management Systems (DBMS)

Download Purpose of Normalization - Database Design - Lecture Slides and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Boyce-Codd NF

Purpose of Normalization

• To reduce the chances for anomalies to

occur in a database.

• normalization prevents the possible

corruption of databases stemming from

what are called "insertion anomalies,"

"deletion anomalies," and "update

anomalies."

Deletion Anomaly

• A failure to remove an existing database

entry when it is time to remove that

entry.

• In a properly normalized database, an

old, to-be-gotten-rid-of entry needs to be

deleted from only one place in the

database

Update anomaly

• An update of a database involves

modifications that may be additions, deletions,

or both. Thus "update anomalies" can be

either of the kinds of anomalies discussed

above.

2nd Normal Form

• A table is in 2NF if it is in 1NF and if all non-key

attributes are fully dependent on each

candidate key.

• A partial dependency occurs when a non-key

attribute is dependent on only a part of the

(composite) key

1NF but not 2NF

• Supplier (supplier#, status, city, part#,

quantity)

• (supplier#, part#) -> quantity
• supplier# -> status
• supplier# -> city
• city -> status
• => status and city are dependent on just part

of the key, namely supplier#.

3rd Normal Form (3NF)

• A table is in 3NF if it is in 2NF and if it has no

transitive dependencies.

• Transitive dependency is a functional

dependency between non-key attributes.

2NF but not 3NF

• Supplier (supplier#, status, city)
  • supplier# -> status
  • supplier# -> city
  • city -> status

=> Lacks mutual independence among non-key

attributes.

Boyce-Codd NF

• A table is in BCNF if it is in 3NF and if every

determinant is a candidate key.

• the definition of 3NF does not deal with a

relation that:

• has multiple candidate keys, where
• Those candidate keys are composite, and
• the candidate keys overlap (i.e., have at least one

common attribute)

3NF but not boyce-codd NF

• SUPPLIER_PART (supplier#, supplier_name, part#,

quantity)

• Two candidate keys:
  • (supplier#, part#) and (supplier_name, part#)
• (supplier#, part#) -> quantity
• (supplier#, part#) -> supplier_name
• (supplier#, part#) -> quantity
• (supplier#, part#) -> supplier#
• supplier_name -> supplier#
• supplier# -> supplier_name

Example (cont’d)

• {title, year, starName} as candidate key
• title, year  length, filmType, studioName
• The above FD (Functional Dependency)

violates the BCNF condition because title and

year do not determine the sixth attribute,

starName

Example (cont’d)

• We solve this BCNF violation by decomposing

relation Movies into

1. The schema with all the attributes of the FD

{title, year, length, filmType,

studioName}

2. The schema with all attributes of Movies

except the three that appear on the right of the

FD

{title, year, starName}