1 / 15

normalization

Data Design. normalization. - Mr. Ahmad Al-Ghoul. learning Objectives. Explain the concept of table design Explain unnormalized design and the first normal form (1NF). Normalization. Normalization

lilian
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. Data Design normalization - Mr. Ahmad Al-Ghoul

  2. learning Objectives • Explain the concept of table design • Explain unnormalized design and the first normal form (1NF)

  3. Normalization • Normalization • Normalization is a process by which analysts identify and correct inherent problems and complexities in their table designs. • Table design • A table design specifies the fields and identifies the primary key in a particular table or file. • You will use normalization to develop an overall database design that is simple, flexible, and free of data redundancy • In addition to being simpler and more stable, normalized data structure are more easily maintained than other data structure • Involves four stages: unnormalized design, first normal form, second normal form, and third normal form • Most business-related databases must be designed in third normal form

  4. Normalization • Standard Notation Format • Designing tables is easier if you use a standard notation formatto show a table’s structure, fields, and primary key • Standard notation format starts with the name of the table, followed by a parenthetical expression that contains the fieldnames separated by commas, the primary key field is underlined Example: NAME (FIELD 1, FIELD 2, FIELD 3)

  5. Normalization • Repeating Groups and Unnormalized Design • Repeating group: is a set of one or more fields that can occur any number of times in a single record, with each occurrence having different values. • Often occur in manual documents prepared by users • Unnormalized design • An unnormalized record is one that contains a repeating group, which means that a single record has multiple occurrences of a particular field, with each occurrence having different values.

  6. Normalization Repeating groups In the ORDER table design, records 1 and 2 have repeating groups because they contain several products. ORDER-NUM is the primary key for the ORDER table, and PRODUCT-NUM serves as a primary key for the repeating group. Because it contains a repeating group, the ORDER table design is unnormalized. [1]

  7. Normalization • Now let’s review the unnormalized ORDER table design. Following the notation guidelines, you can describe it as follows: • ORDER (ORDER-NUM, ORDER-DATE, (PRODUCT-NUM, PRODUCT-DESC, NUM-ORDERED)) • The ORDER-NUM field is underlined to show that it is the primary key • The PRODUCT-NUM is also underlined because it acts as the primary key of the repeating group

  8. Normalization • First Normal Form • A table is in first normal form (1NF) if it does not contain a repeating group • To convert, you must expand the table’s primary key to include the primary key of the repeating group • Let us look to previous example again • When you expand the primary key of ORDER table to include PRODUCT-NUM, you eliminate the repeating group and the ORDER table is now in 1NF • ORDER (ORDER-NUM, ORDER-DATE, PRODUCT-NUM, PRODUCT-DESC, NUM-ORDERED)

  9. Normalization [1] The ORDER table as it appears in 1NF. The repeating groups have been eliminated. Notice that the repeating group for order 40311 has become three separate records, and the repeating group for order 40312 has become two separate records. The 1NF primary key is a combination of ORDER-NUM and PRODUCT-NUM, which uniquely identifies each record.

  10. Normalization • Note that neither ORDER-NUM nor PRODUCT-NUM does not uniquely identify each product in a multiple item product • Each record must reflect a specific product in a specific order, you need both fields, ORDER-NUM and PRODUCT-NUM, to identify a single record uniquely. • The primary key in this example is the combination of two fields: ORDER-NUM and PRODUCT-NUM

  11. Normalization • 1NF • All key attributes defined • No repeating groups in a table • All attributes dependent on a primary key

  12. Normalization • Dependencies • Dependencies can be identified • functional dependency (desirable dependencies) based on primary key • Less desirable dependencies • partial • - based on part of composite primary key • transitive • - one nonprime attribute depends on anther nonprime attribute

  13. Sequence Summary • Repeating group: is a set of one or more fields that can occur any number of times in a single record, with each occurrence having different values • An unnormalized record is one that contains a repeating group • Normalization is a process for avoiding problems in data design • A table is in first normal form (1NF) if it does not contain a repeating group • To convert, you must expand the table’s primary key to include the primary key of the repeating group

  14. Sequence Summary • In this Sequence we have • Defined the term normalization • Explained table design • Defined the standard notation format • Defined and discussed the repeating groups and unnormalized design • Explained the first normal form (1NF) • Explained the different types of attributes dependencies

  15. Reference [1] System Analysis and Design, Sixth Edition Authors: Gary B. Shelly, Thomas J. Cashman and Harry J. Rosenblatt Publisher: SHELLY CASHMAN SEWIES.

More Related