Il-Han Yoo - PowerPoint PPT Presentation

1 / 26
About This Presentation
Title:

Il-Han Yoo

Description:

... applies to tables that have composite primary keys, where two or more ... In order to find if a table is in 2NF, ask whether any of the non-key attributes ... – PowerPoint PPT presentation

Number of Views:55
Avg rating:3.0/5.0
Slides: 27
Provided by: ilha
Category:
Tags: han | yoo

less

Transcript and Presenter's Notes

Title: Il-Han Yoo


1
Database Normalization
Il-Han Yoo CS 157A Professor Sin-Min Lee
2
Database Normalization
  • Database normalization relates to the level of
    redundancy in a relational databases structure.
  • The key idea is to reduce the chance of having
    multiple different version of the same data.
  • Well-normalized databases have a schema that
    reflects the true dependencies between tracked
    quantities.
  • Any increase in normalization generally involves
    splitting existing tables into multiple ones,
    which must be re-joined each time a query is
    issued.

3
Normal Forms
  • Edgar F. Codd originally established three
    normal forms 1NF, 2NF and 3NF.
  • 3NF is widely considered to be sufficient.
  • Normalizing beyond 3NF can be tricky with current
    SQL technology as of 2005
  • Full normalization is considered a good exercise
    to help discover all potential internal database
    consistency problems.

4
First Normal Form ( 1NF )
What is your favorite color? What food will you not eat?
TABLE 1 Person / Favorite Color Bob / blue Jane / green TABLE 2 Person / Foods Not Eaten Bob / okra Bob / brussel sprouts Jane / peas
5
Second normal Form ( 2NF )
  • 2NF prescribes full functional dependency on the
    primary key.
  • It most commonly applies to tables that have
    composite primary keys, where two or more
    attributes comprise the primary key.
  • It requires that there are no non-trivial
    functional dependencies of a non-key attribute on
    a part (subset) of a candidate key. A table is
    said to be in the 2NF if and only if it is in the
    1NF and every non-key attribute is irreducibly
    dependent on the primary key

6
2NF Example
PART_NUMBER (PRIMARY KEY) SUPPLIER_NAME (PRIMARY KEY) PRICE SUPPLIER_ADDRESS
  • The PART_NUMBER and SUPPLIER_NAME form the
    composite primary key.
  • SUPPLIER_ADDRESS is only dependent on the
    SUPPLIER_NAME, and therefore this table breaks
    2NF.

7
2NF Example (Cont)
SUPPLIER_NAME (PRIMARY KEY) SUPPLIER_ADDRESS
  • In order to find if a table is in 2NF, ask
    whether any of the non-key attributes of the
    table could be derived from a subset of the
    composite key, rather than the whole composite
    key.
  • If the answer is yes, it's not in 2NF.
  • This is solved sometimes by using a correlation
    file, such as the supplier table above.

8
Third normal form
  • 3NF requires that there are no non-trivial
    functional dependencies of non-key attributes on
    something other than a superset of a candidate
    key.
  • A table is in 3NF if none of the non-primary key
    attributes is a fact about any other non-primary
    key attribute.
  • In summary, all non-key attributes are mutually
    independent.

9
3NF Example
PART_NUMBER (PRIMARY KEY) MANUFACTURER_NAME MANUFACTURER_ADDRESS
MANUFACTURER_NAME (PRIMARY KEY) MANUFACTURER_ADDRESS
PART_NUMBER (PRIMARY KEY) MANUFACTURER_NAME
10
Example
  • Problems ?
  • Not very efficient with storage
  • This design does not protect data integrity
  • This table does not scale well

11
First Normal Form
12
(No Transcript)
13
  • Defining Relationships
  • One to One
  • One to Many
  • Many to Many

14
Second Normal Form
15
(No Transcript)
16
(No Transcript)
17
Third Normal Form
  • This new table violate Second Normal Form as the
    street and city will be verically redundant.
  • Province will need to be in its own table which
    the city table will refer to as a foreign key.

18
Boyce-Codd normal form (BCNF)
  • BCNF requires that there are no non-trivial
    functional dependencies of attributes on
    something other than a superset of a candidate
    key (called a superkey).
  • All attributes are dependent on a key, a whole
    key and nothing but a key (excluding trivial
    dependencies, like A-gtA).

19
  • A table is said to be in the BCNF if and only if
    it is in the 3NF and every non-trivial,
    left-irreducible functional dependency has a
    candidate key as its determinant.
  • In more informal terms, a table is in BCNF if it
    is in 3NF and the only determinants are the
    candidate keys.

20
Fourth normal form (4NF)
  • 4NF requires that there are no non-trivial
    multi-valued dependencies of attribute sets on
    something else than a superset of a candidate key
    (called a superkey).
  • A table is said to be in 4NF if and only if it
    is in the BCNF and multi-valued dependencies are
    functional dependencies.

21
4NF Example
EMPLOYEE_ID QUALIFICATION_ID TRAINING_COURSE_ID
employee_qualification table EMPLOYEE_ID QUALIFICATION_ID employee_training_course table EMPLOYEE_ID TRAINING_COURSE_ID
22
4NF Expample (cont)
EMPLOYEE_ID DEGREE_ID UNIVERSITY_ID
  • This would require no changes to fit the fourth
    normal form requirements.

23
Fifth normal form (5NF and also PJ/NF)
  • 5NF requires that there are no non-trivial join
    dependencies that not follow from the key
    constraints.
  • A table is said to be in the 5NF if and only if
    it is in 4NF and every join dependency in it is
    implied by the candidate keys.

24
Domain/key normal form(DKNF)
  • DKNF requires that each key uniquely identifies
    each row in a table.
  • A domain is the set of permissible values for an
    attribute.
  • By enforcing key and domain restrictions, the
    database is assured of being freed from
    modification anomalies.

25
  • While sometimes called the 6NF, the DKNF should
    not be considered together with the seven other
    normal forms (16 and Boyce-Codd), because
    contrary to them it is not always achievable
    furthermore, tables in the real 6NF are not
    always in the DKNF.

26
Sixth normal form(6NF)
  • This normal form was, as of 2005, only recently
    conjectured the sixth normal form (6NF) was only
    defined when extending the relational model to
    take into account the temporal dimension (ie.
    time).
  • Unfortunately, most current SQL technologies as
    of 2005 do not take into account this work, and
    most temporal extensions to SQL are not
    relational.
Write a Comment
User Comments (0)
About PowerShow.com