Alternative Terminology for Relational

Model

1
 Properties of Relations
• Relation name is distinct from all other relation
names in relational schema.

• Each cell of relation contains exactly one

atomic (single) value.

• Each attribute has a distinct name.

• Values of an attribute are all from the same

domain.

2
 Properties of Relations
• Each tuple is distinct; there are no duplicate
tuples.

• Order of attributes has no significance.

• Order of tuples has no significance.

3
 Candidate Keys
• A set of attributes in a
relation is called a candidate ID First Last
key if, and only if, S139 John Smith
– Every tuple has a unique S140 Mary Jones
value for the set of attributes S141 John Brown
(uniqueness) S142 Jane Smith
– No proper subset of the set
has the uniqueness property Candidate key: {ID}; {First,Last}
looks reasonable but we may get
(minimality)
people with the same name
{ID, First}, {ID, Last} and {ID,
First, Last} satisfy uniqueness,
but are not minimal
{First} and {Last} do not give
a unique identifier for each row
 Choosing Candidate Keys

• Important: don’t look just on the data in the table to

determine what is a candidate key

• The table may contain just one tuple, so anything

would do!

• Use knowledge of the real world – what is going to

stay unique!
 Primary Keys

• One Candidate Key is usually chosen to be used to

identify tuples in a relation
• This is called the Primary Key
• Often a special ID attribute is used as the Primary Key
 NULLs and Primary Keys
• Missing information can • Entity Integrity: Primary
be represented using Keys cannot contain
NULLs NULL values
• A NULL indicates a
missing or unknown
value
• More on this later...
 Foreign Keys
• Foreign Keys are used to link data in two
relations. A set of attributes in the first
(referencing) relation is a Foreign Key if its
value always either
– Matches a Candidate Key value in the second
(referenced) relation, or
– Is wholly NULL
• This is called Referential Integrity
 Foreign Keys - Example

Department Employee
DID DName EID EName DID
13 Marketing 15 John Smith 13
14 Accounts 16 Mary Brown 14
15 Personnel 17 Mark Jones 13
18 Jane Smith NULL

{DID} is a Candidate Key {DID} is a Foreign Key in Employee -

for Department - Each each Employee’s DID value is either
entry has a unique value NULL, or matches an entry in the
for DID Department relation. This links each
Employee to (at most) one Department

The Relational Model

 Foreign Keys - Example

Employee
{ID} is a Candidate Key for
ID Name Manager Employee, and {Manager} is
E1496 John Smith E1499 a Foreign Key, which refers
E1497 Mary Brown E1498 to the same relation - every
E1498 Mark Jones E1499 tuple’s Manager value is either
E1499 Jane Smith NULL NULL or matches an ID value
 Referential Integrity
• When relations are • There are a number of
updated, referential options:
integrity can be violated – RESTRICT - stop the user from
• This usually occurs when a doing it
referenced tuple is updated – CASCADE - let the changes
flow on
or deleted
– NULLIFY - make values NULL
 Referential Integrity - Example
• What happens if
– Marketing’s DID is changed to Department
16 in Department? DID DName
– The entry for Accounts is 13 Marketing
deleted from Department? 14 Accounts
15 Personnel

Employee
EID EName DID
15 John Smith 13
16 Mary Brown 14
17 Mark Jones 13
18 Jane Smith NULL
 RESTRICT
• RESTRICT stops any action
that violates integrity Department
– You cannot update or delete DID DName
Marketing or Accounts 13 Marketing
– You can change Personnel as 14 Accounts
it is not referenced 15 Personnel

Employee
EID EName DID
15 John Smith 13
16 Mary Brown 14
17 Mark Jones 13
18 Jane Smith NULL
 CASCADE

• CASCADE allows the changes Department

made to flow through DID DName
– If Marketing’s DID is changed to 13 16 Marketing
16 in Department, then the 14 Accounts
DIDs for John Smith and Mark 15 Personnel
Jones also change
– If Accounts is deleted then so is Employee
Mary Brown EID EName DID
15 John Smith 13 16
16 Mary Brown 14
17 Mark Jones 13 16
18 Jane Smith NULL

The Relational Model

 NULLIFY

• NULLIFY sets problem values Department

to NULL DID DName
– If Marketing’s DID changes then 13 16 Marketing
John Smith’s and Mark Jones’ 14 Accounts
DIDs are set to NULL 15 Personnel
– If Accounts is deleted, Mary
Brown’s DID becomes NULL Employee
EID EName DID
15 John Smith 13 NULL
16 Mary Brown 14 NULL
17 Mark Jones 13 NULL
18 Jane Smith NULL

The Relational Model

 Relational Integrity
• Enterprise Constraints
– Additional rules specified by database
administrators.

16
 Views
• Base Relation
– Named relation corresponding to an entity in
conceptual schema, whose tuples are
physically stored in database.

• View
– Dynamic result of one or more relational
operations operating on base relations to
produce another relation.

17
 Views
• A virtual relation that does not necessarily
actually exist in the database but is produced
upon request, at time of request.

• Contents of a view are defined as a query on

one or more base relations.

• Views are dynamic, meaning that changes made

to base relations that affect view attributes are
immediately reflected in the view.

18
 Purpose of Views
• Provides powerful and flexible security
mechanism by hiding parts of database from
certain users.

• Permits users to access data in a customized

way, so that same data can be seen by
different users in different ways, at same time.

• Can simplify complex operations on base

relations.

19
 Updating Views
• All updates to a base relation should be
immediately reflected in all views that
reference that base relation.

• If view is updated, underlying base relation

should reflect change.

20
 Relational Database Operations(1)
Can be categorized into two groups:
• Updates
– Insert
– Modify
– Delete
All update operations must satisfy all
constraints (entity integrity, referential integrity
and enterprise constraints)
 Relational Database Operations(2)
• Retrievals
– Relational Algebra operations are used to specify
retrievals
• Relational algebra operations include:
– SELECT operation
– PROJECT operation
– Set theoretic operations
– JOIN Operation
 INSERT Operation(1)
• Used to insert a new tuple or tuples in a
relation
• Provides a list of attribute values for a new
tuple
• Insert can violate any of four types of
constraints
 INSERT Operation(2)
• Domain constraint can be violated
– value of some attribute does not appear in its domain
• Key constraint can be violated
– Key value of the tuple already exists in another tuple as its
key value
• Entity integrity constraint can be violated
– Key value of tuple is null
• Referential integrity constraint can be violated
– Some foreign key value of tuple does not exist in the
references relation
 DELETE Operation
• Used to delete a tuple or tuples from a
relation
• It can violate only referential integrity
– If tuple being deleted is referenced by the foreign
keys from other tuples in the database
 MODIFY/UPDATE Operation
• Used to change the values of one or more
attributes in a tuple or tuples of a relation
• Like INSERT operation all four constraints can
be violated by UPDATE operation
 Relational Algebra Operations(1)
• Enable user to specify basic retrieval requests
• Result of a retrieval is a new relation
 Relational Algebra Operations(2)
• SELECT operation
• PROJECT operation
• Set theoretic operations
– UNION, DIFFERENCE, INTERSECTION and
CARTESION PRODUCT
• JOIN Operation
– EQUI JOIN, NATURAL JOIN, INNER JOIN, OUTER
JOIN, SELF JOIN
 SELECT Operation(1)
• Used to select a subset of tuples from a relation that
satisfy a selection condition
• In other words SELECT operation can be considered
as a filter that keeps only those tuples which satisfy a
qualifying condition

  select condition  (Relation)

 Here sigma denote select operator
 Select condition is the boolean expression
specified on the attributes or relation
 Relation is either itself a relation or another
select/project operation which results a relation
 SELECT Operation(2)
• The resulting relation has the same attributes
as the original relation.
 Example Database
SP S# P# QTY
S1 P1 300
S S# SNAME STATUS CITY S1 P2 200
S1 Smith 20 London S1 P3 400
S2 Jones 10 Paris S2 P1 300
S3 Blake 30 Paris S2 P2 400
S3 P2 200
P P# PNAME COLOUR WEIGHT CITY
P1 Nut Red 12 London
P2 Bolt Green 17 Paris
P3 Screw Blue 17 Rome
P4 Screw Red 14 London
 SELECT Operation(3)
city  paris (S)
S# SNAME STATUS CITY
S2 Jones 10 Paris
S3 Blake 30 Paris

weight  17 ( P)
P# PNAME COLOUR WEIGHT CITY
P1 Nut Red 12 London
P4 Screw Red 14 London
 SELECT Operation(4)

 S#  s1 and p#  p1 ( SP)
S# P# QTY
S1 P1 300
 PROJECT Operation(1)
• PROJECT operations selects certain columns
from a relation and discards other columns
and hence constructs a vertical subset of a
relation
• When we are interested in only certain
attributes of a relation we use PROJECT
operation
 PROJECT Operation(2)

  attribute list  (Relation)

 Here pi denote project operator
 Attribute list is list of attributes to be projected
 Relation is either itself a relation or another
select/project operation which results a relation
 PROJECT Operation(2)

 city (S) sname, status(S)

SNAME STATUS
CITY Smith 20
London Jones 10
Paris Blake 30
 Sequences of operations
• Many operations can be performed in sequence in
one expression. For example you want part names
where part weight is less than 17.

pname( weight  17 (P) )

 Both operations can be written seperately

Temp  weight  17 (P)

Result  πpname(Temp)
 Set Theoretic Operations
• UNION, DIFFERENCE, INTERSECTION binary
operations - they take two relations
• The two relations must be union-compatible
i.e same degree and matching domains (ith
column of first relation and ith column of
second relation have same domain)