Consider the following relations: Driver(did: integer, name: string, age: intege
ID: 3687214 • Letter: C
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 sameExplanation / 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