44220: Database Design

1
44220 Database Design ImplementationAvoiding
Database Anomalies

• Ian Perry
• Room C41C Tel Ext. 7287
• E-mail I.P.Perry_at_hull.ac.uk
• http//itsy.co.uk/ac/0809/sem2/44220_DDI/

2
Avoiding Database Anomalies

• This lecture concentrates upon building a
  robust Logical Data Model. i.e.
• Transforming a Conceptual Data Model into a set
  of Relations.
• Checking these Relations for any Anomalies.
• Documenting them as a Database Schema.
• Most Database books have a section describing a
  mathematically-based technique called
  Normalisation
• I will show you a much easier way of achieving
  the same result, i.e. a robust database design.

3
What is an Anomaly?

• Anything we try to do with a database that leads
  to unexpected and/or unpredictable results.
• Three types of Anomaly to guard against
• insert
• delete
• update
• Need to check your database design carefully
• the only good database is an anomaly free
  database.

4
Insert Anomaly

• When we want to enter a value into a data cell
  but the attempt is prevented, as another value is
  not known.

• e.g. We have built a new Room (e.g. B123), but it
  has not yet been timetabled for any courses.

5
Delete Anomaly

• When a value we want to delete also means we will
  delete values we wish to keep.

• e.g. CoNo 351 has ended, but Room C320 will be
  used elsewhere.

6
Update Anomaly

• When we want to change a single data item value,
  but must update multiple entries

• e.g. Room H940 has been improved, it is now of
  RSize 500.

7
A Conceptual Model

• Consider the following simple conceptual data
  model

Staff(Staff-ID, Name, Address, ScalePoint,
RateOfPay, DOB, ...) Student(Enrol-No, Name,
Address, OLevelPoints, ...) Course(CourseCode,
Name, Duration, ...)
8
The Translation Process

• Entities become Relations
• Attributes become Attributes(?)
• Key Attribute(s) become Primary Key(s)
• Relationships are represented by additional
  Foreign Key Attributes
• for those Relations that are at the M end of
  each 1M Relationship.

9
The Staff Student Relations

• Staff(Staff-ID, Name, Address, ScalePoint,
  RateOfPay, DOB, ...)
• becomes
• Staff

Student(Enrol-No, Name, Address, OLevelPoints,
...) becomes Student
NB. Foreign Key Tutor references Staff.Staff-ID
10
The Staff Course Relations

• Course(CourseCode, Name, Duration, ...)
• becomes
• Course

• NB. Cant add a Foreign Key as BOTH Relations
  have a M end
• I warned you about leaving MM relationships in
  your Conceptual Data Model.
• Must create an artificial linking Relation.

11
Staff, Course Team Relations
NB. In the artificial Relation (i.e.
Team) The Primary Key is a composite of
CourseCode Staff-ID Foreign Key CourseCode
references Course.CourseCode Foreign Key Staff-ID
references Staff.Staff-ID
12
4 Relations from 3 Entities?
BUT - are they anomaly free?
13
Check Relations for Anomalies!

• every Tuple unique?
• no hidden meaning from location?
• data cells atomic?
• for Relations with single-attribute keys
• every Attribute depends upon the Primary Key?
• for Relations with composite keys
• every Attribute depends upon all of the Composite
  Key?

14
What if the checks fail?

• If any Relation fails checks
• especially those checking dependency.
• we MUST split that Relation into multiple
  Relations
• until they pass the tests.
• but MUST remember to leave behind a Foreign Key
• to point forwards to the Primary Key of the
  new split-off Relation.

15
Are they Anomaly Free?
16
Fixing this Problem

• The Attribute RateOfPay depends upon
  ScalePoint NOT Staff-ID.
• So, we need to split this Relation

NB. Foreign Key ScalePoint references
Pay.ScalePoint
17
5 Relations from 3 Entities
18
Never change your Conceptual Model

• Remember, we can chose from one of a range of
  Database Theories with which to build our Logical
  Data Model
• Hierarchical
• Relational
• Object
• Each of these Database Theories may require
  different compromises (i.e. at the Logical
  Modelling stage)
• from the pure meaning captured by your
  Conceptual Model.

19
Document Relations as a Database Schema

• A Database Schema
• defines all Relations,
• lists all Attributes (with their Domains),
• and identifies all Primary Foreign Keys.
• We should have captured the Business situation
  (assumptions and constraints) in the Conceptual
  Data Model, e.g
• a College only delivers 10 Courses.
• a Hospital only has 12 Wards.
• These assumptions and constraints need to be
  expressed as the Domains of the Database Schema.

20
Logical Schema 1 - Domains

• Schema College
• Domains
• StudentIdentifiers 1 - 9999
• StaffIdentifiers 1001 - 1199
• PersonNames TextString (15 Characters)
• Addresses TextString (25 Characters)
• CourseIdentifiers 101 - 110
• CourseNames Comp, IS, Law, Mkt, ...
• OLevelPoints 0 - 100
• ScalePoints 1 - 12
• PayRates 14,005, 14,789, 15,407, ...
• StaffBirthDates Date (dd/mm/yyyy), gt21 Years
  before Today

21
Logical Schema 2 - Relations

• Relation Student
• Enrol-No StudentIdentifiers
• Name PersonNames
• Address Addresses
• OLevelPoints OLevelPoints
• Tutor StaffIdentifiers
• Primary Key Enrol-No
• Foreign Key Tutor references Staff.Staff-ID

22
Logical Schema 3 - Relations

• Relation Staff
• Staff-ID StaffIdentifiers
• Name PersonNames
• Address Addresses
• ScalePoint ScalePoints
• DOB StaffBirthDates
• Primary Key Staff-ID
• Foreign Key ScalePoint references Pay.ScalePoint

23
Logical Schema ...

• Relation Course
• CourseCode CourseIdentifiers
• Name CourseNames
• etc.
• Continue to define each of the Relations in a
  similar manner.
• All Relations MUST have a Primary Key.
• Any Relation at the M-end of a 1M Relationship
  MUST have a Foreign Key.
• Make sure that you define ALL of the Relations,
  including
• artificial ones (e.g. Team)
• split-off ones (e.g. Pay)

24
Assignment 1?

• Read the Case Study carefully
• Must understand the Business (i.e. Perrys
  Newsagents) for whom you are developing this
  database.
• Two parts
• develop an appropriate conceptual data model
  (i.e. an ER Diagram) which MIGHT deliver the
  information requirements.
• develop a robust logical data model (i.e. a
  Database Schema) that WILL deliver the
  information requirements.
• NB.
• Test BOTH Data Models with the 10 questions at
  the end of the Perrys Newsagents Case Study.

25
Answer the Questions I have set!

• Part 1 Conceptual Data Model (40 Marks)
• ER Diagram
• depicting the Relationships between ALL Entities,
  AND indicating the degree, type participation
  of each Relationship.
• Part 2 Logical Data Model (60 Marks)
• Database Schema
• specifying ALL Domains, Relations, Attributes AND
  Primary Foreign Keys.
• NB.
• BOTH of the above MUST be in the format as
  defined in the Lectures and practised during the
  Workshops.

26
This Weeks Workshop

• In this Workshops session
• I will demonstrate how to test repair a logical
  data model to ensure that it is anomaly free
  (i.e. robust).
• I will provide some useful advice to help you
  document successfully a Database Schema for
  Perrrys Newsagents.
• I will then attempt to answer any questions you
  have about Assignment 1.
• NB. Assignment 1 Deadline
• Wednesday, the 18th of March, 2009.