2003 Ungraded Homework P1

1
2003 Ungraded Homework P1

• Reminder Reserves(sid,bid,day)
• a.       Create a table Together(sid1,sid2,day)
  that contains all pairs of sailors that have a
  reservation for the same boat on the same day.
• ?(Reserves1 (1?sid1),Reserves)
• ?(Reserves2 (1?sid2),Reserves)
• ?(Together, ?sid1,sid2,day(Reserves1 X
  Reserves2))
• b. Give the sids of all sailors that have a
  reservation for 10/13/2003 but not for
  10/17/2003.
• ?sid(?day10/13/2003 (Reserves)) ?
  ?sid(?day10/17/2003 (Reserves))

2
2003 Ungraded Homework P1 (cont)

• c.       Give the name and sid of all sailors
  that have reservations for all red boats
• ?(Tempsids, ?sid,bid(Reserves) / ?bid(?colorred
  (Boats)) )
• ?sid,sname(Tempsids X Sailors)
• d.      Give the name and sid of all sailors that
  have exactly one reservation for 11/11/2003
• ?(Reserves2 (1?sid2,2?bid2), ?day11/11/2003
  Reserves)
• ?(2R, (?sid(?day11/11/2003(Reserves)
  Xsidsid2 and bid?bid2 Reserves2))
• ?sid,sname((?sid(Reserves) ? 2R) X Sailors))

3
P2 All but one boat problem

• SELECT R.sid
• FROM Reserves R
• GROUPED BY R.sid
• HAVING COUNT(DISTINCT R.bid)) 1
• (SELECT
  count()
• FROM
  Boats Bo)

Remarks still needs to be tested if the query
runs on ORACLE9i counting, instead of checking
the real bids is okay because of
referential integrity.
4
P3 NFL E/R Design Problem

• Design an Entity-Relationship Diagram that models
  the following objects and relationships in the
  world of football (NFL) teams, players, games,
  managers and contracts. Each (NFL-) team has a
  unique team name, and a city it plays in. Each
  person being part of the NFL-world has a unique
  ssn and a name. Additionally, for players their
  weight, height, position and birth dates are of
  importance. Players have a contract with at most
  one team and receive a salary for their services,
  and teams have at least 24 and at most 99 players
  under contract. Each team has one to three
  managers managers can work for at most 4 teams
  and receive a salary for each of their
  employments. Players cannot be managers. A game
  involves a home-team and visiting-team
  additionally, the day of the game, and the score
  of the game are of importance teams play each
  other several times in a season (not on the same
  day!). Moreover, for each game played we like to
  know which players participated in the game and
  how many minutes they played.
•  
• Indicate the cardinalities for each relationship
  type assign roles (role names) to each
  relationship if there are ambiguities! Use
  sub-types, if helpful to express constraints!

5
empl.
name
ssn
Sal
(0,4)
name
Manager
isa
(1,3)
Person
Team
isa
city
contr
(24,99)
(0,1)
Player
birthd
Home
Visit
NFL E/R Problem
(0,)
(0,)
weight
pos
height
Sal
(0,)
play
played-in.
score
min
(1,1)
(22,)

• Scoring
• Play relationship a Set 3
• Person/Player/Manager 3
• Weak Game Entity 3
• Played-in 2
• Can Only Play once on a day 1
• Contract 3
• Salary, score, min attribute 3

Game
Day
6
Using the Default Mappingto Map the E/R Diagram
to theRelational Data Model
Contract(Team,Player,Salary)
Team(name, city)
Player(ssn, birthd, pos,)
Person(ssn, name)
Game(home, visit, day, score)
Played_in(home,visit, day, ssn, min)
7

• SQL-Queries
• B1) Give the dates of all reservations for red
  boats 2
• SELECT R.day
• FROM Reserve R, Boat B
• WHERE R.bid B. bid AND B.color red
• B2) Give the boats (return bid ) that have at
  least 2 reservations for 5/5/2003 4
• SELECT DISTINCT R1.bid
• FROM Reserve R1, Reserve R2
• WHERE R1.day 5/5/03 AND R2.day 5/5/03 AND
  R1.bidR2.bid AND R1.Sid ltgt R2.Sid

8

• SQL-Queries
• B2) Give the name and sid of all sailors that do
  not have any reservations for green boat(There
  is no green boat that is reserved by this
  sailor)4
• Wrong
• SELECT S.sname, S.sid
• FROM Sailor S, Reserve R, Boat B
• WHERE S.sid R.sid AND R.bidB.bid AND
  not(B.color green)

9

• SQL-Queries
• B2) Give the name and sid of all sailors that do
  not have any reservations for green boat(There
  is no green boat that is reserved by this
  sailor)4
• Correct
• SELECT S.sname, S.sid
• FROM Sailor S
• EXECPT SELECT S.sname S.sid
• FROM Sailor S, Reserve R, Boat B
• WHERE S.sid R.sid AND R.bidB.bid AND B.color
  green

10
Homework2 --- Problem1

• R(A, B, C, D, E) with the following functional
  dependencies is given
• (1)   A ? BC bad
• (2)   C ? A bad
• (3)   E ? A good
• (4)   B? D bad
• What is (are) the candidate key(s) for R? 2 E

The schema R1, R21, R22 is in BCNF and has no
lost FDs!!
R(A,B,C,D,E)
B?D
A ? BC bad E ? A good C ? A bad
R2(A,B,C,E)
R1(B,D)
B ? D good
A?BC
R22(A, E)
R21(A,B,C)
E ? A good
A ? BC good C ? A good
11
Homework2 --- Problem1

• R(A, B, C, D, E) with the following functional
  dependencies is given
• (1)   A ? BC bad
• (2)   C ? A bad
• (3)   E ? A good
• (4)   B? D bad
• What is (are) the candidate key(s) for R? 2 E

The schema R3, R41, R42 is in BCNF and but B?D is
lost!
R(A,B,C,D,E)
A?BC
B?D lost!!
A ? D bad E ? A good
R4(A,D,E)
R3(A,B,C)
A ? BC good C ? A good
R42(A, E)
R41(A,D)
12
Problem2a Homework2

• 2)    Properties of Decompositions 19
• R(A,B,C,D,E) with
• (1) A ? C
• (2) BC ? E
• a)      Assume , we decompose R into R1(A,E) and
  R2(A,B,C,D). Does this decomposition have the
  lossless join property --- is it possible to
  reconstruct R from R1 and R2 using a natural
  join? Give reasons if your answer is yes give a
  counter example if your answer is no (hint
  tuples of your counter example cannot violate (1)
  and (2))! 9
• Counter example Assume R(A,B,C,D,E) contains
  (a,b1,c,d1,e1) and (a,b2,c,d2, e2) obviously, R
  satisfies the functional dependencies (1) and
  (2).
• However, ?A,,E(R) X ?A,,B,C,D(R) (?R)
  additionally contains (a,b2,c,d2,e1) and
  (a,b1,c,d1,e2) which are not in R. Therefore, the
  decomposition of R into R1 and R2 does not have
  the lossless join property.
• Remark (a,b1,c1,d1,e1) and (a,b2,c2,d2,e2) is
  not a correct counter example, because it
  violates A?C (c1 and c2 have to be the same!!)
  also be aware of the fact that A?E not necessary
  holds for R.