Lecture 3: The relational model

1. Lecture 3: The relational model www.cl.cam.ac.uk/Teaching/current/Databases/ E.F. Codd

2. Today’s lecture • What’s the relational model? • What’s SQL? • How do we create databases in SQL? • How do we convert E/R models to a relational model? • How do we enforce real-life constraints in a relational database?

3. Why study the relational model? • It’s the dominant model in the marketplace • Vendors: Microsoft, Oracle, IBM, … • SQL is the industrial realisation of the relational model • SQL has been standardised (several times) • Most of the commercial systems have substantially extended the standard!

4. The relational model: Early history • Proposed by E.F. Codd (IBM San José) ~1970 • Prior to this the dominant model was the network model (CODASYL) • Mid 70’s: prototypes • Sequel at IBM San José • INGRES at UC Berkeley (M. Stonebraker) • PRTV at IBM UK • 1976-: SystemR at IBM San José • Transactions (J. Gray et al.) • Query optimiser (P. Selinger et al.) • Extended -testing (Boeing et al.) • 1978/9-: CODD at Cambridge Computer Lab • Extended relational algebra query language (K. Moody)

5. The relational model: Basics • A relationaldatabase is a collection of relations • A relation consists of two parts: • Relationinstance: a table, with columns and rows • Relationschema: Specifies the name of the relation, plus the name and type of each column • Can think of a relation instance as a set of rows or tuples

6. Relation name Field name Domain Examples • Relation schema Students(sid:string, name:string, login:string, age:integer) • In general R(A1:1, …, An:n)

7. Fields Field names Tuples Examples • Relation instance

8. Relational terminology • A domain is a set of values. All domains in a relation must be atomic (indivisible) • Given a relation R=R(A1:1, …, An:n), R is said to have arity (degree) n • Given a relation instance, its cardinality is the number of rows • For example, in Students, cardinality=5 (arity=4)

9. Relations and sets • A relation R=R(A1:1, …, An:n) can be defined more formally as R  1    n • Thus a relation is a set of tuples, so there is no ordering of the tuples in the table • Moreover, there are no duplicate rows in the table

10. Keys • Given a relation R=R(A1:1, …, An:n) a (candidate) key is a subset of fields K{A1, …, An} that acts as a unique identifier for each tuple in the relation instance • We annotate the schema accordingly, e.g. R=R(A1:1, …, An:n)

11. SQL • SQL is the ubiquitous language for relational databases • Standardised by ANSI/ISO in 1992: SQL/92 • Part of SQL is a Data Definition Language (DDL)that supports the creation, deletion and modification of tables

12. Creating tables • The CREATE TABLE statement, e.g.CREATE TABLE Students (sid CHAR(20), name CHAR(20), login CHAR(10), age INTEGER); • Note that the domain of each field is specified and enforced by the DBMS

13. Removing and altering tables • We can delete both the schema information and all the tuples, e.g. DROP TABLE Students; • We can alter existing schemas, e.g. adding an extra field ALTER TABLE Students ADD COLUMN matric INTEGER;

14. Adding and deleting tuples • Can insert tuples into a table, e.g.INSERT INTO Students(sid,name,login,age) VALUES (“1006”, “Julia”, “jfg”, 21); • Can remove tuples satisfying certain conditions, e.g. DELETE FROM Students WHERE name=“Myleene”;

15. Querying relations • We can list the current contents of a table with a querySELECT * FROM Students; • We can add conditions to the query, e.g. SELECT * FROM Students S WHERE S.age=23;

16. From E/R diagrams to relations • The E/R model is convenient for representing the high-level database design • Given an E/R diagram there is a reasonably straightforward method to generate a relation schema that corresponds to the E/R design

17. Name NI dob Employees Entity types to relations • A (strong) entity type maps to a relation schema in the obvious way, e.g. is mapped to the relation schema Employees(NI:1, Name:2, dob:3)

18. Relationship types to relations • Given a relationship type, we generate a relation schema with fields consisting of: • The keys of each associated entity type • Any associated relationship attributes

19. since Works_in Name dname NI DID dob budget Employees Departments Example is mapped to the relation schema: Works_in(NI:1, DID:2, since:3) M N

20. name dob NI Employees supervisor subordinate Reports-to Recursive relationship sets • Just pick appropriate field names! E.g. is mapped to Reports_to(sup_NI:1, sub_NI: 1)

21. Weak entity types • Given a weak entity type, W, we generate a relation schema with fields consisting of the attributes of W, and the primary key attributes of the owner entity type • For any relationship in which W appears we generate a relation schema which must take as the key for W all of its key attributes, including those from its owner set

22. Example pName age is mapped to the following schema: Dependents(pName:1, NI:2, age:3) Policy(pName:1, NI:2, Cost:4) Cost Name NI N Policy 1 Dependents Employees Alternatively: Policy(pName :1, NI :2, age :3, Cost :4)

23. Two choices: 3 relations (Employees, Temp_Emp and Contract_Emp) 2 relations (Temp_Emp and Contract_Emp) ISA Hierarchies Name NI dob Employees ISA cid hours rate Contract_Emp Temp_Emp

24. Other features • Other features can also be mapped from the E/R model to relational model, including • Constraints • Aggregation • The textbooks cover this material in detail

25. Summary You should now understand: • Relational model • Relation schema, relation instance, … • How to create/update/delete tables in SQL/92 • How to convert E/R model to a relational schema Next lecture: Relational Algebra