# Part 2 - 1 The Relational Database Model by suchenfz

VIEWS: 1 PAGES: 58

• pg 1
The Relational Database Model

-Software School of Hunan University-

2006.09
Learning Objectives
• Terminology of data model, relational model.
• How tables are used to represent data.
• Connection between mathematical relations and
relations in the relational model.
• Properties of database relations.
• Concepts of primary, and foreign keys.
• Meaning of integrity.
• Relation Algebra
Data Model
• Integrated collection of concepts for describing data,
relationships between data, and constraints on the
data in an organization.

• Data Model comprises:
– a structural part;
– a manipulative part;
– possibly a set of integrity rules.

• Purpose:
– To represent data in an accurate and
understandable way.
Categories of data models
Logic level:
– Record-Based Data Models
• Relational Data Model
• Network Data Model
• Hierarchical Data Model.
– Object-Based Data Models
•   Entity-Relationship
•   Semantic data model
•   Functional data model
•   Object-Oriented data model.
Physical level:
– Physical Data Model
Relational Model History
• The relational model was proposed by E. F. Codd in 1970.
• One of the first relational database systems, System R, developed
at IBM led to several important breakthroughs:
– the first version of SQL
– various commercial products such as Oracle and DB2
– extensive research on concurrency control, transaction
management, and query processing and optimization
• Commercial implementations (RDBMSs) appeared in the late
1970s and early 1980s. Currently, the relational model is the
foundation of the majority of commercial database management
systems.
Relational Model Definitions
•   A relation is a table with columns and rows.
•   An attribute is a named column of a relation.
•   A tuple is a row of a relation.
•   A domain（域） is a set of allowable values for one or more
attributes.
•   The degree （度）of a relation is the number of attributes it
contains.
•   The cardinality（基）of a relation is the number of tuples it
contains.
•   A relational database is a collection of normalized relations with
distinct relation names.
•   The intension(内涵） of a relation is the structure of the relation
including its domains.
•   The extension （外延）of a relation is the set of tuples currently in
the relation.
Relation Example
Relation
Attributes
PropertyForRent
PropertyNo      Address       Type    rooms    rent   OwnerNo

PA14          16 Holhead       House   6        650    C046

Cardinality =
PL94          6 Argyll St      Flat    4        400    C087
PG4           6 Lawrence St.   Flat    3        350    C040
Tuples
PG36          2 Manor Rd       Flat    3        375    C093
PG21          18 Dale Rd       House   5        600    C087
PG16          5 Novar Dr.      Flat    4        450    C093       75
…

…
Degree = 6
Domain is currency

Domain is integer: 1..100
Example of Domain
Alternative Terminologies
Relation Practice Questions
Lease
LeaseNo   PropertyNo   ClientNo   rent    Payment   Deposit   Paid    RentStart   Rentfinish   Duration
method

10024     PA14         CR62       650    Visa       1300      Y      1-Jun-01     31-May-02    12

10075     PL94         CR76       400    Cash       800       N      1-Aug-01     31-Jan-02    6

10012     PG21         CR74       600    cheque     1200      Y      1-Jul-01     30-Jun-02    12

1) What is the name of the relation?
2) What is the cardinality of the relation?
3) What is the degree of the relation?
4) What is the domain of RentStart? What is the domain of
PaymentMethod?
5) What is larger the size of the intension or extension?
Relational Model Formal Definition
• The relational model may be visualized as tables and fields, but it is
formally defined in terms of sets and set operations.
• A relation schema R with attributes A =<A1, A2, …, An> is denoted R(A1,
A2, …, An) where each Ai is an attribute name that ranges over a domain
Di denoted dom(Ai).

• Example:
• Relation schema: PrivateOwner (OwnerNo, FirstName, LastName,
• R = PrivateOwner (relation name)
• Set A = {OwnerNo, FirstName, LastName, Address, TelephoneNo}
• dom(TelephoneNo) is set of all possible Telephone codes
• dom(OwnerNo) is set of all possible strings that represent OwnerNo in
the organization
Relation Schemas and Instances
• A relation schema is a definition of Named relation with a set of attribute
and domain name pairs.
– The relation schema is the intension(内涵） of the
relation.
• A relational database schema is a set of relation schemas , each with a
distinct name
• A relation instance denoted r(R) over a relation schema R(A1,A2, …, An)
is a set of n-tuples <d1, d2, ..., dn> where each di is an element of dom(Ai)
or is null.
– The relation instance is the extension（外延） of the
relation.
– A value of null represents a missing or unknown value.
Cartesian Product (review)
• The Cartesian product written as D1 × D2 is a set operation that takes
two sets D1 and D2 and returns the set of all ordered pairs such that the
first element is a member of D1 and the second element is a member of
D 2.
• Example:
– D1 = {1,2,3}
– D2 = {A,B}
– D1 × D2 = {(1,A), (2,A), (3,A), (1,B), (2,B), (3,B)}
• Practice Questions:
– 1) Compute D2 × D1.
– 2) Compute D2 × D2.
– 3) If |D| denotes the number of elements in set D, how many
elements are there in D1 × D2 in general.
– What is the cardinality of D1 × D2 × D1 × D1?
Relation Instance
• A relation instance r(R) can also be defined as a subset of the
Cartesian product of the domains of all attributes in the relation schema.
That is,
• r(R)  dom(A1) × dom(A2) × … × dom(An)

• Example:
• R = Person(id, firstName, lastName)
• dom(id) = {1,2}, dom(firstName) = {Joe, Steve}
• dom(lastName) = {Jones, Perry}
• dom(id) × dom(firstName) × dom(lastName) =
• { (1,Joe,Jones), (1,Joe,Perry), (1,Steve,Jones), (1,Steve,Perry),
• (2,Joe,Jones), (2,Joe,Perry), (2,Steve,Jones), (2,Steve,Perry)}
• Assume our DB stores people Joe Jones and Steve Perry, then
• r(R) = { (1,Joe, Jones), (2,Steve,Perry)}.
Properties of Relations
•   A relation has several properties:
– 1) Each relation name is unique. No two relations have the same
name.
– 2) Each cell of the relation (value of a domain) contains exactly one
atomic (single) value.
– 3) Each attribute of a relation has a distinct name.
– 4) The values of an attribute are all from the same domain.
– 5) Each tuple is distinct. There are no duplicate tuples. This is
because relations are sets.
– 6) The order of attributes is not really important.
• Note that this is different that a mathematical relation and our
definitions which specify an ordered tuple. The reason is that the
attribute names represent the domain and can be reordered.
– 7) The order of tuples has no significance.
Relational Keys
• Keys are used to uniquely identify a tuple in a relation.
– Note that keys apply to the relational schema not to the relational
instance. That is, looking at the current instance cannot tell you for
sure if the set of attributes is a key.
• A superkey (超码) is a set of attributes that uniquely identifies a tuple in
a relation.
• A key (码) is a minimal set of attributes that uniquely identifies a tuple in
a relation.
• A candidate key (候选码) is one of the possible keys of a relation.
• A primary key (主码) is the candidate key designated as the
distinguishing key of a relation.
• A foreign key (外码) is a set of attributes in one relation referring to the
primary key of another relation.
– Foreign keys allow referential integrity to be enforced.
Example Relations
• DreamHome Database:
• Propertys have a unique number, address, type, and rent.
• Clients have a unique number, name, and Address.
• An client may view multiple properties and a property may
Be viewed by multiple clients

•   Relations:
•   PropertyForRent (PropertyNo, Address, Type, Rooms)
•   Viewing (PropertyNo, ClientNo, DateViewed, Comments)
•   Underlined attributes denote keys.
Example Relation Instances
ProperityForRent                                           Client
No                                                      No
PA14          16 Holhead        Flat       5               CR76     John    56 High St.
PL94          6 Argyll St       House      4               CR74     Mike    18 Tain St.
PG21          18 Dale Rd        Flat       3               CR62     Mary    5 Tarbot Rd.

Viewing
•   Questions:
•   1) Is name a key for Client?
yNo        tNo
1-Jun-01
•   2) Is viewdate a key for viewing?
PA14         CR62                      goods
1-Aug-01
•   3) List all the superkeys for viewing.
PL94         CR74                      preference
1-Jul-01
•   4) List the candidate keys for viewing.
PG21         CR62                      expensive
Relational Integrity
• Integrity rules are used to insure the data is accurate.
• Constraints are rules or restrictions that apply to the database and
limit the data values it may store.
• Types of constraints:
• 1) Domain constraint - Every value for an attribute must be an
element of the attribute's domain or be null.
– null represents a value that is currently unknown or not applicable.
– null is not the same as zero or an empty string.
• 2) Entity integrity constraint - In a base relation, no attribute of a
primary key can be null.
• 3) Referential integrity constraint - If a foreign key exists in a
relation, then the foreign key value must match a primary key value of
a tuple in the referenced relation or be null.
Foreign Keys Example
ProperityForRent                              Client
No                                s         No
PA14        16            Flat     5          CR76     John    56 High St.
CR74     Mike    18 Tain St.
PL94        6 Argyll St   House    4
CR62     Mary    5 Tarbot
PG21        18 Dale Rd Flat        3                           Rd.
Viewing
yNo       tNo
PA14        CR62 1-Jun-01        goods
PL94        CR74 1-Aug-01        preference
PG21        CR62 1-Jul-01        expensive
Integrity Questions
ProperityForRent                                     Client
Null         25Xishilu     house        4            No

PA14         16            Flat         5           CR76     John    56 High St.
PL94         6 Argyll St   House        Tom         Null     Tom     56 High St.
PG21         18 Dale Rd Flat            3           CR62     2004    5 Tarbot
Rd.
Viewing
yNo        tNo
Question:
PA14         CR62 1-Jun-01         goods
1) Find all violations of integrity
PL94         CR74 1-Aug-01         preference       constraints in these three relations.
PL54         CR76 4-May-01         Null
PA14         null    4-May-01      yes
PG21         CR62 1-Jul-01         Expensive
General Constraints
• There are more general constraints that some DBMSs can
enforce. These constraints are often called enterprise
constraints or semantic integrity constraints.
• Examples:
– An staff cannot work on more than 2 projects.
– An staff cannot make more money than their manager.
– An staff must be assigned to one position.

• Ensuring the database follows these constraints is usually
achieved using triggers and assertions.
Relational Algebra (代数）
• A query language is used to update and retrieve data that is stored in
a data model.
• Relational algebra is a set of relational operations for retrieving data.
• Just like algebra with numbers, relational algebra consists of
operands (操作数）which are relations) and a set of operators（操作
符）.
• Every relational operator takes as input one or more relations and
produces a relation as output.
– Closure property - input is relations, output is relations
– Unary operations - operate on one relation
– Binary operations - have two relations as input
• A sequence of relational algebra operators is called a relational
algebra expression.
Relational Algebra Operators
• Relational Operators:
 Selection              σ
 Projection             Π
 Cartesian product ×
 Join                   ⋈
 Union                  ∪
 Difference              -
 Intersection           ∩
 Division               ÷
• Note that relational algebra is the foundation of ALL
relational database systems. SQL gets translated into
relational algebra.
Selection Operation
• The selection operation is a unary operation that takes in
a relation as input and returns a new relation as output
that contains a subset of the tuples of the input relation.
That is, the output relation has the same number of
columns as the input relation, but may have less tuples.
• To determine which tuples are in the output, the selection
operation has a specified condition, called a predicate,
that tuples must satisfy to be in the output.
• The predicate is similar to a condition in an if statement.
Selection Operation Formal
Definition
• The selection operation on relation R with predicate F is
denoted by σF(R).

• σF(R) = {t | t∈R and F(t) is true}

• Where R is a relation, t is a tuple variable, F is a formula
(predicate) consisting of
– operands that are constants or attributes
– comparison operators: <, >, =, ≠, ≤, ≥
– logical operators: AND, OR, NOT
PropertyForRent:                        Selection Example
PropertyNo         Street       Type       rooms   rent   OwnerNo

PA14            16 Holhead      House   6           650    C046
PL94            6 Argyll St     Flat    4           400    C087
PG4             6 Lawrence St. Flat     3           350    C040
PG36            2 Manor Rd      Flat    3           475    C093
PG21            18 Dale Rd      House   5           600    C087
PG16            5 Novar Dr.     Flat    4           450    C093
σType＝’House’(PropertyForRent)
PropertyNo        Street        Type    rooms       rent   OwnerNo

PA14          16 Holhead       House    6           650    C046
PG21          18 Dale Rd       House    5           600    C087

σType＝’flat ‘ AND Rent<400 (PropertyForRent)
PropertyNo        Street        Type    rooms       rent   OwnerNo

PG4           6 Lawrence St.   Flat     3           350    C040
Selection Questions

Viewing                                       • Write the relational algebra
Properity   Client   ViewDate    Comments       expression that:
No        No
PA14        CR62     1-Jun-01    goods
• 1) Returns all clients viewing
PL94        CR74     1-Aug-01    preference     property PA14.
PL54        CR76     4-May-01    Null
PA14        CR76     12-May-01   yes          • 2) Returns all all clients viewing
PA14        CR74     22-Jul-01   Too small      during 1-May-01 and 30-Jul-01
PG21        CR62     1-Jul-01    Expensive
• 3) Returns all property that have
been viewed by client CR74.
Projection Operation
• The projection operation is a unary operation that takes
in a relation as input and returns a new relation as output
that contains a subset of the attributes of the input relation
and all non-duplicate tuples.
• The output relation has the same number of tuples as the
input relation unless removing the attributes caused
duplicates to be present.
– Question: When are we guaranteed to never have
duplicates when performing a projection operation?
• Besides the relation, the projection operation takes as
input the names of the attributes that are to be in the
output relation.
Projection Operation Formal
Definition
• The projection operation on relation R with output
attributes A1,…,Am is denoted by ΠA1,…,Am(R).

• ΠA1,…,Am(R) = { t [A1,…, Am] | t∈R }

• Where R is a relation, t is a tuple variable
• {A1,…,Am} is a subset of the attributes of R over which
the projection will be performed.
• Order of A1,…, Am is significant in the result.
• Cardinality of ΠA1,…,Am(R) is not necessarily the same as
R because of duplicate removal.
Projection Example
Viewing
yNo       tNo
tNo
PA14       CR62 1-Jun-01      goods           CR62       goods                CR62
PL94       CR74 1-Aug-01      preference      CR74       preference           CR74
PL54       CR76 4-May-01      Null            CR76       Null
CR76
PA14       CR76 12-May-01     yes             CR76       yes
PA14       CR74 22-Jul-01     Too small       CR74       Too small
PG21       CR62 1-Jul-01      Expensive       CR62       Expensive
Union
• Union is a binary operation that takes two relations R and
S as input and produces an output relation that includes all
tuples that are either in R or in S or in both R and S.
Duplicate tuples are eliminated.
• General form:

• R ∪ S = {t | t∈R or t∈S}

• where R, S are relations, t is a tuple variable.
• R and S must be union-compatible. To be union-
compatible means that the relations must have the same
number of attributes with the same domains.
ProperityNo(Viewing) ProperityNo(PropertyForRent)
Viewing
PA08           CR62         1-Jun-01        goods                    ProperityNo
PL94           CR74         1-Aug-01        preference                  PA08
PA14           CR76         12-May-01       yes                         PL94
PA14           CR74         22-Jul-01       Too small                   PA14
PropertyForRent                                                         PG4
PropertyNo       Street        Type     rooms       rent   OwnerNo      PG36

PA14          16 Holhead      House     6           650    C046         PG21
PG16
PL94          6 Argyll St     Flat      4           400    C087
PG4           6 Lawrence St. Flat       3           350    C040
PG36          2 Manor Rd      Flat      3           375    C093
PG21          18 Dale Rd      House     5           600    C087
PG16          5 Novar Dr.     Flat      4           450    C093
Set Difference
• Set difference is a binary operation that takes two
relations R and S as input and produces an output relation
that contains all the tuples of R that are not in S. General
form:
• R – S = {t |t∈R and tS}

• where R and S are relations, t is a tuple variable.
• Note that:
 R - S ≠S - R
 R and S must be union compatible.
ProperityNo(Viewing) -ProperityNo(PropertyForRent)

Viewing
PA08           CR62         1-Jun-01        goods
ProperityNo
PL94           CR74         1-Aug-01        preference
PA08
PA14           CR76         12-May-01       yes
PA14           CR74         22-Jul-01       Too small

PropertyForRent
PropertyNo        Street       Type      rooms       rent   OwnerNo

PA14           16 Holhead     House      6           650    C046
PL94           6 Argyll St    Flat       4           400    C087
PG4            6 Lawrence St. Flat       3           350    C040
PG36           2 Manor Rd     Flat       3           375    C093
PG21           18 Dale Rd     House      5           600    C087
PG16           5 Novar Dr.    Flat       4           450    C093
Intersection
• Intersection is a binary operation that takes two relations
R and S as input and produces an output relation which
contains all tuples that are in both R and S. General form:

• R ∩ S = {t | t∈R and t∈S}

• where R, S are relations, t is a tuple variable.
• R and S must be union-compatible.
• Note that R ∩ S = R - (R - S) = S - (S - R).
ProperityNo(Viewing) ∩ProperityNo(PropertyForRent)

Viewing
PA08          CR62          1-Jun-01    goods
PL94          CR74          1-Aug-01    preference
12-May-01
ProperityNo
PA14          CR76                      yes
22-Jul-01
PL94
PA14          CR74                      Too small
PropertyForRent                                                   PA14

PropertyNo       Street         Type    rooms rent OwnerNo
PA14          16 Holhead       House    6         650   C046
PL94          6 Argyll St      Flat     4         400   C087
PG4           6 Lawrence St. Flat       3         350   C040
PG36          2 Manor Rd       Flat     3         375   C093
PG21          18 Dale Rd       House    5         600   C087
PG16          5 Novar Dr.      Flat     4         450   C093
Cartesian Product
• The Cartesian product of two relations R (of degree k1)
and S (of degree k2) is:

• R × S = {t | t [A1, …, Ak1]∈R and t [Ak1+1, …, Ak1+k2]∈S}

• The result of R × S is a relation of degree (k1 + k2) and
consists of all (k1 + k2)-tuples where each tuple is a
concatenation of one tuple of R with one tuple of S.
• The cardinality of R × S is |R| * |S|.
• The Cartesian product is also known as cross product.
Cartesian Product Example
Staff
Name      StaffNo      BrachNo    Name   StaffNo   Brach   Proper      Street      Type
Mike        S01          B01                        No      tyNo

Tom        S07          B01      Mike   S01       B01     PA14     16 Holhead     House
Mary        S15          B03      Mike   S01       B01     PL94     6 Argyll St    Flat
Mike   S01       B01     PG4      9 Lawrence St. Flat
Tom    S07       B01     PA14     16 Holhead     House
PropertyForRent
Tom    S07       B01     PL94     6 Argyll St    Flat
Prope       Street        Type
rtyNo                             Tom    S07       B01     PG4      9 Lawrence St. Flat

PL94     6 Argyll St      Flat    Mary   S15       B03     PL94     6 Argyll St    Flat
PG4      6 Lawrence St. Flat      Mary   S15       B03     PG4      9Lawrence St. Flat
θ-Join
• Theta (θ) join is a derivative of the Cartesian product.
Instead of taking all combinations of tuples from R and S,
we only take a subset of those tuples that match a given
condition F:

• R ⋈FS = {t | t [A1, …, Ak1]∈R and t [Ak1+1, …, Ak1+k2]∈S and
F(t) is true}

• where
• R, S are relations, t is a tuple variable
• F(t) is a formula defined as that of selection.
• Note that R ⋈FS = σF(R × S).
θ-Join Example
Staff
Staff ⋈Type=‘Flat’ PropertyForRent
Name     StaffNo        BrachNo
Mike     S01            B01             Name     StaffNo   Brach   Property      Street      Type
Tom      S07            B01                                 No       No

Mary     S15            B03            Mike      S01       B01     PL94       6 Argyll St    Flat
Mike      S01       B01     PG4        9 Lawrence St. Flat
Tom       S07       B01     PL94       6 Argyll St    Flat
PropertyForRent
Tom       S07       B01     PG4        9 Lawrence St. Flat
Proper         Street         Type
Mary      S15       B03     PL94       6 Argyll St    Flat
tyNo
House    Mary      S15       B03     PG4        9Lawrence St. Flat
PL94     6 Argyll St          Flat
PG4      6 Lawrence St. Flat
Types of Joins
• The θ-Join is a general join in that it allows any expression
in the condition F. However, there are more specialized
joins that are frequently used.
• A equijoin(等值连接) only contains the equality operator (=)
in formula F.
• e.g. Viewing ⋈ Viewing.PropertyNo = PropertyForRent.PropertyNo PropertyForRent
• A natural join(自然连接) over two relations R and S
denoted by R * S is the equijoin of R and S over a set of
attributes common to both R and S.
– It removes the “extra copies” of the join attributes.
– Normally assumes the attributes have the same name in
both relations.
EquiJoin Example
Staff
Staff ⋈Staff.staffNo=PropertyforRent.StaffNo PropertyForRent
Name       StaffNo     Branch
Mike       S01       B01
Name     S.Staff   BrachN   P.Staff   Propert    Type
Tom        S07       B01
No         o       No       yNo
Mary       S15       B03
Mike     S01        B01      S01       PL94      Flat
PropertyForRent                        Tom      S07        B01      S07       PA08      Flat
Propert     StaffNo       Type        Tom      S07        B01      S07       PG36      House
yNo
Mary     S15        B03      S15       PA14      House
PA14        S15        House
Mary     S15        B03      S15       PA74      Flat
PL94        S01        Flat
Mary     S15        B03      S15       PG4       Flat
PA74        S15        Flat
PA08        S07        Flat
PG36        S07        House

PG4         S15        Flat
NaturalJoin Example
Staff
Name       StaffNo     Branch     Staff ⋈ PropertyForRent
Mike       S01       B01

Tom        S07       B01           Name    StaffNo   BrachNo   Property     Type
B03
No
Mary       S15
Mike    S01        B01       PL94       Flat
PropertyForRent                   Tom     S07        B01       PA08       Flat
Property     StaffNo       Type   Tom     S07        B01       PG36       House
No
Mary    S15        B03       PA14       House
PA14        S15         House
Mary    S15        B03       PA74       Flat
PL94        S01         Flat
Mary    S15        B03       PG4        Flat
PA74        S15         Flat
PA08        S07         Flat
PG36        S07         House

PG4         S15         Flat
Join Practice Questions
Viewing                                               Client
No        No                                         No
PA08        CR62     1-Jun-01    goods                CR76      John    56 High St.
PL94        CR74     1-Aug-01    preference           CR74      Mike    18 Tain St.
PA14        CR74     22-Jul-01   Too small            CR78      Tom     7 London St.

PropertyForRent                                      CR62      Mary    5 Tarbot Rd.

PA14            16 Holhead       House        Compute the following joins:
PL94            6 Argyll St      Flat         Client ⋈ Viewing ⋈ PropertyForRent
PG4             6 Lawrence St. Flat

PG36            2 Manor Rd       Flat
PG21            18 Dale Rd       House

PA08            5 Novar Dr.      Flat
Outer Joins
• Outer joins are used in cases where performing a join "loses“ some
tuples of the relations. These are called dangling tuples.
• There are three types of outer joins:
• 1) Left outer join - R ⋊ S - The output contains all tuples of R that
match with tuples of S. If there is a tuple in R that matches with no
tuple in S, the tuple is included in the final result and is padded with
nulls for the attributes of S.
• 2) Right outer join - R ⋉ S - The output contains all tuples of S that
match with tuples of R. If there is a tuple in S that matches with no
tuple in R, the tuple is included in the final result and is padded with
nulls for the attributes of R.
• 3) Full outer join - R ⋊ ⋉ S - All tuples of R and S are included in the
result whether or not they have a matching tuple in the other relation.
Left outer join Example
Viewing                                        PropertyForRent ⋊ Viewing
rityNo    No         te
Prope     Address       Type   Clien   ViewD     Com
PA08     CR62     1-Jun-01    goods        rtyNo                          tNo      ate      ments
PL94     CR74     1-Aug-01    preference   PA14    16 Holhead     House   Null    Null      Null

PA08     CR74     22-Jul-01   Too small    PL94    6 Argyll St    Flat    CR74    1-Aug-    prefere
01        nce
PropertyForRent                         PG4     6 Lawrence St. Flat    Null    Null      Null
PropertyNo        Address        Type      PG36    2 Manor Rd     Flat    Null    Null      Null

PA14            16 Holhead      House      PG21    18 Dale Rd     House   Null    Null      Null

PL94            6 Argyll St     Flat       PA08    5 Novar Dr.    Flat    CR74    22-Jul-   Too
01        small
PG4             6 Lawrence St. Flat
PA08    5 Novar Dr.    Flat    CR62    1-Jun-    goods
PG36            2 Manor Rd      Flat                                              01

PG21            18 Dale Rd      House

PA08            5 Novar Dr.     Flat
SemiJoin
• The Semijoin operation defines a relation that contain the tuples
of R that participate in the join of R with S.
• The simijoin operation performs a join of two relationa and then
projects over the attributes of the first operand.
• It is particularly useful for computing joins in distributed
systems.
• R⊲F S = ΠA（R ⋈FS）, A is the set of all attributes of R.
Semijoin Example
Viewing PropertyForRent ⊲ PropertyForRent.propertyNo = viewing.PropertyNo Viewing
ityNo     No           e
PA08     CR62       1-Jun-01    goods        Proper     Address      Type
tyNo
PL94     CR74       1-Aug-01    preference
PL94     6 Argyll St   Flat
PA08     CR74       22-Jul-01   Too small
PA08     5 Novar Dr.   Flat
PropertyForRent

PL94              6 Argyll St     Flat
PG4               6 Lawrence St. Flat

PG36              2 Manor Rd      Flat
PG21              18 Dale Rd      House

PA08              5 Novar Dr.     Flat
Division Operator
• For the division operation to be defined the set of
attributes of S must be a subset of the attributes of R.
• Let A is the set of attributes of R, B is the set of attributes
of S, B⊆ A, C = A – B.
• R ÷ S define a relation over C that consists of the set of
tuples from R that match the combination of every tuple in
S . Duplicate tuples are eliminated

• Note that R ÷ S = ΠR-S(R) -ΠR-S ((ΠR-S (R)×S)-R).
Division Example
Viewing             • Viewing ÷ PropertyForRent
Proper   Client
ClientNo
ityNo     No
CR56
PA14      CR36
PG4       CR76
PG4       CR56     Question:
PA14      CR62
1) Can you give the relational algebra expression to
PG36      CR56
find the property that are viewed on by all Clients?

2) If there are 6 properties in the database, and the
PropertyForRent
result of the query ΠClientNo,PropertyNo(Viewing ) ÷
PropertyNo           Π PropertyNo(PropertyForRent) is 2 records,what is
PG4                   the minimum # of records that must be in the
Viewing relation?
PG36
Combining Operations
• Relational algebra operations can be combined in one expression by
nesting them:

Return the addresses of “House” properties that are overseen by staff
„Mike'.

• Operations also can be combined by using temporary relation variables
to hold intermediate results.

• We will use the assignment operator for indicating that the result of an
operation is assigned to a temporary relation:
HosuseProperty  σType＝’House’(PropertyForRent)
Rename Operation

• Renaming can be applied when assigning a result:

Result(StaffNum, FullName, Earning) 
Πstaffno,fName,salary (Staff)
Operator Precedence

• Just like mathematical operators, the relational
operators have precedence.
• The precedence of operators from highest to lowest is:
unary operators: σ,Π
Cartesian product and joins : ×, ⋈
intersection, division: ∩, ÷
union and set difference: ,－
• Parentheses can be used to changed the order of
operations.
Complete Set of
Relational Algebra Operators

• The five fundamental operations in relational algebra:
σ,Π, ∪， －， ×

• Other operations call be derived from the five
fundamental operations .
Practice Questions
• Relational database schema:
– branch (bname, address, city, assets)
– customer (cname, street, city)
– deposit (accnum, cname, bname, balance)
– borrow (accnum, cname, bname, amount)
•   1) List the names of all branches of the bank.
•   2) List the names of all deposit customers together with their account
numbers.
•   3) Find all cities where at least one customer lives.
•   4) Find all cities with at least one branch.
•   5) Find all cities with at least one branch or customer.
•   6) Find all cities that have a branch but no customers who live in that city.
•   7) Find the names of all branches with assets greater than \$2,500.
•   8) List the name and cities of all customers who have an account with
balance greater than \$2,000.
Practice Questions (2)
• Relational database schema:
– branch (bname, address, city, assets)
– customer (cname, street, city)
– deposit (accnum, cname, bname, balance)
– borrow (accnum, cname, bname, amount)
•   9) List all the cities with at least one customer but without any bank
branches.
•   10) Find the name of all the customers who live in a city with no bank
branches.
•   11) Find all the cities that have both customers and bank branches.
•   12) Find the name of customers who have deposits in every branch of
the bank.
•   13) Find the name and assets of all branches which have deposit
customers living in Iowa City.
•   14) Find all the customers who have both an account and a loan at the
Coralville branch.
Conclusion
• The relational model represents data as relations which are
sets of tuples. Each relational schema consists of a set of
attribute names which represent a domain.
• The relational model has several forms of constraints to
guarantee data integrity including: domain, entity integrity
and referential integrity constraints
• Keys are used to uniquely identify tuples in relations.
• Relational algebra is a set of operations for answering
queries on data stored in the relational model.
– The 5 basic relational operators are: {σ, Π, ×, ∪, -}.
– By combining relational operators, queries can be