Databases
Lecture 6 – The Relational Model
Bernhard Reus
1
Relational DBMS Products
2
© Bernhard Reus, University of Sussex, 2004-16
RDBMS Market Share 2009
3
So it’s all about Relations In this lecture:
[C&B, Ch. 3]
• What is a data model and why do we need it?
• Terminology of relational model
• How tables represent relations
• Properties of relations
• About candidate, primary and foreign keys and integrity
4
© Bernhard Reus, University of Sussex, 2004-16
DBMS come with a …
• Data Definition Language (DDL)
define relation schemes and constraints
• Data Manipulation Language (DML) insert, update,
procedural
non-procedural (declarative)
• Data Control Language (DCL)
delete, retrieve
tell the system what and how to get it
tell the system what, not how
security, accessibility
5
Need for a Data Model
• DDL is low level, need for a higher-level description:
Data Model: “An integrated collection of concepts for describing and manipulating data, relationships between data, and constraints on the data in an organization.”
Connolly & Begg: Database Systems
6
© Bernhard Reus, University of Sussex, 2004-16
Record-Based Data Models
• Network Data Model
relations between data as pointers (graph structure) (e.g. Comp.Associate’s IDMS/R)
• Hierarchical Data Model
restricted network model: graphs are trees (e.g. IBM’s IMS)
• Relational Data Model (this lecture)
Disadvantage of the first two:
not enough data independence!
7
Relational Model • Most Databases use it (not all).
• Based on Codd’s seminal paper of 1970. (see Lecture 2: History)
• Advantage:
– sound mathematical theory of relations – Data independence
• Three areas: data structure, data integrity, data manipulation.
8
© Bernhard Reus, University of Sussex, 2004-16
Relational Data Model
n Relation
Table with columns and rows
n Attribute named column
n Tuple
row of a relation
n Domain
set of allowable values for an attribute (number, string etc)
Branch
branch No
005
007
003
004
002
street
22 Deer Rd
16 Argyll St
163 Main St
32 Manse Rd
56 Clove St
city
London
Aberdeen
Glasgow
Bristol
London
postcode
SW1 4EH
AB2 3SU
G11 9QX
BS99 1NZ
NW10 6EU
9
Properties of Relations
• Degree number of
attributes
• Cardinality number of tuples
• Relational Database
collection of (appropriate) relations
Branch
branch No
street
city
postcode
005
22 Deer Rd
London
SW1 4EH
007
16 Argyll St
Aberdeen
AB2 3SU
003
163 Main St
Glasgow
G11 9QX
040
32 Manse Rd
Bristol
BS99 1NZ
002
56 Clove St
London
NW10 6EU
Degree = 4
10
© Bernhard Reus, University of Sussex, 2004-16
Cardinality = 5
Relation Schemas
• Relation Schema is a named relation defined by a set of attributes, e.g.:
Branch(branchNo,street,city,postcode)
• Later we will also say which domains the attributes have. The available domains depend on the DDL.
• Relational Database Schema is a set of relation schemas each with a distinct name.
• Relation Instance is a tuple of a relation compliant with its schema, e.g.
((branchNo,5),(street,22 Deer Rd),(city,London),(postcode,SW1 4EH) )
11
Relation Properties
• Relation name is unique.
• Attribute names are unique only per relation.
• Values of one attribute are of the same domain.
• In mathematics tuples are distinct (sets!), there are no duplicate tuples. In database systems there may be duplicate tuples.
• Order of attributes has no significance.
• Order of tuples has no significance semantically, but may influence efficiency.
12
© Bernhard Reus, University of Sussex, 2004-16
Unknown value
• Sometimes data values are yet unknown or not applicable for a specific tuple.
• Then a special “value” is used : “null”
• representing the absence of a value
• to deal with incomplete or exceptional data.
• null is NOT a (proper) value.
(which leads to some difficulties discussed later)
13
Keys
• Keys are used to identify the unique rows (tuples)
Client
fname
Michael
Michael
Janet
lname
Owen
Jackson
Jackson
city
a possible key
Only if there are no two clients
with the same name
Liverpool
Neverland
not a key not a key
not a key city not a good key either, why?
14
L.A.
property
penthouse
ranch
penthouse
© Bernhard Reus, University of Sussex, 2004-16
Relational Keys
• (Super)key is an attribute or a set of attributes that uniquely identify a tuple within a relation.
• Candidate Key is a superkey such that no proper subset is a superkey (minimal superkey).
• Primary key is the candidate key in use in a relation. In a relation schema it is usually underlined.
• Foreign key is an attribute or a set of attributes within one relation that matches the candidate key of some other (maybe the same) relation.
15
Keys in Relation Schemas
• In a relation schema the primary key attribute(s) are underlined and usually listed first.
• Example: Branch(branchNo,street,city,postcode) Client(fname,lname,city,property)
16
© Bernhard Reus, University of Sussex, 2004-16
005
007
003
Foreign Keys
Branch
branch No
street
22 Deer Rd
16 Agyll St
163 Main St
city
London
Aberdeen
Glasgow
postcode
SW1 4EH
AB2 3SU
G11 9QX
Staff
staffNo
l21
37
14
fname
John
Ann
David
lname
White
Beech
Ford
jobTitle
Manager
Assistant
Supervisor
dob
1-Oct-45
10-Nov-60
24-Mar-58
salary
30,000
12,000
18,000
branchNo
005
007
003
primary key foreign key 17
Relational Integrity
• When manipulating data (insert, update, delete) make sure that data is accurate (consistent).
• Integrity rules are in force – Domain constraints
– Entity integrity
– Referential integrity
– Enterprise constraints.
18
© Bernhard Reus, University of Sussex, 2004-16
Relational Integrity
• Domain constraints: data values must live in specified domain.
• Entity integrity: no value of a primary key can be null.
• Referential integrity: if there is a foreign key then its value must match a primary key in the referenced relation or must be null.
• Enterprise constraints: additional rules specified by users or administrators.
19
Referential Integrity Example
Branch
branch No
005
007
003
street
22 Deer Rd
16 Agyll St
163 Main St
city
London
Aberdeen
Glasgow
postcode
SW1 4EH
AB2 3SU
G11 9QX
Staff
staffNo
121
37
14
fname
John
Ann
David
lname
White
Beech
Ford
jobTitle
Manager
Assistant
Supervisor
dob
1-Oct-45
10-Nov-60
24-Mar-58
salary
30,000
12,000
18,000
branchNo
005
́
004
null
foreign key
20
© Bernhard Reus, University of Sussex, 2004-16
Referential Integrity
• Constraints are in place to enforce Referential Integrity.
• As reaction to an operation violating referential integrity constraints there are several options:
– Restrict: do not perform operation
– Cascade: let changes flow through to keep integrity
– Nullify: make violating values null to keep integrity
– Triggers: specific user-defined action to keep integrity
21
Cascade Example
b ́ranchNo 005 006
street
city
postcode
22 Deer Rd
London
SW1 4EH
007
16 Agyll St
Aberdeen
AB2 3SU
003
163 Main St
Glasgow
G11 9QX
Branch
Update London branchNo to 006
Delete branch in Aberdeen
Staff
staffNo
l21
37
14
fname
John
Ann
David
lname
White
Beech
Ford
position
Manager
Assistant
Supervisor
dob
1-Oct-45
10-Nov-60
24-Mar-58
salary
30,000
12,000
18,000
branchNo
́
005
006
003
007
22
© Bernhard Reus, University of Sussex, 2004-16
Branch
Update
London branchNo to 006
Delete branch in Aberdeen
́branchNo
005
007
003
Nullify Example
006
street
22 Deer Rd
16 Agyll St
163 Main St
city
London
Aberdeen
Glasgow
postcode
SW1 4EH
AB2 3SU
G11 9QX
Staff
staffNo
l21
37
14
fname
John
Ann
David
lname
White
Beech
Ford
position
Manager
Assistant
Supervisor
dob
1-Oct-45
10-Nov-60
24-Mar-58
salary
30,000
12,000
18,000
branchNo
0 ́05
003
007 NULL ́
NULL
23
Diagrams for Relational Database Schema
Schema Branch(branchNo,street,city,postcode)
Staff(staffNo,fname,lname,position,dob,salary,branchNo) Foreign key branchNo references Branch(branchNo)
24
Staff
Branch
PK
FK
staffNo
fname
lname
position
dob
salary
branchNo
PK
branchNo
street
city
postcode
© Bernhard Reus, University of Sussex, 2004-16
Views in the Relational Model • A view is a virtual or derived relation
• dynamically created from underlying (base) relations.
• Views provide:
– customization for different users – logical data independence (why?) – security (why?)
25
© Bernhard Reus, University of Sussex, 2004-16