Normalization Courtesy of Dr John Mote Normalization Rules

  • Slides: 17
Download presentation
Normalization Courtesy of Dr. John Mote

Normalization Courtesy of Dr. John Mote

Normalization “Rules” 1 2 3 4 Field Uniqueness Primary Keys Functional Dependence Field Independence

Normalization “Rules” 1 2 3 4 Field Uniqueness Primary Keys Functional Dependence Field Independence

Normalization “Rules” n Field Uniqueness n Each field in a table should represent a

Normalization “Rules” n Field Uniqueness n Each field in a table should represent a unique type of information n Eliminate compound fields Eliminate repeating fields n

Compound Fields SSN 123 -45 -6789 987 -65 -4321 111 -22 -3333 Name Address

Compound Fields SSN 123 -45 -6789 987 -65 -4321 111 -22 -3333 Name Address Bob White 708 Pine, San Antonio, Texas, 78201 Jaye Byrd 5127 Maple, Austin, Texas, 78712 C. Gull 1300 Teak, San Antonio, Texas, 78201

Compound Fields SSN Name 123 -45 -6789 987 -65 -4321 111 -22 -3333 Bob

Compound Fields SSN Name 123 -45 -6789 987 -65 -4321 111 -22 -3333 Bob White Jaye Byrd C. Gull Children Mary, James, Kenneth Thomas, Leslie

Repeating Fields SSN Name 123 -45 -6789 987 -65 -4321 111 -22 -3333 Bob

Repeating Fields SSN Name 123 -45 -6789 987 -65 -4321 111 -22 -3333 Bob White Jaye Byrd C. Gull Child-1 Child-2 Child-3 Mary James Thomas Leslie --------- Child-4 Kenneth -----------------

Normalization “Rules” n n Field Uniqueness Primary Keys n Each table must have a

Normalization “Rules” n n Field Uniqueness Primary Keys n Each table must have a primary key n “Natural” keys System-generated keys n

Normalization “Rules” n n n Field Uniqueness Primary Keys Functional Dependence n For each

Normalization “Rules” n n n Field Uniqueness Primary Keys Functional Dependence n 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” n n n Field Uniqueness Primary Keys Functional Dependence n For each

Normalization “Rules” n n n Field Uniqueness Primary Keys Functional Dependence n 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 n What?

Functional Dependence SSN Name Pay Date Earnings 123 -45 -6789 Bob White Nov 1998

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

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 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

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 Jan 1999 Nov 1998 Jan 1999 $4, 800 $2, 750 $5, 125 $4, 000 $5, 125 $3, 300 $4, 000

Normalization “Rules” n n n Field Uniqueness Primary Keys Functional Dependence n For each

Normalization “Rules” n n n Field Uniqueness Primary Keys Functional Dependence n 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” n n Field Uniqueness Primary Keys Functional Dependence Field Independence n You

Normalization “Rules” n n Field Uniqueness Primary Keys Functional Dependence Field Independence n 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

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 78201 78712 78201

Field Independence SSN Name Zip 123 -45 -6789 987 -65 -4321 111 -22 -3333

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 78201 78712

Normalization “Rules” 1 2 3 4 Field Uniqueness Primary Keys Functional Dependence Field Independence

Normalization “Rules” 1 2 3 4 Field Uniqueness Primary Keys Functional Dependence Field Independence