Lecture 3: The Relational Model

Title slideThis morning’s lecture introduced some refinements to the Entity-Relationship modelling of Lecture 2 and then set out the basics of the Relational Model for structured data. Where ER diagrams aim to give a conceptual language for describing things as they are, and have applications well outside databases for general organisation and management, the relational model is explicitly intended as a mathematically precise scheme for the computer-assisted building and querying of large datasets.

On ER modelling the lecture covered the following areas:

  • Relationships of higher arity: binary, ternary, n-ary;
  • Cardinality of relationships: many-to-one, one-to-many, many-to-many;
  • Key constraints and participation constraints in ER diagrams; partial and total participation;
  • Weak entities, their identifying relationship and identifying owner;
  • Entity hierarchies: superclasses, subclasses, specialisation and inheritance.

On the relational model the lecture covered the following areas:

  • Relations, schemas and instances: fields/attributes/columns and tuples/records/rows;
  • SQL and its Data Definition Language DDL;
  • Table declarations, attribute declarations, and integrity constraints: primary key constraints and foreign key constraints.

That’s quite a lot of material, and you could reasonably spend some time reviewing it: go through the slides, look at online tutorials about these features, or follow the lecture video.

Link: Slides for Lecture 3; Video of Lecture 3

Homework

Read the remaining sections of the Ramakrishnan and Gehrke handout from last lecture: §§2.5 to the end of the chapter.

Download the first tutorial worksheet, read through it, and start the exercises.

Link: Exercises for Tutorial 1

References

“[Codd’s] relational model was at first very controversial; people thought that the model was too simplistic and that it could never give good performance.”

Jim Gray in Database Systems: A Textbook Case of Research Paying Off

Codd's original diagram of a relational database The Relational Database
IBM100: Icons of Progress
Introduced by Edgar F. Codd in 1970, working at the IBM San Jose research lab in California, this mathematical model for database design ended up transforming the industry.
Link: The Relational Database
Picture of Edgar F. Codd Edgar F. (Ted) Codd
ACM Turing Award 1981
For his fundamental and continuing contributions to the theory and practice of database management systems.
Link: ACM Turing Award Citation
A simple configuration in Codd's cellular automaton Codd’s Cellular Automaton
An 8-state machine that is computationally complete and capable of self-replication.
Tim Hutton, Codd’s Self-Replicating Computer. Artificial Life 16(2):99–117, 2010.
Links: Publisher page; Author copy; Wikipedia