Second Normal form

1
Second Normal form

• For tutorial

2
Second Normal Form

• Remove part key dependencies
• Only applies to forms that are in first normal
  form and have a compound key
• i.e. more than one field in the key.
• Remove the fields that are dependent on the part
  key and borrow the part key, to become the
  primary key of the new form. This form will then
  be a master form for the new form.

3
TNF Exercise.

• Objective.
• To practise the normalisation technique and to
  produce a set of optimised relations from the
  normalisation of six documents.
• Method.
• 1. Apply the rules of normalisation to the
  supplied documents.
• 2. Note the business rules.

4
Supplied Documents

• Product Sales Report
• Wellingtons Invoice
• Product Stock Report
• Sales Executive report
• New Product / Amendment
• Customer Amendment

5
Business Rules

• Product Code is not unique each may have a
  number of pack sizes.
• Some Customers are allowed a discount. Each item
  has a Standard Price and up to 3 discount prices.
  A customer entitled to a discount has a single
  discount code A, B, C and will be charged the
  appropriate A, B or C price for all Products he
  orders.
• At any Warehouse, a Product is only stocked in
  one bin. The bin code may be considered unique
  to a warehouse.
• A customer deals with one, and only one, Sales
  Executive.

6
Product Sales Report
7
Unnormalised Form

• Product Code PackSize ProductDescription
  InvoiceNumber InvoiceDate CustomerNumber
  CustomerName InvoiceQuantity LineValue

8
First Normal Form

• ProductCodePackSize ProductDescription
• (ProductCode PackSize) InvoiceNumber
  InvoiceDate CustomerNumber CustomerName
  InvoiceQuantity LineValue

9
First Normal form Product Sales Report

• The product description is dependent only on the
  ProductCode, so
• ProductCodePackSize ProductDescription
• Becomes
• ProductCodePackSize
• ProductCode ProductDescription

10
2nf Product Sales Report

• and
• (ProductCode PackSize) InvoiceNumber
  InvoiceDate CustomerNumber CustomerName
  InvoiceQuantity LineValue
• becomes
• (ProductCode PackSize) InvoiceNumber
  InvoiceQuantity LineValue
• And
• InvoiceNumber InvoiceDate CustomerNumber
  CustomerName

11
2nd Normal form Product Sales Report
12
Invoice
13
Unnormalised Form

• InvoiceNumber InvoiceDate CustomerNumber
  CustomerName CustomerInvoiceAddr
  ProductDescription ProductCode PackSize
  StandardPrice DiscountCode DiscountPrice
  InvoiceQuantity LineValue InvoiceValue

14
First Normal Form

• InvoiceNumber InvoiceDate CustomerNumber
  CustomerName CustomerInvoiceAddr DiscountCode
  
• InvoiceNumber ProductCode PackSize
  ProductDescription StandardPrice
  DiscountPrice InvoiceQuantity

15
Second Normal Form

• InvoiceNumber InvoiceDate CustomerNumber
  CustomerName CustomerInvoiceAddr DiscountCode
  
• InvoiceNumber ProductCode PackSize
  DiscountPrice InvoiceQuantity
• ProductCode ProductDescription
• ProductCode PackSize StandardPrice

16
Product Stock Report
17
Unnormalised Form

• ProductCode PackSize Warehouse
  StockQuantity BinCode NoOrdYTD
  StockQuantityTotal NoOrdYTDTotal

18
First Normal Form

• ProductCode PackSize StockQuantityTotal
  NoOrdYTDTotal
• (ProductCode PackSize) Warehouse
  StockQuantity BinCode NoOrdYTD

19
2nd Normal Form

• ProductCode PackSize
• (ProductCode PackSize) Warehouse
  StockQuantity BinCode NoOrdYTD
• remains the same

20
Sales Executive Report
21
Unnormalised Form

• SalesExecutive CustomerNumber OrdersYTD
  ValueOrders DiscountCode OrdersYTDTotal
  ValueOrdersTotal

22
First Normal Form

• SalesExecutive OrdersYTDTotal
  ValueOrdersTotal
• CustomerNumber SalesExecutive OrdersYTD
  ValueOrders DiscountCode
• No compound keys, so no change for 2NF.

23
New Product / Amendment
24
Unnormalised Form

• ProductCode PackSize ProductDescription
  PrescriptionIndicator UnitQuantity
  UKDrugRegNo MinimumStock DiscountCode
  DiscountPrice

25
First Normal Form

• ProductCode PackSize ProductDescription
  PrescriptionIndicator UnitQuantity
  UKDrugRegNo MinimumStock
• ProductCode PackSize DiscountCode
  DiscountPrice
• Product Description, Prescription Indicator and
  UKDrugRegNo only depends on Product Code

26
Second Normal Form

• ProductCode PackSize UnitQuantity
  MinimumStock
• ProductCode ProductDescription UKDrugRegNo
  PrescriptionIndicator
• ProductCode PackSize DiscountCode
  DiscountPrice

27
Customer Amendment
28
Unnormalised Form

• CustomerNumber SalesExecutive
  PreferredCustomer CustomerName
  CustomerInvoiceAddr CreditLimit BulkPackOnly
  DiscountCode LicensedDrugVendor
  CustomerDeliveryAddress