data normalization
Download
Skip this Video
Download Presentation
Data Normalization

Loading in 2 Seconds...

play fullscreen
1 / 16

Data Normalization - PowerPoint PPT Presentation


  • 341 Views
  • Uploaded on

Chapter 7 Logical Database Design Relational Systems (Normalization) . Data Normalization. Primarily a tool to validate and improve a logical design so that it satisfies certain constraints that avoid unnecessary duplication of data

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Data Normalization' - wren


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
data normalization
Chapter 7Logical Database Design Relational Systems (Normalization) Data Normalization
  • Primarily a tool to validate and improve a logical design so that it satisfies certain constraints that avoid unnecessary duplication of data
  • The process of decomposing relations with anomalies to produce smaller, well-structured relations

Database Principles

well structured relations
Well-Structured Relations
  • A relation that contains minimal data redundancy and allows users to insert, delete, and update rows without causing data inconsistencies
  • Goal is to avoid anomalies
    • Insertion Anomaly – adding new rows forces user to create duplicate data
    • Deletion Anomaly – deleting rows may cause a loss of data that would be needed for other future rows
    • Modification Anomaly – changing data in a row forces changes to other rows because of duplication

General rule of thumb: a table should not pertain to more than one entity type

Database Principles

example figure 5 2b
Example – Figure 5.2b

Question – Is this a relation?

Answer – Yes: unique rows and no multivalued attributes

Question – What’s the primary key?

Answer – Composite: Emp_ID, Course_Title

Database Principles

anomalies in this table
Anomalies in this Table
  • Insertion – can’t enter a new employee without having the employee take a class
  • Deletion – if we remove employee 140, we lose information about the existence of a Tax Acc class
  • Modification – giving a salary increase to employee 100 forces us to update multiple records
  • Why do these anomalies exist?
    • Because we’ve combined two themes (entity types) into one relation. This results in duplication, and an unnecessary dependency between the entities

Database Principles

functional dependencies and keys
Functional Dependencies and Keys
  • Functional Dependency: The value of one attribute (the determinant) determines the value of another attribute
  • Candidate Key:
    • A unique identifier. One of the candidate keys will become the primary key
      • E.g. perhaps there is both credit card number and SS# in a table…in this case both are candidate keys
    • Each non-key field is functionally dependent on every candidate key

Database Principles

slide6
5.22 -Steps in normalization

Database Principles

first normal form
First Normal Form
  • No multivalued attributes
  • Every attribute value is atomic
  • Fig. 5-2a is not in 1st Normal Form (multivalued attributes)  it is not a relation
  • Fig. 5-2b is in 1st Normal form
  • All relations are in 1st Normal Form

Database Principles

second normal form
Second Normal Form
  • 1NF plusevery non-key attribute is fully functionally dependent on the ENTIRE primary key
    • Every non-key attribute must be defined by the entire key, not by only part of the key
    • No partial functional dependencies
  • Fig. 5-2b is NOT in 2nd Normal Form (see fig 5-23b)

Database Principles

fig 5 23 b functional dependencies in employee2
EmpID

CourseTitle

Name

DeptName

Salary

DateCompleted

EmpID, CourseTitle  DateCompleted

EmpID  Name, DeptName, Salary

Fig 5.23(b) – Functional Dependencies in EMPLOYEE2

Dependency on entire primary key

Dependency on only part of the key

Therefore, NOT in 2nd Normal Form!!

Database Principles

getting it into 2 nd normal form
EmpID

Name

DeptName

Salary

EmpID

CourseTitle

DateCompleted

Getting it into 2nd Normal Form
  • See p193 – decomposed into two separate relations

Both are full functional dependencies

Database Principles

third normal form
Third Normal Form
  • 2NF PLUS no transitive dependencies (one attribute functionally determines a second, which functionally determines a third)
  • Fig. 5-24, 5-25

Database Principles

slide12
Figure 5-24 -- Relation with transitive dependency

(a) SALES relation with simple data

Database Principles

slide13
BUT

CustID  Salesperson  Region

Transitive dependency

(not 3rd NF)

Figure 5-24(b) Relation with transitive dependency

CustID  Name

CustID  Salesperson

CustID  Region

All this is OK

(2nd NF)

Database Principles

slide14
Figure 5.25 -- Removing a transitive dependency

(a) Decomposing the SALES relation

Database Principles

slide15
Figure 5.25(b) Relations in 3NF

Salesperson  Region

CustID  Name

CustID  Salesperson

Now, there are no transitive dependencies…

Both relations are in 3rd NF

Database Principles

other normal forms from appendix b
Other Normal Forms (from Appendix B)
  • Boyce-Codd NF
    • All determinants are candidate keys…there is no determinant that is not a unique identifier
  • 4th NF
    • No multivalued dependencies
  • 5th NF
    • No “lossless joins”
  • Domain-key NF
    • The “ultimate” NF…perfect elimination of all possible anomalies

Database Principles

ad