Lecture 3 The relational model E F Codd

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

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? 2

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! 3

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 -: System R 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) 4

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

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

Examples Fields • Relation instance Field names Tuples login age sid name 1001 1002 Myleene MK Danny DF 23 22 1003 1004 1005 Noel NS Suzanne SS Johnny JS 21 20 23 7

Relational terminology • A domain is a set of values. All domains in a relation must be atomic (indivisible) • Given a relation R=R(A 1: 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) 8

Relations and sets • A relation R=R(A 1: 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 9

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

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 11

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 12

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; 13

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”; 14

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

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 16

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

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 18

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

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

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 21

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

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

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 24

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 25