Transcript Database Systems: Design, Implementation, and Management

2
Chapter 2
The Relational Database Model
Database Systems: Design, Implementation, and
Management, Fifth Edition, Rob and Coronel
2
In this chapter, you will learn:
• That the relational database model takes a
logical view of data
• That the relational database model’s basic
components 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 the relational
database model
• Why indexing is important
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
2
2
Logical View of Data
• Relational Database
– Designer focuses on logical representation rather
than physical
– Use of table advantageous
• Structural and data independence
• Related records stored in independent tables
• Logical simplicity
– Allows for more effective design strategies
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
3
2
Logical View of Data (con’t.)
• Entities and Attributes
– Entity is a person, place, event, or thing about
which data is collected
– Attributes are characteristics of the entity
• Tables
– Holds related entities or entity set
– Also called relations
– Comprised of rows and columns
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
4
2
Table Characteristics
• Two-dimensional structure with rows and
columns
• Rows (tuples) represent single entity
• Columns represent attributes
• Row/column intersection represents single
value
• Tables must have an attribute to uniquely
identify each row
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
5
2
Table Characteristics (con’t.)
• Column values all have same data format
• Each column has range of values called
attribute domain
• Order of the rows and columns is immaterial to
the DBMS
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
6
2
Keys
• One or more attributes that
determine other attributes
– Key attribute
– Composite key
• Full functional dependence
• Entity integrity
– Uniqueness
– No ‘null’ value in key
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
7
2
Example Tables
Figure 2.1
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
8
2
Simple Relational Database
Figure 2.2
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
9
2
Keys (con’t.)
• Superkey
– Uniquely identifies each entity
• Candidate key
– Minimal superkey
• Primary key
– Candidate key to uniquely identify all other
attributes in a given row
• Secondary key
– Used only for data retrieval
• Foreign key
– Values must match primary key in another table
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
10
2
Integrity Rules
• Entity integrity
– Ensures all entities are unique
– Each entity has unique key
• Referential integrity
– Foreign key must have null value or match
primary key values
– Makes it impossible to delete row whose primary
key has mandatory matching foreign key values in
another table
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
11
2
Relational Database Operators
• Relational algebra determines
table manipulations
• Key operators
– SELECT
– PROJECT
– JOIN
• Other operators
–
–
–
–
–
INTERSECT
UNION
DIFFERENCE
PRODUCT
DIVIDE
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
12
2
Union
Combines all rows
Figure 2.5
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
13
2
Intersect
Yields rows that appear in both tables
Figure 2.6
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
14
2
Difference
Yields rows not found in other tables
Figure 2.7
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
15
2
Product
Yields all possible pairs from two tables
Figure 2.8
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
16
2
Select
Yields a subset of rows based on specified criterion
Figure 2.9
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
17
2
Project
Yields all values for selected attributes
Figure 2.10
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
18
2
Join
Information from two or more tables is combined
Figure 2.11
Figure 2.14
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
19
2
Natural Join Process
• Links tables by selecting rows with common
values in common attribute(s)
• Three-stage process
– Product creates one table
– Select yields appropriate rows
– Project yields single copy of each attribute to
eliminate duplicate columns
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
20
2
Other Joins
• EquiJOIN
– Links tables based on equality condition that
compares specified columns of tables
– Does not eliminate duplicate columns
– Join criteria must be explicitly defined
• Theta JOIN
– EquiJOIN that compares specified columns of
each table using operator other than equality one
• Outer JOIN
– Matched pairs are retained
– Unmatched values in other tables left null
– Right and left
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
21
2
Divide
Requires user of single-column table and two-column table
Figure 2.17
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
22
2
Data Dictionary and System Catalog
• Data dictionary
– Provides detailed account of all tables found within
database
– Metadata
– Attribute names and characteristics
• System catalog
–
–
–
–
–
Detailed data dictionary
System-created database
Stores database characteristics and contents
Tables can be queried just like any other tables
Automatically produces database documentation
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
23
2
Relationships within Relational Database
• Relationship classifications
– 1:1
– 1:M
– M:N
• E-R Model
– ERD Maps E-R model
– Chen
– Crow’s Feet
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
24
2
ERD Symbols
• Rectangles represent entities
• Diamonds represent the relationship(s)
between the entities
• “1” side of relationship
– Number 1 in Chen Model
– Bar crossing line in Crow’s Feet Model
• “Many” relationships
– Letter “M” and “N” in Chen Model
– Three pronged “Crow’s foot” in Crow’s Feet
Model
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
25
2
Example 1:M Relationship
Figure 2.18
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
26
2
Example 1:M Relationship
Figure 2.20
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
27
2
Example M:N Relationship
Figure 2.23
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
28
2
Example M:N Relationship
Figure 2.24
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
29
2
Converting M:N Relationship to Two
1:M Relationships
Figure 2.25
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
30
2
Converting M:N Relationship to Two
1:M Relationships (con’t.)
Figure 2.26
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
31
2
Converting M:N Relationship to Two
1:M Relationships (con’t.)
Figure 2.27
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
32
2
Converting M:N Relationship to Two
1:M Relationships (con’t.)
Figure 2.28
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
33
2
Data Redundancy Revisited
• Foreign keys can reduce redundancy
• Some redundancy is desirable
– Called controlled redundancy
– Speed
– Information requirements
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
34
2
Indexes
• Points to location
• Makes retrieval of data faster
Figure 2.31
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
35