Transcript Slides I

Midterm Review I
Database Studies
• Design of databases.
– What kinds of information go into the database?
– How is the information structured?
– How do data items connect?
• Database programming.
– How does one express queries on the database?
– How does one use other capabilities of a DBMS, such as transactions or
constraints, in an application?
– How is database programming combined with conventional
programming?
• Database system implementation.
– How does one build a DBMS, including such matters as query
processing, transaction processing and organizing storage for efficient
access?
Database Modeling
Ideas
E/R design
Abstract design
Relational schema
A mechanical process
Relational DBMS
Concrete design
“Bars-Beer-Drinkers” (BBD) Example
name
addr
name
Bars
Beers
Sells
license
Note:
license =
beer, full,
none
Frequents
name
Why we need it?
Drinkers
manf
Likes
Bars sell some
beers.
Drinkers like
some beers.
Drinkers frequent
some bars.
addr
Ternary Relationships
name
license
addr
name
Bars
manf
Beers
Preferences
Why we would
need it?
Drinkers
name
addr
Weak Entity Sets
• Occasionally, entities of an entity set need “help” to identify them
uniquely.
• Entity set E is weak if in order to identify entities of E uniquely, we
need to follow one or more many-one relationships from E and
include the key of the related entities from the connected entity sets.
• Example. Crews might have a number and some description,
• E.g. “Crew 1, Special Effects.” However, there can be another crew
with the same data “Crew 1, Special Effects” but which belongs to
another studio.
• Clearly, we need to add the key for studios, in order to have a key
for crews.
In E/R diagrams
number
name
role
Crews
Unit-of
address
Studios
No. of emp.
• Double diamond for supporting many-one relationship.
• Double rectangle for the weak entity set.
Another Example – Football Players
• name is almost a key for football players, but
there might be two with the same name.
• number is certainly not a key, since players on
two teams could have the same number.
• But number, together with the team name
related to the player by Plays-on should be
unique.
In E/R Diagrams
name
number
Players
name
Playson
Teams
• Double diamond for supporting many-one relationship.
• Double rectangle for the weak entity set.
Weak Entity-Set Rules
• A weak entity set has one or more many-one
relationships to other (supporting) entity sets.
– Not every many-one relationship from a weak entity set
need be supporting.
• The key for a weak entity set is its own underlined
attributes and the keys for the supporting entity
sets.
Supporting relationships
• The supporting relationships must have the referential integrity
enforced.
– I.e. rounded arrow in the “one side”.
• If there are more than one supporting relationships, then all of
them will “bring” a key component for the key of the weak
entity set.
• Not all the many-one relationships connecting a weak entity set
(many side) to other entity sets are supporting relationships. E.g.
number
Operat. Cities
role
Crews
Op
name
Unit-of
address
Studios
No. of emp.
When Do We Need Weak Entity Sets?
• The usual reason is that there is no global
authority capable of creating unique ID’s.
• Example: it is unlikely that there could be an
agreement to assign unique player numbers
across all football teams in the world.
Subclasses
•
Often, a class contains certain objects that have special properties not
associated with all members of the class.
•
If so, we find it useful to organize the class into subclasses, each subclass
having its own special attributes and/or relationships.
•
We relate the parent with child entity sets by a special (1-1) relationship called
isa.
to Stars
length
title
year
filmType
Movies
Voices
isa
Cartoons
isa
weapon
MurderMysteries
The Relational Data Model
Database
Model
(E/R)
Diagrams (E/R)
Relational
Schema
Tables:
row names: attributes
rows: tuples
Physical
storage
Complex
file organization
and index
structures.
Terminology
Attribute names
tuples
Title
Year
Star Wars
1997
124
color
Mighty Ducks
1991
104
color
Wayne’s World 1992
95
color
...
...
...
...
Length
FilmType
components of tuples
More Terminology
Every attribute has an atomic type.
Relation Schema: relation name + attribute names + attribute types
Relation instance: a set of tuples. Only one copy of any tuple!
Database Schema: a set of relation schemas.
Database instance: a relation instance for every relation in the schema.
From E/R Diagrams to Relations
• Entity sets become relations with the same
set of attributes.
• Relationships become relations whose
attributes are only:
– The keys of the connected entity sets.
– Attributes of the relationship itself.
Entity Sets to Relations
year
title
Relation schema:
Movies(title, year, length, filmtype )
Movies
length
filmType
A relation instance:
title
year
length
filmtype
Star Wars
1977
124
Color
Might Ducks
1991
104
Color
Wayne’s World
1992
95
Color
E/R Relationships to Relations
• Relationships in the E/R model are also represented by relations.
• The relation for a given relationship R has the following attributes:
– For each entity set involved in relationship, we take its key attribute(s) as
part of the schema of the relation for R.
– If the relationship has attributes, these are also attributes of relation R.
• When one entity set is involved several times in a relationship
- or the same attribute name appears in the keys of different ES participating in
the relationship R,
- or even when we like to avoid confusion and to be clearer in meaning
we should rename the attributes.
Example (with Renaming)
name
husband
addr
Drinkers
1
name
Likes
manf
Beers
2
Buddies
Favorite
wife
Married
Likes(drinker, beer)
Favorite(drinker, beer)
Buddies(name1, name2)
Married(husband, wife)
Combining Relations
• It’s OK to combine the relation for an entity-set E
with the relation R for a many-one relationship
from E to another entity set.
• Example:
Drinkers(name, addr) and Favorite(drinker, beer)
combine to make
Drinker1(name, addr, favBeer)
Risk with Many-Many Relationships
• Combining Drinkers with Likes would be
a mistake. Why?
• It leads to redundancy, as:
name
addr
beer
Sally 123 Maple Bud
Sally 123 Maple Miller
Redundancy
Handling Weak Entity Sets
• Relation for a weak entity set must include
attributes for its complete key (including
those belonging to other entity sets), as well
as its own, nonkey attributes.
• A supporting (double-diamond) relationship
is redundant and yields no relation.
Example
name
number
Players
name
Playson
Teams
Teams(name)
Players(number, teamName, name)
PlaysOn(number, teamName, teamName2)
PlaysOn becomes part of
Players
Must be the same
Entity Sets Versus Attributes I
• Example: Bad design
name
manf
manfAddr
Beers
This design repeats the manufacturer’s address once
for each beer;
Loses the address if there are temporarily no beers for
a manufacturer.
Ex.1,2
location
area
cid
Complexes
eventid
Takes place
Chief Org
MultiSport
date
np
Events
Used for
Involved
OneSport
Officials
Equipment
locIndicator
Designated for
Sports
name
Designated for
oid
name
equipid
description
Complexes(cid,location,area)
MultiSport(cid)
DesignatedFor(cid,sportname,locIndicator)
OneSport(cid,sportname)
Officials(oid, name)
Events(eventid,date,np,chief_org_oid)
Involved(oid,eventid)
Equipment(equipid,description)
UsedFor(equipid,eventid)
Ex.3,4
name
Personnel(pid,name,dob,pob)
Players(pid,ba,baorientation,tid)
Pitchers(pid,era)
Coaches(pid,tid)
Manangers(pid,tid)
Umpires(pid)
Teams(tid,name,city,division)
Games(gid,date,winning_pid,losing_pid)
Plays(gid,tid1,tid2,runs1,hits1,error1, runs2,hits2,error2)
Hits(gid,pid,singles,doubles,triples,hruns)
SavePitcher(gid,pid)
dob
pid
pob
Personnel
ba
borientation
Players
Coaches
Managers
Umpires
coaches
era
manages
memberOf
name
tid
city
Pitchers
Teams
singles
division
doubles
save
winning
hits
losing
triples
hruns
runs1
plays
errors1
runs2
Games
gid
errors2
date
hits2
hits1
Ex.5a
numbe
r
name
name
code
dob
Students
Enroll
ed
phone
Departments
semester
Registered
Offere
d
grade
Courses
location
name
desc
Ex.5b
numbe
r
name
name
code
DOB
Students
Enroll
ed
Semesters
phone
Departments
Registered
Offere
d
grade
SemId
Courses
location
name
desc
Exercise 3.6.1
R(A,B,C,D) with FD’s ABC, CD, and DA
Indicate all the BCNF violations. Do not forget to
consider FD’s that are not in the given set, but
follow from them.
Indicate all the BCNF violations. Do not forget to
consider FD’s that are not in the given set, but
follow from them.
Babies
Exercise 2.2.5: At a birth, there is one baby (twins would
be represented by two births), one mother, any number
of nurses, and any number of doctors. Suppose,
therefore, that we have entity sets Babies, Mothers,
Nurses, and Doctors.
Suppose we also use a relationship Births, which
connects these four entity sets. Note that a tuple of the
relationship set for Births has the form
(baby, mother, nurse, doctor)
Babies (Cont’ed)
There are certain assumptions that we might
wish to incorporate into our design. For each,
tell how to add arrows or other elements to the
E/R diagram in order to express the
assumption.
a) For every baby, there is a unique mother.
c) For every combination of a baby and a mother
there is a unique doctor.