44220: Database Design - PowerPoint PPT Presentation


PPT – 44220: Database Design PowerPoint presentation | free to download - id: 227514-N2U1Z


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation

44220: Database Design


the same data can give different information if a different structure and/or context is applied. ... Entity Identifiers become Primary Keys ... – PowerPoint PPT presentation

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


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

Title: 44220: Database Design

44220 Database Design ImplementationReview
Assignment 1
  • Ian Perry
  • Room C48 Tel Ext. 7287
  • E-mail I.P.Perry_at_hull.ac.uk
  • http//itsy.co.uk/ac/0607/sem2/44220_DDI/

Data, Information,
Data ? Information
  • What is Data?
  • a series of observations, measurements, or facts.
  • What is Information?
  • data that have been transformed into a meaningful
    and useful form for people.
  • Information Data Structure Context
  • the same data can give different information if a
    different structure and/or context is applied.

Database Data Structure
  • A database is
  • an organised collection of data.
  • A database management system (DBMS) is
  • software designed to assist in maintaining and
    utilising large collections of data.
  • A relational database management system (RDBMS)
  • a specific type of DBMS.

Modelling the Real World
  • Requires us to focus on the critical aspects of
    the real worlds richness.
  • Models are not Real/Complete.
  • All models require decision of what to include
    what to exclude.
  • These design decisions represent someones view
    of what is important (and what is not important)
    about a particular reality.
  • As such, there is no right answer!
  • All one might say is that this is a good model,
    given the purpose we want to use it for.

Example Models
  • Model Duck
  • Purpose to show shape, colour, size, etc.
  • Model Aeroplane
  • Purpose to show general structure,
    identification of parts, flight characteristics,
  • Data Model
  • Purpose the representation of objects of
    interest to an enterprise, allowing data to be
    structured (i.e. given meaning) and manipulated
    (for specific purposes).

The Data Modelling Stack
Conceptual Data Modelling
  • Identify ALL of the relevant Entities.
  • must play a necessary role in the business
  • Identify those Attributes that adequately
    describe each Entity.
  • remember to choose key attribute(s).
  • Identify the Relationships between Entities.
  • determine the Degree of each Relationship
  • determine the Type of each Relationship.
  • attempt to decompose any many-to-many
    Relationships that you have identified.

Entities Attributes
  • Real World Situation
  • Hospital
  • Entities objects of interest, e.g.
  • Doctor, Nurse, Ward, Patient, etc.
  • Attributes describing each Entity, e.g.
  • Patient Name, Address, Date-of-Birth, Gender,
  • Entity Definitions
  • Staff (StaffID, Role, Name, Room, Extension,
    Speciality, )
  • Patient (FirstName, FamilyName, DOB, Address,
    Gender, )
  • NB. key Attribute(s) MUST be identified.

Occurrence Diagrams?
  • Use these (with values for Key Attributes) to
    discover how many occurrences of each Entity are
    actually on either side of a Relationship (i.e.
    the Degree of the Relationship).

Staff Fred Smith Jane Bloggs Arthur
Jones Angela Oust
Ward Ward 1 Ward 2 Ward 3
Degree, Type Participation Diagrams
NB. the above Relationships are also Exclusive.
i.e. having solved the MM Relationship
ER Diagram for a Hospital
Logical Data Modelling
  • All about
  • translating our Conceptual Data Model so that it
    might be implemented using software that matches
    a specific Database Theory.
  • Relational Database Theory, Codd (1970)
  • allows us to develop mathematically rigorous
    abstract data models, composed of a number of
    distinct Relations.
  • Tables are NOT Relations
  • simply the way we choose to mentally give flesh
    to our Logical Data Model.

  • Are defined by a list of Attributes (i.e.
    columns), that
  • must be distinctly named.
  • contain data entries that are atomic, of the same
    type, from the same domain.
  • can be defined in any order.
  • Tuples (i.e. rows)
  • once again, ordering is irrelevant.
  • must be unique (so need a Key).
  • Relationships
  • are made via Primary/Foreign Key mechanism.

Example Relations ( Tables)
  • Staff (SCode, Name, Address, DoB, DoE)
  • Contract (CCode, Site, Begin, End, Super)

Avoid Database Anomalies!
  • What is an Anomaly?
  • Anything we try to do with a database that leads
    to unexpected (unpredictable) results.
  • Three types of Anomaly
  • insert
  • delete
  • update
  • Need to check your logical database design
  • the only good database is an anomaly free

A Conceptual Model
  • Consider the following simple conceptual data

Staff(Staff-ID, Name, Address, ScalePoint,
RateOfPay, DOB, ...) Student(Enrol-No, Name,
Address, OLevelPoints, ...) Course(CourseCode,
Name, Duration, ...)
The Translation Process
  • Entities become Relations
  • Attributes become Attributes (?)
  • Entity Identifiers become Primary Keys
  • Relationships are represented by additional
    Foreign Key Attributes in those Relations that
    are at the M end of a 1M relationship.
  • Usually end up with more Relations than we
    originally defined as Entities, with
  • Artificial Relations to solve MM problems.
  • Split-off Relations to avoid dependency

5 Relations from 3 Entities
Document as Database Schema?
  • A Database Schema
  • defines all Relations (together with Attributes
    and Primary/Foreign Keys) and their relevant
  • We should have captured the Business situation
    (assumptions and constraints) in the Conceptual
    Data Model, e.g
  • a College only delivers 10 Courses.
  • These assumptions and constraints become the
    Domains of the Database Schema.

Database Schema - 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

Database Schema - Relations
  • Relation Student
  • Enrol-No StudentIdentifiers
  • Name PersonNames
  • Address Addresses
  • OLevel OLevelPoints
  • Tutor StaffIdentifiers
  • Primary Key Enrol-No.
  • Foreign Key Tutor refs Staff.Staff-ID
  • Relation Staff
  • Staff-ID StaffIdentifiers
  • Name PersonNames
  • Address Addresses
  • ScalePoint ScalePoints
  • DOB StaffBirthDates
  • Primary Key Staff-ID.
  • Foreign Key ScalePoint refs Pay.ScalePoint

Continue to define each of the other Relations in
a similar manner.
Remember to define ALL of the Relations,
including artificial ones (e.g. Team)
split-off ones (e.g. Pay)
Assignment 1?
  • Read the Case Study carefully
  • Must understand the Business (i.e. Perrys
    Newsagents) for whom you are developing this
  • Two parts
  • develop an appropriate conceptual data model that
    might deliver the information requirements.
  • develop a robust logical data model that will
    deliver the information requirements.
  • NB.
  • Test BOTH Data Models with the 10 questions at
    the end of the Perrys Newsagents Case Study.

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
  • Ass 1 Deadline Friday, the 23rd of March, 2007.
About PowerShow.com