Lecture 07 - FD & Normalization

Lecture 07 - FD & Normalization - Functional Dependencies...

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

View Full Document Right Arrow Icon
Functional Dependencies and Normalization for Relational Databases
Background image of page 1

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

View Full DocumentRight Arrow Icon
Database Systems Chapter Outline Informal Design Guidelines for Relational Databases Semantics of the Relation Attributes Redundant Information in Tuples and Update Anomalies Null Values in Tuples Spurious Tuples Functional Dependencies (FDs) Definition of FD Inference Rules for FDs Normal Forms Based on Primary Keys Normalization of Relations Definitions of Keys and Attributes Participating in Keys 1NF, 2NF, 3NF BCNF (Boyce-Codd Normal Form)
Background image of page 2
Slide 10- 3 1 Informal Design Guidelines for Relational Databases What is relational database design? The grouping of attributes to form "good" relation schemas Two levels of relation schemas The logical "user view" level The storage "base relation" level Design is concerned mainly with base relations What are the criteria for "good" base relations?
Background image of page 3

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

View Full DocumentRight Arrow Icon
Slide 10- 4 1.1 Semantics of the Relation Attributes GUIDELINE 1: Informally, each tuple in a relation should represent one entity or relationship instance. (Applies to individual relations and their attributes). Attributes of different entities (EMPLOYEEs, DEPARTMENTs, PROJECTs) should not be mixed in the same relation Only foreign keys should be used to refer to other entities Entity and relationship attributes should be kept apart as much as possible. Bottom Line: Design a schema that can be explained easily relation by relation. The semantics of attributes should be easy to interpret.
Background image of page 4
Slide 10- 5 A simplified COMPANY relational database schema
Background image of page 5

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

View Full DocumentRight Arrow Icon
Slide 10- 6 1.2 Redundant Information in Tuples and Update Anomalies When information is stored redundantly Wastes storage Causes problems with update anomalies Insertion Anomalies Deletion Anomalies Update Anomalies
Background image of page 6
Slide 10- 7 Two relation schemas suffering from update anomalies
Background image of page 7

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

View Full DocumentRight Arrow Icon
Slide 10- 8 Base Relations EMP_DEPT and EMP_PROJ formed after a Natural Join : with redundant information
Background image of page 8
Slide 10- 9 EXAMPLE OF UPDATE ANOMALY Consider the relation: EMP_PROJ(Emp#, Proj#, Ename, Pname, No_hours) Update Anomaly: Changing the name of project number P1 from Billing ” to “ Customer-Accounting ” may cause this update to be made for all 100 employees working on project P1.
Background image of page 9

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

View Full DocumentRight Arrow Icon
Slide 10- 10 EXAMPLE OF AN INSERT ANOMALY Consider the relation: EMP_PROJ(Emp#, Proj#, Ename, Pname, No_hours) Insert Anomaly: Cannot insert a project unless an employee is assigned to it. Conversely Cannot insert an employee unless a he/she is assigned to a project.
Background image of page 10
EXAMPLE OF AN DELETE ANOMALY Consider the relation: EMP_PROJ(Emp#, Proj#, Ename, Pname, No_hours) Delete Anomaly: When a project is deleted, it will result in deleting all the employees who work on that project. Alternately, if an employee is the sole employee
Background image of page 11

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

View Full DocumentRight Arrow Icon
Image of page 12
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 03/19/2012 for the course CMP 202 taught by Professor Hanem during the Spring '12 term at Cairo University.

Page1 / 41

Lecture 07 - FD & Normalization - Functional Dependencies...

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

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