Lecture 3: The relational model

1
Lecture 3 The relational model
E.F. Codd

• www.cl.cam.ac.uk/Teaching/current/Databases/

2
Todays lecture

• Whats the relational model?
• Whats 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?

• Its 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 70s 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)

5
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 rows
  or tuples

6
Examples

• Relation schema
• Students(sidstring, namestring,
• loginstring, ageinteger)
• In general
• R(A1?1, , An?n)

7
Examples

• Relation instance

sid name login age
1001 Myleene MK 23
1002 Danny DF 22
1003 Noel NS 21
1004 Suzanne SS 20
1005 Johnny JS 23
8
Relational terminology

• A domain is a set of values. All domains in a
  relation must be atomic (indivisible)
• Given a relation RR(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, cardinality5 (arity4)

9
Relations and sets

• A relation RR(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 RR(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.
  RR(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 nameMyleene

15
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.age23

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
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
Example

• is mapped to the relation schema
• Works_in(NI?1, DID?2, since?3)

M
N
20
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
ISA Hierarchies

• Two choices
• 3 relations
• (Employees, Temp_Emp and Contract_Emp)
• 2 relations
• (Temp_Emp and Contract_Emp)

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