Transcript Itec 3220 - Department of Mathematics and Statistics

ITEC 3220M
Using and Designing Database Systems
Instructor: Prof. Z.Yang
Course Website:
http://people.math.yorku.ca/~zyang/itec
3220m.htm
Office: Tel 3049
Chapter 3
The Relational Database Model
(Cont’d)
Relational Database Operators
• Relational algebra
– Defines theoretical way of manipulating table
contents using relational operators:
•SELECT
•PROJECT
•JOIN
•INTERSECT
•UNION
•DIFFERENCE
•PRODUCT
•DIVIDE
– Use of relational algebra operators on existing
tables (relations) produces new relations
3
Relational Algebra Operators
(continued)
• Union:
– Combines all rows from two tables,
excluding duplicate rows
– Tables must have the same attribute
characteristics
• Intersect:
– Yields only the rows that appear in both
tables
4
Union
5
Intersect
6
Relational Algebra Operators
(continued)
• Difference
– Yields all rows in one table not found in the other
table—that is, it subtracts one table from the other
7
Venn Diagrams for Traditional Set
Operators
Venn Diagram:
Union
Intersection
Differences
8
Product
Yields all possible pairs of rows from two tables
9
Relational Algebra Operators
(continued)
• 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
• Project
– Yields all values for selected attributes
– Yields a vertical subset of a table
10
Select
11
Project
12
Relational Algebra Operators
(continued)
• Join
– Allows us to combine information from two
or more tables
– Real power behind the relational database,
allowing the use of independent tables
linked by common attributes
13
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
14
Natural Join (continued)
• 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
15
Other Joins
• EquiJOIN
– Links tables based on equality condition that
compares specified columns of tables
– 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
16
Divide
Requires use of single-column table and two-column
table
17
Summary of Meanings of the
Relational Algebra Operators
• Select: Extracts rows that satisfy a specified
condition
• Project: Extracts specified columns
• Product: Builds a table from two tables consisting
of all possible combinations of rows, one from
each of the two tables
• Union: Builds a table from all rows appearing in
either of two tables
• Intersect: Builds a table consisting of all rows
appearing in both of two specified tables
18
Summary of Meanings of the Relational Algebra
Operators (Cont’d)
• Join: Extracts rows from a product of two
tables such that two input rows contributing
to any output row satisfy some specified
condition
• Outer Join: Extracts the matching rows of two
tables and the unmatched rows from both
tables
• Divide: Builds a table consisting of all values
of one column of a binary table that match all
values in a unary table
19
Chapter 4
Entity Relationship (E-R)
Modeling
In this chapter, you will learn:
• How relationships between entities are
defined and refined, and how such
relationships are incorporated into the
database design process
• Key terms: cardinality, connectivity, optional,
mandatory, strong relationship, weak
relationship, supertype, subtype, etc.
• How to develop an E-R diagram
21
The Entity Relationship (ER)
Model
• ER model forms the basis of an ER diagram
• ERD represents the conceptual database
as viewed by end user
• ERDs depict the ER model’s three main
components:
– Entities
– Attributes
– Relationships
22
Entities
• Refers to the entity set and not to a single
entity occurrence
• Corresponds to a table and not to a row in the
relational environment
• In both the Chen and Crow’s Foot models, an
entity is represented by a rectangle
containing the entity’s name
• Entity name, a noun, is usually written in
capital letters
23
Attributes
• Characteristics of entities
• Domain is set of possible values
• Primary keys underlined
24
Examples
• EMPLOYEE (EMPLOYEE _ID, EMPLOYEE
_NAME, ADDRESS, DATE-EMPLOYED)
EMPLOYEE
EMPLOYEE
_NAME
EMPLOYEE
_ID
ADDRESS
EMPLOYEE _ID
EMPLOYEE
_NAME
EMPLOYEE
DATEEMPLOYED
ADDRESS
DATEEMPLOYED
25
Attributes (Cont’d)
• Simple
– Cannot be subdivided
– Age, sex, marital status
• Composite
– Can be subdivided into
additional attributes
– Address into street, city, zip
• Single-valued
– Can have only a single value
– Person has one social
security number
• Multi-valued
– Can have many values
– Person may have several
college degrees
– In the Chen E-R model, the
multivalued attributes are
shown by a double line
connecting the attributes to
the entity
• Derived
– Can be derived with algorithm
– Age can be derived from date
of birth
– Versus stored attribute
26
Attributes (Cont’d)
An attribute
broken into
component parts
Street_Address
Address
City
State
Post_Code
27
Attributes (Cont’d)
Entity with a multivalued attribute (Skill) and
derived attribute (Years_Employed)
Employee_ID
Years_Employed
Employee_Name
EMPLOYEE
Address
Skills
Date_Employed
28
How to Deal with Multivalued
Attributes
• With the original entity, create several new
attributes, one for each of the original
multivalued attribute’s components.
• Create a new entity composed of the original
multivalued attribute’s components.
29
An Example
Mod_code
Car_Vin
Car_Year
CAR
Car_Color
30
Relationships
• Association between entities
• Connected entities are called participants
• Operate in both directions
• Connectivity describes relationship
classification
– 1:1, 1:M, M:N
• Cardinality
– Expresses number of entity occurrences
associated with one occurrence of related entity
31
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
32
Connectivity and Cardinality in an
ERD
33
Relationship Strength
• Existence dependence
– Entity’s existence depends on existence of related
entities
– Existence-independent entities can exist apart from
related entities
– EMPLOYEE claims DEPENDENT
• Weak (non-identifying)
– One entity is existence-independent on another
– PK of related entity doesn’t contain PK component of
parent entity
• Strong (identifying)
– One entity is existence-dependent on another
– PK of related entity contains PK component of parent
entity
34
Weak Entity
• Existence-dependent on another entity
• Has primary key that is partially or totally
derived from parent entity
35
Relationship Participation
• Optional
– Entity occurrence does not require a corresponding
occurrence in related entity
– Shown by drawing a small circle on side of optional
entity on ERD
• Mandatory
– Entity occurrence requires corresponding
occurrence in related entity
– If no optionality symbol is shown on ERD, it is
mandatory
36
Relationship Degree
• Indicates number of associated entities
• Unary
– Single entity
– Exists between occurrences of same entity set
• Binary
– Two entities associated
– Most common
– To simplify the conceptual design, most higher-order
relationships are decomposed into appropriate
equivalent relationships when possible
• Ternary
– Three entities associated
37
Three Types of Relationships
38
Recursive Relationship
• Definition: A relationship can exist
between occurrences of the same entity
set.
1
1
PERSON
is married to
1
M
EMPLOYEE
manages
39
Composite Entities
• Also known as bridge entities
• Composed of the primary keys of each of
the entities to be connected
• May also contain additional attributes that
play no role in the connective process
40
A Composite Entity in an ERD
41
Example M:N Relationship
42
Converting M:N Relationship to Two 1:M
Relationships (Cont’d)
43
An Example
1
W
X
STORE
(a,b)
Y
Z
ORDER
(e,f)
(g,h)
PRODUCT
(i,j)
(k,l)
employs
M
(c,d)
1
EMPLOYEE
M
DEPENDENT
claims
(m,n)
(o,p)
44
Comparison of E-R Modeling
Symbols
45
Developing an E-R Diagram
• Iterative Process
– Step1: General narrative of organizational
operations developed
– Step2: Basic E-R Model graphically depicted and
reviewed
– Step3: Modifications made to incorporate newly
discovered E-R components
• Repeat process until designers and users
agree E-R Diagram complete
46
Example
• Create an ERD using the following
business rules:
– A company operates four departments
– Each department employs employees
– Each of the employees may or may not have
one or more dependents
– Each employee may or may not have an
employment history
47
Exercise
Design an E-R diagram for a real estate firm that lists property
of sale.
The
firm has a number of sales offices in several states.
Each
sales office is assigned one or more employees.
Attributes of employees include ID and name. An employee
must be assigned to only one sales office.
For
each sales office, there is always one employee assigned
to manage that office. An employee may manage only the sales
office to which he is assigned.
The
firm lists property for sale. Attributes of property include ID
and location. Components of location include address, city,
state, and Zip_code.
Each
unit of property must be listed with one of the sales
offices. A sales office may have any number of properties listed,
or may have no properties listed.
Each
unit of property has one or more owners. An owner may
own one or more units of property. An attribute of the
relationship between property and owner is Percent_Owned.48