Introduction to Coursework You are part of a consultancy company that specialises in the provision of database applications. The Director of FastCabs has recently approached your company to undertake a project to design and partially implement a database management system for the company. Notes 1. You are in the initial stages of user requirements collection and analysis and are required to read the FastCabs case study. 2. The information presented in the case study is an overview of how business is conducted at the FastCabs. As the information described in the case study is an overview and ambiguous in places, it will be necessary for you to make your own assumptions about certain aspects of the case study. Coursework Requirements Part 1 Design the Database 1. Create an EntityRelationship (ER) model of the data requirements for the FastCabs case study using the UML notation. Note: Use the additional concepts of the Enhanced EntityRelationship (EER) model. State any assumptions necessary to support your design. 2. Derive relational schema from your ER model that represents the entities and relationships. Identify primary and foreign keys. Note: use the following notation to describe your relational schema, as shown in the example of a Staff relation given below. Staff (staffNo, fName, lName, address, ICNo, sex, DOB, deptNo) Primary Key staffNo Foreign Key deptNo references Department(deptNo) On Delete No Action On Update Cascade 3. Use the technique of normalization to validate the structure of your relational schema. Demonstrate that each of your relations is in third normal form (3NF) by displaying the functional dependencies between attributes in each relation. Note, if any of your relations are not in 3NF, this may indicate that your ER model is structurally incorrect or that you have introduced errors in the process of deriving relations from your model. 4. To further demonstrate your knowledge of normalization, assume that a proposed (badly structured) relation for the FastCabs database has the following structure.

jobID pickupDateTime driverID dFName dLName clientIDD cFName cLName cAddress

1001 25/07/00.10.00 I456 Jane Watt C034 Anne Way 111 Storrie Road, Paisley

1102 29/07/00.10.00 I456 Jane Watt C034 Anne Way 111 Storrie Road, Paisley

1203 30/07/00.11.00 I344 Tom Jones C034 Anne Way 111 Storrie Road, Paisley

1334 2/08/00.13.00 I666 Karen Black C089 Mark Fields 120 Lady Lane, Paisley

1455 2/08/00.13.00 I957 Steven Smith C019 John Brown 13 Renfrew Road, Paisley

1676 25/08/00.10.00 I344 Tom Jones C039 Karen Worth 34 High Street, Paisley

Identify the functional dependencies represented in this relation and demonstrate the process of normalizing this relation into 3NF relations. Part 2 Implement the Database 1. Create the tables for the FastCabs database. Ensure that referential integrity is established between related tables. 2. Enter some test data (approximately 5 10 rows) into each table. Part 3 Query the Database Before starting this section, please ensure that your tables contain sufficient data to enable you to test the query transactions described in the FastCabs case study. 1. Create PL/SQL blocks to implement the query transactions. 2. Use suitable PL/SQL constructs such as control structures, composite data types, records, cursors, procedures, functions and packages to implement the queries. 3. Provide 5 additional examples of queries, which retrieve useful data from the FastCabs database. State the purpose of each query and attempt to use each example to demonstrate the breadth of your knowledge of PL/SQL. The FastCabs Case Study A private taxi company called FastCabs was established in Malaysia in 2012. Since then, the company has grown steadily and now has offices in most of the main cities of Malaysia. However, the company is now so large that more and more administrative staff are being employed to cope with the ever-increasing amount of paperwork. Furthermore, the communication and sharing of information within the company is poor. The Director of the company feels that too many mistakes are being made and that the success of his company will be short-lived if he does not do something to remedy the situation. He knows that a database could help in part to solve the problem and has approached you and your team to help in creating a database application to support the running of FastCabs. The Director has provided the following brief description of how FastCabs operates. Each office has a Manager; several taxi owners, drivers and administrative staff. The Manager is responsible for the day-to-day running of the office. An owner provides one or more taxis to FastCabs and each taxi is allocated for use to a number of drivers. The majority of owners are also drivers. FastCab taxis are not available for hire by the public hailing a taxi in the street but must be requested by first phoning the company to attend a given address. There are two kinds of clients, namely private and business. The business provided by private clients is on an ad hoc basis. The details of private clients are collected on the first booking of a taxi. However, the business provided by business clients is more formal and involves agreeing a contract of work with the business. A contract stipulates the number of jobs that FastCabs will undertake for a fixed fee. When a job comes into FastCabs the name, phone number and contract number (when appropriate) of the client is taken and then the pick-up date/time and pick-up/drop-off addresses are noted. Each job is allocated a unique jobID. The nearest driver to the pick-up address is called by radio and is informed of the details of the job. When a job is completed the driver should note the mileage used and the charge made (for private clients only). If a job is not complete, the reason for the failed job should be noted. The Director has provided some examples of typical queries that the database application for FastCabs must support. a) The names and phone numbers of the Managers at each office. b) The names of all female drivers based in the Kuala Lumpur office. c) The total number of staff at each office. d) The details of all taxis at the Kuala Lumpur office. e) The total number of registered taxis. f) The number of drivers allocated to each taxi. g) The name and number of owners with more than one taxi. h) The full address of all business clients in Kuala Lumpur. i) The details of the current contracts with business clients in Kuala Lumpur. j) The total number of private clients in each city. k) The details of jobs undertaken by a driver on a given day. l) The names of drivers who are over 55 years old. m) The names and numbers of private clients who hired a taxi in November 2015. n) The names and addresses of private clients who have hired a taxi more than three times. o) The average number of miles driven during a job. p) The total number of jobs allocated to each car. q) The total number of jobs allocated to each driver. r) The total amount charged for each car in November 2015. s) The total number of jobs and miles driven for a given contract.