Chapter 2

The Relational Model of Data

第 2 章 关系数据模型
2 The Relational Model of Data

What is a data model? 关系数据模型

What is a relational data model?

How to define a relation schema in
SQL?
Which operations can be in the
relational model? What are the result of
these operations?
 The operations in the relational model
can be expressed in either关系代数
an algebra,
called “relational algebra”.
 Relational algebra can express not
only operations, but also constraints
约束 on
relations.
2.1 An Overview of Data Models

What is a data model?

Mathematical representation of
data.
Examples: relational model = tables;
semistructured model = trees/graphs.
Operations on data.
Constraints.
2.1 An Overview of Data Models

Several data models

The relational model, including
object-relational extensions
The semistructured-data model,
including XML and related standards
The hierarchical model 层次模型
The network model 网状模型
2.2 Basics of the Relational Model

Relation 关系
A two-dimensional table called a relation.

title year length filmType

Star Wars 1977 124 color

Wayne’s World 1992 95 color
Mighty Ducks 1991 104 color
… … … …
Movies
2.2 Basics of the Relational Model

Why Relations?
 Very simple model.
 Often matches how we think
about data.
 Abstract model that
underlies SQL, the most
important database language
today.
2.2.1 Attributes

Attribute 属性
 Names for the columns of the relation,
describe the meaning of entries in the
column below. Such as length of
Movies.
 An attribute have a name.
 Any two attributes of a relation can’t
have same name.
title year length filmType
Attributes
1977 124 color
(column Star Wars
1992 95 color
headers) Wayne’s World
Mighty Ducks 1991 104 color
… … … …
2.2.2 Schemas
关系模式

Relation schema = relation name and

attribute list.
Optionally: types of attributes.

Example: Movies ( title, year, length,

filmtype) or Movies ( title: string, year:
int, length: int, filmtype: string)

Database = collection of relations.

Database schema = set of all relation
schemas in the database.
2.2.3 Tuples

Tuples 元组
 The rows of a relation, other than
the header row containing the
attributes, are called tuples.
 There may be no tuple in a relation.
分量
 A tuple has one component for each
attribute of the relation.

title year length filmType

Star Wars 1977 124 color

Tuples Wayne’s World 1992 95 color
(rows) Mighty Ducks 1991 104 color
… … … …
2.2.3 Tuples

How to describe a tuple?

Use commas to separate
components, and use parentheses
to surround the tuple.
 Example: (Star Wars, 1977, 124,
color)
 We should always use the order in
which the attributes were listed in
the relation schema.
2.2.3 Tuples

The mapping of tuples and

objects:
A relation ------- a class
A tuple ------ a object
A component of a tuple ------ a
property of a object
2.2.3 Tuples

The difference of tuples and

objects:
Objects have identities, while
tuples have not.
A class could have two different
objects with the same values in all
attributes, but a tuple can’t appear
more than once in a relation.
2.2.4 Domains

Domains 域
A domain is an elementary type, such
as integer, char(n), date, time.
Each attribute of a relation is a
domain, that is, a particular elementary
type.
 Each component of any tuple must be
atomic.
 Movies ( title: string, year: int, length:
int, filmtype: string)
Can not be broken into
smaller components
2.2.5 Equivalent Representations of a Relation

We can reorder the

attributes of a relation,
without changing the relation.
 We can reorder the tuples of
a relation, without changing
the relation.
title year length filmType

Star Wars 1977 124 color

Wayne’s World 1992 95 color
Mighty Ducks 1991 104 color
… … … …
2.2.6 Relation Instances

An instance of a relation 关系实例

A set of tuples for the relation
A current instance 当前实例
The set of tuples that are in the
relation “now”
2.2.7 Keys of Relations

What is a key? 键，关键字，码

A set of attributes forms a key

for a relation if we do not allow
two tuples in a relation instance to
have the same values in all the
attribute of the key.
 Movies ( title, year, length, genre )
 employee-ID, Social-Security
number, student-ID, drivers’ license
numbers and automobile
registration number
2.2.7 Keys of Relations

Key of the relation Movies ( title,

year, length, genre, studioName,
starName ):
{title, year} ?
{title, year, starName} ?
2.2 Basics of the Relational Model
2.2.8 An Example Database Schema

Following is an example of database application:

We'll build a marketing database system
for a sale company (supermarket). It will
manage all the following information:
1. Manage all departments' information in
the company (such as "Shanghai sales
department", "JiangSu sales department").
Also manage every salesman information in
those departments including exclusive
employee number, ID card number, and some
private information (such as name, gender,
birthday and phone number). By the way a
salesman will act as the department manager
in his department.
2.2.8 An Example Database Schema

2. Manage a group of customers: name, province,

city, company name, phone number.
3.Manage all the merchandises' information:
manufacturers (e.g. Chunlan, Hailer ), types
(e.g. motorcycle, air conditioner ),
specifications (e.g. "MT125", "RE1500" ),
prices, descriptions.
4. Manage sales order which record each deal
has been done.
Notes: every sales order contains an unique
order No. ,sign date, a corresponding
customer, a salesman, and at least one kind of
products. Each merchandise in the order
should have its quantity and unit price which
will be used to calculate the total prices.
2.2.8 An Example Database Schema

Customer (custid, name, prov, city, phone,

company)

Merchandise (merid, manufacturer, type, spec,

price, desc)

Salesman (empid, idno, name, gender, phone,

deptid)

Department (deptid, name, managerid)

Salesorder (orderno, signdate, empid, custid)

Salesitem (orderno, lineno, merid, unitprice,

quantity)
2.3 Defining a Relation Schema in SQL

SQL is primarily a queryStructured Query

language, for getting information
Language
from a database.

结构化查询语言
SQL also includes a data-
definition component for
describing database schemas.
2.3.1 Relations in SQL

Three kinds of relations:

Tables exist in the database and
can be modified by changing their
tuples, as well as queried.
 Views are defined by a
computation.
 Temporary tables are constructed
by the SQL language processor
when it performs its job of
executing queries and data
modifications.
2.3.2 Data Types
All attributes must have a data type.
1.Character strings of fixed or varying length.
Char(n), varchar(n)
2. Bit strings of fixed or varying length.
Bit(n), bit varying(n)
3. integer values
Tinyint, Smallint, Int | integer, Bigint
4. Floating-point numbers Real number
Real, double, float with a fixed
Decimal | dec(precision, scale), decimal point
numeric(precision, scale)
5. Dates and times
Date yyyy-mm-dd
Time hh:mm:ss.sssss
6. Boolean
Ture, False, Unknown
Logic
MySQL: Data Type value
SQLite: Data Types (techonthenet.com)
2.3.3 Simple Table Declarations

How to declare a relation schema?

CREATE TABLE salesman

(
empid char(10),
idno char(18),
name char(30),
gender char(1),
phone char(20),
deptid integer
)
2.3.3 Simple Table Declarations

CREATE TABLE department

(
deptid integer,
name char(40),
managerid char(10)
)

 Note that there is no

cognominal tables in a database.
 Auto-increment allows a unique number to be
2.3.3 Table Declarations in generated MySQL automatically when a new record is
inserted into a table. By default, the starting
the full syntax for the MySQL CREATE TABLE statement is:
value for AUTO_INCREMENT is 1, and it
will increment by 1 for each new record.
CREATE [ TEMPORARY ] TABLE [IF NOT EXISTS] table_name
(
column1 datatype [ NULL | NOT NULL ] [ DEFAULT default_value ] [ AUTO_INCREMENT ]
[ UNIQUE KEY | PRIMARY KEY ] [ COMMENT 'string' ],

column2 datatype [ NULL | NOT NULL ] [ DEFAULT default_value ] [ AUTO_INCREMENT ]

[ UNIQUE KEY | PRIMARY KEY ] [ COMMENT 'string' ],
...

| [CONSTRAINT [constraint_name]] PRIMARY KEY [ USING BTREE | HASH ]

(index_col_name, ...)

| [INDEX | KEY] index_name [ USING BTREE | HASH ] (index_col_name, ...)

| [CONSTRAINT [constraint_name]] UNIQUE [ INDEX | KEY ]

[ index_name ] [ USING BTREE | HASH ] (index_col_name, ...)

| {FULLTEXT | SPATIAL} [ INDEX | KEY] index_name (index_col_name, ...)

| [CONSTRAINT [constraint_name]]
FOREIGN KEY index_name (index_col_name, ...)
REFERENCES another_table_name (index_col_name, ...)
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE { RESTRICT | CASCADE | SET NULL | NO ACTION } ]
[ ON UPDATE { RESTRICT | CASCADE | SET NULL | NO ACTION } ]
);
2.3.5 Default Values 缺省值

The use of default values:

When we create or modify tuples, we
sometimes do not have values for all
components.
 When we declare an attribute and its
data type, we may add the keyword
DEFAULT and an appropriate value.

Example: In relation salesman:

gender char(1) DEFAULT ‘M’;
2.3.6 Declaring Keys

How to declare a primary key?

 There are two way to declare a
primary key in SQL statement.
1. We may declare an attribute to be a
primary key when that attribute is
listed in the relation schema.
Example:
CREATE TABLE department
(
deptid integer PRIMARY KEY,
name char(40),
managerid char(10)
);
2.3.6 Declaring Keys

2. We may add to the list of items in the

schema an additional declaration that
says a particular attribute or set of
attributes forms the primary key.
Example:
CREATE TABLE salesitem
(
orderno char(10),
lineno char(4),
merid char(6),
unitprice float,
quantity int,
Primary Key(orderno, lineno)
);
2.3.4 Modifying Relation Schemas

How to delete a table?

DROP TABLE T;

Note that all records in T will be

deleted.
2.3.4 Modifying Relation Schemas

 How to modify the schema of an

existing relation?
datatype
ALTER TABLE R Add column-name
[NOT] NULL
ALTER TABLE R Drop column-name
datatype
ALTER TABLE R Modify column-name
[NOT] NULL
ALTER TABLE R Rename S
ToALTER TABLE R Rename column-name1
column-name2
Example:
Alter Table department Rename
name To deptname;
2.3.4 Modifying Relation Schemas in
MYSQL
1, Dropping, Adding, or Repositioning a
Column
Use the DROP or ADD clauses of ALTER
TABLE to remove or add a column. To move
a column, drop it and then put it back
where you want it.
 ALTER TABLE mytbl DROP i; // 删除表的一列
 ALTER TABLE mytbl ADD i INT; 给表增加一列 , 该列排
在最后
 ALTER TABLE mytbl ADD i INT FIRST;// 给表增
加一列 , 该列排在最前
 ALTER TABLE mytbl ADD i INT AFTER c;// 给
表增加一列 , 该列排在 c 列后面
2.3.4 Modifying Relation Schemas in
MYSQL
2, Changing a Column Definition or Name
Use MODIFY or CHANGE. MODIFY is
simpler, but cannot change the column
name. CHANGE is more confusing to use,
but can change both the name and the
definition.
 ALTER TABLE tbl_name MODIFY
col_name ... ;
 ALTER TABLE tbl_name CHANGE
col_name1 col_name2 ... ;
3, Renaming a Table
 ALTER TABLE old_name RENAME TO
new_name;
2.3.4 Modifying Relation Schemas in
MYSQL
4, Changing a Column's Default Value
Use SET DEFAULT to specify the default
value explicitly, or DROP DEFAULT to
remove the current default and allow
MySQL to assign the "default default."
 To change a default value, use ALTER
col_name SET DEFAULT:
ALTER TABLE mytbl ALTER j SET DEFAULT
1000;
 To drop a default value, use ALTER
col_name DROP DEFAULT:
ALTER TABLE mytbl ALTER j DROP
DEFAULT;
2.3.1 Relations in SQL
1) Define the table Accounts in
SQL. Declare primary key. Let
the default value for
attribute acctNo is auto
increment, the default value
for attribute type is ‘saving’.
2) Define the table Customers
in SQL. Declare primary key.
3) Alter the table Customers to
include an attribute address
with default value ‘none’.
4) Alter the table Customers to
delete the attribute
lastName.
2.4 An Algebraic Query Language

Mathematical system consisting of:

Operands --- variables or
values from which new values
can be constructed.
Operators --- symbols
denoting procedures that
construct new values from
given values.
2.4.2 What is an Algebra

What is Relational Algebra?

An algebra whose operands are
relations or variables that represent
relations.
Operators are designed to do the
most common things that we need
to do with relations in a database.
 The result is an algebra that can
be used as a query language for
relations.
2.4.3 Overview of Relational Algebra

The operations of relational algebra fall into

four broad classes:
1. the usual set operations: union, intersection
and difference (on two relations)
2. operations that remove parts of a relation:
(on a relation)
 Selection: eliminates some rows (tuples)
 Projection: eliminates some colunms
(attributes)
2.4.3 Overview of Relational Algebra

3.operations that combine the tuples of two

relations
 Cartesian product: pairs the tuples of
two relations in all possible ways
 Join: selectively pair tuples from two
relations
Natural join
Theta-join
4.renaming: does not affect the tuples of a
relation, but changes the relation schema.
2.4.4 Set Operations on Relations

Operations on sets R and S:

R∪S = { t | t∈R or t∈S }: union
the set of elements that are in R
or S or both, and an element
appears only once.
并
2.4.4 Set Operations on Relations

Operations on sets R and S (Cons.):

 R∩S = { t| t ∈R and t∈S }:
intersection
 the set of elements that are in
both R and S.
交
2.4.4 Set Operations on Relations

Operations on sets R and S (Cons.):

 R - S = { t | t∈R and not t∈S }:
difference
 the set of elements that are in R
but not in S.
 Note that差 R – S is different from S
– R.
2.4.4 Set Operations on Relations

Conditions on R and S:
 R and S must have schemas with
identical sets of attributes.
 Before we compute the set-
theoretic operations, the columns
of R and S must be ordered so that
the order of attributes is the same
for both relations.
2.4.4 Set Operations on Relations

R S
A B A B

a1 b1 a1 b1
a1 b2 a1 b3

R∪S R∩S R–S

2.4.5 Projection

Projection 投影 :
 Produce from a relation R1 a new
relation that has only some of R2’s
columns.
 Denotation: R1:=∏A1,A2,…,An (R2)
 A1,A2,…,An is a list of attributes
from the schema of R2.
 R1 is constructed by looking at each
tuple of R2, extracting the attributes
on list A1,A2,…,An, in the order
specified, and creating from those
components a tuple for R1.
 Eliminate duplicate tuples, if any.
2.4.5 Projection

Example: Movie
title year lengt inColo studioNa ProducerC#
h r me
Star Wars 1977 124 True Fox 12345
Mighty Ducks 1991 104 True Disney 67890
Wayne’s 1992 95 True Paramount 99999
World
πtitle,year,length(Movie) πinColor(Movie)

title year lengt inColor

h True
Star Wars 1977 124
Mighty Ducks 1991 104
Wayne’s 1992 95
World
Example

Student (Sid, name, phone, sex)

 Find IDs and names of all
students.
Πsid, name(student)
2.4.6 Selection

Selection 选择 :
 Produce from a relation R a new
relation with a subset of R’s tuple.
 Denotation: σC(R)
 Result: The schema for the resulting
relation is the same as R’s schema, and
the tuples in the resulting relation are
those that satisfy some condition C
that involves the attributes of R.
Expression of condition C:
 Operands: constants or attributes of R
 Operators: = ≠ > ≥ < ≤ NOT ˄(AND)
˅(OR)
2.4.6 Selection

Example: Movie
title year lengt inColo studioNam ProducerC
h r e #
Star Wars 1977 124 True Fox 12345
Mighty Ducks 1991 104 True Disney 67890
Wayne’s 1992 95 True Paramount 99999
World
σlength ≥ 100(Movie):
title year lengt inColo studioNam ProducerC
h r e #
Star Wars 1977 124 True Fox 12345
Mighty 1991 104 True Disney 67890
σlength ≥ 100 ˄ studioName =‘FOX’(Movie):
Ducks

title year lengt inColo studioNam ProducerC#

h r e
Star Wars 1977 124 True Fox 12345
Example

Student (Sid, name, phone,

gender)
 Find all female students.
σ gender=‘F’ (student)
2.4.7 Cartesian Product

Cartesian product 笛卡尔积

 The Cartesian product of two
sets R and S is the set of pairs
that can be formed by choosing
the first element of the pair to be
any element of R and the second
an element of S.
 Denotation: R×S
2.4.7 Cartesian Product

Result:
 The relation schema for the
resulting relation is the attributes
of R and then S, in order.
 To disambiguate an attribute A
that is in the schemas of both R
and S, we use R.A for the attribute
from R and S.A for the attribute
from S.
 The tuples in the resulting
relation are pairs the tuples of R
and S in all possible ways.
 The number of tuples is NR×NS.
2.4.7 Cartesian Product

Example: R S
A B B C D
1 2 2 5 6
3 4 4 7 8
9 10 11

R×S ？
A R.B S.B C D
1 2 2 5 6
1 2 4 7 8
1 2 9 10 11
3 4 2 5 6
3 4 4 7 8
3 4 9 10 11
2.4.8 Natural Joins

Natural join connects two relations by:

 Equating attributes of the same
name, and
 Projecting out one copy of each
pair of equated attributes.
Denotation: R S
2.4.8 Natural Joins

Example: R S
A E B C D
1 2 2 5 6
3 4 4 7 8
9 10 11

R S

Note that the natural join of two relations R

and S is invalid, if R and S have no common
attributes.
2.4.8 Natural Joins

Example: U V
A B C B C D
1 2 3 2 3 4
6 7 8 2 3 5
9 7 9 7 8 10

U V
A B C D A tuple that fails to pair
1 2 3 4 with any tuple of the other
1 2 3 5 relation in join is sometimes
6 7 8 10
said to be a dangling tuple.
2.4.9 Theta-Joins

Theta-join:
Pair tuples using an arbitrary
condition.
Denotation: R C S

Result:
Take the product of R and S.
Select from the product only
those tuples that satisfy the
condition C.
2.4.9 Theta-Joins

Example: U V
A B C B C D
1 2 3 2 3 4
6 7 8 2 3 5
9 7 9 7 8 10

U A<D
V
A U.B U.C V.B V.C D

1 2 3 2 3 4
1 2 3 2 3 5
1 2 3 7 8 10
6 7 8 7 8 10
9 7 9 7 8 10
2.4.10 Combining Operations to Form Queries

Combining operations:
 Form expressions of arbitrary
complexity by applying operators
either to given relations or to
relations that are the result of
applying one or more relational
operators to relations.
 Use parentheses when necessary
to indicate grouping of operands.
2.4.10 Combining Operations to Form Queries
Suppose the values of relation R and relation S
are as follows ：
R A B C S B C D

a1 b1 c1 b2 c1 d1
a2 b2 c2 b2 c2 d2
a3 b3 c3 b2 c2 d3

1)Give the results of R∞S(natural join);

2)Give the results of B,C(R)-B,C(S);
3)Give the results of A,R.C,S.C,D(R∞R.B=S.BS);
4)Give the results of A,C,D(R∞S).
Example

Student (Sid, name)

Course (Cid, name)
Enrollment ( Sid, Cid, score)
1. Find names of all students
who learned ‘C1’.
2. Find No. of all students who
learned both ‘C1’ and ‘C2’.
3. Find No. of all students who
never learned ‘C1’.
2.4.11 Naming and Renaming

Renaming 改名
Denotation: ρS (R)
(A1, A2,…,An)

Result: the resulting relation has

exactly the same tuples as R, but the
name of the relation is S. And the
attributes of the result relation S are
named A1, A2, ..., An, in order from
the left.
 Denotation: ρS (R)
Result: only change the name of the
relation to S and leave the attributes
as they are in R.
2.4.11 Naming and Renaming

Example: R S

A B B C D

1 2 2 5 6
3 4 4 7 8
9 10 11

R×S R×ρS (X, C, D) (S)

A R.B S.B C D A B X C D
1 2 2 5 6 1 2 2 5 6
1 2 4 7 8 1 2 4 7 8
1 2 9 10 11 1 2 9 10 11
3 4 2 5 6 3 4 2 5 6
3 4 4 7 8 3 4 4 7 8
3 4 9 10 11 3 4 9 10 11

An equivalent expression: ρRS (A, B, X, C, D) (R×S)

2.4.11 Naming and Renaming

Example: Student(Sid, name)

 Find the student IDs and names of all
pairs of students who have the same
name.
πs1.Sid, s2.Sid, s1.name(ρS1 (Student)
∞s1.name=s2.name ˄ s1.Sid<s2.Sid
( ρS2 (Student)))
2.4.12 Relationships Among Operations

 Some of the operations can be

expressed in terms of other relational-
algebra operations.
 R∩S ＝ R—(R—S)
 θ-join: R ∞c S ＝ σc (R × S)
 Natural join ： R ∞ S ＝ πL (σc (R × S))
 C is the form where R.A1=S.A1
AND R.A2=S.A2 AND ... AND
R.An=S.An where A1, A2, ..., An are
all the attributes appearing in the
schemas of both R and S.
 L is the list of attributes in the
schema of R followed by those
2.4.12 Relationships Among Operations

These three operations are

called dependent operations
which can be expressed by other
operations, and the six other
operations are independent
operations.
Schemas for Results

Union, intersection, and difference:

the schemas of the two operands
must be the same, so use that
schema for the result.
Selection: schema of the result is
the same as the schema of the
operand.
Projection: list of attributes tells us
the schema.
Schemas for Results

Product: schema is the attributes of

both relations.
Use R.A, etc., to distinguish two
attributes named A.
 Theta-join: same as product.
 Natural join: union of the attributes
of the two relations.
 Renaming: the operator tells the
schema.
2.4.14 Example
Customer (custid, name, prov, city, phone, company)

Merchandise (merid, manufacturer, type, spec, price,

desc)

Salesman (empid, idno, name, gender, phone, deptid)

Department (deptid, name, managerid)

Salesorder (orderno, signdate, empid, custid)

Salesitem (orderno, lineno, merid, unitprice, quantity)

1 ． Find the IDs and phone numbers of all salesmen named “Zhangsan”.
2 ． Find the names and phone numbers of all customers who have transacted
with the salesman whose ID is “A0043”.
3 ． Find the types and specifications of all merchandises which are sold by
the salesman whose ID is “A0043”.
2.5 Constraints on Relations

How to express constraints in

relational algebra, such as key
constraints, referential integrity
constraints?
2.5.1 Relational Algebra as a Constraint Language

 There are two ways in which we can use

expressions of relational algebra to express
constraints.
 If R is an expression of relational algebra,
then R=Φ is a constraint that says “the
value of R must be empty”.
 If R and S are expressions of relational
algebra, then R S is a constraint that
says “every tuple in the result of R must
be in the result of S”.
 These two ways both can express
equivalent constraints.
 R=Φ is equivalent to RΦ
 R S is equivalent to R – S =Φ
2.5.2 Referential Integrity constraints

Referential integrity constraint 参照完整性约束

A value appearing in one context also appears in
another, related context.
For example, should we see a Starsln tuple that has
person p in the starName component, we would
expect that p appears as the name of some star in
the MovieStar relation. If not, then we would
question whether the listed “star” really was a star.
 In general, if we have any value v as the component
in attribute A of some tuple in one relation R, then
because of our design intentions we may expect that
v will appear in a particular component (say for
attribute B) of some tuple of another relation S.
2.5.2 Referential Integrity constraints

Express referential integrity

constraints using relational algebra:
π (R)  π (S)
A B

 π (R) - π (S)=
A B

Example:
π custid (Salesorder)  πcustid(Customer)
2.5.3 Key constraints

Example:
Salesman (empid, idno, name,
gender, phone, deptid)
 S1:=S1(Salesman)
S2:= S2 (Salesman)
 S1.empid=S2.empid ˄ S1.nameS2.name (S1S2) =

2.5.4 Additional Constraint Examples

Domain constraints:
The only legal values for the
gender attribute of Salesman are
0(female) and 1(male).
σgender  0 ˄ gender  1(Salesman) ＝ Φ