Consider the following relational schema of an insurance database. Solve the fol

Question

Consider the following relational schema of an insurance database. Solve the following questions first on paper, then enter your solutions In the corresponding submission page in eLearning. Express each of the following rotational algebra queries in plain English: Formulate relational algebra queries that answer the following questions: Rod the registration numbers of al cars which are older than the year 2002. List the registration numbers and model of all cars that were ever involved in an accident. Find name and address of the owner of the car with registration number 'NSW 4711'. List all accidents (report.nr) including the corresponding damage amount which Michael Schumacher had with his F2011.

Explanation / Answer

A) means that you project the attribute named "location" which is found on the relation Accident

Accident is a relation that has attributes (columns) and tuples(rows) (like relation Person has attributes like license, name, address....and the tuples are the actual persons like 012019201912,John Smith, Baconville 1223...)
B)So that one is a little bit trickier we start from the right:
From the relation Person we select() the tuple named John Smith (as said before that means that you are going to have the row where John Smith is and all of his information)next we see that butterfly symbol its the join which units two relations (you can see that Person and Involved have both a same "key" the underlined) so that means that you take John Smith and all Involved rows where license is same for both relations, by now have something that looks like that : 0120120120,John Smith, Baconville 123, 8828388(regno),8828392736552(report.nr), and damage account.
So that why we need the project() to choose only a particular attribute of what we have found in that case we want only report.nr and damage account.
So your result is only the report.nr and damage account of John Smith.

C) So the question asks for all registration numbers of cars older than 2002 so you are looking for years <2002
Your query should look something like regno( year<2002(Car))
From relation Car you select all "cars" older than 2002 and you project only their regno

D)Ok so now you can see in the question the word car involved and accident so probably those 3 relations are gonna be needed. The key in understanding how to do that is do find a common attribute in those relations Involved and Accident have report.nr as common factor and Involved and Car have regno.
So what you need is regno, model(Car join(Involved join(Accident)))

Usually in join we also specify the common key

regno, model(Car join car.regno=involved.regno (Involved join involved.report.nr=accident.report.nr(Accident)))

I dont know if your professor has explained that stuff to u but normaly thats the way to do it.

E)That now should be easy enough so we take the Person relation and Car the first thing you want is to find regno=NSW .. so youd do   regno="NSW 4711"(Car) ok now you have the car but you need the person who drives it and that is the owner. So you would need to join Person with Car in such manner that name=owner (Person join name=owner( regno="NSW 4711"(Car))) and the the easiest and last part you want to project only the columns that are asked in this case name and address

The solution is: name, address(Person join name=owner( regno="NSW 4711"(Car)))

F)Ok soooo we start by selecting model=F2011 from Car and maybe you should use owner="Michael Shumaher" to be more precise since M.Shum can drive other model cars as well.

second you join Involved with car so that involded.regno=car.regno

third you join Accident with Involved accident.reportnr=involved.report.nr

and last you project what u need report.nr and damage account

your solution should look like that:

report.nr,damage.amount(Accident join Accident.report.nr=Involved.report.nr(Involved join Involved.regno=Car.regno( model="F2011"(Car join owner=name ( name="Michael Shumaher(Person)))))

I dont think that the first(name=shume) part is necessary since the car model i belive is unique but you can use it that way to be sure, and it is up to you to put the join common keys.

I know it is a long post and i hope you actually understood something.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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