The Relational Model
Data Model
DBMS models real world
Data Model is link
between users view of
the world and bits stored
in computer. CUSTOMER (Cust-id: integer, Cust-name: text,
Address: text, Phone#: text)
Many models exist
We will concentrate on
the Relational Model
101001
101100
2
The Relational Model Concepts
Database is a collection of relations
Tables (relations)
Tuples (rows)
Attributes (columns)
Domain
Relational Database: Some
definitions
Relational database: a set of relations.
Relation: made up of 2 parts:
Schema : specifies name of relation, plus name and
type of each column.
E.g. CUSTOMER (Cust-id: integer, Cust-name:
text, Address: text, Phone#: text)
Instance : the current values of a table are specified
with rows and columns.
Number of rows = cardinality
Number of fields = degree
4
Relation
RELATION: A table of values
A relation may be thought of as a set of rows.
A relation may alternately be though of as a set of
columns.
Each row represents a fact that corresponds to a real-
world entity or relationship.
Each row has a value of an item or set of items that
uniquely identifies that row in the table.
Sometimes row-ids or sequential numbers are assigned
to identify the rows in the table.
Each column typically is called by its column name or
column header or attribute name.
5
Relation
A Relation may be defined in multiple ways.
The Schema of a Relation: R (A1, A2, .....An)
Relation schema R is defined over attributes A1, A2, .....An
Each attribute Ai is the name of a role played by some domain
D in the relation schema R.
D is called the domain of Ai and is denoted by dom(Ai).
R is called the name of the relation.
The degree of a relation is the number of attributes n of its
relation schema.
6
Relation Example
For Example -
CUSTOMER (Cust-id, Cust-name, Address, Phone#)
Here, CUSTOMER is a relation defined over the four attributes
Cust-id, Cust-name, Address, Phone#, each of which has a
domain or a set of valid values. For example, the domain of
Cust-id is 6 digit numbers.
Tuples
A tuple is an ordered set of values
Each value is derived from an appropriate domain.
Each row in the CUSTOMER table may be referred to as a
tuple in the table and would consist of four values.
<632895, "John Smith", "101 Main St. Atlanta, GA 30332", "(404) 894-2000">
is a tuple belonging to the CUSTOMER relation.
A relation may be regarded as a set of tuples (rows).
Columns in a table are also called attributes of the relation.
8
Domains
A domain has a logical definition:
e.g., USA_phone_numbers are the set of 10 digit
phone numbers valid in the U.S.
A domain may have a data-type or a format defined for it. The
USA_phone_numbers may have a format: (ddd)-ddd-dddd
where each d is a decimal digit.
E.g., Dates have various formats such as monthname,
date, year or yyyy-mm-dd, or dd mm,yyyy etc.
An attribute designates the role played by the domain.
E.g., the domain Date may be used to define attributes
Invoice-date and Payment-date.
Example
STUDENT(Name, SSN, HomePhone, Address,
OfficePhone, Age, GPA)
What is the relation name?
What is the degree of the relation?
What are the attributes?
List each dom(Ai).
10
Definition Summary
Informal Terms Formal Terms
Table Relation
Column Attribute/Domain
Row Tuple
Values in a column Domain
Table Definition Schema of a Relation
Populated Table Extension
11
Example - Figure 5.1
An instance of the table STUDENT
Cardinality = 5 Degree = 7
12
Characteristics of Relations
Ordering of tuples in a relation r(R): The tuples are not
considered to be ordered, even though they appear to be in
the tabular form.
Ordering of attributes in a relation schema R (and of
values within each tuple): We will consider the attributes in
R(A1, A2, ..., An) and the values in t=<v1, v2, ..., vn> to be ordered
Values in a tuple: All values are considered atomic
(indivisible). A special null value is used to represent values
that are unknown or inapplicable to certain tuples.
13
Relational Integrity Constraints
Constraints are conditions that must hold on
all valid relation instances. There are three
main types of constraints:
Key constraints
Entity integrity constraints
Referential integrity constraints
14
Key Constraint
A set of fields is a superkey if:
No two distinct tuples can have same values in all key fields
A set of fields is a key for a relation if :
It is a superkey
No subset of the fields is a superkey
What if there is more than 1 key for a relation?
One of the keys is chosen (by DBA) to be the primary key.
Other keys are called candidate keys.
Primary keys are underlined
E.g.
SSN is a key for Students.
What about Name?
The set {SSN, GPA} is a superkey.
15
Example Schema
16
Relational Database State
17
Entity Integrity
The primary key is used to identify individual
tuples.
The entity integrity constraint states that no
primary key value can be null.
Key constraints and entity constraints are
specified on individual relations.
18
Referential Integrity
The referential integrity constraint is specified
between two relations. It is used to maintain
consistency among tuples of the two relations.
Informally, it states that a tuple in one relation
that refers to another relation must refer to an
existing tuple in that relation.
19
Referential Integrity Constraint
Foreign key: Set of fields in one relation that is
used to `refer to a tuple in another relation.
Must correspond to the primary key of the other
relation.
Like a `logical pointer.
If all foreign key constraints are enforced,
referential integrity is achieved (i.e., no
dangling references.)
20
Referential Integrity Revisited
What are the referential integrity constraints of
the example database?
A foreign key can refer to its own relation.
Can be shown in the schema diagram as
directed arrows drawn from each foreign key
to the primary key of the referenced relation.
21
Referential Integrity Example
22
Update Operations on Relations
INSERT a tuple
DELETE a tuple
MODIFY a tuple
Integrity constraints should not be violated by the update
operations.
Several update operations may have to be grouped
together.
Updates may propagate to cause other updates
automatically. This may be necessary to maintain integrity
constraints.
23
References
Fundamentals of Database Systems, 4th
Edition, by Elmasri and Navathe
Chapter 5
Database Management Systems, Third
Edition by Ramakrishnan and Gehrke
Chapter 3
Database Management Systems, Third
Edition, by A Vaisman
24
