Title slide
Slides : Recording

Tuesday’s lecture revisited ideas on Entity-Relationship modelling from last week, introduced some refinements that can enrich an ER diagram, and then set out basic elements of the Relational Model for structured data.

These additional features for ER diagrams make it possible to capture high-level properties that we expect of the data being gathered. Having a precise language for these is helpful for design, planning, and eventual implementation of an effective database.

There are lots of such features: this course covers the following.

  • 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

While ER diagrams provide a conceptual language for describing things as they are, and can be used 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. This lecture set out the following basic elements of relational modelling.

  • 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 of Lecture 3

Book cover

Read This

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

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

“[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

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.”
Photograph from Computer History Museum archive Programmers, 1957

Four programmers at the operator’s console of a Univac I: Donald Cropper, K. C. Krishnan, Grace Hopper, and Norman Rothberg.

Source: Computer History Museum, Mountain View, CA

Cavalry charge

Poniatowski’s Last Charge at Leipzig
Richard Caton Woodville, 1912

Source: Tate Gallery

Lecture 3: The Relational Model