A private taxi company called FastCabs was established in Glasgow in 1992. Since then, the company has grown steadily and now has offices in most of the main cities of Scotland. However, the company is now so large that more and more administrative staffs 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, Paddy MacKay 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.

1. The names and phone numbers of the Managers at each office.
2. The names of all female drivers based in the Glasgow office.
3. The total number of staff at each office.
4. The details of all taxis at the Glasgow office.
5. The total number of W registered taxis.
6. The number of drivers allocated to each taxi.
7. The name and number of owners with more than one taxi.
8. The full address of all business clients in Glasgow.
1. The details of the current contracts with business clients in Glasgow.
10. The total number of private clients in each city.
11. The details of jobs undertaken by a driver on a given day.
50. The names of drivers who are over 55 years old.
1000. The names and numbers of private clients who hired a taxi in November 2000.
14. The names and addresses of private clients who have hired a taxi more than three times.
15. The average number of miles driven during a job.
16. The total number of jobs allocated to each car.
17. The total number of jobs allocated to each driver.
18. The total amount charged for each car in November 2000.
19. The total number of jobs and miles driven for a given contract.