Session Three
Normalization

Topics

Relational Database Overview

 
Conceptual / ER Model Relational Database
Entity Table or Relation
Attribute Column
Unique Identifier Primary Key
Keys Notation:
 
CUSTOMER(CODE, F-NAME, L-NAME, ADDRESS)

CUSTOMER is the name of the Relation or Table
CODE, F-NAME, L-NAME and ADDRESS are columns in the table
CODE is the primary key - it is underlined

Functional Dependency

Given the attributes X and Y:
X ---> Y, or X functionally determines Y if and only if when you know value of X, then you also will know the value of Y.

EmpNo ---> EmpName

EmpAge --/--> EmpName
  • If you know the value of the EmpAge then you DO NOT  know the value of the EmpName for a specific occurrence.
  • EmpName is NOT FUNCTIONALLY DEPENDENT upon EmpAge

    • EmpAge DOES NOT DETERMINES EmpName
    Examples:

    1. ATHLETE(NAME, SSNO, PAY, SEX, LOCKERNBR)
    Each athlete has a unique name and has his or her own locker.  Pay amounts will vary based upon performance and agent.
     

    NAME    SSNO
    NAME   LOCKERNBR
    SSNO   PAY
    PAY   SEX
    SEX   PAY
    SEX   NAME
    LOCKERNBR   NAME
    PAY   SSNO
    LOCKERNBR   SEX
    SEX   LOCKERNBR
    NAME   SEX

    2.  SCHOOL(STUDENTNBR, CLASSID, GRADE)
    What attribute(s) functionally determine the grade?
     
     

    3. MOVIES(MOV-NAME, STAR, THEATRE-ADD, SEAT-PRICE, MOV-RATING, TOWN)

    This relation contains information about movies being shown at various theatres.  No movie was shown more than once at any theatre.  Seat prices vary at any given theatre depending upon the movie being shown.  Each Theatres charges its price for a movie.  Be sure that the functional dependency is based upon all the attributes if there is more than one attribute.
     

    MOV-NAME   STAR
    THEATRE-ADD   SEAT-PRICE
    MOV-NAME and THEATRE-ADD   SEAT PRICE
    TOWN   THEATRE-ADD
    THEATRE-ADD   TOWN
    MOV-RATING   STAR
    MOV-NAME and THEATRE-ADD   STAR

    4. INVOICE(INVOICE#,PART#, QTY, PART-COLOR, PART-NAME)
     
     
     
     

     

    Normalization

    A step by step process that remove anomalies and situations that can cause anomalies from a set of relations.
    Do Not Repeat;
    "The Key, the whole key 
    and nothing but the key, 
    so help me Codd."    		 No Repeating Groups. 
    No Partial Key Dependencies,
    No Transitive Dependencies.

    When you  have NO REPEATING GROUPS you are in 1nf (first normal form)

    In the relation notation, when you see { } inside of ( ) then you have a repeating group.

    STATE (CODE, CAPITOL, BIRD, SLOGAN, {CITY, POPULATION})

    AZ Phoenix Cactus Wren Grand Canyon State Phoenix 1,220,000
    AZ Phoenix Cactus Wren Grand Canyon State Tucson 490,000
    AZ Phoenix Cactus Wren Grand Canyon State Mesa 380,000
    AZ Phoenix Cactus Wren Grand Canyon State Scottsdale 200,000
    WA Olympia Willow Goldfinch Evergreen State Spokane 180,000
    WA Olympia Willow Goldfinch Evergreen State Seattle 520,000
    WA Olympia Willow Goldfinch Evergreen State Tacoma 176,000
    TX Austin Mockingbird  Lone Star Austin 475,000
    TX Austin Mockingbird  Lone Star Houston 1,640,000
    TX Austin Mockingbird  Lone Star Dallas 1,006,000

    When you  have NO PARTIAL KEY DEPENDENCIES you are in 2nf.

    If you have an atomic key then no problem.  If you have a composite key then you should not be able to remove any of the attributes from the composite key and still determine a non-key item.
    STATE (CODE, CAPITOL, BIRD, SLOGAN)

    When you  have NO TRANSITIVE DEPENDENCIES you are in 3nf.

    When a non-key item is functionally dependent upon another non-key item.
    STATE (CODE, CAPITOL)  If the state birds are unique then they could be used to uniquely identify other non-key attributes.