Course Name: Database Systems
Members: Nathania Kezia Barakati, Regita Isada, Sefira Karina
Case Study: FastCabs Company
For the final project of database systems, we are required to make a database based on a case study that was given by the lecturer. The case study is about a company called FastCabs. The FastCabs company is a company that provides taxi service for people. What we have to do for this project is to make the tables for the database and make the queries that are listed in the specification.
In this project, I am the one in charge of making the normalization, ER Diagram, and the documentation of the project.
This is the ER diagram of our project.
(Created with Creately)
This is the normalization. The first time it was made, it was a little confusing since we keep changing the tables.
1NF
Driver [Driver_ID, Driver_Name, Driver_Age, Driver_Gender]
Owner [Owner_ID, Owner_Name, Owner_Phone]
Staff [Staff_ID, Staff_Name, Staff_Age, Staff_Gender, Staff_Type]
Branch [Branch_ID, Branch_Location, Branch_Address]
Taxi [Taxi_Plate]
Client [Client_ID, Client_Name, Client_Address, Client_Phone, Client_Type]
Contract [Contract_ID, Client_ID, Fee, Taxi_Amount]
Order [Order_ID, Order_Date, Order_Time, Mileage, Price, Order_Status, Reason, Price]
2NF
Driver [Driver_ID, Driver_Name, Driver_Age, Driver_Gender]
Owner [Owner_ID, Owner_Name, Owner_Phone]
Staff [Staff_ID, Staff_Name, Staff_Age, Staff_Gender, Staff_Type]
Branch [Branch_ID, Branch_Location, Branch_Address]
Taxi [Taxi_Plate]
Client [Client_ID, Client_Name, Client_Address, Client_Phone, Client_Type]
Contract [Contract_ID, Client_ID, Fee, Taxi_Amount]
Order [Order_ID, Order_Date, Order_Time, Mileage, Price, Order_Status, Reason, Price]
3NF
Driver [Driver_ID, Branch_Location(FK), Driver_Name, Driver_Age, Driver_Gender]
Owner [Owner_ID, Owner_Name, Owner_Phone]
Staff [Staff_ID, Branch_Location(FK), Staff_Name, Staff_Age, Staff_Gender, Staff_Type]
Branch [Branch_ID, Branch_Location, Branch_Address]
Taxi [Taxi_Plate, Branch_Location(FK), Taxi_Plate(FK)]
Client [Client_ID, Client_Name, Client_Address, Client_Phone, Client_Type]
Contract [Contract_ID, Client_ID(FK), Branch_Location(FK), Fee, Taxi_Amount]
Order [Order_ID, Order_Date, Order_Time, Mileage, Price, Order_Status, Reason, Price]
Jobs[Driver_ID(FK), Taxi_Plate(FK)]
***Primary Key is underlined and bold