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.

zaina
Download Presentation

11 NORMALISATION - A worked example

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