Category Archives: Database System

–

Database System Final Project: FastCabs

Posted on by
Reply

The Assignment

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 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, 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.

Week 1

Given the assignment above, we were told to create a database to help the FastCabs company. Mr. Bahana told the class to form a group consist of 3 people. Me, Jeffrey and Mikha join to form a group together. Then we discuss what to do with the given data. We create a rough ER table that day.

From the table ER we created, we asked Mr. Bahana for advice and he gave us many feedbacks, he also told us where we went wrong and how to fix it.

Week 2

Although we were busy with other projects, we still manage some time to gather and do this FastCabs project. Creating the tables and its attributes are hard because we cannot imagine how this cab works in real life. So we compared this FastCabs with UBER application which is an online-transportation application similar to FastCabs.

 

The biggest problem we encountered is the relation between tables. We created a new table (not mentioned in the specification) External, which is a table that contain extID which later will be use to determine whether the owner is a driver or not. The relation between tables are also confusing, we spent the whole weekend to create a ‘perfect’ Entity diagram.

Week 3

Creating the database is not eas    y. Although we use phpMyAdmin, the connection between foreign keys and primary keys are a bit confusing. My task here is to populate the database as much as I could. The requirements in this project are some questions that related to our database. The query for the requirements are created by Mikha, here are the lists of query Mikha has made:

  • The names and phone numbers of the Managers at each office.
SELECT o.name AS Office_Name, s.name AS Name, s.phone AS Phone 
FROM manager m 
JOIN staff s ON s.staffID = m.staffID
JOIN office o ON o.officeID = m.officeID
  • The names of all female drivers based in the Glasgow office.
SELECT ex.name AS Name FROM external ex
JOIN driver d ON d.extID = ex.extID
JOIN manager m ON m.staffID = d.staffID
JOIN office o ON o.officeID = m.officeID
WHERE o.name = 'Glasgow' AND ex.gender = 'female'
  • The total number of staff at each office.
SELECT o.name AS Name, (COUNT(a.staffID) + COUNT(m.staffID)) AS Total_Staff 
FROM office o, admin a, manager m
WHERE a.officeID = o.officeID AND m.officeID = o.officeID
GROUP BY o.name
  • The details of all taxis at the Glasgow office.
SELECT plateNumber AS Plate_Number, type AS Type, color AS Color, extID AS OwnerID 
FROM taxi WHERE extID IN(SELECT extID FROM owner 
 WHERE staffID IN(SELECT staffID FROM manager 
 WHERE officeID IN(SELECT officeID FROM office 
 WHERE name = 'Glasgow')))
  • The total number of registered taxis.
SELECT COUNT(platenumber) AS Total_Taxi FROM taxi
  • The number of drivers allocated to each taxi.
SELECT d.plateNumber AS Plate_Number, COUNT(extID) AS Drivers_Allocated 
FROM driver d 
GROUP BY plateNumber
  • The name and number of owners with more than one taxi.
SELECT ex.name AS Owner_Name, COUNT(t.plateNumber) AS Number_of_Taxi 
FROM external ex, taxi t 
WHERE ex.extID = t.extID 
GROUP BY ex.name 
HAVING COUNT(t.plateNumber)>1
  • The full address of all business clients in Glasgow.
SELECT c.name AS Client_Name, c.address AS Client_Address 
FROM client c
JOIN business b ON b.clientID = c.clientID
JOIN contract con ON con.contractID = b.contractID
JOIN manager m ON m.staffID = con.staffID
JOIN office of ON of.officeID = m.officeID
WHERE of.name = 'Glasgow'
  • The details of the current contracts with business clients in Glasgow.
SELECT con.contractID AS ContractID, con.staffID AS ManagerID, con.numberOfJob AS Number_of_Jobs, con.totalMilage AS Total_Mileage
FROM contract con
JOIN manager m ON m.staffID = con.staffID
JOIN office o ON o.officeID = m.officeID
WHERE o.name = 'Glasgow'
  • The total number of private clients in each city.
SELECT o.city AS City, COUNT(p.clientID) AS Total_Private_Clients 
FROM office o, private p, manager m 
WHERE p.staffID = m.staffID AND m.officeID = o.officeID
GROUP BY o.name
  • The details of jobs undertaken by a driver on a given day (June 3, 2017).
SELECT ex.name AS Driver_Name, j.jobID AS JobID, j.extID AS DriverID, j.clientID AS ClientID, j.date AS Date, j.pTime AS Pick_up_Time, j.dTime AS Drop_off_Time, j.pAddress AS Pick_up_Address, 
j.dAddress AS Drop_off_Address 
FROM external ex, job j 
WHERE date = '2017-06-03' AND ex.extID = j.extID AND ex.extID = 'ex33'
  • The names of drivers who are over 55 years old.
SELECT ex.name AS Driver_Name
FROM external ex, driver d 
WHERE ex.extID = d.extID AND ex.age > 55
  • The names and numbers of private clients who hired a taxi in November 2016.
  • SELECT c.name AS Client_Name, COUNT(j.jobID) AS Number_of_Jobs 
FROM client c, job j 
JOIN private p ON p.clientId = j.clientID 
WHERE p.clientID = c.clientID AND (j.date BETWEEN '2016-10-31' AND '2016-12-01') GROUP BY c.name
  • The names and addresses of private clients who have hired a taxi more than three times.
  • SELECT c.name AS Client_Name, c.address AS Client_Address
FROM client c
JOIN private p ON p.clientID = c.clientID
JOIN job j ON j.clientID = p.clientID
HAVING COUNT(j.jobID) > 3
  • The average number of miles driven during a job.
SELECT AVG(mileage) AS Average_Milage FROM receipt
  • The total number of jobs allocated to each car.
SELECT d.plateNumber AS Plate_Number, COUNT(j.jobID) AS Total_Jobs
FROM driver d, job j
WHERE d.extID = j.extID
GROUP BY d.plateNumber
  • The total number of jobs allocated to each driver.
SELECT ext.name AS Driver_Name, COUNT(j.jobID) AS totalJob
FROM external ext, job j, driver d
WHERE d.extID = ext.extID AND d.extID = j.extID
GROUP BY ext.name
  • The total amount charged for each car in November 2016.
SELECT t.plateNumber, SUM(r.charge) AS TotalCharge 
FROM taxi t, receipt r 
JOIN job j ON j.jobID = r.jobID 
JOIN driver d ON d.extID = j.extID 
WHERE D.plateNumber = T.plateNumber 
GROUP BY t.plateNumber
  • The total number of jobs and miles driven for a given contract.

 

SELECT numberOfJob, totalMilage FROM contract

Week 4

We add some features which is a login page and a driving simulation with receipt

 

I created the GUI, connection to the database, passing the values between forms, and some debugging. The user manual is already explained in the report. The program can be found in here. Thanks to my group Jeffrey and Mikha so this project can be finished in time. And special thanks to Mr. Bahana and Marvin because of their guidance throughout the whole course (and after).