44220: Database Design

1
44220 Database Design ImplementationModelling
the Real World

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

2
Last Lecture ended with this

• This Lecture
• Explores the complex idea of Data Modelling.

3
What is this?
4
And this?

• Any of the family of aquatic birds, especially
  those having short legs, webbed feet, and a broad
  blunt bill.
• Collins Concise English Dictionary

5
And this?
6
And this?

• Duck

• What do they have in common?
• They are ALL, depending upon your information
  requirements, perfectly adequate models of a Duck.

7
So, what is a Model?

• Always remember that
• Models ARE NOT the Real thing.
• They are
• the appearance of reality.
• an analogue of the real world.
• a simplified representation of reality.
• the abstraction of meaning (the semantics).
• We build models for a purpose, so
• being clear as to the purpose of a model is the
  key to success.

8
Every-day Models

• Model Duck
• Purpose to show shape, colour, size, etc.
• Model Aeroplane
• Purpose to show general structure,
  identification of parts, flight characteristics,
  etc.
• 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).

9
The Data Abstraction Process

• Requires us to focus on the critical aspects of
  the real worlds richness.
• no model is complete!
• All models require decisions about
• what to INCLUDE what to EXCLUDE?
• These decisions represent someones view of a
  particular reality, i.e.
• what is important, MUST be included.
• what is not important, CAN be left out.

10
Data Models in the Plural

• The complexity of the database design process
  means that we must use a hierarchy of data
  models
• Conceptual Data Model
• Logical Data Model
• Physical Data Model
• Successful database design requires
• an orderly, and rigorous, progression through
  this Data Modelling Stack.

11
The Data Modelling Stack
12
Conceptual Data Model

• Initial view of the Objects of interest
• their properties, relationships, semantics.
• An integrated view of the whole
• which is compromise free AND Software Hardware
  independent.
• Conceptual Data Modelling, is all about
• capturing WHAT real-world Objects MUST be
  included, i.e. to suit a particular purpose.
• We will build Conceptual Models using
• Entity Relationship (ER) Diagrams

13
Logical Data Model

• Logical Data Modelling, is all about
• deciding HOW to best represent the properties
  of the Objects of interest, and the relationships
  between them.
• We will build these using
• Database Schema
• A Database Schema
• defines the single, integrated, data collection
  that is the whole database.
• within the constraints of a chosen/imposed
  theoretical framework.

14
Theories of Logical Modelling

• Several to choose from, e.g.
• Hierarchical, Relational, Object-based, etc.
• Each Logical Modelling Theory comes with its own
• Data Definition Language (DDL)
• Data Manipulation Language (DDM)
• Data Query Language (DQL)
• Software availability tends to influence the
  Logical Modelling Theory chosen here
• As we will be using Microsoft Access (a
  Relational DataBase Management System), our
  logical choice must be Relational.

15
Physical Data Model

• Physical Data Modelling, is all about
• mapping a Logical Data Model onto a specific
  Physical Storage System.
• This process may be influenced by
• both Software specific Hardware specific
  considerations.
• i.e. one may have to compromise the Logical Data
  Model
• in order to make it fit the Software (in our
  case Microsoft Access) that will be used build,
  maintain manipulate the Physical Data Model.

16
Database Design Development

• Analyse
• a business situation in order to discover the
  purpose.
• Develop
• a conceptual data model from the above analysis.
• Develop
• a logical data model (constrained by a particular
  database theory) from the conceptual data model.
• Implement
• a physical data model (constrained by software
  availability) based on the logical data model.
• Manipulate
• i.e. test the physical data model to prove it
  suits the purpose discovered by the original
  analysis.

17
DDI Assessment Method

• Assignment 1 (50) 18 Mar, 2009
• Develop an appropriate Conceptual Data Model
  for a Case Study organisation.
• Develop a robust Logical Data Model based on
  the Conceptual Data Model.
• Assignment 2 (50) 13 May, 2009
• Implement a accurate Physical Data Model based
  on the Logical Data Model.
• Manipulate the Physical Data Model to prove it
  can Answer Specific Questions from the Case
  Study.

18
This Weeks Workshop

• In this Workshop we will begin to explore some of
  the decisions we must make as we attempt to
  construct a model of real world things.
• Working alone, or in a small team, consider the
  following things that exist in the real
  world.
• University,
• Computer,
• Sports Activity.
• For each of the above
• list as many as you can imagine (try to be as
  creative inventive as you can) of the ways that
  it might be modelled.
• For each way of modelling the above things
• indicate who might regard this form of model as a
  meaningful and useful abstraction that captures
  its essential aspects.