Database Design Concepts

1
Database Design Concepts

• Lecture 9
• Worked example of normalisation

You should also read thoroughly the example shown
in Chapter 6 of the text book
2
Step by step plan to normalisation

• Investigate the users requirements
• Use documents ( there may be several)

3
(No Transcript)
4

• List attributes
• Booking reference number Flight number
• Customer name Direction
• Customer Address Operator
• Postcode Arrival time
• Phone Departure airport
• Villa name number of passengers
• Address cost
• Destination Total
• Start date
• Cost Total Cost
• End Date

5
Give attributes a full name. Identify groups of
data which repeat by adding bracketsAdd any
missing attributes ( in this case weekly cost of
villa)

• Booking reference number
• Customer Name
• Customer Address
• (Customer Phone)
• Villa name
• Villa Address
• Start Date
• End Date
• Cost of villa
• Weekly cost of villa

A customer can have more than one phone number
6
These are all contained in a bracket because they
will repeat as a group together

• (Direction
• Flight number
• Operator
• Arrival Time
• Departure airport
• Number of Passengers
• Cost of Flight
• Total cost of flight)
• Total cost of holiday

7
Find a key

• We need to check all the attributes to identify a
  unique identifier
• This is an attribute which will uniquely identify
  what the table is showing. It is usually a
  number.There will be no other row with this value
  in the key field
• In this case booking reference number
• If there is no key field you may need to add one
• Name the table Booking
• Name any column that can have multiple values by
  creating a new attribute. In this case Customer
  phone number 2

Row/tuple
8
Normally we show the table like this

• Booking( Bookingreferencenumber,customer
  name,customer address, customer phone, Customer
  phone2, villa name, villa address, destination,
  start date of holiday, end date of holiday,weekly
  cost of villa, villa cost for holiday,(Flight
  number, direction, operator, departure airport,
  flight time, arrival time, number of
  passengers,cost of flight, total cost for
  flight), total cost of holiday)

9
We are now ready to normalise

• Summary so far
• List all attributes from source document(s)
• Give all attributes their full name
• Create extra attributes for attributes which may
  have may have several values eg Phones
• Add any obvious missing attributes
• Show repeating groups in brackets
• Identify the unique identifier if there is one
• Add a unique identifier if necessary
• Name table in this case booking

10
First normal formRemove repeating groups

• Identify each repeating group. In this case the
  group with flight number
• For each group
• Remove the attributes of the repeating group from
  the existing table to a new table
• You now have
• Booking reference number,customer name,customer
  address, customer phone, customer phone 2, villa
  name, villa address, destination, start date of
  holiday, end date of holiday,weekly cost of
  villa, villa cost for holiday,total cost of
  holiday

11
and

• (Flight number, direction, operator, departure
  airport, flight time, arrival time, number of
  passengers,cost of flight, total cost for flight)
• As it is a new table - give it a name
  (boookedFlights)
• Add the unique identifier from the original table
  to the new table ( as a foreign key- Booking
  reference number)
• Identify a unique identifier for the new table

12
Creating a unique identifier

• This could be
• A composite key
• Made up of the foreign key and another attribute
  from the table. These two (or more) attributes
  uniquely identify any row of the table
• An entirely new attribute added to the table

13

• Booked Flights(Booking reference number, Flight
  number, direction, operator,departure airport,
  flight time, arrival time, number of
  passengers,cost of flight, total cost for flight)
• Repeat process for any other repeating groups
  none in this case.

14
Second Normal FormAll attributes in the table
must be determined by the whole unique identifier

• Take each table with a composite unique
  identifier and check each attribute can only
  determined from the whole unique identifier-
• In other words is the value of the attribute
  associated with the value in the identifier or
  just one part of it

15
Starting with the flight table

• Booked Flight (Booking reference number, Flight
  number, direction, operator, departure airport,
  flight time, arrival time, number of
  passengers,cost of flight, total cost for flight)
• e.g. is direction determined by the whole key or
  part of it?
• Direction is determined by the flight number only
• Work through the attributes individually in this
  way remembering which part of the key they are
  dependant on.

16

• Booked Flight (Booking reference number, Flight
  number, direction, operator, departure airport,
  flight time, arrival time, number of
  passengers,cost of flight, total cost for flight)
• Remove these attributes from the table
• Place all attributes that are dependant on on
  part of the key in a table together. Give the
  table a name.
• Add a unique identifier this will be the
  attribute they are dependant on.
• This becomes-

17

• BookedFlight (Booking reference number, Flight
  number, number of passengers, total cost for
  flight)
• flights(flight number,direction, operator,
  departure airport, flight time, arrival time,
  cost of flight)
• All the attributes removed were dependant on the
  same attribute so are all in the same table
• There are no more tables with composites

18
Third normal formRemove transitive dependencies

• Transitive dependency is when an attribute can
  be determined by a non key attribute/unique
  identifier
• Identify them and the non-key attribute they are
  dependant on
• For each table check for transitive dependency

19
Booking table

• Booking (Booking reference number,customer
  name,customer address, customer phone, customer
  phone2,villa name, villa address, destination,
  start date of holiday, end date of holiday,weekly
  cost of villa, villa cost for holiday,total cost
  of holiday)
• Customer address is determined by the customers
  name ie a customer name will have one customer
  address associated with it.
• Continue through the remaining attributes

20
Booking (Booking reference number,customer
name,customer address, customer phone, customer
phone 2,villa name, villa address, destination,
start date of holiday, end date of holiday,weekly
cost of villa, villa cost for holiday,total cost
of holiday)

• Red all refer to the customer and are determined
  by customer name
• Green all refer to the villa and are determined
  by the villa name
• Remove from the table into tables for each
  grouping (two tables), add the attribute they are
  dependant on as a key( this will be in the
  original table as well), give the table a name.

21

• Booking (Booking reference number,customer name,
  villa name, start date of holiday, end date of
  holiday, villa cost for holiday,total cost of
  holiday
• Customer(customer name,customer address, customer
  phone, customer phone2)
• Villa(villa name, villa address, destination,
  weekly cost of villa)

Are these sensible unique identifiers? see later
22

• Check flights and booked flights- these have no
  transitive dependencies

23
Finally remove calculated attributes if you wish

• Booking (Booking reference number, customer
  number, villa number, start date of holiday, end
  date of holiday, villa cost for holiday,total
  cost of holiday
• Customer(customer number, customer name,customer
  address,customer town,customer postcode, customer
  phone, customer mobile)
• Villa(villa number, villa name, villa address,
  destination, weekly cost of villa)
• Booked Flight (Booking reference number, Flight
  number, number of passengers, total cost for
  flight)
• flights(flight number,direction,operator,
  departure airport, flight time, arrival time,
  cost of flight)

24
Fully normalised Tables

• Booking (Booking reference number, customer
  number, villa number, start date of holiday, end
  date of holiday, villa cost for holiday)
• Customer(customer number, customer name,customer
  address,customer town,customer postcode, customer
  phone, customer mobile)
• Villa(villa number, villa name, villa address,
  destination, weekly cost of villa)
• Flight (Booking reference number, Flight number,
  direction, number of passengers, total cost for
  flight)
• Booked flights(flight number,operator, departure
  airport, flight time, arrival time, cost of
  flight)