This was posted by CHAROTTAMA OSHMAR (2001585455),
with help from my teammates: Archel Taneka and Vincent Alexander
this post is about my final project, titled the FastCabs, a taxi company based in the UK. the project asks that I should create a working database system for the company. the company has been growing steadily, but now needs a more organized communication and information sharing due to its growth.
to do that, i formed a group of 3 that consists of the two names i mentioned above. at first we created an Entity Relationship diagram. an entity relationship diagram (ERD) shows the relationships of entity sets stored in a database. an entity in this context is a component of data. in other words, an ERD illustrates the logical structure of databases. at first glance an entity relationship diagram looks very much like a flowchart. It is the specialized symbols, and the meanings of those symbols, that make it unique. here is an example of an ERD.
this is the part where i contribute mostly. after showing the rudimentary ERD design to our teacher, he suggested that we try to group a few of our tables into one, which after that connects to the other tables. and after working it out and with a few tweaks, voila! This is the end result, which we used for our database system for the FastCabs Company
As stated in the requisites, an office branch is run by several staff, all of which have different jobs and responsibilities. So table office is connected to two other tables, namely the staff and car. Since staff can be a manager, taxi driver, taxi owner, or admins, the table staff bundles all the different types of staff into one. This is why staff is connected to tables manager, owner, and driver. The table owner is connected to car, since car belongs to owners.
A single job has many components: details of the trip, a car used for the trip, a driver driving the car, and the client itself which includes his/her details. This is why jobs is connected to three other tables, the driver, car, and clients.
By creating the database in such a way that is described in figure I.a, the database for the approach to tackle the question only has two large groups that connect all the other tables in the database, which are staff and jobs. This way, the database does not become complicated and bloated, ensuring efficiency.
apart from the ERD, I also helped in making the queries to access the database. this is needed because the question for our final project specifies some specific requests. some of the queries that I helped to fulfill those requests are listed below:
a. The names and phone numbers of the managers at each branch
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 Glasgow
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 branch
SELECT office.officeID, office.city, COUNT(staff.staffID) AS totalStaff FROM office, staff WHERE staff.officeID = office.officeID GROUP BY office.city
d. The number of driver 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
e. The address of all business clients in Glasgow
SELECT address FROM clients WHERE city = ‘Glasgow’ AND type = ‘Business’
f. The names and phone numbers of private clients who hired a taxi in November 2016