Transcript Kroenke-DBC-e02-PP

Chapter Two
The Relational Model
Chapter Objectives





Learn the conceptual foundation of the relational
model
Understand how relations differ from nonrelational
tables
Learn basic relational terminology
Learn the meaning and importance of keys, foreign
keys, and related terminology
Understand how foreign keys represent relationships
2
Chapter Objectives (continued)



Learn the purpose and use of surrogate keys
Learn the meaning of functional dependencies
Learn to apply a process for normalizing relations
3
Entity



An entity is something of importance to a user
that needs to be represented in a database
An entity represents one theme or topic
In an entity-relationship model (discussed in
Chapter 4), entities are restricted to things that
can be represented by a single table
4
Relation


A relation is a two-dimensional table that has
specific characteristics
The table dimensions, like a matrix, consist of
rows and columns
5
Characteristics of a Relation








Rows contain data about an entity
Columns contain data about attributes of the entity
Cells of the table hold a single value
All entries in a column are of the same kind
Each column has a unique name
The order of the columns is unimportant
The order of the rows is unimportant
No two rows may be identical
6
A Sample Relation
EmployeeNumber
100
101
104
107
FirstName
Mary
Jerry
Alea
Murugan
LastName
Abermany
Caldera
Copley
Jacksoni
7
A Nonrelation Example
Cells of the table hold multiple values
EmployeeNumber
Phone
100
335-6421,
454-9744
101
215-7789
104
610-9850
107
299-9090
LastName
Abermany
Caldera
Copley
Jacksoni
8
A Nonrelation Example
No two rows may be identical
EmployeeNumber
100
101
104
100
107
Phone
335-6421
215-7789
610-9850
335-6421
299-9090
LastName
Abermany
Caldera
Copley
Abermany
Jacksoni
9
A Sample Relation
10
A Nonrelation Example
11
A Nonrelation Example
12
A Sample Relation
13
Terminology
Synonyms…
Table
Row
Column
File or Datafile
Record
Field
Relation
Tuple
Attribute
14
A Key

A key is one (or more) columns of a
relation that is (are) used to identify a row
15
Uniqueness of Keys
Unique Key
Data value is unique for
each row.
Consequently, the key
will uniquely identify a
row.
Nonunique Key
Data value may be
shared among several
rows.
Consequently, the key
will identify a set of
rows.
16
A Composite Key


A composite key is a key that contains two or
more attributes
For a key to be unique, often it must
become a composite key
17
Composite Key Example



To identify a family member, you need to know
a FamilyID, a FirstName, and a Suffix (e.g., Jr.)
The composite key is:
(FamilyID, FirstName, Suffix)
One needs to know the value of all three
columns to uniquely identify an individual
18
A Candidate Key


A candidate key is called “candidate” because
it is a candidate to become the primary key
A candidate key is a unique key
19
A Primary Key


A primary key is a candidate key chosen to
be the main key for the relation
If you know the value of the primary key,
you will be able to uniquely identify a single
row
20
Relationship Notation
EMPLOYEE (EmployeeNumber, FirstName,
LastName, Department, Email, Phone)
DEPARTMENT (DeptName, BudgetCode,
OfficeNumber)
21
Specifying Primary Keys
EMPLOYEE (EmployeeNumber, FirstName,
LastName, Department, Email, Phone)
DEPARTMENT (DeptName, BudgetCode,
OfficeNumber)
22
Relationships Between Tables


A table may be related to other tables
For example
An Employee works in a Department
 A Manager controls a Project

23
A Foreign Key



To preserve relationships, you may need to
create a foreign key
A foreign key is a primary key from one table
placed into another table
The key is called a foreign key in the table that
received the key
24
Foreign Key Example
PROJECT
MANAGER
Primary Key
ProjID
ProjName
MgrID
MgrID
MgrName
Foreign Key
PROJECT (ProjID, ProjName, MgrID)
MANAGER (MgrID, MgrName)
25
Foreign Key Example
DEPARTMENT
DeptID
EMPLOYEE
Primary Key
DeptName
Location
EmpID
DeptID
Foreign Key
EmpName
DEPARTMENT (DeptID, DeptName, Location)
EMPLOYEE (EmpID, DeptID, EmpName)
26
Referential Integrity


Referential integrity states that every value of a
foreign key must match a value of an existing
primary key
For example (see previous slide)
If EmpID = 4 in EMPLOYEE has a DeptID = 7
(a foreign key), a Department with DeptID = 7 must
exist in DEPARTMENT
 The primary key value must exist before the foreign
key value is entered

27
Referential Integrity
EQUIPMENT (SerialNumber, Type, AcquisitionCost)
28
Suppose Equipment can be assigned to Employees.
Primary key of EMPLOYEE is EmployeeNumber.
EQUIPMENT (SerialNumber, Type, AcquisitionCost, EmployeeNumber)
Constraint: EmployeeNumber in EQUIPMENT must exist
in EmployeeNumber in EMPLOYEE
29
A Surrogate Key



A Surrogate Key is a unique, numeric value that is
added to a relation to serve as the Primary Key
Surrogate Key values have no meaning to users
and are usually hidden on forms, queries and
reports
A Surrogate Key is often used in place of a
composite primary key
30
Surrogate Key Example



If the Family Member Primary Key is FamilyID,
FirstName, Suffix, it would be easier to append and use a
surrogate key of FamMemberID
FamilyID, FirstName and Suffix remain in the relation
Referential Integrity:
Use…
(FamMemberID) in School must exist in
(FamMemberID) in FamilyMember
Instead of:
(FamilyID, FirstName, Suffix) in School must exist in
(FamilyID, FirstName, Suffix) in FamilyMember
31
Surrogate Key Example
Landscaping company – has tables:
PROPERTY (Street, City, State, Zip)
PLANT (ItemNumber, VarietyName, Price)
SERVICE (InvoiceNumber, Date, TotalHours)
32
33
34
The Relationships
A plant is sold for a particular property.
• A service is rendered for a particular
property.
PROPERTY (Street, City, State, Zip)
PLANT (ItemNumber, VarietyName, Price,
Street, City, State, Zip)
SERVICE (InvoiceNumber, Date,
TotalHours, Street , City, State, Zip)
•
35
The Constraints
(Street, City, State, Zip) in PLANT
must exist in (Street, City, State, Zip) in
PROPERTY
(Street, City, State, Zip) in SERVICE
must exist in (Street, City, State, Zip) in
PROPERTY
36
Fixing the Mess
That’s a lot of duplication in each table.
Let’s add surrogate keys to the tables.
PROPERTY (PropertyID, Street, City, State, Zip)
PLANT (ItemNumber, VarietyName, Price,
PropertyID)
SERVICE (InvoiceNumber, Date, TotalHours,
PropertyID)
37
The New Constraints
PropertyID in PLANT must exist in
PropertyID in PROPERTY
PropertyID in SERVICE must exist in
PropertyID in PROPERTY
38
39
40