1 / 20

# 11 NORMALISATION - A worked example

And Franchise Colleges. 11 NORMALISATION - A worked example. By MANSHA NAWAZ. Normalisation Summary. Rules to assist in the creation of a DATA MODEL A step by step technique which restructures the data of a system into a more efficient and desirable form.

## 11 NORMALISATION - A worked example

E N D

### Presentation Transcript

1. And Franchise Colleges 11 NORMALISATION - A worked example • By MANSHA NAWAZ Section 11 : Normalisation - A Worked Example

2. Normalisation Summary • Rules to assist in the creation of a DATA MODEL • A step by step technique which restructures the data of a system into a more efficient and desirable form. • Takes logical datastore view to physical table view • Makes improvements in terms of : • NO DUPLICATION • NO REDUNDANT • NO NULL • REDUCTION IN PHYSICAL SIZE • QUICKER INFORMATION RETRIVAL • LEADS TO A FULLY OPTIMISED SET OF TABLES Section 11 : Normalisation - A Worked Example

3. Normalisation Rules • 0NF Zero Normal Form or Unnormalised data • Data Dictionary Structure and Elements of a datastore • List datastore data: identify key(s) and repeating group of data • represents the logical form view of a datastore • 1NF first Normal Form or first normalised data • Remove repeating group(s) to new table(s) • 2NF second Normal Form or second normalised data • Remove partial key dependency data to new table(s) • 3NF third Normal Form or third normalised data • Remove non key dependency data to new table(s) • represents the physical tables view of a datastore Section 11 : Normalisation - A Worked Example

4. Normalisation Example : ORDERS Section 11 : Normalisation - A Worked Example

5. The data derived from the form is : • Order# • Date • Customer Number, Name & Address • Salespersons Number & Name • Below that is a repeated group for each item ordered: • Item Number • Description • Quantity • Price • At the bottom we have : • Order Value Section 11 : Normalisation - A Worked Example

6. 0-NF : UN-NORMAL FORM • A single table of data holding a list of data for our orders entity. • A listing of data related to the entity type ORDER. • Any repeating group of information is contained in brackets. • Primary key or a compound key to retrieve the information is identified by the @symbol. • Presented as follows • ORDERS-0 (@order#, Date, C#, Cname, Caddress, SP#, SPname, (Item#, Desc, Qty, Price), Order_Value) Section 11 : Normalisation - A Worked Example

7. 1-NF : FIRST NORMAL FORM • To make improvements in our table we must remove any repeating groups. • This process is referred to as first normal form (1-NF) • To move from 0-NF to 1-NF we must • Remove repeating group of data to a new table. • The new table (entity) must inherit the key from 0-NF stage. • For the new tables there will be a compound key within which the original key will participate. Section 11 : Normalisation - A Worked Example

8. 0-NF • ORDERS-0 (@order#, @Date, C#, Cname, Caddress, SP#, SPname, (Item#, Desc, Qty, Price), Order_Value) • 1-NF Remove Repeating Group(s) • ORDERS-1 (@order#, Date, C#, Cname, Caddress, SP#, SPname, Order_Value) • ORDER-ITEM-1 (@order#, @Item#, Desc, Qty, Price) Section 11 : Normalisation - A Worked Example

9. 2-NF : SECOND NORMAL FORM • To make improvements in our table we must remove any partial key dependency. • This process is refereed to as second normal form (2-NF) • To move from 1-NF to 2-NF we must • Identify non-key attributes that are dependent on part of the key. • Remove non-key attributes that are dependent on part of the key and remove them together with that part of the key in to a new table. Section 11 : Normalisation - A Worked Example

10. From the ORDERS-1 table : • Tables with single key are automatically in 2-NF • From the ORDER_ITEM-1 table : • Qty - Dependent on whole key so must remain • Price – Variable price if dependent on whole or fixed price if dependent on part. • Desc - Dependent on only Item# so remove to new table • 1-NF Remove repeating group(s) • ORDERS-1 (@order#, Date, C#, Cname, Caddress, SP#, SPname, Order_Value) • ORDER-ITEM-1 (@order#, @Item#, Desc, Qty, Price) • 2-NF Remove Partial Key Dependency • ORDERS-2 (@order#, Date, C#, Cname, Caddress, SP#, SPname, Order_Value) • ORDER-ITEM-2 (@order#, @Item#, Qty, Price) • ITEM-2 (@Item#, Desc) Section 11 : Normalisation - A Worked Example

11. 3-NF : THIRD NORMAL FORM • To make improvements in our table we must remove any non-key dependency. • This process is refereed to as third normal form (3-NF) • To move from 2-NF to 3-NF we must • Identity non-key attributes that depend on other non-key attributes. • Remove non-key attributes that are dependent on other non-key attributes and place them into a new table. The key attribute remains in the original table. • Identify the key in the new table. Section 11 : Normalisation - A Worked Example

12. From the Order-2 table • Cname, CAddressis dependent onC# • MoveCname, CAddresswith a copy of the key C#to new table • Spname is dependent on SP#: • Move Spname with a copy of the key SP# to new table • 2-NF Remove Partial Key Dependency • ORDERS-2 (@order#, Date, C#, Cname, Caddress, SP#, SPname Order_Value) • ORDER-ITEM-2 (@order#, @Item#, Qty, Price) • ITEMS-2 (@Item#, Desc) • 3-NF Remove Non Key Dependency • ORDERS-3 (@order#, Date, C#, SP#, Order_Value) • CUSTOMERS-3 (@C#, Cname, Caddress) • SALESPERSONS-3 (@SP#, SPname) • ORDER-ITEM-1 (@order#, @Item#, Qty, Price) • ITEM-2 (@Item#, Desc) Section 11 : Normalisation - A Worked Example

13. DATASTORE - ORDERS • 0NF • ORDERS • @Order# • C# • Date • Cname Caddress • @SP# • SPname • @Item# • Desc • Qty • Price • Order_Value 2NF ORDERS @Order# C# Date @SP# SPname Order_Value CUSTOMERS @C# Cname Caddress ORDITEM @Order# @Item# Qty Price ITEM @Item# Desc 1NF ORDERS @Order# C# Date Cname Caddress @SP# SPname Order_Value ORDITEM @Order# @Item# Desc Qty Price 3NF ORDERS @Order# C# Date @SP# Order_Value CUSTOMERS @C# Cname Caddress ORDITEM @Order# @Item# Qty Price ITEM @Item# Desc SalesPerosn @SP# SPname ASCENT LAYOUT 0NF repeating group is indented TABLESPRIMARY KEY FOREIGN KEY Section 11 : Normalisation - A Worked Example

14. Populating your tables with sample data provided. Section 11 : Normalisation - A Worked Example

15. Load each table with the sample data provided as follows. ORDER-3 (@order#, C#, @Date, SP#, Order_Value) 001 12345 01.01.98 01 700.00 • ORDER-ITEM-3 • (@order#, @Date, @Item#, Qty, Price) • 001 01.01.98 0001 10 50.00 • 001 01.01.98 0004 02 100.00 • ITEM-3 • (@Item#, Desc, ) • 0001 16mb SIMM • 0004 32mb DIMM • SALESPERSON-3 • (@SP#, Spname) • 01 Fred Bloggs • CUSTOMER-3 • (@C#, Cname, Caddress) • 12345 NAWAZ UOT Section 11 : Normalisation - A Worked Example

16. Normalisation has produced a DATA MODEL • For orders datastore NF derived five tables • Review improvements in terms of • NO DUPLICATION • NO REDUNDANT • NO NULL • REDUCTION IN PHYSICAL SIZE • QUICKER INFORMATION RETRIVAL • LEADS TO A FULLY OPTIMISED SET OF TABLES • common criticism of Normalisation • breaks down too far • must be tempered by practical considerations. Section 11 : Normalisation - A Worked Example

17. NORMALISATION CHECKLIST • Identify Attributes and represent them in 0NF • pick a key for each table • bracket repeating groups • Transform data to 1NF • remove repeating groups, remember to POST KEY of the original table as part of the new tables key. • Pick new key • Transform data to 2NF • remove partial key dependencies • determinant(s) will become key(s) of the new table(s) • Transform data to 3NF • remove non-key dependencies • determinant(s) will become key(s) of the new table(s) Section 11 : Normalisation - A Worked Example

18. Normalisation Example : DATASTORE : PROJECTS P# Ptitle Pdesc E# Ename Eaddress p1 Accounts Excel e4 MBC Middlesbrough e4 Middlesbrough Council M’Boro e8 Teesside University Eston - p2 Stock Control Database e8 University of Teesside Borough Rd - - - p3 Reservation Rooms e8 University of Teesside Borough Rd e1 ICI Wilton Rd e2 British Steel South Bank - p4 Sales Cobol e2 British Steel South Bank - - - Q. Normalise the PROJECTS table ? Section 11 : Normalisation - A Worked Example

19. DATASTORE - PROJECTS • 0NF PROJECTS • @P# • Ptitle • Pdesc • @E# • Ename • Eaddress 3NF PROJECTS @P# Ptitle Pdesc 1NF PROJECTS @P# Ptitle Pdesc 2NF PROJECTS @P# Ptitle Pdesc 3NF PRO-EMP @P# @E# 2NF PRO-EMP @P# @E# 1NF PRO-EMP @P# @E# Ename Eaddress 2NF EMPLOYER @E# Ename Eaddress 3NF EMPLOYER @E# Ename Eaddress KEY 0NF repeating group is indented TABLESPRIMARY KEY FOREIGN KEY Section 11 : Normalisation - A Worked Example

20. Document : Design Specification • Data Dictionary • Data Description • Structures & Elements • Starting point is DATA STORES& DATA FLOW • Data Store Descriptions • Data Flow Descriptions • Process Descriptions • NORMALISATION • Database Tables derived from Data Store Descriptions Section 11 : Normalisation - A Worked Example

More Related