FastCab – Database Final Project

ABSTRACT

Database has been used increasingly much in this era to help organizing data, from simple applications to humongous, complex systems like in huge enterprises. Knowing that softwares are now so glued to databases, as a computer science student, I need to learn about databases, how they work, and how to implement database systems in applications. Therefore, I undergo the Database Systems course.

The Database Systems course demands me (us; with other students) to understand the concept of database designs, relations, and of course, systems themselves. As for the final project, we given a task to create a system to support a <quite> big taxi company.

Long story short, the taxi company has some offices in the country which each of them has a manager, staffs, taxi owners who provides the taxis, and drivers. The clients are divided into 2 groups: those who jump and call the taxi at any time, namely private clients, and those who are constricted with a contract, called business clients.

The project however, asks for some queries to show several displays. That was the main problem in the project. Despite being required to fulfill the requirements, we are allowed to improve the application by applying some of our creativity into the program.

In this final project, me and my team did not quite assign specific tasks to each person. In general, all we did was initiatively do a task and let the other check whether the work done had fulfilled the requirements or not. Even so, we figured that each of us actually did our own part during the project.

DATABASE DESIGN

Before creating our database and application, we discussed a lot about the database design. The first design came out before we really dug deeper into the content of each table. After creating the first database design, we tried to fill the tables with necessary attributes and we found out that the design did not fit right. After several fixes, we finally came out with the final design.

First ER Design
Rough list of tables and its attributes
Final ERD
Final List of Tables and Attributes

After discussing a lot about the database design, we then do our part of the assignment.

MY PART

After we made the design, I created the database and the tables in it, along with assigning the primary keys and foreign keys to implement the relationships between tables.

Example:

CREATE DATABASE fastcab;

CREATE TABLE office (
officeID varchar(5) not null PRIMARY KEY,
name varchar(20) not null,
city varchar(20) not null);

CREATE TABLE staff (
staffID varchar(5) not null PRIMARY KEY,
name varchar(20) not null,
phone varchar(15) not null,
gender varchar(6) not null,
age int not null,
password varchar(20) not null);

CREATE TABLE manager (
staffID varchar(5) not null,
officeID varchar(5) not null,
FOREIGN KEY (staffID) REFERENCES staff(staffID) ON DELETE NO ACTION ON UPDATE CASCADE,
FOREIGN KEY (officeID) REFERENCES office(officeID) ON DELETE NO ACTION ON UPDATE CASCADE);

Then, I also helped Wilson checking data conformity between tables. For example, a taxi can only be driven by drivers that are in the same city (thus, same manager).

I also created a class to ease the value passing between forms, so that we do not have to assign value by each attribute but as a group of attributes.

Classes used

Another thing that I did was making the in-program queries; the queries that runs when I run the application, queries when registering new account, when creating an order, or when making the receipt of the trip.

Example:

Registering new account (client)
Dim queryADD As String = "INSERT INTO client VALUES ('" & nextID & "','" & NameTxt.Text & "', '" & PhoneTxt.Text & "', '" & a & "' , '" & AddressTxt.Text & "', '" & PasswordTxt.Text & "');"

Dim queryADD As String = "INSERT INTO private VALUES ('" & UserYa.ClientID & "', '" & GenderBox.SelectedItem.ToString & "', " & AgeNum.Value & ", '" & staffID & "');"

Creating an order:
Dim query As String = "INSERT INTO job VALUES ('" & nextID & "', '" & find_driver() & "', '" & UserYa.ClientID & "', CURDATE(), CURTIME(), CURTIME(), '
        " & DepartureBox.SelectedItem.ToString() & "', '" & DestinationBox.SelectedItem.ToString() & "');"

Making a receipt of the trip
Dim queryADD As String = "INSERT INTO receipt VALUES ('" & Jour.JobID & "', " & Jour.Mileage & ", 'succeed', 'ok', " & sCharge & ");
                          UPDATE job SET dTime = CURTIME() WHERE jobID = '" & Jour.JobID & "'; "
If UserYa.ClassYa = "b" Then
    queryADD = queryADD & "UPDATE contract SET totalMilage = totalMilage + " & Jour.Mileage & " WHERE contractID = '" & UserYa.ContractYa.ContractID & "';
                           UPDATE contract SET numberOfJob = numberOfJob + 1 WHERE contractID = '" & UserYa.ContractYa.ContractID & "';"
End If

Leave a Reply

Your email address will not be published. Required fields are marked *