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