170 likes | 552 Views
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
E N D
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 Address 123-45-6789 987-65-4321 111-22-3333 Bob White Jaye Byrd C. Gull 708 Pine, San Antonio, Texas, 78201 5127 Maple, Austin, Texas, 78712 1300 Teak, San Antonio, Texas, 78201
Compound Fields SSN Name Children 123-45-6789 987-65-4321 111-22-3333 Bob White Jaye Byrd C. Gull Mary, James, Kenneth Thomas, Leslie
Repeating Fields SSN Name Child-1 Child-2 Child-3 Child-4 123-45-6789 987-65-4321 111-22-3333 Bob White Jaye Byrd C. Gull Mary Thomas --------- James Leslie --------- Kenneth --------- --------- --------- --------- ---------
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?
Functional Dependence SSN Name Pay Date Earnings 123-45-6789 Bob White Nov 1998 $4,800 Composite Primary Key
Functional Dependence SSN Name Pay Date Earnings 123-45-6789 987-65-4321 123-45-6789 111-22-3333 123-45-6789 987-65-4321 111-22-3333 Bob White Jaye Byrd Bob White C. Gull Bob White Jaye Byrd C. Gull Nov 1998 Dec 1998 Dec 1998 Dec 1998 Jan 1999 Nov 1998 Jan 1999 $4,800 $2,750 $5,125 $4,000 $5,125 $3,300 $4,000
Functional Dependence SSN Name SSN Pay Date Earnings 123-45-6789 987-65-4321 111-22-3333 Bob White Jaye Byrd C. Gull 123-45-6789 987-65-4321 123-45-6789 111-22-3333 123-45-6789 987-65-4321 111-22-3333 Nov 1998 Dec 1998 Dec 1998 Dec 1998 Jan 1999 Nov 1998 Jan 1999 $4,800 $2,750 $5,125 $4,000 $5,125 $3,300 $4,000
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 City State Zip 123-45-6789 987-65-4321 111-22-3333 Bob White Jaye Byrd C. Gull San Antonio Austin San Antonio Texas Texas Texas 78201 78712 78201
Field Independence SSN Name Zip 123-45-6789 987-65-4321 111-22-3333 Bob White Jaye Byrd C. Gull 78201 78712 78201 City State Zip San Antonio Austin Texas Texas 78201 78712
Normalization “Rules” 1 Field Uniqueness 2 Primary Keys 3 Functional Dependence 4 Field Independence