Month: June 2017

Abstract

People might not realise how database has played a big role in this technology era. People could find it used in every applications, software and etc. As a computer science student, I am obligated to learn on how does database work.

On the second-half of the second semester, computer science students in Binus International were introduced to Database System. For almost 3 months to learn database intensively, the students were given a final task to complete by the end of the semester. In a group of maximum 3 people, we were assigned to create and design a suitable database for a growing private taxi company, FastCabs. Developing a database system for this company is expected to improve the communication and sharing information between staffs. Not only that, but to reduce any problems that will appear within the company as well. Also, we were given a specific order to show data by making its query.

DESIGNING THE DATABASE

Before my team and I get our hands dirty on coding, we have to think thoroughly about the design of the database. First thing first, we were designing the Entity Relation Diagram. It was quite a challenge to make the perfect one as we try to match with the specification given. We were aiming to create the database as efficient as it can. In final, we constructed 14 tables

.

 

ADDING DATA TO THE DATABASE

Having a minimum 5 data in each table is one of the requirements. My teammates, Jeffrey and Wilson, are the ones who mostly filling the database. Adding data to 14 tables is not an easy job because we need to conform to other tables. For example, a contract manages by a manager. This manager must work in the office, which located in the same city as the business client.

MAKING THE QUERY

After adding data, I’m in charge to create the query. There are 19 specifications in total that I need to solve.

• 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