HW1 Solutions601.315/415/615 Databases

October 30, 2017

Part 1 ER DiagramThere is no single correct answer for this question. Below are 2 sample solutions.

1

Part 1 Relational Algebra

4

Part 2 Relational Algebra

6 Worked_With_Clint← ΠepisodeσJob=”director”worked_on

./artistid=artistid σArtistName=”ClintEastwood”artist

Result← ΠArtistID,ArtistNameartist./artistid=artistid (Πartistidworked_on./episode=episode

(Πepisodeworked_on./artist=artist(Πartistid worked_on ./episode=episodeWorked_With_Clint)))

7 result← Πartistid,artistnameartist./artistid=artistidworked_on./episodeid=episodeidepisode./showid=showid

(Πshowidepisode./episodeid=episodeidworked_on./artistid=artistid (Πartistidworked_on./episodeid=episodeid

episode./showid=showid (Πshowidepisode./episodeid=episodeidworked_on./artistid=artistid

(σArtistName=”SethMeyers”artist))))

8 result← Πshowtitletv_show ./showid=showid (episode./episode=episode (σrating≤35.0broadcast_in)./locationid=locationid (σcity=′baltimore′∧Date<”01−01−2017”∧Date>=”01−01−2016”location))

7

9 studio_morethan_20← Πstudioname(tv_show./showid=showidepisode./episodeid=episodeid (σrating>20

broadcast_in))

Studio_without_Jennifer← Πstudionametv_show./showid=showidworked_on./episodeid=episodeid

(σartistname=”JenniferLopez”artist)

result ← ΠStudioNametv_show - studio_morethan_20 - Studio_without_Jennifer

10 same ← Πepisodeid(σb.locationid=f.locationidbroadcast_in ./episodeid=episodeidfilmed_in)

result ← Πstudioname,episodetitle(tv_show./episodeid=episodeidepisode./episodeid 6=s.episodesame)

11 (SHOWEPISODE ← πstudioname="HBO",completiondate>"01-01-2017"(TV SHOW ./ EPISODE))ARTISTWORK ← (WorkedON ÷ SHOWEPISODE)RESULTS ← πA1.artistid,A2.artistid (σA1.artistid < A2.artistid(ARTISTWORKA1×ARTISTWORKA2))

12 (πshow.showid,episode.title,episode.ID(σshowid,shdirec.episodeId=shprod.episodeidSHDIREC ← (πJob="Director"(WorkedOn))SHPROD ← (πJob="Producer"(WorkedOn))ARTISTLIST ← (πartistid,artistname="MartinSheen"(Artist))RESULT ← ((SHDIREC ./ episodeid=episodeidSHPROD) ./ artistidARTISTLIST )(SHOW ./ episdeid=episodeidRESULT ))

13 (πshow.showid,episode.title,episode.ID(πshdirec.episodeId=shprod.episodeidSHDIREC ← (πJob="Director"(WorkedOn))SHPROD ← (πJob="Producer"(WorkedOn))ARTISTLIST ← (πartistid(Artist))RESULT ← ((SHDIREC ./ episodeid=episodeidSHPROD) ./ artistidARTISTLIST )(SHOW ./ episdeid=episodeidRESULT ))

14 EPLIST ← (πepisodeid,showid,location.locationid(σshowname="The Wire",showidTV SHOW )(σlocation="Baltimore",locationidLOCATION)EPISODE)πshowid,showname(σepisodeid,rating>BRR(σeplist.locationid=broadcastin.locationid,broadcastdate="XYZ",max(broadcastin.Rating)as BRR(EPLIST ./ BROADCASTIN)EPISODE)TV SHOW )

15 πshowid,showname(πshowid(πepisodeid,rating(BroadcastIn)−(σBR1.rating>BR2.rating(BroadcastIn BR1 ./ BroadcastIn BR2))Episode)Show)

16 got_labor ←− ΠEpisodeID, Amount(σShowTitle=”Game of Thrones” ∧ BroadCategory=”Labor”TV_SHOW ./EPISODE ./ EXPENDITURE ./ SPENT_ON)

most_expensive_ep←−EpisodeID Gmax Amount(got_labor)

sum_spent←−EpisodeID Gsum Amount(most_expensive_ep ./ got_labor)

result←− ΠExpenseType, EpisodeT itle, total_amount(got_labor ./ most_expensive_ep ./ sum_spent)

17 SPENT_M ←− ΠEpisodeID, Amount(SPENT_ON./ExpenseID=ExpenseID

(σExpenseType=”Musicians”(EXPENDITURE)))

SPENT_A←− ΠEpisodeID, Amount(SPENT_ON ./ExpenseID=ExpenseID

(σExpenseType=”Actors”(EXPENDITURE)))

RESULT ←− ΠEpisodeT itle, CompletionDate(EPISODE./EpisodeID=a.EpisodeID

(σa.EpisodeID=b.EpisodeID ∧ a.Amount>b.Amount(ρa(SPENT_M)×ρb(SPENT_A))))

18 SPENT_M ←− ΠEpisodeID, Amount(SPENT_ON ./ExpenseID=ExpenseID

(σExpenseType=”Musicians”(EXPENDITURE)))

SPENT_A←− ΠEpisodeID, Amount(SPENT_ON ./ExpenseID=ExpenseID

(σExpenseType=”Actors”(EXPENDITURE)))

8

FILTERED_EP ←− ΠEpisodeT itle, CompletionDate, EpisodeID, ShowID(EPISODE ./EpisodeID=a.EpisodeID

(σa.EpisodeID=b.EpisodeID ∧ a.Amount>b.Amount(ρa(SPENT_M)×ρb(SPENT_A))))

RESULT ←− ΠEpisodeT itle, CompletionDate, ShowTitle, ArtistName(TV_SHOW ./ShowID=ShowID

FILTERED_EP ./EpisodeID=EpisodeID WORKED_ON ./ArtistID=ArtistID ARTIST )

19 RESULT ←−EpisodeT itle,EpisodeID Gsum Amount(σShowTitle=”Game of Thrones”(TV_SHOW )./ShowID=ShowID EPISODE ./EpisodeID=EpisodeID SPENT_ON./ExpenseID=ExpenseID (σBroadCategory=”Labor”(EXPENDITURE)))

20 got_sites ←− ΠSiteName, Score(σShowTitle=”Game of Thrones”TV_SHOW ./ EPISODE ./EPISODE_REV IEW ./ REV IEW_SITE)

lower_reviews←− ΠSiteName(σScore<9.0(got_sites))

result←− ΠSiteName(got_sites)− lower_reviews

Part 2 Relational Calculus1 {t | ∃ a ∈ ARTIST (t[ArtistName] = a[ArtistName]∧ ∃ w ∈WORDED_ON(w[ArtistID] = a[ArtistID]∧ ∃ f ∈ FILMED_IN(f [EpisodeID] = w[EpisodeID]∧ f [LocationID] = a[BirthLocationID]∧ ∃ l ∈ LOCATION(t[City] = l[City]∧ t[State] = l[State] ∧ l[LocationID] = a[BirthLocationID]))))}

2 {t | ∃ a ∈ ARTIST (t[ArtistName] = a[ArtistName] ∧ t[ArtistID] = a[ArtistID]∧ ∃ l ∈ LOCATION(l[LocationID] = a[BirthLocationID]∧ ∃ w ∈WORKED_ON(w[ArtistID] = a[ArtistID]∧ ¬∃ f ∈ FILMED_IN(f [EpisodeID] = w[EpisodeID]∧ ∃ l1 ∈ LOCATION(l1[LocationID] = f [LocationID]∧ l[State] = l1[State]))∧ ¬∃ b ∈ BROADCAST_IN(b[EpisodeID] = w[EpisodeID]∧ ∃ l2 ∈ LOCATION(l2[LocationID] = b[LocationID]∧ l[State] = l2[State])))))}

3 {t | ∀ s ∈ TV_SHOW (s[StudioName] = ”Sundance”∧ ∃ e ∈ EPISODE(e[ShowID] = s[ShowID]∧ ∃ w ∈WORKED_ON(w[EpisodeID] = e[EpisodeID]∧ ∃ a ∈ ARTIST (a[ArtistID] = w[ArtistID]∧ t[ArtistName] = a[ArtistName]))))}

6 {t | ∃ a ∈ ARTIST (t[ArtistName] = a[ArtistName]∧ ∃ w ∈WORKED_ON(w[ArtistID] = a[ArtistID]∧ ∃ w2 ∈WORKED_ON(w2[EpisodeID] = w[EpisodeID]∧ ∃ w3 ∈WORKED_ON(w3[ArtistID] = w2[ArtistID]∧ ∃ w4 ∈WORKED_ON(w4[EpisodeID] = w3[EpisodeID] ∧ w4[Job] = ”Director”∧ ∃ a2 ∈ ARTIST (a2[ArtistID] = w4[ArtistID]∧a2[ArtistName] = ”Clint Eastwood”)))))}

8 {t | ∃ s ∈ TV_SHOW (t[ShowTitle] = s[ShowTitle]∧ ∃ e ∈ EPISODE(e[ShowID] = s[ShowID]∧ ∃ b ∈ BROADCAST_IN(b[EpisodeID] = e[EpisodeID]∧ b[Rating] ≤ 35.0 ∧ b[Date] ≥ 2016-01-01 ∧ b[Date] ≤ 2016-12-31∧ ∃ l ∈ LOCATION(l[LocationID] = b[LocationID] ∧ l[City] = ”Baltimore”))))}

9

Scanned by CamScanner