Normalisation up to 1NF - PowerPoint PPT Presentation

1 / 21
About This Presentation
Title:

Normalisation up to 1NF

Description:

Unnormalised form rules (Data Dictionary format) First Normal Form rules ... The customer name is also kept on record. Data Dictionary Definition of the Docket ... – PowerPoint PPT presentation

Number of Views:59
Avg rating:3.0/5.0
Slides: 22
Provided by: poby
Category:

less

Transcript and Presenter's Notes

Title: 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
Write a Comment
User Comments (0)
About PowerShow.com