Transcript Lecture 4

CPSC 603 Database Systems
Lecturer: Laurie Webster II, M.S.S.E., M.S.E.E.,
M.S.BME, Ph.D., P.E.
Lecture 4
Introduction to a First Course in Database Systems
Topics to be covered this Lecture
•Weak Entity Sets
•Summary of Chapter 2
•Relational Data ModelConversion from ER
Introduction
Weak Entity Sets
Weak Entity Set ==> Entity Set’s key is composed of
attributes of which some or all belong to another
Entity Set
There are two principal sources of weak entity sets
1) If entities of set E are subunits of entities in F, then
it is possible that then names of E entities are not
unique until we take into account the F entity to which
the E entity is subordinate
2) Connecting Entity Sets
Examples: Fig. 2.20 - Fig 2.22
Requirements for Weak Entities
• We cannot obtain key attributes for a weak entity set
indiscriminately. If E is a weak entity set then its
key consists of:
– Zero or more of its own attributes, and
– Key attributes from entity sets that are reached by certain
many-one relationships
Intuitive Rule for E.S. vs.
Attributes
• Make an Entity Set only if it either:
– Is more than a name of something; i.e., it has nonkey
attributes or relationships with a number of different
entity sets, or
– Is the “many” in a many-one relationship.
Example that Illustrates both
points
Beers Database E-R Model
Name
addr
name
Beers
ManfBy
Manfs
Manfs deserves to be an E.S. because we record addr, a nonkey
attribute
Beers deserves to be an E.S. because it is at the “many”
end
Summary of Chapter 2
• E/R Model - we describe entity sets, relationships
among entity sets, and attributes of entity sets and
relationships
• E/Diagrams - use rectangles, diamonds, and ovals to
draw entity sets, relationships, and attributes,
respectively
• Multiplicity of Relationships - Binary =>one-one,
many-one, or many-many
• Keys: a set of attributes that uniquely determines an
entity in a given entity set is a key for that entity set
Summary of Chapter 2 continued
• Good Design - Designing databases effectively
requires that we represent the real world
faithfully, the we select appropriate elements (e.g.,
relationships, attributes), and that we avoid
redundancy - saying the same thing twice or
saying something in an indirect or overly complex
manner
• Referential Integrity - A requirement that an entity
be connected, through a given relationship, to an
entity of some other entity set, and that latter
entity exists in the database, is called a referential
Summary of Chapter 2 continued
• Weak Entity Sets - An occasional complication
that arises in the E/R model is a weak entity set
that requires attributes of some related entity
set(s) to identify its own entities. A special
notation involving diamonds and rectangles with
double borders is used to distinguish weak entity
sets
The Relational Data Model
Chapter 3
The E/R model is:
a) simple
b) appropriate way to describe the structure of
data
HOWEVER!!!
Today's database implementations are almost always
based on another approach called the relational model
Relational Model
Relational Data Model ==> extremely useful
has a single data-modeling
concept => the relation
Relation ==> 2-D table
SQL ==> lets us write programs to manipulate in
powerful ways the data stored in relations
(2-D tables)
Relational Data Model
CONCEPTUAL MODELING ==> an important phase
in designing a successful data applications!!!
Our first goal is to see how to translate designs for E/R
notations into relations
The relation model has design theory of its own
This theory is called “normalization” of relations
Relational Data Model
Relational Model design theory ==> “normalization of
relations”
Normalization of relations ==> based on “functional
dependencies”
Using Normalization Theory ==> improve choice of
relations with which to represent a particular database
design
Basics of the Relational Model
The relational model gives us a single way to represent
data : 2-D table called a relation
EXAMPLE (Fig 3.1) relation name Movies
title
year
Star Wars
1977
Mighty Ducks 1991
Wayne’s World 1992d
length
filmType
124
104
95
color
color
color
Relational Data Model
• We will see that the Relation can do more than
represent entity sets
• NOTE: the attributes of the relation named Movies
is the same as the attributes of the entity set Movies
in the E/R model !!!!
• The name of a relation and the set of attributes for a
relation is called the schema for that relation
Relational Data Model
EXAMPLE (Fig 3.1) relation name Movies
title
year
Star Wars
1977
Mighty Ducks 1991
Wayne’s World 1992d
length
124
104
95
filmType
color
color
color
SCHEMAs
Movies(title, year, length, filmType)
The attributes in a relation schema are a set,not a list!!!
Relational Data Model
In a relational model, a design consists of one or
more relation schemas
The set of schemas for the relations in a design is
called a: relational database schema
Relational Database Schema ==> sometimes called
simply database schema
Tuples of the Relational Model
The rows of a relation, other than the header row
containing the attributes, are called tuples
Tuples has one component for each attribute of the
relation!!
The first of the three tuples of Fig. 3.1 has the four
components Star Wars, 1977, 124, and color for
attributes title, year, length, and filmType,
respectively
Functional Dependencies
Relational Model design theory ==> “normalization of
relations”
Normalization of relations ==> based on “functional
dependencies”
Using Normalization Theory ==> improve choice of
relations with which to represent a particular database
design
Functional Dependencies
Each Relation Schema consists of a number of
attributes
Each Relational Database Schema consists of a
number of relations schemas
Up to now we have assumed that attributes are
grouped to form a relation schema by using the
common sense of the database designer
Functional Dependencies
HOWEVER! We still need some formal measure of
why one grouping of attributes into a relation schema
may be better than another
We have not, up to this point, developed any measure
of the appropriateness, “goodness” or quality of the
design, other than the intuition of the designer
Functional Dependencies
Starting in this section of the Chapter we will begin
to discuss some of the theory that has been
developed to attempt to choose “good” relation
schemas
+ measure formally why one set of groupings
of attributes into relation schemas is better
than another
There are two levels at which we can discuss the
“goodness” of relations schemas
1) logical ( or conceptual) level
Functional Dependencies
• There are two levels at which we can discuss the
“goodness” of relations schemas
– 1) logical ( or conceptual) level
– 2) implementation ( or storage) level
• Logical level - how users interpret the relation
schemas and the meaning of their attributes. Having
good relation schemas at this level enables users to
understand clearly the meaning of the data in the
relations, and hence to formulate their queries
correctly
Functional Dependencies
• Implementation Level- how tuples in a base
relation are stored and updated. This applies only
to base relations - which will be physically stored
as files.
– At the Logical level we are interested in schemas of
both base relations and views (virtual relations)
Functional Dependencies
Informal Design Guidelines for
Relation Schemas
•
•
•
•
Semantics of the attributes
Reducing the redundant values in tuples
Reducing the null values in tuples
Disallowing the possibility of generating spurious
tuples
THESE MEASURES ARE NOT ALWAYS
INDEPENDENT OF ONE ANOTHER
Next Lecture
• Functional Dependencies
• Designing of Relational Database Schemas
• Normalization