Transcript Slide 1

3
Chapter 3
The Relational Database Model
Database Systems:
Design, Implementation, and Management,
Seventh Edition, Rob and Coronel
1
3
In this chapter, you will learn:
• That the relational database model takes a
logical view of data
• The relational model’s basic components are
relations implemented through tables in a
relational DBMS
• How relations are organized in tables
composed of rows (tuples) and columns
(attributes)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
2
3
In this chapter, you will learn (continued):
• About relational database operators, the data
dictionary, and the system catalog
• How data redundancy is handled in the
relational database model
• Why indexing is important
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
3
3
A Logical View of Data
• Relational model
– Enables programmer to view data logically
rather than physically
• Table
– Has advantages of structural and data
independence
– Resembles a file from conceptual point of view
– Easier to understand than its hierarchical and
network database predecessors
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
4
3
Tables and Their Characteristics
• Table: two-dimensional structure composed
of rows and columns
• Contains group of related entities = an entity
set
– Terms entity set and table are often used
interchangeably
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
5
3
Tables and Their Characteristics
(continued)
• Table also called a relation because the
relational model’s creator, Codd, used the
term relation as a synonym for table
• Think of a table as a persistent relation:
– A relation whose contents can be permanently
saved for future use
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
6
3
Tables and Their Characteristics
(continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
7
3
Tables and Their Characteristics (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
8
3
Keys
• Consists of one or more attributes that determine
other attributes
• Primary key (PK) is an attribute (or a combination of
attributes) that uniquely identifies any given entity
(row)
• Key’s role is based on determination
– If you know the value of attribute A, you can look up
(determine) the value of attribute B
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
9
3
Keys (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
10
3
Composite Keys
• Composite key
– Composed of more than one attribute
• Example (CUS_FirstName, CUS_LastName)
• Key attribute
– Any attribute that is part of a key
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
11
3
Superkeys and Candidate keys
• Superkey
– Any key that uniquely identifies each row
• Example: (CUS_NUM, CUS_LastName)
• Can have redundancies as CUS_LastName is not unique
• Candidate key
– A superkey without redundancies (minimal superkey)
• Example CUS_NUM
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
12
3
NULLS
• Null value:
– Represent No data entry (has its own code e.g. ASCII code)
• Not permitted in primary key
• Should be avoided in other attributes
• Can represent
– An unknown attribute value
– A known, but missing, attribute value
– A “not applicable” condition
• Can create problems when functions such as COUNT,
AVERAGE, and SUM are used
• Can create logical problems when relational tables are linked
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
13
3
Redundancy
• Controlled redundancy:
– Makes the relational database work
• Tables within the database share common
attributes that enable the tables to be linked
together
• Multiple occurrences of values in a table are
not redundant when they are required to make
the relationship work
– Redundancy exists only when there is
unnecessary duplication of attribute values
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
14
3
Keys (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
15
3
Keys (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
16
3
Foreign key (FK)
• An attribute whose values match primary key
values in the related table
• Foreign keys are necessary to implement 1:M
relationships
• Referential integrity
– FK contains a value that refers to an existing
valid tuple (row) in another relation
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
17
3
Secondary Key
• Key used strictly for data retrieval purposes
• Example:
• A CUSTOMER Table has CUS_NUM as Primary key.
• Most Customers can not remember their CUS_NUM
• Use (CUS_LastName, CUS_Phone) as a secondary
key to retrieve customer record
• Secondary key can yield several records (not unique)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
18
3
Keys (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
19
3
Integrity Rules
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
20
3
Integrity Rules (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
21
3
Integrity Rules (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
22
3
Relational Database Operators
• Relational algebra
– Defines theoretical way of manipulating
table contents using relational operators
– Use of relational algebra operators on
existing tables (relations) produces new
relations
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
23
3
Relational Algebra Operators (continued)
1. UNION
2. INTERSECT
3. DIFFERENCE
4. PRODUCT
5. SELECT
6. PROJECT
7. JOIN
8. DIVIDE
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
24
3
Relational Algebra Operators (continued)
1. Union:
– Combines all rows from two tables, excluding
duplicate rows
– Tables must have the same attribute
characteristics
2. Intersect:
– Yields only the rows that appear in both tables
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
25
3
Relational Algebra Operators (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
26
3
Relational Algebra Operators (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
27
3
Relational Algebra Operators (continued)
3. Difference
– Yields all rows in one table not found in the
other table — that is, it subtracts one table
from the other
4. Product
– Yields all possible pairs of rows from two
tables
•
Also known as the Cartesian product
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
28
3
Relational Algebra Operators (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
29
3
Relational Algebra Operators (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
30
3
Relational Algebra Operators (continued)
5. Select
– Yields values for all rows found in a table
– Can be used to list either all row values or it
can yield only those row values that match a
specified criterion
– Yields a horizontal subset of a table
6. Project
– Yields all values for selected attributes
– Yields a vertical subset of a table
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
31
3
Relational Algebra Operators (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
32
3
Relational Algebra Operators (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
33
3
Relational Algebra Operators (continued)
7. Join
– Allows information to be combined from two
or more tables
– Real power behind the relational database,
allowing the use of independent tables linked
by common attributes
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
34
3
Relational Algebra Operators (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
35
3
Relational Algebra Operators (continued)
• Natural Join
– Links tables by selecting only rows with common
values in their common attribute(s)
– Result of a three-stage process:
• PRODUCT of the tables is created
• SELECT is performed on Step 1 output to yield only the
rows for which the AGENT_CODE values are equal
– Common column(s) are called join column(s)
• PROJECT is performed on Step 2 results to yield a
single copy of each attribute, thereby eliminating
duplicate columns
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
36
3
Relational Algebra Operators (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
37
3
Relational Algebra Operators (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
38
3
Relational Algebra Operators (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
39
3
Relational Algebra Operators (continued)
• Natural Join:
– Final outcome yields table that
• Does not include unmatched pairs
• Provides only copies of matches
– If no match is made between the table
rows
• the new table does not include the
unmatched row
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
40
3
Relational Algebra Operators (continued)
• Natural Join (continued):
– The column on which the join was made that is, AGENT_CODE - occurs only once
in the new table
– If the same AGENT_CODE were to occur
several times in the AGENT table,
• a customer would be listed for each match
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
41
3
Relational Algebra Operators (continued)
• Equijoin
– Links tables on the basis of an equality condition that
compares specified columns of each table
– Outcome does not eliminate duplicate columns
– Condition or criterion to join tables must be explicitly
defined
– Takes its name from the equality comparison operator
(=) used in the condition
• Theta join
– If any other comparison operator is used
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
42
3
Relational Algebra Operators (continued)
• Outer join:
– Matched pairs are retained and any unmatched values
in other table are left null
– In outer join for tables CUSTOMER and AGENT, two
scenarios are possible:
• Left outer join
– Yields all rows in CUSTOMER table, including those
that do not have a matching value in the AGENT
table
• Right outer join
– Yields all rows in AGENT table, including those that
do not have matching values in the CUSTOMER
table
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
43
3
Relational Algebra Operators (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
44
3
Relational Algebra Operators (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
45
3
Relational Algebra Operators (continued)
8. DIVIDE requires the use of one singlecolumn table and one two-column table
–
Table 1 (two-column) is divided by Table 2 (single-column)
to produce Table 3 (single-column)
–
The result will have the unshared column with values
associated with every value in Table 1 for the common
values between Tables 1 & 2
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
46
3
Relational Algebra Operators (continued)
Table 1
Table 2
Table 3
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
47
3
The Data Dictionary and System Catalog
• Data dictionary
– Provides detailed accounting of all tables found within
the user/designer-created database
– Contains (at least) all the attribute names and
characteristics for each table in the system
– Contains metadata—data about data
– Sometimes described as “the database designer’s
database” because it records the design decisions
about tables and their structures
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
48
3
A Sample Data Dictionary
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
49
3
The Data Dictionary and System Catalog
(continued)
• System catalog
– Contains metadata
– Detailed system data dictionary that describes
all objects within the database
– Terms “system catalog” and “data dictionary”
are often used interchangeably
– Can be queried just like any user/designercreated table
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
50
3
Relationships within the Relational
Database
• 1:M relationship
– Relational modeling ideal
– Should be the norm in any relational database design
• 1:1 relationship
– Should be rare in any relational database design
• M:N relationships
– Cannot be implemented as such in the relational model
– M:N relationships can be changed into two 1:M relationships
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
51
3
The 1:M Relationship
• Relational database norm
• Found in any database environment
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
52
3
The 1:M Relationship (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
53
3
The 1:M Relationship (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
54
3
The 1:M Relationship (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
55
3
The 1:M Relationship (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
56
3
The 1:1 Relationship
• One entity can be related to only one other
entity, and vice versa
• Sometimes means that entity components
were not defined properly
• Could indicate that two entities actually
belong in the same table
• As rare as 1:1 relationships should be, certain
conditions absolutely require their use
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
57
3
The 1:1 Relationship (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
58
3
The 1:1 Relationship (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
59
3
The M:N Relationship
• Can be implemented by breaking it up to
produce a set of 1:M relationships
• Can avoid problems inherent to M:N
relationship by creating a composite entity or
bridge entity
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
60
3
The M:N Relationship (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
61
3
The M:N Relationship (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
62
3
The M:N Relationship (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
63
3
The M:N Relationship (continued)
• Implementation of a composite entity
• Yields required M:N to 1:M conversion
• Composite entity table must contain at least
the primary keys of original tables
• Linking table contains multiple occurrences of
the foreign key values
• Additional attributes may be assigned as
needed
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
64
3
The M:N Relationship (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
65
3
The M:N Relationship (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
66
3
The M:N Relationship (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
67
3
The M:N Relationship (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
68
3
Data Redundancy Revisited
• Data redundancy leads to data anomalies
– Such anomalies can destroy the effectiveness
of the database
• Foreign keys
– Control data redundancies by using common
attributes shared by tables
– Crucial to exercising data redundancy control
• Sometimes, data redundancy is necessary
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
69
3
Data Redundancy Revisited (continued)
INV_NUMBER
INVOICE
1003
LINE_NUMBER
1)-------2)-------3)--------
Note: Each line has only one product
information
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
70
3
Data Redundancy Revisited (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
71
3
Indexes
• Arrangement used to logically access rows in
a table
• Index key
– Index’s reference point
– Points to data location identified by the key
• Unique index
– Index in which the index key can have only one
pointer value (row) associated with it
• Each index is associated with only one table
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
72
3
Indexes (continued)
Indexed
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
73
3
Indexes (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
74
3
Codd’s Relational Database Rules
• In 1985, Codd published a list of 12 rules to
define a relational database system
• The reason was the concern that many
vendors were marketing products as
“relational” even though those products did
not meet minimum relational standards
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
75
3
Codd’s Relational Database Rules
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
76
3
Summary
• Tables are basic building blocks of a relational
database
• Keys are central to the use of relational tables
• Keys define functional dependencies
–
–
–
–
–
Superkey
Candidate key
Primary key
Secondary key
Foreign key
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
77
3
Summary (continued)
• Each table row must have a primary key which uniquely
identifies all attributes
• Tables can be linked by common attributes. Thus, the
primary key of one table can appear as the foreign key in
another table to which it is linked
• The relational model supports relational algebra functions:
SELECT, PROJECT, JOIN, INTERSECT, UNION,
DIFFERENCE, PRODUCT, and DIVIDE.
• Good design begins by identifying appropriate entities and
attributes and the relationships among the entities. Those
relationships (1:1, 1:M, and M:N) can be represented
using ERDs.
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
78