Chapter 19- NormalForms [Compatibility Mode]

Chapter 19- NormalForms [Compatibility Mode] - Schema...

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

View Full Document Right Arrow Icon
Database Management Systems, 2 nd Edition. R. Ramakrishnan and J. Gehrke 1 Schema Design and Normal Forms Database Management Systems, 2 nd Edition. R. Ramakrishnan and J. Gehrke 2 Entity-Relationship Diagram Sailor Sid Name Level Rating Wage Hours Database Management Systems, 2 nd Edition. R. Ramakrishnan and J. Gehrke 3 S N L R W H 123-22-3666 Attishoo 48 8 10 40 231-31-5368 Smiley 22 8 10 30 131-24-3650 Smethurst 35 5 7 30 434-26-3751 Guldu 35 5 7 32 612-67-4134 Madayan 35 8 10 40 Data Redundancy • Application constraint: all sailors with the same rating have the same wage (R W) • Problems due to data redundancy?
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 Management Systems, 2 nd Edition. R. Ramakrishnan and J. Gehrke 4 Problems due to Data Redundancy Problems due to R W : Update anomaly : Can we change W in just the first tuple of SNLRWH? Insertion anomaly : What if we want to insert an employee and don’t know the hourly wage for his rating? Deletion anomaly : If we delete all employees with rating 5, we lose the information about the wage for rating 5! Solution? Database Management Systems, 2 nd Edition. R. Ramakrishnan and J. Gehrke 5 Relation Decomposition S N L R W H 123-22-3666 Attishoo 48 8 10 40 231-31-5368 Smiley 22 8 10 30 131-24-3650 Smethurst 35 5 7 30 434-26-3751 Guldu 35 5 7 32 612-67-4134 Madayan 35 8 10 40 S N L R H 123-22-3666 Attishoo 48 8 40 231-31-5368 Smiley 22 8 30 131-24-3650 Smethurst 35 5 30 434-26-3751 Guldu 35 5 32 612-67-4134 Madayan 35 8 40 R W 81 0 57 Wages Problem? Database Management Systems, 2 nd Edition. R. Ramakrishnan and J. Gehrke 6 Modifying ER Diagram Sailor Sid Name Level Rating Wage Hours Sailor Sid Name Level Rating Hours Wages Sailor-Rating
Background image of page 2
Database Management Systems, 2 nd Edition. R. Ramakrishnan and J. Gehrke 7 Normal Forms First question is to ask whether any schema refinement is needed If a relation is in a normal form ( BCNF, 3NF etc.), certain anomalies are avoided/minimized If not, decompose relation to normal form Role of FDs in detecting redundancy: Consider a relation R with 3 attributes, ABC. No FDs hold: There is no redundancy here. Given A B: Several tuples could have the same A value, and if so, they’ll all have the same B value! Database Management Systems, 2 nd Edition. R. Ramakrishnan and J. Gehrke 8 Outline Functional Dependencies Decompositions Normal Forms Database Management Systems, 2 nd Edition. R. Ramakrishnan and J. Gehrke 9 Functional Dependencies (FDs) A functional dependency X Y holds over relation R if, for every allowable instance r of R: t1 r, t2 r, ( t1 ) = ( t2 ) implies ( t1 ) = ( t2 ) i.e., given two tuples in r , if the X values agree, then the Y values must also agree. (X and Y are sets of attributes.) An FD is a statement about all allowable relations. Must be identified based on semantics of application. Given some allowable instance r1 of R, we can check if it violates some FD f , but we cannot tell if f holds over R!
Background image of page 3

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

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

This note was uploaded on 02/12/2012 for the course CS 4320 taught by Professor Koch during the Fall '07 term at Cornell University (Engineering School).

Page1 / 10

Chapter 19- NormalForms [Compatibility Mode] - Schema...

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

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