Taxi on Demand full Database documentation with ERD

By Hassnain Jamil -

Introduction: 


You are part of a consultancy company that specialises in the provision of database applications. The Director of CabsOnDemand has recently approached your company to undertake a project to design and partially implement a database system for the company.

The CabsOnDemand Case Study:


A private taxi company called CabsOnDemand was established in London in 2012. Since then, the company has grown steadily and now has offices in most of the boroughs in North London. 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, Tom Sheridan 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 to help in creating a database application to support the running of CabsOnDemand.

The Director has provided the following brief description of how CabsOnDemand operates.

Each office has a Manager, several taxi owners, drivers and administrative staff. The Manager is responsible for the day-to-day running of the office. An owner provides one or more taxis to CabsOnDemand and each taxi is allocated to a number of drivers for use. The majority of owners are also drivers themselves. For each taxi details such as Vehicle Registration Number, model, make, colour, capacity, currentMileage, DueDate for insurance are recorded.

For each staff, Staff Number, National Insurance Number, Address, Date of Birth, First Name, Last Name, Salary, Gender are recorded.

Each office is identified by a unique number, its location details, telephone and mobile numbers and email addresses are recorded.

The office issues contracts for clients and should record details of the contracts. Each contract is identified by a unique contract number, date at which contract was signed, its completion time, the total charge for contract and a maximum number of jobs allocated are recorded.

CabsOnDemand taxis are not available for hire by the public hailing a taxi in the street but must be requested by first phoning the company to attend a given address. This request is treated as a job and each job has to be identified uniquely; also all the details such as pick-up date, location, time, drop-off location, used mileages and the total chargeable amount should be recorded.

There are two kinds of clients, namely private and business. The business provided by private clients is on an ad-hoc basis. During the first booking, each client is given a unique identity and other details such as name, address information and contact details are collected.

However, the business provided by business clients is more formal and involves agreeing on a contract of work with the business. A contract stipulates the number of jobs that CabsOnDemand will undertake for a fixed fee hence, extra details such as the client's business type and email address are recorded too.

As described above, when a job comes into CabsOnDemand the name, phone number and contract number of the client is taken and then the pick-up date/time and pick-up/drop-off addresses are noted. Each job is allocated a unique jobID. The nearest driver to the pick-up address is called on his/her mobile and the job details are confirmed.

When a job is completed the driver should note the mileage covered or used and the charge made (for private clients only). If a job is not complete, the reason for the failed job should be a note.

ERD of Taxi on Demend



Create Table Queries

Tbl_office


CREATE TABLE [dbo].[tbl_office](
       [office_id] [int] NOT NULL,
       [location] [varchar](50) NOT NULL,
       [phone_number] [int] NOT NULL,
       [email] [varchar](50) NOT NULL,
 CONSTRAINT [PK_tbl_office] PRIMARY KEY CLUSTERED
(
       [office_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Tbl_staff


CREATE TABLE [dbo].[tbl_staff](
       [staff_id] [int] NOT NULL,
       [office_id] [int] NOT NULL,
       [national_insurance_number] [varchar](50) NOT NULL,
       [address] [varchar](50) NULL,
       [first_name] [varchar](50) NOT NULL,
       [last_name] [varchar](50) NOT NULL,
       [data_of_birth] [date] NULL,
       [salary] [int] NOT NULL,
       [gender] [varchar](50) NOT NULL,
       [occupation] [varchar](50) NOT NULL,
       [phone] [int] NULL,
       foreign key (office_id) references tbl_office(office_id),
 CONSTRAINT [PK_tbl_staff] PRIMARY KEY CLUSTERED
(
       [staff_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

TBL_owner


CREATE TABLE [dbo].[tbl_owners](
       [owner_id] [int] NOT NULL,
       [name] [varchar](50) NULL,
       [email] [varchar](50) NULL,
       [address] [varchar](50) NULL,
       [phone] [int] NULL,
 CONSTRAINT [PK_tbl_owners] PRIMARY KEY CLUSTERED
(
       [owner_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Tbl_driver


CREATE TABLE [dbo].[tbl_drivers](
       [driver_id] [int] NOT NULL,
       [name] [varchar](50) NULL,
       [address] [varchar](50) NULL,
       [phone_number] [int] NULL,
 CONSTRAINT [PK_tbl_drivers] PRIMARY KEY CLUSTERED
(
       [driver_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Tbl_taxi


CREATE TABLE [dbo].[tbl_taxi](
       [texi_id] [int] NOT NULL,
       [owner_id] [int] NOT NULL,
       [driver_id] [int] NOT NULL,
       [registration_number] [varchar](50) NULL,
       [current_mileage] [int] NULL,
       [model] [varchar](50) NULL,
       [location] [varchar](50) NULL,
       [make] [varchar](50) NULL,
       [color] [varchar](50) NULL,
       [capacity] [varchar](50) NULL,
       foreign key (owner_id) references tbl_owners(owner_id),
       foreign key (driver_id) references tbl_drivers(driver_id),
 CONSTRAINT [PK_tbl_taxi] PRIMARY KEY CLUSTERED
(
       [texi_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Tbl_contract


CREATE TABLE [dbo].[tbl_contracts](
       [contract_id] [int] NOT NULL,
       [charges] [int] NOT NULL,
       [date] [date] NOT NULL,
       [completion_time] [time](7) NOT NULL,
       [status] [varchar](50) NOT NULL,
       [reason] [varchar](50) NOT NULL,
 CONSTRAINT [PK_tbl_contracts] PRIMARY KEY CLUSTERED
(
       [contract_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Tbl_client


CREATE TABLE [dbo].[tbl_clients](
       [client_id] [int] NOT NULL,
       [address] [varchar](50) NULL,
       [contract_id] [int] NULL,
       [email] [varchar](50) NULL,
       [client_type] [varchar](50) NULL,
       [business_type] [varchar](50) NULL,
       [location] [varchar](50) NULL,
       [name] [varchar](50) NULL,
       foreign key(contract_id) references tbl_contracts(contract_id),
 CONSTRAINT [PK_tbl_clients] PRIMARY KEY CLUSTERED
(
       [client_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Tbl_jobs


CREATE TABLE [dbo].[tbl_jobs](
       [job_id] [int] NOT NULL,
       [used_mileage] [int] NULL,
       [client_id] [int] NULL,
       [charges] [int] NULL,
       [pick_up_date] [date] NULL,
       [pick_up_location] [varchar](50) NULL,
       [drop_off_location] [varchar](50) NULL,
       [time] [time](7) NULL,
       [driver_id] [int] NULL,
       foreign key(client_id) references tbl_clients(client_id),
 CONSTRAINT [PK_tbl_jobs] PRIMARY KEY CLUSTERED
(
       [job_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Insert Table Queries


Tbl_office


INSERT INTO [dbo].[tbl_office]
           ([office_id]
           ,[location]
           ,[phone_number]
           ,[email])
     VALUES
           (<office_id, int,>
           ,<location, varchar(50),>
           ,<phone_number, int,>
           ,<email, varchar(50),>)

Tbl_staff


INSERT INTO [dbo].[tbl_staff]
           ([staff_id]
           ,[office_id]
           ,[national_insurance_number]
           ,[address]
           ,[first_name]
           ,[last_name]
           ,[data_of_birth]
           ,[salary]
           ,[gender]
           ,[occupation]
           ,[phone])
     VALUES
           (<staff_id, int,>
           ,<office_id, int,>
           ,<national_insurance_number, varchar(50),>
           ,<address, varchar(50),>
           ,<first_name, varchar(50),>
           ,<last_name, varchar(50),>
           ,<data_of_birth, date,>
           ,<salary, int,>
           ,<gender, varchar(50),>
           ,<occupation, varchar(50),>
           ,<phone, int,>)

Tbl_owner


INSERT INTO [dbo].[tbl_office]
           ([office_id]
           ,[location]
           ,[phone_number]
           ,[email])
     VALUES
           (<office_id, int,>
           ,<location, varchar(50),>
           ,<phone_number, int,>
           ,<email, varchar(50),>)

Tbl_drivers


INSERT INTO [dbo].[tbl_drivers]
           ([driver_id]
           ,[name]
           ,[address]
           ,[phone_number])
     VALUES
           (<driver_id, int,>
           ,<name, varchar(50),>
           ,<address, varchar(50),>
           ,<phone_number, int,>)

Tbl_taxi


INSERT INTO [dbo].[tbl_taxi]
           ([texi_id]
           ,[owner_id]
           ,[driver_id]
           ,[registration_number]
           ,[current_mileage]
           ,[model]
           ,[location]
           ,[make]
           ,[color]
           ,[capacity])
     VALUES
           (<texi_id, int,>
           ,<owner_id, int,>
           ,<driver_id, int,>
           ,<registration_number, varchar(50),>
           ,<current_mileage, int,>
           ,<model, varchar(50),>
           ,<location, varchar(50),>
           ,<make, varchar(50),>
           ,<color, varchar(50),>
           ,<capacity, varchar(50),>)

Tbl_contract


INSERT INTO [dbo].[tbl_contracts]
           ([contract_id]
           ,[charges]
           ,[date]
           ,[completion_time]
           ,[status]
           ,[reason])
     VALUES
           (<contract_id, int,>
           ,<charges, int,>
           ,<date, date,>
           ,<completion_time, time(7),>
           ,<status, varchar(50),>
           ,<reason, varchar(50),>)

Tbl_clients


INSERT INTO [dbo].[tbl_clients]
           ([client_id]
           ,[address]
           ,[contract_id]
           ,[email]
           ,[client_type]
           ,[business_type]
           ,[location]
           ,[name])
     VALUES
           (<client_id, int,>
           ,<address, varchar(50),>
           ,<contract_id, int,>
           ,<email, varchar(50),>
           ,<client_type, varchar(50),>
           ,<business_type, varchar(50),>
           ,<location, varchar(50),>
           ,<name, varchar(50),>)

Tbl_Jobs


INSERT INTO [dbo].[tbl_jobs]
           ([job_id]
           ,[used_mileage]
           ,[client_id]
           ,[charges]
           ,[pick_up_date]
           ,[pick_up_location]
           ,[drop_off_location]
           ,[time]
           ,[driver_id])
     VALUES
           (<job_id, int,>
           ,<used_mileage, int,>
           ,<client_id, int,>
           ,<charges, int,>
           ,<pick_up_date, date,>
           ,<pick_up_location, varchar(50),>
           ,<drop_off_location, varchar(50),>
           ,<time, time(7),>

           ,<driver_id, int,>)

Report Queries


Report# 1:

The names and phone numbers of the Managers at each office.

Query:

select first_name as "First Name", last_name as "Last Name", tbl_staff.phone as "Contact", occupation as "Occupation"
from tbl_staff, tbl_office
where tbl_office.office_id=tbl_staff.office_id and tbl_staff.occupation='Manager'

Report# 2:

The names of all female drivers based in the Ealing or Islington office.

Query:

select tbl_staff.first_name as "First Name", tbl_staff.last_name as "Last Name"
from tbl_staff, tbl_office
where tbl_staff.office_id=tbl_office.office_id and tbl_staff.gender='female'
and (tbl_office.location = 'Ealing' or tbl_office.location='Islington')

Report# 3:

The total number of staff at each office and their managers.

Query:

select count(tbl_staff.staff_id) as "Total Staff", tbl_staff.occupation as "Occupation" from tbl_staff
group by tbl_staff.occupation

Report# 4:

The details of all taxis at the Ealing office.

Query:

select * from tbl_taxi
where tbl_taxi.location='Ealing'

Report# 5:

The name and number of owners with more than one taxi.

Query:

select tbl_owners.name as "Name", tbl_owners.phone as "Phone Number" from tbl_owners
where 1<(select COUNT(tbl_taxi.texi_id) from tbl_taxi where tbl_owners.owner_id=tbl_taxi.owner_id)

Report# 6:

The full address of all business clients in Islington.

Query:

select tbl_clients.address as "Address" from tbl_clients
where tbl_clients.location='Islington'

Report# 7:

The details of the current contracts with business clients in Ealing.

Query:

select tbl_contracts.contract_id as "ID", tbl_contracts.date as "Date",
tbl_contracts.completion_time as "Time", tbl_contracts.charges as "Charges"
from tbl_contracts, tbl_clients
where tbl_contracts.contract_id=tbl_clients.contract_id and tbl_clients.location='Ealing'

Report# 8:

The total number of private clients in each borough.

Query:

select count(tbl_clients.client_id) as "Total number of clients" from tbl_clients
where tbl_clients.client_type='Private clients'

Report# 9:

The details of jobs undertaken by a driver on a given day.

Query:

select tbl_jobs.job_id as "Job ID", tbl_jobs.pick_up_location as "Pick up Location",
tbl_jobs.drop_off_location as "Drop off Location", tbl_jobs.charges as "Charges",
tbl_jobs.time as "Time", tbl_contracts.date as "Date" from tbl_clients, tbl_contracts, tbl_jobs
where tbl_clients.contract_id=tbl_contracts.contract_id and tbl_clients.client_id=tbl_jobs.client_id
and date='11/18/2015'

Report# 10:

The names of private clients who hired a taxi in June 2014. 

Query:

select tbl_clients.name as "Name" from tbl_clients, tbl_contracts
where tbl_clients.contract_id=tbl_contracts.contract_id
and tbl_contracts.date between '06/01/2014' and '06/30/2014'

Download full documentataion with Visio file

Download Now!





CONTACT DETAILS

For any other questions or other tasks please feel free to contact me
via email: mhassnainjamil@gmail.com
via WhatsApp: +92-324-7042178
via skype: hassnainjamil1



Comments

Post a Comment

Popular posts from this blog

[SOLVED] Tape for a Turing Machine using Doubly-linked List in Java with full source code

By Anonymous -
Tape for a Turing Machine (Doubly-linked List) Short exercise in which you will code part of an existing project. In this case, you will be working with a doubly-linked list. To make this topic more interesting, the list that you work on will be part of a Turing Machine. A Turing Machine is a kind of very simple, abstract computing device that was introduced in the 1930's by Alan Turing as a way of studying computation and its limitations. The classes that you need for the lab are in a package named turing. You will define a new class named Tape in this package. You can find the Java source in the code directory. You should copy this directory into an Eclipse project. There will be errors, since the existing classes depend on the Tape class, which you have not yet written. Don't forget the Javadoc comments. A Class for Turing Machine Tapes A Turing machine works on a "tape" that is used for both input and output. The tape is made up of little squares called cell...
Read more

[SOLVED] ICT285 Databases TMA 2017 Assignment 1 | Relational Algebra, Database Design & Implementation

By Anonymous -
Relational Algebra A database records information about athletes competing at the Olympics. An athlete competes for a particular country in one or more events. Events take place at a scheduled day and time in a particular venue. The result (rank) is recorded for all athletes in the final of the event. The medal (gold, silver or bronze) is also recorded for the medal winners in the event. Note that we are not considering team sports or heats in this example – only individuals competing in the finals. The schema for this database is as follows: (Note that primary keys are shown underlined, foreign keys in bold). ATHLETE ( AthleteNo , AthleteName, CountryName) COUNTRY ( CountryName , NumberOfCompetitors) EVENT (EventName, ScheduledStart, VenueName ) VENUE (VenueName, City, Capacity) FINAL ( AthleteNo , EventName , Rank, Medal) Provide relational algebra (NOT SQL) queries to find the following informaList the name and country of all athletes. List the event name and scheduled...
Read more