44220: Database Design & Implementation Avoiding Database Anomalies - PowerPoint PPT Presentation

Loading...

PPT – 44220: Database Design & Implementation Avoiding Database Anomalies PowerPoint presentation | free to download - id: 3c56f4-YmZhY



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

44220: Database Design & Implementation Avoiding Database Anomalies

Description:

44220: Database Design & Implementation Avoiding Database Anomalies Ian Perry Room: C49 Tel Ext.: 7287 E-mail: I.P.Perry_at_hull.ac.uk http://itsy.co.uk/ac/0506/sem2 ... – PowerPoint PPT presentation

Number of Views:57
Avg rating:3.0/5.0
Slides: 25
Provided by: itsyCoUk
Learn more at: http://itsy.co.uk
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: 44220: Database Design & Implementation Avoiding Database Anomalies


1
44220 Database Design ImplementationAvoiding
Database Anomalies
  • Ian Perry
  • Room C49 Tel Ext. 7287
  • E-mail I.P.Perry_at_hull.ac.uk
  • http//itsy.co.uk/ac/0506/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 or
    members of staff.

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
Dont change 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
This Weeks Workshop
  • In this Workshops session we will
  • test a logical data model to ensure that it is
    anomaly free (i.e. robust),
  • practice documenting a Database Schema based on
    a small conceptual model (as represented by an ER
    Diagram).
  • Examine a table of data
  • Explain the potential for insert, delete
    update anomalies in a table of data.
  • Define what a better set of tables (Relations?)
    to store the data look like?
  • Examine an ER Diagram
  • Identify suitable Attributes for each Relation
    as a minimum those that will act as the Primary
    Foreign Keys.
  • Document as a Database Schema starting with the
    Relations first, then coming back to document
    suitable Domains.
About PowerShow.com