Lecture 3: The Relational Model

Title slideToday’s lecture expanded on last week’s material on Entity-Relationship modelling, and then set out the basic elements of the Relational Model for structured data. While ER diagrams provide a conceptual language for describing things as they are, and have applications outside databases for general organisation and management, the relational model is explicitly intended as a mathematically precise scheme for the computer-assisted creation and querying of large datasets.

On ER modelling the lecture covered the following areas:

  • Constrained relationships: many-to-one, one-to-many, many-to-many;
  • Key constraints in ER diagrams that capture those;
  • Participation constraints: total or partial;
  • 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, read the book chapter, find online tutorials about these features. You will get to explore all of them further in the tutorial exercises over the next few weeks; and many things will become clearer once you get to use them in practice.

Links: Slides for Lecture 3; Recording

Homework

Read This

The rest of Chapter 2, from Section 2.5 to the end, of Database Management Systems by Ramakrishnan and Gehrke.

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
“The body of the final machine occupies an area that is 22,254 cells wide and 55,601 cells high, composed of over 45 million nonzero cells in its unsheathed form. The data tape is 208 million cells long, and self-replication is estimated to take at least 1.7×1018 time steps.”