44220: Database Design & Implementation Logical Data Modelling

1. 44220: Database Design & ImplementationLogical Data Modelling Ian Perry Room: C49 Tel Ext.: 7287 E-mail: I.P.Perry@hull.ac.uk http://itsy.co.uk/ac/0506/sem2/44220_DDI/

2. Remember the ‘Stack’

3. What is a Logical Data Model? • A ‘robust’ representation of the initial decisions made when building our Conceptual Data Model, which was composed of: • Entities • Attributes • Relationships • When I say ‘robust’ I mean that this model MUST ‘perform’ well with respect to a specific style/type of software.

4. Database Theories & Software • Logical Modelling is hardware independent; the match to ‘type’ of software is our only concern; e.g.: • hierarchy Hierarchical DBMS • relations Relational DBMS • objects Object-based DBMS • Whichever Database Theory is ‘chosen’ they generally separate the following three things: • Data Structure • Defining how the data will be stored. • Data Manipulation • Defining how data is to be added/updated/deleted. • Data Query • Defining how ‘questions’ can be asked of the stored data.

5. Database Theory = Relational Model • First proposed by Dr. E. F. Codd in June 1970. • Codd E F, (1970), A Relational Model of Data for Large Shared Data Banks, Communications of the ACM, Vol. 13, No. 6, Pgs 377 – 387. • Codd's model is now accepted as the definitive model for relational database management systems (RDBMS). • Structured English Query Language ("SEQUEL") was developed by IBM Corporation, Inc., to use Codd's model. • SEQUEL later became SQL. • In 1979, Relational Software, Inc. (now Oracle Corporation) introduced the first commercial implementation of SQL. • SQL is still (i.e. after more than 25 Years) the most widely used RDBMS manipulation & query language.

6. Relational Modelling Language Don’t get too hung up on Codd’s strange language, but that is what you often read in Relational Database Design books.

7. Relations look very like Entities • Staff (SCode, Name, Address, DoB, DoE) • Contract (CCode, Site, Begin, End, Super) • NB. You should only ‘use’ the above tabular form in order to check logic; i.e. Tables are NOT Relations.

8. Attributes • Attribute Values are held in Data Cells: • must be atomic (i.e. simple) values only. • Set of Attribute Values eligible for entry in a Data Cell: • is known as an Attribute’s Domain. • The Relational Model is weak at explicitly modelling relationships: • Relations MUST share an Attribute Domain if a relationship is present/possible.

9. Data Access • Data is retrieved by reference to name (of an Attribute) & value (held in a Data Cell) only. • Most important Attribute(s) is know as the ‘Key’, of which there are two types. • Primary Key: • special Attribute(s) used to control access to Tuples (i.e. rows) of data. • Foreign Key: • used, as logical pointers, instead of lines to represent associations between Relations.

10. Relations are NOT Tables • Tables are ‘possible’ data values presented in rows & columns. • and are very useful in order to check logic. • Relations must obey rules (Codd’s 12 Rules); e.g. we cannot have: • Duplicates: • each Tuple (row) must be unique. • Row or Column dependencies • there is no hidden meaning from location/sequence of Tuples (rows) and/or Attributes (columns). • etc.

11. Keys to Data Integrity • Rule: • each Tuple (i.e. row) must be unique. • Need a way to discriminate between Tuples: • each Relation MUST have a Primary Key • May be many candidates for the job of Primary Key, so select on basis of: • uniqueness AND/OR minimality • Primary Keys with multiple Attributes: • are know as composite keys.

12. Other things about Attributes • Attributes that are part of the Primary Key: • are known as Prime Attributes. • Candidatesnotselected as part of the Primary Key: • are known as Secondary Keys. • Secondary Attributes which serve as the Primary Key in another Relation: • are known as Foreign Keys.

13. Rules for Integrity • Entity Integrity: • No Attribute that is a member of a Primary Key can assume a ‘null’ (i.e. empty) value. • Else, how could we discriminate between Tuples? • Referential Integrity: • A Foreign Key Attribute must take values that are either ‘null’, or from same Domain as the Relation in which this Attribute acts as the Primary Key. • Else, we will lose linkages between Relations.

14. It can be difficult to check all of Codd’s Rules if you don’t express each Relation as a Table. • Staff (SCode, Name, Address, DoB, DoE) • Contract (CCode, Site, Begin, End, Super) BUT always remember that Tables are NOT Relations.

15. In Summary • Logical Data Modelling: • is 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.

16. And • Relations? • are defined by a list of Attributes. • relationships via Primary/Foreign Key mechanism. • Attributes? • must be distinctly named. • ordering is irrelevant. • data entries should be atomic, and of the same type, and from the same domain. • Tuples? • must be unique (so need a Key). • ordering is irrelevant.

17. And Finally! • I know what you are thinking: • the Relation Definitions of a Logical Data Model looks to be exactly the same as the Entity Definitions of a Conceptual Data Model. • However: • it is highly unlikely that your Logical Data Model will be exactly the same as your Conceptual Data Model. • You will have made some logical errors! • Which we will attempt to ‘fix’ in next week’s lecture & workshop sessions.

18. This Week’s Workshop • The purpose of this week’s Workshop is to explore the formality of a developing a logical data model, that conforms to the ‘rules’ of Codd’s Relational Model. • Five Questions, exploring: • the ‘language’ and ‘rules’ of Relational Modelling, and the ‘definition’ of Relations. • Make sure that you ‘have a go’ at all 5 questions in advance of the Workshop.