Normalization Mr Ahmad AlGhoul learning Objectives Explain the

Normalization - Mr. Ahmad Al-Ghoul

learning Objectives § Explain the second normal form (2 NF) § Explain the third normal form (3 NF) § Describe the advantages of normalization 2

Normalization n Second Normal Form n To understand second normal form (2 NF), you must understand the concept of functional dependence 3

Normalization n n An attribute X is said to be functionally dependent on an attribute Y if each value of Y is associated with only one value of X For example, each ORDER-DATE value is functionally dependent on the ORDER-NUM. In contrast, a PRODUCTDESC is not dependent on the order number. For a particular order number, there might be several product description one for each item ordered Another way of phrasing functional dependency is to say that the value of X can be determined from the value of Y, or that Y functionally determines X So ORDER-NUM functionally determines the value of ORDERDATE , or the value of ORDER-DATE can be determined from the value of the ORDER-NUM, i. e. given the value of a ORDER -NUM we can always establish the value of the associated ORDER-DATE. 4

Normalization n n A table design is in second normal form (2 NF) if it is in 1 NF and if all fields that are not part of the primary key are functionally dependent on the entire primary key If any field in a 1 NF table depends on only one of the fields in a combination primary key, then the table is not in 2 NF If 1 NF design has a primary key that consists of only one field, the problem of partial dependence does not a rise, because the entire primary key is a single field 1 NF table with a single field primary key is automatically in 2 NF 5

Normalization n Now examine the 1 NF design for the ORDER table n n ORDER (ORDER-NUM, ORDER-DATE, PRODUCT-NUM, PRODUCT -DESC, NUM-ORDERED) The NUM-ORDERD field depends on the entire primary key, because NUM-ORDERD refers to a specific product number and a specific order number ORDER-DATE field depends on the order number, which is only a part of the primary key PRODUCT-DESC field depends on the product number, which is only a part of the primary key 6

Normalization n The objective for converting a table from 1 NF to 2 NF is to break the original table into two tables or more new tables and reassign the fields so that each nonkey field will depend on the entire primary key in its table 7

Normalization n To do that you follow these steps: Create and name a separate table for each field in the existing primary key. The ORDER table’s primary key has two fields, ORDER-NUM and PRODUCT-NUM, so you must create two tables; ORDER and PRODUCT 2. Create a new table for each possible combination of the original primary key fields. You would create and name a new table with a combination primary key of ORDER-NUM and PRODUCT-NUM. this table describes individual lines in an order, so it is named ORDER-LINE 3. Study the three tables and place each field with its appropriate primary key, which is the minimal key on which it functionally depends 1. 8

Normalization n The three tables for the previous example would be shown as ORDER (ORDER-NUM, ORDER-DATE) n PRODUCT (PRODUCT-NUM, PRODUCT-DESC) n ORDER-LINE ( ORDER-NUM, PRODUCT-NUM, NUM-ORDERD) n 9

Normalization ORDER, PRODUCT, and ORDER-LINE tables in 2 NF. All fields are functionally dependent on the primary key. [1] 10

Normalization n Second Normal Form n Four kinds of problems are found with 1 NF description that do not exist with 2 NF n Consider the work necessary to change a particular product’s description n n Suppose 500 current orders exists for product number 304. changing the product description involves modifying 500 records for product number 304. updating all 500 records would be time consuming and expansive. 1 NF tables can contain inconsistent data. Because someone must enter the product description in each record, nothing prevents product number 304 from having different product descriptions in different records 11

Normalization n Second Normal Form n Four kinds of problems are found with 1 NF description that do not exist with 2 NF n Adding a new product is a problem n n Because the primary key must include an order number and a product number, you need values for both fields in order to add a record Deleting a product is a problem n If all the related records are deleted once an order is filled and paid for, what happens if you delete the only record that contains product number 633? The information about that product number and its description is lost 12

Normalization n 2 NF No attribute dependent on a portion of a primary key n Attributes may be functionally dependent on nonkey attributes n 13

Normalization n Third Normal Form n n n A design is in 3 NF if every nonkey field depends on the key, the whole key, and nothing but the key 3 NF design avoids redundancy and data integrity problems that still can exist in 2 NF designs A table design is in third normal form (3 NF) if it is in 2 NF and if no nonkey field is dependent on another nonkey field 14

Normalization n n Consider the following CUSTOMER table design CUSTOMER (CUSTOMER-NUM, CUSTOMER-NAME, ADDRESS, SALES-REP-NUM, SALES-REP-NAME) 2 NF design for the CUSTOMER table. [1] 15

Normalization n n The previous table still has four potential problems similar to the four 1 Nf problems describes earlier Changing the name of a sales rep still requires changing every record in which that sales rep name appears Nothing prevent a sales rep from having different names in different records Sales rep name is included in the CUSTOMER table, you must create a dummy CUSTOMER record to add a new sales rep who has not yet been assigned any customer If you delete all the records for customers of sales rep number 22, you will lose that sales rep’s number and name 16

Normalization n Those potential problems are caused because the design is not in 3 NF A table design is in third normal form (3 NF) if it is in 2 NF and if no nonkey field is dependent on any anther nonkey field Nonkey field is a field that is not a candidate key for the primary key 17

Normalization n The CUSTOMER table is not in 3 NF because one nonkey field, SALES-REP-NAME, depends on anther nonkey field, SALES-REP-NUM To convert the table to 3 NF, you must remove all fields from the 2 NF table that depend on another nonkey field and place them in a new table that uses the nonkey field as a primary key To reach 3 NF, you must remove SALES-REPNAME and place it into a new table that uses SALES-REP-NUM as the primary key 18

Normalization n 3 NF produces two separate tables n n CUSTOMER (CUSTOMER-NUM, CUSTOMER-NAME, ADDRESS, SALES-REP-NUM) SALES-REP (SALES-REP-NUM, SALES-REP-NAME) [1] 19

Normalization n 3 NF Remove any transitive dependencies n No attribute dependent on a nonkey attribute n 20

Normalization n Advantages of Normalisation n n after describing normalization in detail it is worth mentioning some of its advantages briefly. Data in Third Normal Form (3 NF) consists of tables of closely associated attributes which are entirely dependent on ‘the key, the whole key, and nothing but the key’ This has the effect of minimising data duplication across different tables, thereby resolving many of the problems associated with data redundancy. 21

Sequence Summary n n n An attribute X is said to be functionally dependent on an attribute Y if each value of Y is associated with only one value of X A record is in second normal form (2 NF) if it is in 1 NF and all nonkey fields functionally depend on the entire primary key A record is in third normal form (3 NF) if it is in 2 NF and if no field depends on a nonkey field 22

Sequence Summary n In this Sequence we have § Defined and explained the term functionally dependent § Explained the second normal form (2 NF) § Explained the problems that can be solved by 2 NF § Explained the third normal form (3 NF) § Described the advantages of normalization 23

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