P

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.