Normalization - PowerPoint PPT Presentation

About This Presentation
Title:

Normalization

Description:

If an attribute depends on only part of a multivalued key, remove it to a separate table. ... Remove repeating groups. 1st Normal Form ... Remove Repeating Groups ... – PowerPoint PPT presentation

Number of Views:69
Avg rating:3.0/5.0
Slides: 34
Provided by: ABSchwa
Learn more at: https://www.ou.edu
Category:

less

Transcript and Presenter's Notes

Title: Normalization


1
Normalization
  • A technique for identifying table structures that
    have potential maintenance problems

2
Normalization
  • Normalization is a set of formal conditions that
    assure that a database is maintainable.
  • The results of a well executed normalization
    process are the same as those of a well planned
    E-R model

3
PROCESS OF DATA NORMALIZATION
  • ELIMINATE REPEATING GROUPS
  • Make a separate table for each set of related
    attributes and give each table a primary key.
  • ELIMINATE REDUNDANT DATA
  • If an attribute depends on only part of a
    multivalued key, remove it to a separate table.
  • ELIMINATE COLUMNS NOT DEPENDENT ON KEY
  • If attributes do not contribute to a description
    of the key, remove them to a separate table.
  • Database Programming and Design

4
PROCESS OF DATA NORMALIZATION
  • ISOLATE INDEPENDENT MULTIPLE RELATIONSHIPS
  • No table may contain two or more 1n or nm
    relationships that are not directly related.
  • ISOLATE SEMANTICALLY RELATED MULTIPLE
    RELATIONSHIPS
  • There may be practical constraints on
    information that justify separating logically
    related many-to-many relationships.
  • Database Programming and Design

5
Anomalies
  • A table anomaly is a structure for which a normal
    database operation cannot be executed without
    information loss or full search of the data table
  • Insertion Anomaly
  • Deletion Anomaly
  • Update or Modification Anomaly

6
Normal Forms
  • Relational theory defines a number of structure
    conditions called Normal Forms that assure that
    certain data anomalies do not occur in a database.

7
Normal Forms
  • 1NF Keys No repeating groups
  • 2NF No partial dependencies
  • 3NF No transitive dependencies
  • BCNF Determinants are candidate keys
  • 4NF No multivalued dependencies

8
Premier Products Order Form(Company Order
History)
Order 12003 Date
Oct 1, 1997 Oklahoma Retail Company 1111
Asp Norman Description Code Qty Price Amount 1
. Footballs 21 6 25.00 150 2. Sweat
Shirts 44 20 15.00 300 3. Shorts 37 10 12.00 12
0 Total 570
9
0nf Remove titles and derived quantities
Order 12003 Date
Oct 1, 1997 Oklahoma Retail Company 1111
Asp Norman Description Code Qty Price Amount 1
. Footballs 21 6 25.00 150 2. Sweat
Shirts 44 20 15.00 300 3. Shorts 37 10 12.00 12
0 Total 570
10
0 Normal Form
  • Remove titles and derived quantities
  • Schema notation

HISTORY(CustName, CustAddr, CustCity OrderNum,
OrderDate ProdDescr, ProdCode, QtyOrdered,
OrderPrice
11
1st Normal Form
  • Add keys
  • Remove repeating groups

12
1st Normal Form
  • Add Keys for embedded entities
  • Remove Repeating Groups

HISTORY(CustID, CustName, CustAddr, CustCity
OrderNum, OrderDate ProdDescr, ProdCode,
QtyOrdered, OrderPrice
13
1st Normal Form
  • Add Keys for embedded entities
  • Remove Repeating Groups
  • Create a table for each embedded entity, from the
    outside for nested groups
  • Insert foreign keys and junction tables

CUSTOMER(CustID, CustName, CustAddr,
CustCity) ORDER(OrderNum, CustID, OrderDate
ProdDescr, ProdCode, QtyOrdered, OrderPrice)
14
1st Normal Form
  • CUSTOMER(CustID, CustName, CustAddr, CustCity)
  • ORDER(OrderNum, CustID, OrderDate)
  • PRODUCT(ProdDescr, ProdCode,)
  • ORDER-PRODUCT(OrderNum, ProdCode, QtyOrdered,
    OrderPrice)

15
1st Normal Form
CUSTOMER
ORDER
PRODUCT
16
1NF(Keys, No Repeating Groups)
Table contains multi-valued attributes.
TABLE
ATTRIBUTES
TABLE
??
ATTR-TABLE
17
2nd Normal Form
  • No partial dependencies
  • (an attribute has a partial dependency if it
    depends on part of a concatenated key)

18
2nd Normal Form
  • ROSTER(StuID, ZAPNum, StudentName, CourseTitle,
    CourseGrade)
  • Remove partial dependencies
  • STUDENT(StuID, StudentName)
  • SECTION(ZAPNum, CourseTitle)
  • STUDENT-SECTION(StuID, ZAPNum, CourseGrade)

19
2nd Normal Form
ROSTER
STUDENT
SECTION
STUDENT-SECTION
20
2NFNo partial dependencies
Table has data from several connected tables.
TABLE
TABLE
??
??
21
3rd Normal Form
  • No transitive dependencies
  • (a transitive dependency is an attribute that
    depends on other non-key attributes)

22
3rd Normal Form
  • Note a transitive dependency arises when
    attributes from a second entity appear in a given
    table.
  • SECTION(ZAPNum, RoomNum, Day, Time, CourseTitle,
    HoursCredit)

23
3rd Normal Form
  • SECTION(ZAPNum, RoomNum, Day, Time, CourseID
    ,CourseTitle, HoursCredit)
  • SECTION(ZAPNum, RoomNum, Day, Time, CourseID)
  • COURSE(CourseID, CourseTitle, HoursCredit)

24
3NFNo transitive dependencies
Table contains data from an embedded entity with
non-key attributes.
SUB-TABLE
TABLE
TABLE
??
SUB-TABLE
BCNF is the same, but the embedded table may
involve key attributes.
25
Boyce Codd Normal Form
  • Every determinant is a candidate key

26
BCNF
  • BCNF dependenceies are like 3nf dependencies but
    they involve some key attributes
  • Note BCNF often arises when a 1m relationship
    is modeled as a mn relationship

27
BCNF
  • SALESMAN-CUST(SalesID, CustID, Commission)
  • SALESMAN(SalesID, Commission)
  • CUSTOMER(CustID, SalesID)

28
4th Normal Form
  • No multi-valued dependencies

29
4th Normal Form
  • Note 4th Normal Form violations occur when a
    triple (or higher) concatenated key represents a
    pair of double keys

30
4th Normal Form
31
4th Normal Form
Multuvalued dependencies
32
4th Normal Form
  • INSTR-BOOK-COURSE(InstrID, Book, CourseID)
  • COURSE-BOOK(CourseID, Book)
  • COURSE-INSTR(CourseID, InstrID)

33
4NF(No multivalued dependencies)
Independent repeating groups have been treated as
a complex relationship.
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
Write a Comment
User Comments (0)
About PowerShow.com