Normalisation up to 1NF

1
Normalisation up to 1NF

• Bottom-up Approach to Data Modelling

2
Objectives

• Define the purpose of normalisation
• Determinacy / Dependency
• First Normal Form
• Second Normal Form
• Third Normal Form

3
Normalisation

• Normalisation provides algorithms for reducing
  complex data structures into simple data
  structures
• Concerned with
• tidying up the data so there is no data
  redundancy
• ensuring that data is grouped logically
• Bottom up approach - start with data items
• Codds Law is a set of rules which ensure that
  the data is grouped correctly
• A normal form is a convenient structure into
  which data can be organised

10
4
A relation is relational

• If and only if every non-key attribute
• is determined by
• the KEY
• the WHOLE KEY.
• and nothing but the KEY
• so help me CODD!
• Dr. E.F. Codd, an IBM researcher, first developed
  the relational data model in 1970

5
Concept of Determinacy and Dependency

• if A determines B - then B is dependent on A
• B is dependent on A if given a value for A, there
  is only one possible value for B
• e.g.
• student name is dependent on student number
• and
• student number determines student name

6
First Normal Form (1NF)

• an form is in 1NF if and only if it has an
  identifying key and there are no repeating groups
  of attributes
• To do
• Find the key to the original form
• remove calculated (derived) fields from the
  original form
• move repeating attributes into a new form
• These are generally column headings in a grid.
• Add the key to the original form as a foreign key
  to the new form
• Determine the key to the new form
• Result 1NF forms

12
7
Second Normal Form (2NF)

• a form is in 2NF if and only if it is in 1NF and
  has no attributes which require only part of the
  key to uniquely identify them
• To do - remove part-key dependencies
• where a key has more than one attribute, check
  that each non-key attribute depends on the whole
  key and not part of the key
• for each subset of the key which determines an
  attribute or group of attributes create a new
  form. Move the dependant attributes to the new
  form.
• Add the part key to new form, making it the
  primary key.
• Mark the part key as a foreign key in the
  original form.
• Result 2NF forms

8
Third Normal Form (3NF)

• an form is in 3NF if and only if it is in 2NF and
  no non-key attribute depends on any other non key
  attribute
• To do - remove non key dependencies
• decide on the direction of the dependency
• if A depends on B create new form. Copy B to it
  as the primary key and move A as attribute(s)
• leave B in original form, mark it as a foreign
  key.
• A and B can be groups of attributes
• Result 3NF forms

9
Representing Normal Forms Graphically

• If primary key of form A is a subset of the
  primary key of form B, then the relationship of A
  to B is one to many
• If P contains foreign key F and F is the primary
  key of Q, then the relationship of P to Q is many
  to one
• Use form and relationship notation as before

10
Two Technique Approach to Data Modelling

• E-R diagramming to find and group all data items
• normalisation to ensure data items are grouped
  correctly

11
Joes Docket
12
Before we begin

• We have already discussed
• Entities
• Attributes
• Values
• Data and Metadata
• We now take one of the documents and apply
• Unnormalised form rules (Data Dictionary format)
• First Normal Form rules
• Second Normal Form rules
• Third Normal Form rules

13
Data Dictionary

• central store of data that supports other models
• unambiguous and concise way of recording data
  about data (metadata)
• can be recorded manually or using a software tool
• encourages consistency between models by using
  same names in different models
• prevents duplication of data
• resolves problems of aliases which are all
  recorded against the appropriate data item
• built up as the models are developed
• aids communication as everyone on development
  team knows the exact meaning of words and terms
  used

14
Sequence, selection, iteration

• Sequence
• CustomerDetails Name Address PhoneNo
• Repetition
• Name Title Initial Surname
• Optionality ( )
• CustomerDetails Name Address (PhoneNo)
• Selection
• Name (Title) ForeName Initial
  Surname
• Values
• Title Dr Mr Mrs Ms
• Comments .

15
Example

• Full data dictionary entries for CustomerDetails
• Address AddressLine
• CustomerDetails Name Address (PhoneNo)
• Name (Title) ForeName Initial
  Surname
• Title Dr Mr Mrs Ms

4
16
Our Docket again
17
Making the Docket logical

• Many of the names on the docket are local to
  Joes business.
• We try to make them more standard, so
• A Docket becomes a Customer Order or COrder.
• There are three signatures on the Docket.
  Signatures by their nature are manual. In a
  computerised system, these will be replaced by an
  authorisation procedure, which will show
• which staff member their number will do - took
  payment (StaffPaid) and
• which staff member marked the items as received
  (StaffIssued).
• The customer name is also kept on record.

18
Data Dictionary Definition of the Docket

• Unnormalised data
• COrder COrderNo Stock_Code
  QuantityRequired (StockDescription) UnitPrice
  20VAT TotalPrice TotalAmtDue StaffPaid
  StaffIssued StaffName StaffRole
  customer_name

19
Remember (1NF)

• To do
• Find the key to the original form
• remove calculated (derived) fields from the
  original form
• move repeating attributes into a new form
• Add the key to the original form as a foreign key
  to the new form
• Determine the key to the new form
• Result Forms in First Normal Form

12
20
Normalising

• Find the key to the original form
• The COrderNo uniquely defines the COrder
• Remove calculated (derived) fields
• TotalPrice, 20VAT and TotalAmtDue are derived
  fields. Each can be calculated from other data on
  the form.
• COrder COrderNo Stock_Code
  QuantityRequired (StockDescription) UnitPrice
  StaffPaid StaffIssued StaffName StaffRole
  Customer_Name

21
Normalising

• move repeating attributes into a new form
• Original form COrder COrderNo StaffPaid
  StaffIssued StaffName StaffRole
  Customer_Name
• New form COrderLine Stock_Code
  QuantityRequired (StockDescription) UnitPrice
• Add the key to the original form as a foreign key
  to the new form
• New form COrderLine Stock_Code
  QuantityRequired (StockDescription) UnitPrice
  COrderNo
• Determine the key to the new form
• COrderLine Stock_CodeCOrderNo
  StockDescription UnitPrice QuantityRequired