Relational Data Modeling Notes

Relational Data Modeling

Overview

• Conceptual Modeling transforming into a Relational Schema.
• Standard top-down approach:
  • Conceptual Model → Relational Schema → Relational Database.
• Topics covered:
  • Developing a Conceptual Model.
  • Producing a Relational Model from a Conceptual Model.

The Parts of the Two Models

Conceptual Model:

• Concepts:
  • Entities and Attributes.
  • Relationships:
    • One-to-one.
    • One-to-many.
    • Many-to-many.
  • Association entities:
    • Relationships that have data associated with them.
  • Hierarchies.

Relational Model:

• Concepts:
  • Relations and attributes.
  • Primary keys.
  • Foreign keys.
  • Constraints.
  • Data integrity.
    • Assumes the target database will be relational.

Quick Review of Relationships

• Relationships exemplified:
  • Works in: EMP works in DEPT (1..* to 1..1).
  • Has connection with: EMP has connection with DEPENDENT (1..1 to 0..*).
• Entities:
  • EMP:
    • Attributes: NI Number, Name, Address, Salary, DoB.
  • DEPT:
    • Attributes: Code, Name.
  • DEPENDANT:
    • Attributes: Name, DoB.

Conceptual Model to 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 an attribute in a relation that contains primary keys from another relation.
• The following may appear 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

Example Relation

• Example Table with Attributes: UcasNo, Name, Title, Initial, College.
• Primary Key:
  • A unique identifier for each record/row.
  • Each relation must have a primary key.
  • R: key \rightarrow \mathbb{F} \times … \times \mathbb{F}, a Cartesian product of the columns (features).

Examples of Relationships (1)

• one-to-one.
• one-to-many.
• many-to-many.
• Examples:
  • TEAM --Owns-- COACH (1..1 to 0..*).
  • LECTURER --Teaches-- MODULE (1..* to 0..*).
• Entities:
  • TEAM: teamId.
  • COACH: coachId.
  • PERSON: PersonId.
  • CAR: carReg.
  • LECTURER: lectId.
  • MODULE: modCode.

Quick Quiz 1

• Relationships from the previous case study:
  • EMP --Works in-- DEPT (1..* to 1..1).
  • EMP --Has connection with-- DEPENDANT (1..1 to 0..*).
  • EMP --Supervises-- EMP (0..1 to 0..*).
  • DEPT --Manages-- EMP (1..1 to 0..1).
  • Note: MANAGES has attribute Start Date.

Examples of Relationships (2)

• recursive relationship (reflexive association).
• generalisation/specialisation hierarchies.
• Examples:
  • PERSON --Married_to-- PERSON (0..1 to 0..1).
  • PERSON --Parent-- PERSON (0..* to 0..*).
• Specializations of Person:
  • Student:
    • Attributes: Name, Course, UG Year, Taught.
  • PG:
    • Attributes: First Degree, Child.

Relationships within the Schema

• Foreign keys link one relation to another.
• Example of one-to-one relationship:
  • People:
    • Attributes: id (Primary key), Name, email, dob.
  • Departments:
    • Attributes: id (Primary key), name, manager_id (Foreign key).
  • The manager_id in Departments is a foreign key referencing the id in People.

Many to Many Relationships

• E.g., A student takes 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.
• Example:
  • Students:
    • Attributes: id (Primary key), Name.
  • Modules:
    • Attributes: id (Primary Key), Module.
  • students_modules (link relation):
    • Attributes: studentid, moduleid.
    • In this case, for the link relation, we have a composite primary key, consisting of {studentid, moduleid}.

Quick Quiz 2

• Identify the primary key and any potential foreign keys in this relation.
• Room Booking:
  • Attributes: Date, Time, RoomBooked, PersonBooking.
    Note: It is not specified what the primary key and foreign keys should be.

Reflexive Relationships

• Example:
  • Person:
    • Attributes: id (Primary key), Name, email, tutor_id (Foreign key).
  • Links to Person.

Case study: conceptual data model

• EMP --Works in-- DEPT (1..* to 1..1).
• EMP --Has connection with-- DEPENDANT (1..1 to 0..*).
• EMP --Supervises-- EMP (0..1 to 0..*).
• DEPT --Manages-- EMP (1..1 to 0..1).
  • Note: MANAGES has attribute Start Date.

Case study: conceptual data model (Association entity)

• Association entity becomes a separate/full entity.
• EMP --Works in-- DEPT (1..* to 1..1).
• EMP --Has relationship with-- DEPENDANT (1..1 to 0..*).
  • Note: Association becomes entity.
• EMP --Supervises-- EMP (0..1 to 0..*).
• DEPT --Manages-- EMP (1..1 to 0..1).
  • Note: MANAGES has attribute Start Date.

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 modeling levels.

The Relational Schema (1)

• Create relations and add attributes.
  • EMP (NIN, Name, Address).
  • DEPT (DCode, DeptName).
  • DEPENDANT (Name, Gender, DoB).
  • MANAGES (StartDate).

The Relational Schema (2)

• Map relationships (foreign keys).
• Add primary keys (underlined) if not present.
  • EMP (NIN, Name, Address, DCode, Supervisor).
  • DEPT (DCode, DeptName).
  • DEPENDANT (NIN, Name, Gender, DoB).
  • MANAGES (NIN, DCode, StartDate).

Quick Quiz 3

• Identify (or add) the Primary Keys in the following relations.
  • AUTHOR (Name, DoB).
  • BOOK (ISBN, Title, Publisher).
• How would we add a Foreign Key to implement a “wrote” relationship between the two relations?
  • Add ISBN to Author, ISBN would then become a Foreign Key.

The Relational Data Model: Characteristics

• Properties of a relation:
  • Each relation in a model 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.

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.

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.

Why Structure Data?

• To more closely model many problems in the real world.
• To enable constraints to be specified to avoid inconsistent data.
• But also to avoid anomalies leading to inconsistent data.
• 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 want to add a person to the database, but they do not own a car. We cannot do this as we would have a null in Car_Reg.
• Example of unstructured, flat relation: CarOwner (PersonId, Name, Car_Reg, Make).
  • PK= {PersonId, CarReg}.

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.

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.
• Takahashi, M., The Manga Guide to Databases, 2009.