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

Mote Recanto das Letras Poesia On line MoteMote Recanto das Letras Poesia On line Mote
Courtesy Laura Garchar Courtesy Ball State University CourtesyCourtesy Laura Garchar Courtesy Ball State University Courtesy
Normalization Example Database Tables and Normalization Normalization ProcessNormalization Example Database Tables and Normalization Normalization Process
Database Normalization What is Normalization n Normalization allowsDatabase Normalization What is Normalization n Normalization allows
Normalization CSC 240 Blum 1 Normalization Normalization isNormalization CSC 240 Blum 1 Normalization Normalization is
DATABASE NORMALIZATION DATABASE NORMALIZATION Database Normalization is aDATABASE NORMALIZATION DATABASE NORMALIZATION Database Normalization is a
DATABASE NORMALIZATION What is Normalization NORMALIZATION is aDATABASE NORMALIZATION What is Normalization NORMALIZATION is a
NORMALIZATION Normalization l l Normalization is process ofNORMALIZATION Normalization l l Normalization is process of
Normalization Lecture 9 Normalization Normalization 6 n FirstNormalization Lecture 9 Normalization Normalization 6 n First
Normalization CSC 240 Blum 1 Normalization Normalization isNormalization CSC 240 Blum 1 Normalization Normalization is
Functional Dependencies and Normalization Normalization n n NormalizationFunctional Dependencies and Normalization Normalization n n Normalization
DATABASE NORMALIZATION Fahmida Afrin What is Normalization NORMALIZATIONDATABASE NORMALIZATION Fahmida Afrin What is Normalization NORMALIZATION
DATABASE NORMALIZATION What is Normalization Normalization allows usDATABASE NORMALIZATION What is Normalization Normalization allows us
DATABASE NORMALIZATION What is Normalization Normalization allows usDATABASE NORMALIZATION What is Normalization Normalization allows us
Primo Normalization Rules 101 Ray Henry Chair NormalizationPrimo Normalization Rules 101 Ray Henry Chair Normalization
Map Courtesy Bill Cross Courtesy of Dick LilienthalMap Courtesy Bill Cross Courtesy of Dick Lilienthal