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'
hystoVospa Heather Wang https://wakelet.com/wake/Nrlaff39aw0hxrKROet4Q
ReplyDeletefromiztralvi
Usancredtiaya Jessica Gilmore click
ReplyDeletehttps://colab.research.google.com/drive/1UrMcbsm7E_GhvDo4n9b-VlTvvtY7K3MU
click here
click
ommeperthough