Database Systems $-2023\mathrm{.}05\mathrm{.}25$

Given the following raltional schema:

Driver $($ Name, LicenceNumber, Address$)$

CAR $($ LicencePlate, Type, Manufacturer$)$

OWNER $($LicencePlate$,$ LicenceNumber, Date$\_$of$\_$purchase $)$

Write a relational algebra expression which gives all the drivers living in Debrecen and have a

car!

$(4$ points$)$

A company uses the table below to record details of staff. Each staff has up to three

qualifications:

a$.$ Explain why this table is not in "First Normal Form" $(1$NF$).$

b$.$ Show how this table can be transformed into $1$ NF tables. Give two possible solutions.

$(2$ points$)$

a$.$ Construct an E$-$R diagram for a car$-$insurance company whose customers own one or

more vehicles $($which could be cars, trucks, $...)$ each. Each car has associated with it

zero to any number of recorded accidents. Show the advanced properties of the E$-$R

model! $(6$ points$)$

b$.$ Construct appropriate relational tables for the above ER Diagram $(6$ points$)$