1 / 17

Normalization

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

omer
Télécharger la présentation

Normalization

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Normalization Courtesy of Dr. John Mote

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

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

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

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

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

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

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

  9. 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?

  10. Functional Dependence SSN Name Pay Date Earnings 123-45-6789 Bob White Nov 1998 $4,800 Composite Primary Key

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

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

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

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

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

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

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

More Related