Database Systems: Design, Implementation, and Management: The Relational Database Model
Database Systems: Design, Implementation, and Management: The Relational Database Model
Database Systems: Design, Implementation, and Management: The Relational Database Model
Ninth Edition
Objectives
In this chapter, students will learn:
That the relational database model offers a logical view of data About the relational models basic component: relations That relations are logical constructs composed of rows (tuples) and columns (attributes) That relations are implemented as tables in a relational DBMS
Database Systems, 9th Edition 2
Objectives (contd.)
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
Table
Structural and data independence Resembles a file conceptually
Relational database model is easier to understand than hierarchical and network models
Database Systems, 9th Edition 4
Keys
Each row in a table must be uniquely identifiable Key is one or more attributes that determine other attributes Keys role is based on determination
If you know the value of attribute A, you can determine the value of attribute B
Functional dependence
Attribute B is functionally dependent on A if all rows in table that agree in value for A also agree in value for B
Database Systems, 9th Edition 8
Keys (contd.)
Composite key
Composed of more than one attribute
Key attribute
Any attribute that is part of a key
Superkey
Any key that uniquely identifies each row
Candidate key
A superkey without unnecessary attributes
10
Keys (contd.)
Nulls
No data entry 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
11
Keys (contd.)
Nulls (contd.)
Can create problems when functions such as COUNT, AVERAGE, and SUM are used Can create logical problems when relational tables are linked
12
Keys (contd.)
Controlled redundancy
Makes the relational database work Tables within the database share common attributes
Enables tables to be linked together
Multiple occurrences of values not redundant when required to make the relationship work Redundancy exists only when there is unnecessary duplication of attribute values
Database Systems, 9th Edition 13
14
15
Keys (contd.)
Foreign key (FK)
An attribute whose values match primary key values in the related table
Referential integrity
FK contains a value that refers to an existing valid tuple (row) in another relation
Secondary key
Key used strictly for data retrieval purposes
Database Systems, 9th Edition 16
17
Integrity Rules
Many RDBMs enforce integrity rules automatically Safer to ensure that application design conforms to entity and referential integrity rules Designers use flags to avoid nulls
Flags indicate absence of some value
18
19
20
22
23
24
25
Equijoin
Links tables on the basis of an equality condition that compares specified columns
Theta join
Any other comparison operator is used
Outer join
Matched pairs are retained, and any unmatched values in other table are left null
Database Systems, 9th Edition 26
27
28
29
30
31
System catalog
Contains metadata Detailed system data dictionary that describes all objects within the database
Database Systems, 9th Edition 32
33
1:1 relationship
Should be rare in any relational database design
34
35
36
37
38
39
40
41
42
43
44
45
Foreign keys
Control data redundancies by using common attributes shared by tables Crucial to exercising data redundancy control
46
47
Indexes
Orderly arrangement to logically access rows in a table Index key
Indexs 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
49
50
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
51
Summary (contd.)
Each table row must have a primary key that uniquely identifies all attributes Tables are linked by common attributes The relational model supports relational algebra functions
SELECT, PROJECT, JOIN, INTERSECT UNION, DIFFERENCE, PRODUCT, DIVIDE