normalization courtesy of dr. john mote. normalization “rules” 1field uniqueness 2primary keys...
TRANSCRIPT
Normalization
Courtesy of Dr. John Mote
Normalization “Rules”
1 Field Uniqueness
2 Primary Keys
3 Functional Dependence
4 Field Independence
Normalization “Rules”
Field Uniqueness Each field in a table should represent a unique
type of information
Eliminate compound fields Eliminate repeating fields
Compound Fields
SSN Name
123-45-6789987-65-4321111-22-3333
Bob WhiteJaye ByrdC. Gull
Address
708 Pine, San Antonio, Texas, 782015127 Maple, Austin, Texas, 787121300 Teak, San Antonio, Texas, 78201
Compound Fields
SSN Name
123-45-6789987-65-4321111-22-3333
Bob WhiteJaye ByrdC. Gull
Children
Mary, James, KennethThomas, Leslie
Repeating Fields
SSN Name
123-45-6789987-65-4321111-22-3333
Bob WhiteJaye ByrdC. Gull
Child-1
MaryThomas---------
Child-2
JamesLeslie---------
Child-3
Kenneth------------------
Child-4
---------------------------
Normalization “Rules”
Field Uniqueness Primary Keys
Each table must have a primary key
“Natural” keys System-generated keys
Normalization “Rules”
Field Uniqueness Primary Keys Functional Dependence
For each unique primary key value, the values in the data columns must be relevant to, and must completely describe, the subject of the table
Normalization “Rules”
Field Uniqueness Primary Keys Functional Dependence
For each unique primary key value, the values in the data columns must be relevant to, and must completely describe, the subject of the table
What?
Composite Primary Key
Functional Dependence
SSN Name Pay Date Earnings
123-45-6789 Bob White Nov 1998 $4,800
Functional Dependence
SSN Name Pay Date Earnings
123-45-6789987-65-4321123-45-6789111-22-3333123-45-6789987-65-4321111-22-3333
Bob WhiteJaye ByrdBob WhiteC. GullBob WhiteJaye ByrdC. Gull
Nov 1998Dec 1998Dec 1998Dec 1998Jan 1999Nov 1998Jan 1999
$4,800$2,750$5,125$4,000$5,125$3,300$4,000
Functional Dependence
SSN Pay Date Earnings
123-45-6789987-65-4321123-45-6789111-22-3333123-45-6789987-65-4321111-22-3333
Nov 1998Dec 1998Dec 1998Dec 1998Jan 1999Nov 1998Jan 1999
$4,800$2,750$5,125$4,000$5,125$3,300$4,000
SSN Name
123-45-6789987-65-4321111-22-3333
Bob WhiteJaye ByrdC. Gull
Normalization “Rules”
Field Uniqueness Primary Keys Functional Dependence
For each unique primary key value, the values in the data columns must be relevant to, and must completely describe, the subject of the table
Normalization “Rules”
Field Uniqueness Primary Keys Functional Dependence Field Independence
You must be able to make a change to the data in any field (other than the primary key) without affecting any other field
Field Independence
SSN Name
123-45-6789987-65-4321111-22-3333
Bob WhiteJaye ByrdC. Gull
City State
San AntonioAustinSan Antonio
TexasTexasTexas
Zip
782017871278201
Field Independence
SSN Name
123-45-6789987-65-4321111-22-3333
Bob WhiteJaye ByrdC. Gull
Zip
782017871278201
City State
San AntonioAustin
TexasTexas
Zip
7820178712
Normalization “Rules”
1 Field Uniqueness
2 Primary Keys
3 Functional Dependence
4 Field Independence