Midterm Review I

1
Midterm Review I
2
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?

3
Database Modeling
4
Bars-Beer-Drinkers (BBD) Example
Why we need it?
5
Ternary Relationships
name
addr
name
manf
Bars
Beers
license
Preferences
Why we would need it?
Drinkers
name
addr
6
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.

7
In E/R diagrams

• Double diamond for supporting many-one
  relationship.
• Double rectangle for the weak entity set.

8
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.

9
In E/R Diagrams

• Double diamond for supporting many-one
  relationship.
• Double rectangle for the weak entity set.

10
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.

11
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.

12
When Do We Need Weak Entity Sets?

• The usual reason is that there is no global
  authority capable of creating unique IDs.
• Example it is unlikely that there could be an
  agreement to assign unique player numbers across
  all football teams in the world.

13
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.

14
The Relational Data Model
Database Model (E/R)
Relational Schema
Physical storage
Complex file organization and index structures.
Diagrams (E/R)
Tables row names attributes rows tuples
15
Terminology
Attribute names
Title Year Length
FilmType Star Wars 1997
124 color Mighty Ducks
1991 104
color Waynes World 1992 95
color . . . .
. . . . . . . .
components of tuples
tuples
16
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.
17
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.

18
Entity Sets to Relations
Relation schema Movies(title, year, length,
filmtype )
A relation instance
19
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.

20
Example (with Renaming)
21
Combining Relations

• Its 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)

22
Risk with Many-Many Relationships

• Combining Drinkers with Likes would be a mistake.
  Why?
• It leads to redundancy, as

23
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.

24
Example
Teams(name) Players(number, teamName,
name) PlaysOn(number, teamName, teamName2)
25
Entity Sets Versus Attributes I

• Example Bad design

This design repeats the manufacturers address
once for each beer Loses the address if there
are temporarily no beers for a manufacturer.
26
Ex.1,2
location
area
eventid
date
np
cid
Takes place
Complexes
Events
Used for
Involved
Chief Org
MultiSport
OneSport
Officials
Equipment
locIndicator
Designated for
Designated for
oid
name
equipid
description
Sports
Complexes(cid,location,area) MultiSport(cid) Desig
natedFor(cid,sportname,locIndicator) OneSport(cid,
sportname) Officials(oid, name) Events(eventid,da
te,np,chief_org_oid) Involved(oid,eventid) Equipme
nt(equipid,description) UsedFor(equipid,eventid)
name
27
Ex.3,4
name
dob
Personnel(pid,name,dob,pob) Players(pid,ba,baorien
tation,tid) Pitchers(pid,era) Coaches(pid,tid) Man
angers(pid,tid) Umpires(pid) Teams(tid,name,city,d
ivision) Games(gid,date,winning_pid,losing_pid) Pl
ays(gid,tid1,tid2,runs1,hits1,error1,
runs2,hits2,error2) Hits(gid,pid,singles,doubles,t
riples,hruns) SavePitcher(gid,pid)
pid
pob
Personnel
ba
borientation
Players
Coaches
Umpires
Managers
manages
coaches
memberOf
era
name
tid
city
Pitchers
Teams
singles
division
doubles
save
runs1
hits
triples
winning
losing
plays
hits1
hruns
errors1
Games
runs2
hits2
errors2
gid
date
28
Ex.5a
number
name
code
phone
name
dob
Enrolled
Students
Departments
semester
Registered
Offered
grade
Courses
location
name
desc
29
Ex.5b
number
name
code
phone
name
DOB
Enrolled
Students
Departments
Registered
Semesters
Offered
grade
Courses
SemId
location
name
desc
30
Exercise 3.6.1

• R(A,B,C,D) with FDs AB?C, C?D, and D?A
• Indicate all the BCNF violations. Do not forget
  to consider FDs that are not in the given set,
  but follow from them.
• Indicate all the BCNF violations. Do not forget
  to consider FDs that are not in the given set,
  but follow from them.

31
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)

32
Babies (Conted)

• 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.