Transcript Database Systems: Design, Implementation, and Management

1
Database Systems: Design,
Implementation, and Management
CHAPTER 2
The Relational Database Model
A Logical View of Data






2
In this chapter we will discuss the logical view of data as
represented by the relational database model.
That the relational database model’s basic components or
modules are entities and their attributes, and relationships
among entities
How entities and their attributes are organized into tables
About relational database operators, the data dictionary, and
the system catalog
How data redundancy is handled in relational database
Why indexing is important
A Logical View of Data

3
Entities, Attributes, and Entity Sets
 An
Entity is a person, place, event, or thing for which we
intend to collect data.

 An

Example:
Attribute describes a characteristic of an entity.
Example:
 A group

of entities of the same type is known as an Entity Set.
Example:
A Logical View of Data

4
Table
 A Table
contains a group of related entities -- i.e. an entity set.
 It is also called a relation (Term borrowed from Set theory).

Characteristics of a Relational Table
 A table
is composed of rows and columns.
 Each row (tuple) represents a single entity within the entity set.
 Each column represents an attribute and is identified by a
distinct name.
 Tables must have an attribute to uniquely identify each row
A Logical View of Data

Characteristics of a Relational Table
 Each
row/column intersection represents a single data value
(atomic).
 The number of tuples in a table is called its cardinality.
 The number of columns is known as its degree.
 All values in a column must conform to the same data
format (type) and must be within a specified range, known
as the attribute domain.
 Changing the order of the rows and/or columns does not
change the table.
5
Keys

Functional Dependence
 Attribute
B is functionally dependent on attribute A
(attribute A determines attribute B: A -> B) if all the rows in
a table that agree in value for attribute A must also agree in
value for attribute B.




Example:
A key is an attribute that determines the values of other
attributes within an entity. Example:
A key that is composed of more than one attributes is known
as a composite key. Example:
If attribute B is functionally dependent on a composite key A
but not any subset of A then B is fully functionally dependent
on A. Example:
6
Keys

7
Superkey: An attribute or a combination of attributes that
uniquely identifies each entity in a table.
 Example:

Candidate Key: A minimal superkey, i.e., it does not contain
a subset of attributes that is itself a superkey.
 Example:

Primary Key
 A candidate
key selected to uniquely identify an entity.
 Cannot have null values (A null value is no value, it is NOT
equal to a zero or a blank space).
 Enforces Entity Integrity (Guarantees that each entity is uniquely
identified by a non-null primary key value)
 A primary key is a superkey as well as a candidate key.

Example:
Keys

Foreign Key
 An
attribute (or a combination of attributes) in one table whose
values must either match the primary key values in a designated
table or be null.
 Used to logically link one table with another (compare with the
physical pointers in Hierarchical and Network models).
 Enforces Referential Integrity (Guarantees valid references to
another table, i.e., cannot delete a tuple from a table that is
referenced by in another table through a foreign key).

Example:
8
9
KEYS

Secondary Key
 Used
for data retrieval purpose.
 May consist of a single attribute or a combination of attributes.
 The DBMS maintains indexes on secondary keys for faster
search and retrieval of data.
 May have duplicate values.

Example:
PK
SK
CK
KEYS
10
KEYS
11
Relational Database Operators




12
These operators are based on relational algebra theory.
They define functions to manipulate data in one or more tables
(relations).
Application of a relational operator to one or more tables
results in another table.
The eight relational operators are: UNION, INTERSECT,
DIFFERENCE, PRODUCT, SELECT, PROJECT, JOIN, and
DIVIDE.
Relational Database Operators

Union Compatibility
 Two
tables are said to be union-compatible when they have the
same degree (number of attributes), say n, and the jth attributes
(j in the range of 1 to n) of the two tables are drawn from the
same domain (they need not have the same name).

Example:
13
14
Union


The tables must be union compatible
A Union B results in C that contains all tuples from both A
and B with no duplicates.
 Example:
A (All Insy)
Name Major Gpa
John
Insy
3.4
Joe
Insy
3.7
C (A Union B)
Name Major
John
Insy
Joe
Insy
Jack
Fina
Jeb
Fina
Gpa
3.4
3.7
4.0
2.5
B (All Fina)
Name Major Gpa
Jack
Fina
4.0
Jeb
Fina
2.5
15
Intersect


The tables must be union compatible
A Intersect B results in C that contains tuples that are
common to both A and B.
 Example:
A (All Insy)
Name Major
John
Insy
Joe
Insy
Jill
Insy
Bob
Insy
Gpa
3.4
3.7
4.0
2.7
C (A INTERSECT B)
Name Major Gpa
Jill
Insy
4.0
B (High Achievers)
Name Major Gpa
Jack
Fina
4.0
Jill
Insy
4.0
Jeb
Mktg 3.98
16
Difference


The tables must be union compatible
A MINUS B results in C that contains the tuples that appear in
A but not in B.
 Example:
A (All Insy)
Name Major
John
Insy
Joe
Insy
Jill
Insy
Bob
Insy
Gpa
3.4
3.7
4.0
2.7
C (A Minus B)
Name Major
John
Insy
Joe
Insy
Bob
Insy
Gpa
3.4
3.7
2.7
B (High Achievers)
Name Major Gpa
Jack
Fina
4.0
Jill
Insy
4.0
Jeb
Mktg 3.98
17
Product


PRODUCT produces a list of all possible pairs of rows from two tables.
If table A has 5 rows and B has 10, A product B will yield a table with 50
rows.

Example:
A (Item)
Inumber
101
205
B (Supplier)
IName
SName
Sweat Shirt
WalMartDallas
Trousers
Kmart
C (A Product B)
Inumber
IName
101
Sweat Shirt
101
Sweat Shirt
205
Trousers
205
Trousers
SName
WalMartDallas
Kmart
WalMartDallas
Kmart
SCity
Phoenix
SCity
Phoenix
Phoenix
Select


SELECT yields all attributes of selected tuples that satisfy a specified
condition.
It produces a horizontal subset of a table.
18
Project


19
PROJECT produces a list of all values for selected attributes.
It yields a vertical subset of a table.
Join



JOIN allows us to combine information from two
or more tables.
The tables participating in the join operation must
have attributes defined over a common domain.
EquiJoin: Compares specified columns of two
tables based on equality condition. The result is a
wider table where each row is formed by
concatenating two rows, one from each table, such
that the two rows have the same values in these two
columns.
 Performed
by a Product followed by a Select.
20
JOIN - Example
21
JOIN - Example
22
JOIN - Example
23
Join

24
Natural join: EquiJoin with the duplicate column removed.
Performed by a Project on the result of equijoin.
 When
the term Join is mentioned without any prefix, it is
implied to be Natural Join.


Example:
Outer Join: Unmatched rows from the participating tables are
retained in the result table with unmatched attributes left blank
or null.
 Example:

Theta Join: EquiJoin with the equality operator replaced by
any other comparison operator, such as greater than, less than,
etc.
Outer Join - Example
25
Divide





Consider dividing a relation A with two attributes X
and Y by a relation B with a single attribute Y.
Note that attribute Y is common to both A and B.
A can be thought of as a set of pairs of values <x,y>
and B as a set of single values <y>.
The result of dividing A by B is C, a set of values of
x such that the pair <x,y> appears in A for all values
of y appearing in B.
In general relation A can be of degree m+n, and
relation B can be of degree n.
26
Divide

Examples of Divide
A (Supplier-Part)
Sup# Part#
S1
P1
S1
P2
S1
P3
S1
P4
S1
P5
S1
P6
S2
P1
S2
P2
S3
P2
S4
P2
S4
P4
S4
P5
B1 (Part)
Part#
P1
C1(A Divided by B1)
Sup#
S1
S2
B2 (Part)
Part#
P2
P4
C2 (A Divided by B2)
Sup#
?
?
B3 (Part)
Part#
P1
P2
P3
B4 (Part)
Part#
p5
C3 (A Divided by B3)
Sup#
?
C4(A Divided by B4)
Sup#
?
27
Relationships within the Relational Database



28
The relational database model supports all three types of
relationships, i.e., 1:1, 1:M, M:N
The Entity Relationship (E-R) Model is used to describe the
relationships among entities.
E-R Diagram (ERD) is used to pictorially represent the E-R
model (More in Ch. 4):
 Rectangles
are used to represent entities.
 Diamonds are used to represent the relationship(s) between the
entities.
 The number 1 is used to represent the “1” side of the
relationship and the letter M is used to represent the “many”
sides of the relationship.
Examples of 1:1 and 1:M Relationships
1
1
DEAN
COLLEGE
Administers
1:1 Relationship
M
1
COURSE
Generates
1:M Relationship
CLASS
29
Expanding an M:N Relationship
N
M
STUDENT
TAKES
CLASS
An M:N relationship is converted into two 1:M relationships
by adding a composite or bridge entity.
This simplifies mapping to corresponding tables.
1
STUDENT
M
M
ENROLL
1
CLASS
The composite entity ENROLL has a primary key composed
of the primary keys of STUDENT and CLASS entities.
30
31
E-R Diagram Example
1
COURSE
Generates
1
STUDENT
M
M
ENROLL
1
M
CLASS
Fig 2.26 E-R Diagram showing STUDENT, COURSE, and CLASS
Relational Schema Example
32
Data Redundancy Revisited




The relational database model does not completely eliminate
data redundancy but uses controlled data redundancy.
Foreign keys create redundant data, but serve the useful
purpose of maintaining referential integrity.
They are also used in Join operations.
Sometimes user requirements demand storing apparently
redundant data, such as storing the sale price in Invoice Line
Item. A deeper analysis reveals no redundancy (See the
Invoice example).
33
Data Redundancy Revisited

In this example Product Price is copied from the PRODUCT
table to the LINE table. Does this example actually carry
redundant data?
34
The Data Dictionary


Data dictionary contains metadata that describes the data
stored in the database.
It stores:
 the
names of the data items in the database
 the types and sizes of the data items
 the constraints on each data item
 the names of authorized users, the data items that each user can
access, and the types of access allowed.
35
Example - Data Dictionary
36
The System Catalog

37
System catalog is a very detailed system data dictionary. It
describes all objects within the database.
 System
catalog is a system-created database whose tables store
the database characteristics and contents.
 System catalog tables can be queried just like any other tables.
 System catalog automatically produces database documentation.
 All data dictionary information are found in the system catalog.
Example 1:M Relationship
Figure 2.18
38
Example 1:M Relationship
39
Example M:N Relationship
40
Example M:N Relationship
41
Converting M:N Relationship to Two
1:M Relationships
42
Converting M:N Relationship to Two
1:M Relationships (con’t.)
43
Converting M:N Relationship to Two
1:M Relationships (con’t.)
44
Indexes


Points to location
Makes retrieval of data faster
45
46
Review
A Logical View of Data
Entities, Attributes, and Entity Sets
Table: Characteristics of a Relational Table
Keys
Functional Dependence
Relational Database Operators(eight)
Union Compatibility
Relationships within the Relational Database
E-R Diagram
Relational Schema
The Data Dictionary
The System Catalog