Relational Data Modelling: A Comprehensive Overview

CO887 Relational Data Modelling Peter Rodgers 1

Overview This lecture looks at Conceptual modelling and how to turn a Conceptual Model into a Relational Schema A standard top down approach to produce a database is: Conceptual Model Relational Schema Relational Database Today will look at: Developing a Conceptual Model Producing a Relational Model from a Conceptual Model 2

The Parts of the two models Conceptual model concepts concepts Entities and Attributes Relationships one-to-one one-to-many many-to-many Association Entities Relationships that have data associated with them Hierarchies Modelling without considering the target database The relational model concepts Relations and attributes Primary keys Foreign Keys Constraints Data integrity Assumes the target database will be relational 3

Conceptual model to a relational database model Entities become relations Often one entity becomes a single relation However Multiple entities may merge into single relations More commonly, new relations can be introduced Relationships become foreign keys A foreign key is a attribute in a relation that contains primary keys from another relation These may be included in the conceptual model, but cannot appear in the relational model and so need to be resolved: Many to Many Relationships Inheritance Hierarchies Association Entities 4

Example relation UcasNo Name Title Initial College 1234 5678 9012 3456 7890 Smith Jones Bloggs Brown Jones Ms Ms Mr Mr Miss AX BY CZ PQ AX D E K R E Primary Key a unique identifier for each record/row. Each relation must have a PK 5

Examples of relationships (1) one-to-one 1..1 Coaches 1..1 COACH coachId TEAM teamId one-to-many 1..1 0..* Owns PERSON PersonId CAR carReg many-to-many 1..* Teaches 0..* MODULE modCode LECTURER lectId 6

Examples of relationships (2) recursive relationship (reflexive association) 0..1 Married_to PERSON personId 0..1 generalisation/specialisation hierarchies Student Name Course Parent Child UG Year Taught PG First Degree 7

Relationships within the Schema Foreign keys link one relation to another. Example of one-to-one relationship: Foreign key People Departments id Name email dob id name manager_id 1 Bob bob@bo b.com 1990-12- 01 25 Computing 2 2 Alice alice@ali ce.com 1984-05- 06 29 Electronics 1 Primary key 8

Many to many relationships Eg. A student has many modules, a module has many students. Requires adding a link-relation, whose sole purpose is to link relations together using 2 one-to-many relationships Students Modules students_modules link relation id Name student_id module_id 14 id 14 Module Computing 1 Bob 1 2 Alice 2 14 27 Electronics 1 27 2 27 In this case, for the link relation, we have a composite primary key, consisting of {student_id,module_id} 9

Exercise Identify the primary key and any potential foreign keys in this relation Room Booking Date 23/06/2015 23/06/2015 27/07/2015 23/06/2015 Time 10:00 12:00 10:00 10:00 Room_Booked CC03 CC03 CC01 CC01 Person_Booking pjr ec34 pjr pjr 10

Reflexive Relationships Person id Name email bob@bob.com tutor_id 1 Bob NULL 2 Alice alice@alice.com 1 Links to Primary key Foreign key 11

Generalisation / specialisation in databases The standard relational schema does not support hierarchies. To resolve them we can: Create a relation for each child and a relation for the parent Create a relation for each child where both include the parent attributes Create a single relation with attributes from each specialisation and also the parent Null values will be used where there is no relevant data 12

Case study: conceptual data model Supervises 0..* 1..* Works in 1..1 EMP DEPT NI Number Name Address Salary Gender DoB Code Name 0..1 1..1 Manages 0..1 MANAGES Start Date 1..1 Has relationship with 0..* DEPENDANT Name Gender DoB 13

Case study: conceptual data model (revised) Association entity becomes a separate/full entity Supervises 0..* 1..* Works in 1..1 EMP DEPT NI Number Name Address Salary Gender DoB Code Name Manages 1..1 0..1 1..1 0..1 0..1 MANAGES Start Date 1..1 Has relationship with 0..* DEPENDANT Name Gender DoB 14

The Relational Schema There are a few ways to represent a relational schema Text based Graphical based UML can be used for both conceptual and logical modelling levels 15

The Relational Schema (1) Create relations and add attributes EMP(NINo, Name, Address) DEPT(DCode, DeptName) DEPENDANT(Name, Gender, DoB) MANAGES(StartDate) 16

The Relational Schema (2) Add primary keys (underlined) if not present and Map relationships (foreign keys) EMP(NINo, Name, Address, DCode, Supervisor) DEPT(DCode, DeptName) DEPENDANT(NINo, Name, Gender, DoB) MANAGES(NINo, DCode, StartDate) 17

The relational data model: Characteristics properties of a relation each relation has a distinct name (object identity) each attribute in a relation has a distinct name all values of an attribute are drawn from the same domain attribute values must be atomic ordering of attributes in a relation is of no significance ordering of elements in a relation is of no significance elements in a relation must be distinct; primary key (unique row identifier) must not have a null value primary key may be composite non-key attributes may have null values 18

Relational model: Integrity Constraints The definition of a schema encompasses integrity constraints (rules to help ensure that the database does not have invalid values and configurations) The relational model also has domain constraints It must be an atomic value from the attribute domain, for example, Integer Other constraints may be: application/database specific - equivalent to enterprise or business rules For example, for a club membership db, age must be over 18 19

Integrity Constraints Entity Integrity Rule by definition, all rows in a relation are distinct; so, primary keys should have distinct values no primary key (or part thereof) should have a null value Referential Integrity Rule constraint specified on two relations the value of a foreign key must be the value of an existing primary key or wholly null foreign keys may be simple or composite the domains of the FK and the PK must be the same 20

Why Structure Data? To more closely model the real world To enable constraints to be specified to avoid inconsistent data But also to avoid anomalies leading to inconsistent data Car_Owner (Unstructured, Flat Relation) Person_Id Name Car_Reg Make 1 Fred Bloggs HT61 IYT Ford 2 Amanda Panda RE58 POI Peugeot 3 Zeke Faux CT46 NRE Ford 1 Fred Bloggs TY92 THY Ford PK= {Person_Id, Car_Reg} Delete Anomalies. If owner 2 sells car RE58 POI, we also remove the owner, so we have lost more data than we wanted Update Anomalies. The owner with Person_Id 1 changes name to Freda Bloggs. If we only update the first row we will have inconsistent data. Insert Anomalies. We would like to add a person to the database, but they currently do not own a car. We cannot do this as we would have a null in Car_Reg. 21

Conclusion Relational databases are based on the relational model of data; this implies: A structural aspect: data is perceived as relations and nothing but relations An integrity aspect: Relations must satisfy integrity constraints We structure data to model the real world to help avoid inconsistent data 22

Reading Connolly, C. and Begg, C., Database Solutions: A step-by-step guide to building databases (chapters 2, 9, 10 and maybe chapter 6) CJ Date, An Introduction to Database Systems, Chapter 13 23