SIT103 Database
Trimester 2 Assignment 2 2014
DoeLiver
Due Date: Friday 19th September, 2014
____________________________________________________________________
DoeLiver (Do Deliver) is a new courier business that is being set up.
The basic service provided by this courier service is timely pickup and delivery of items for clients. They pride themselves on being able to cater to any size items, because their couriers use a variety of transports.
A call is received by a client, the urgency of the delivery is ascertained by the operator and a job is created with the relevant details. The delivery address, the pickup address, the urgency of the job and the size of the item are recorded. The application software will scan the database for unfinished jobs and the location and status of working couriers, and then allocate the job to the courier best able to deliver the job in the fastest time. Once a courier has completed a job, he or she calls in and registers the completion and checks for the next job pickup.
The courier service wishes to keep a record of each client. They also wish to record the duration of each job, in order to provide statistics on time taken to complete a job at given times of the day through specific areas. Details of each driver (courier) are also kept, along with size and capacity of their vehicle. This is important, so that couriers with motor bikes and couriers with vans are sent to jobs which they can handle.
They are also intending to provide a website that clients can use to enter delivery requests, without having to go through the phone. They will also be able to check on the status of their jobs through the website as well.
The whole company’s operations will be based on the data stored in this database model. It is important that due consideration is given to provide the infrastructure required to develop this system. The foundation to the entire system relies on this database model.
Pass/Credit Level - Design
To assist this company, try and come up with a small database design for their new system.
Provide the following:
• Entity Relationship Diagram.
• Relational Model (showing fields, primary keys, foreign keys and concatenated keys).
• Justify the choices you have made. Are the entities and relationships you have chosen free of problems ? What assumptions have you made ?
Higher Level – Construct and Test
Create the database tables using SQL statements in your own database. Include
• CREATE TABLE queries – that create each of the tables in your design
• INSERT INTO queries – that insert values into your tables (at least 5 rows per table)
Write some queries to test out the database.
• One query should be a simple one involving one table and a selection criteria. • The second query should involve a join (at least 2 tables)
• The third should be a group by query.
• Fourth query that involves a nested query.