Transcript Lecture 3: The relational model E.F. Codd www.cl.cam.ac.uk/Teaching/current/Databases/ Today’s lecture • • • • What’s the relational model? What’s SQL? How do we create databases in SQL? How do we convert.

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(A1:1, …, An:n)
6
Examples
Fields
• Relation instance
Field
names
Tuples
login age
sid
name
1001
Myleene MK
23
1002
Danny
DF
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(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)
8
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
9
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)
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
pName
Cost
1
Policy
N
age
Dependents
is mapped to the following schema:
Dependents(pName:1, NI:2, age:3)
Policy(pName:1, NI:2, Cost:4)
Alternatively:
Policy(pName :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