14 Jun

Database Final Project: Fastcabs

Posted by Archel Taneka in Uncategorized. No Comments

Course: Database Systems(COMP 6339)

Members: Charottama Oshmar, Vincent Alexander Seliang

Overview

This final project is for the following course: Database Systems. The project is about a private company, FastCabs, which has problems in communication and sharing information. I should form a group of 3, including myself to solve the problem in creating a database application to support the running of the company.

Getting Started

The first step that I did with my team was creating the Entity Relationship(ER) Diagram and the logical flow. I created the first draft of the ER Diagram.

It appeared that I had to create 9 tables. Office has a manager with 1 to 1 relationship, manager will also manage 1 office only. Manager manages staffs such as taxi owners and drivers. Taxi owners are mostly also drivers. A taxi owner can own 1 or more than 1 taxi and 1 taxi can be used by more than 1 driver. On the other hand, clients were divided by 2, private and business clients. Both clients will reserve a taxi and it will form a job. Then, I found some problems which was quite troublesome. The first problem was, how to identify taxi owners who are also drivers. Second, how private and business client with different table could use the job service, since private clients did not have contract to identify them. Additionally, charge and fixed fare was also a problem because there were no identifiers that will give them ‘sign’ to change its value.

Fixing The ER Diagram

After having a consultation, I modified my first ER Diagram. I created a staff table, which held name, age, gender, and office ID where he is working at. Then, the owner, driver, and manager table will held staff ID to identify each staff and his/her position. For clients table, I decided to combine both private and business clients into 1 table. To distinguish between private and business client, there is a column ‘type’ which later affects the charge and fixed fare.

In this modified ER Diagram, those problems that were mentioned before, are finally fixed. Here, staff table will identify each position in the company. If both staff ID in table owner and staff ID in table driver same, then that particular staff is a taxi owner and also a driver. Staff ID cannot be the same for the rest of the position, except for owner and driver.

Queries ‘A – S’

The report specification gave us ‘a’ through ‘s’ query example to display the table results. Here are the sample queries:

(a) The names and phone numbers of the Managers at each office.

SELECT name, phone FROM staff, manager, office WHERE staff.officeID = office.officeID AND staff.staffID = manager.staffID

(b) The names of all female drivers based in the Glasgow office.

SELECT DISTINCT driver.driverID, staff.name FROM staff, driver WHERE staff.gender = ‘Female’ AND staff.officeID = 300 AND driver.staffID = staff.staffID

(c) The total number of staff at each office.

SELECT office.officeID, office.city, COUNT(staff.staffID) AS totalStaff FROM office, staff WHERE staff.officeID = office.officeID GROUP BY office.city

(d) The details of all taxis at the Glasgow office.

SELECT * FROM car WHERE car.officeID = 300

(e) The total number of W registered taxis.

SELECT office.city, COUNT(*) AS registeredTaxis FROM car, office WHERE car.officeID = office.officeID GROUP BY office.city

(f) The number of drivers allocated to each taxi.

SELECT car.carID, car.licensePlate, COUNT(driver.carID) AS allocatedDriver FROM driver, car WHERE car.carID = driver.carID GROUP BY car.carID

(g) The name and number of owners with more than one taxi.

SELECT staff.name, count(car.ownerID) AS carOwned from staff, car, owner WHERE owner.staffID = staff.staffID AND owner.ownerID = car.ownerid GROUP BY car.ownerID Having Count(car.ownerID) > 1;

(h) The full address of all business clients in Glasgow.

SELECT address FROM clients WHERE city = ‘Glasgow’ AND type = ‘Business’

(i) The details of the current contracts with business clients in Glasgow.

SELECT * FROM clients WHERE city = ‘Glasgow’ AND type = ‘Business’

(j) The total number of private clients in each city.

SELECT clients.city, COUNT(clients.clientID) AS totalPrivateClients FROM clients WHERE type = ‘Private’ GROUP BY clients.city

(k) The details of jobs undertaken by a driver on a given day

SELECT * FROM jobs WHERE date = CURRENT_DATE

(l) The names of drivers who are over 55 years old.

SELECT DISTINCT staff.name FROM driver, office, staff WHERE staff.officeID = office.officeID AND staff.age > 55

(m)The names and numbers of private clients who hired a taxi in November 2016.

SELECT clients.name, COUNT(clients.name) AS Total FROM clients, jobs WHERE clients.type = ‘Private’ AND clients.clientID = jobs.clientID AND MONTH(jobs.date) = 11 AND YEAR(jobs.date) = 2016 GROUP BY clients.name

(n) The names and addresses of private clients who have hired a taxi more than three times.

SELECT clients.name, clients.address, clients.TimesOrder FROM clients WHERE clients.TimesOrder > 3 AND clients.type = ‘Private’

(o) The average number of miles driven during a job.

SELECT AVG(mileage) AS averageMileage FROM jobs

(p) The total number of jobs allocated to each car.

SELECT car.carID, car.licensePlate, COUNT(jobs.carID) AS totalJobs FROM car, jobs WHERE car.carID = jobs.carID GROUP BY car.carID

(q) The total number of jobs allocated to each driver.

SELECT driver.driverID, staff.name, COUNT(jobs.driverID) AS totalJobs FROM driver, jobs WHERE driver.driverID = jobs.driverID AND staff.staffID = driver.staffID GROUP BY driver.driverID