Advanced databases

Case Study Study the following case study and answer the questions that are based thereon:

NORTH TO SOUTH is a small privately owned delivery company situated in your local

neighbourhood. NORTH TO SOUTH has grown from strength to strength due to excellent service

and a wide variety of delivery options. The company at present is under pressure due to the

increase in customers, staff, drivers, vehicles and deliveries.

You have been contracted to design a database for NORTH TO SOUTH. The company opened their doors to the public $24$ months ago and requires a database to manage their customers, staff, drivers, vehicles, deliveries and billing. At present there is only one outlet, but a new franchised outlet will be opening in the next six months. The following has been provided:

CUSTOMER TABLE CUSTOMER$\_$I

D

FIRST$\_$NAME SURNAME ADDRESS PHONE$\_$NUM EMAIL

$11011$ Bob Smith $18$ Water rd $0877277521$ bobs@isat.com

$11012$ Sam Hendricks $22$ Water rd $0863257857$ shen@mcom.co$.$z

a

$11013$ Larry Clark $101$

Summer

lane

$0834567891$ larc@mcom.co$.$za

$11014$ Jeff Jones $55$

Mountain

way

$0612547895$ jj@isat.co$.$za

$11015$ Andre Kerk $5$ Main rd $0827238521$ akerk@mcal.co$.$za

STAFF TABLE STAFF$\_$ID FIRST$\_$NAME SURNAME POSITION PHONE$\_$NUM ADDRESS EMAIL

$51011$ Sally Du Toit Logistics $082569854718$ Main

rd

sdut@isat.com

$51012$ Mark Wright CRM $083698417812$ Cape

Way

mwright@isat.com

$51013$ Harry Sheen Logistics $072564896515$

Water

Street

hsheen@isat.com

$51014$ Jabu Xolani Logistics $082311659818$

White

Lane

jxo@isat.com

$51015$ Roberto Henry Packaging $078352145755$ Cape

Street

rhenry@isat.com

DRIVER TABLE

DRIVER$\_$ID FIRST$\_$NAME SURNAME DRIVER$\_$CODE PHONE$\_$NUM ADDRESS

$81011$ Brett Marshall C$1072569854718$ Leopard creek

$81012$ Tina Mtati C $063698417812$ Cape rd

$81013$ Richard Mvuyisi EC$1072564896515$ Circle lane

$81014$ Jonathan Smith C$1062311659818$ Beach rd

$81015$ Sisanda Buthelezi EB $088352145755$ Summer lane

VEHICLE TABLE VIN$\_$NUMBER VEHICLE$\_$TYPE MILEAGE COLOUR MANUFACTURER

$1$ZA$55858541$ Cutaway van chassis $115352$ RED MAN

$1$ZA$51858542$ Flatbed truck $315856$ BLUE ISUZU

$1$ZA$35858543$ Medium Standard

Truck

$789587$ SILVER MAN

$1$ZA$15851545$ Flatbed truck $555050$ WHITE MAN

$1$ZA$35868540$ Cutaway van chassis $79058$ WHITE ISUZU

BILLING TABLE BILL$\_$ID CUSTOMER$\_$ID STAFF$\_$ID BILL$\_$DATE

$800110115101106/$SEP$/16$

$801110125101307/$SEP$/16$

$802110145101510/$NOV$/16$

$803110155101209/$DEC$/16$

$804110135101409/$DEC$/16$ DELIVERY$\_$ITEMS TABLE DELIVERY$\_$ITEM$\_$ID DESCRIPTION STAFF$\_$ID

$71011$ House relocation $51011$

$71012$ Office relocation $51013$

$71013$ Delivery of specialized

consignments

$51015$

$71014$ Office relocation $51012$

$71015$ Delivery of specialized

consignments

$51014$ DRIVER$\_$DELIVERIES TABLE DRIVER$\_$DELIVERY$\_$ID VIN$\_$NUMBER DRIVER$\_$ID DELIVERY$\_$ITEM$\_$ID

$910111$ZA$558585418101171011$

$910121$ZA$358585438101271013$

$910131$ZA$558585418101171014$

$910141$ZA$358685408101371015$

$910151$ZA$158515458101471012$

NORTH SOUTH DELIVERIES

Question $2$

Create a PL$/$SQL query that will display the combined customer name, bill date, description of the delivery and the vehicle that will be used for the delivery. In your query only display the deliveries taking place on the $10$ November $2016.$