44220: Database Design & Implementation Modelling the ‘Real’ World

1. 44220: Database Design & ImplementationModelling the ‘Real’ World Ian Perry Room: C49 Ext.: 7287 E-mail: I.P.Perry@hull.ac.uk http://itsy.co.uk/ac/0506/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 world’s richness. • no model is complete! • All models require decisions about: • what to include & what to exclude? • These decisions are represent someone’s view of a particular reality, i.e.; • what is important, and MUST be included. • what is not important, and 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%) [01 Mar, 2006] • 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%) [28 Apr, 2006] • 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 Week’s 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 half dozen ‘things’ that exist in the ‘real’ world. • University, Computer, Bank, Shoe Shop, Doctor’s Surgery, 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. • DO NOT arrive at the workshop un-prepared!