The British University in Dubai Database Systems $($CS $205/216)$

Assignment# $1$ Term $2$ AY $23/24$

A$.$ ER$-$ Design, Mapping ER$-$ to Relational Model $(75\%)$

Overview

In this assignment, we ask you to use your know$-$how to model the management of a transport company. It is a question of proposing a conceptual model of data and the list of management constraints to capture the company's information system.

Management of a transport company

A transport company has trucks and drivers. Each truck is characterized by its weight$($kg$)$ and volume $($m$3-$ cubic meter$)$ capacities.

The Company provides deliveries. A delivery concerns a certain number of boxes to be carried from one address to another. Pick$-$up and drop$-$off are done on the same day. The trucks make rounds to deliver. When making its rounds, a truck leaves the office station empty in the morning; it then stops in a certain number of cities to load or unload a delivery before returning empty to the office station in the evening. The same truck ensures several deliveries during its round.

At any given time, it may contain multiple deliveries. We want to be able to answer the questions:

$$ What deliveries were made by truck $1234$JV$86$ on Friday, November $15?$ How many kilometres has it taken since the last oil change?

$$ What are the deliveries made by the driver Damien LIGET?

$$ Through which towns does truck $1234$ JV$86$ make its rounds on Friday, November $15,2002?$

$$ The truck used by driver X on Friday, November $15,$ goes through Maramaa, then Tartu.

It's necessary to deliver $1$ m$3$ and $100$ kg between these two cities. Is there enough free

space in the truck?

$$ It is Friday, August $13,10$:$30$ a$.$m$.,$ who is the Truck$$s driver whose tires were changed on

August $10?$

Questions

$-$ Model an ER diagram for this problem. You have to specify all the Integrity constraints to be respected. Draw your ER diagram using any graphical tools you desire. $(25\%)$

$-$ Map your ER diagram to a relational schema, draw your relations $($tables$)$ and use arrows to represent the referential integrity constraints and underline primary keys in each relation $(25\%)$

$-$ Based on your relational model, give the relational$-$algebraic expression ofthe following: $(25\%)$

o Listalltrucksofthecompany.

o Countthenumberofcompanytruckswithavolumegreaterthan$150$m$3.$ o What are the deliveries made by the driver Damien LIGET?

o Whatdeliveriesweremadebytruck$1234$JV$86$onFridayNovember$15?$

o List ofcities crossed by truck $1234$ JV$86$ on Wednesday, July $8,2002.$ o ListoftruckspreviouslydrivenbyX.

o Numberofboxesdeliveredbydriverxsincebeinghired.

B$.$ Evaluation of relational$-$algebraic expressions $(25\%)$

Consider the relations DOGS, MEN and TRAIN.

DOGS DName Arthur

Billy Corso

MEN

MName Armand David Alois Arthur

TRAIN

Men Armand David Alois

Dog Year Corso $2010$ Billy $2012$ Corso $2008$

$1.$ Givethevalue$($i$.$e$.$writetherelationcalculatedby$)$ofthefollowingexpressions$,$andindicatein English what they calculate:

$(),>2009$

$2.$ SayinEnglishwhatthefollowingconditionsdo

$\backslash$cup $\{()\},$

$>2011(),$ Men $-()()$

$3.$ Givethealgebraicexpressionofthefollowingterms

$$ All possible pairs $($dog; human$)$

$$ Dogs that are trained

$$ DogstrainedbyAloisafter$2010$

$$ NumberofDogs

$$ Numberoftraineddogswithoutduplication