Consider the following relations: Driver(did: integer, name: string, age: intege

Question

Consider the following relations: Driver(did: integer, name: string, age: integer) Result(did: integer, teamid: integer, place: integer, race: integer) test(did: integer, teamid: integer, testdate: date, circuit: string) team(teamid: integer, tname: string) The meaning of the relations is straightforward. Result indicates which driver, running for which team finished in what place. Test indicates which driver is/was scheduled to test for which team. Write the following queries in relational algebra, domain relational calculus and SQL. No duplicates should be printed in the results. If some queries cannot be expressed in relational algebra or calculus, explain the reason. Find the name of the oldest driver who either won a race or tested for team 'Alpha'. Find the names of all the teams that either won a race or tested at least 5 different drivers. Find the names of the drivers who are scheduled to test for 2 different teams on the same

Explanation / Answer

1)

  (R1, did(tname='Alpha' ((Driver ~Result )))
tname (R1 , rdid(tname=‘Alpha (Result~ Team))))

{<EN> | Did,EN Driver Rdid Result Tteamid Team
Did = Ddid Rdid = Teamid tname = 'alpaha'))

Select name

From driver D

Where D.did IN (select R.did

From Result R ,Team T

Where R.teamid = T.teamid and tname ='Alpha')

2)

  tname(did (Team ~ Result ~Driver)) did( count(did)>5 (Test ~ Result ~driver ))

{<EN> | <Teamid,Tname> Team   Rdid,Ddid(Rdid,Ddid Result Ddid(did Driver  Pdid,(Pdid Test RDid = Ddid Rteamid = Tteamid count(did) >5)}

Select tname

From Team T

Where T.did IN (select did from Team T, Result R ,Driver D

where Tteamid=R.teamid and R.did = D.did)

Union

(select did from Test P

where count(did)>5))

3) SQL

Select name

From driver D

Where D.did IN (select teamid

From Test T

Where teamid1 != teamid2) // != Not equal to

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.