Revised E/R Model

Relation Schemas

Summary

staff(tckno, name, salary, phone, homeaddress, start_date)

systemuser(tckno, password)
FK: tckno references staff

manager(tckno, position)
FK: tckno references systemuser

salesperson(tckno, officecity, officeaddress)
FK: tckno references systemuser
FK: (officecity, officeaddress) references sales_office(city, address)

terminalagent(tckno, terminalcity, terminaladdress)
FK: tckno references systemuser
FK: (terminalcity, terminal_address) references terminal(city,address)

sales_office(city, address, telephone)

driver(tckno, licenseno)
FK: tck_no references staff

rent(plate, starttime, endtime, price, rented_from)
FK: plate references bus

renteddriver(tckno, plate, starttime)
FK: tckno references driver
FK: (plate, start_time) references rent

assistant(tckno, rank)
FK: tckno references staff

rentedassistant(plate, starttime, tckno)
FK: tckno references assistant
FK: (plate, start_time) references rent

customer(tck_no, name, surname, dateofbirth, gender)

rentedby(tckno, plate, starttime)
FK: tckno references customer
FK: (plate,start_time) references rent

garage(city, address, telephone, capacity)

is_at(city, address, plate)
FK: (city, address) references garage
FK: plate references bus

bustype(brand, model, year, layout, storagecapacity, passangercapacity, height, width, length)

bustypefeature(brand, model, year, featurename)
FK: (brand, model, year) references bustype

terminal(city, address, telephone, capacity)

bus(plate, brand, model, year, roamingdistance, startdate, is_operational)
FK: (brand, model, year) references bustype

route(id, departcity, departaddress, arrivecity, arriveaddress, estimatedduration, distance, numberofbreaks)
FK: (departcity, departaddress) references terminal(city, address)
FK: (arrivecity, arrive_address) references terminal(city, address)

stopover(city, address, routeid)
FK: routeid references route(id)
FK: (city, address) references terminal

servicearea(address, start_year)

break(routeid, address, duration)
FK: routeid references route(id)
FK: address references servicearea

voyage(plate, routeid, departuretime, arrivaltime, price, occupiedseats)
FK: plate references bus
FK: route_id references route(id)

reservation(tckno, plate, routeid, departuretime, seat, time, price)
FK: tckno references customer
FK: (plate,routeid, departuretime) references voyage

associateddriver(tckno, plate, routeid, departuretime)
FK: tckno references driver
FK: (plate,routeid, departure_time) references voyage

associatedassistant(tckno, plate, routeid, departuretime)
FK: tckno references assistant
FK: (plate,routeid, departure_time) references voyage

ticket(tckno, plate, routeid, departuretime, seat, paymenttype, paymenttime, price)
FK: tckno references customer
FK: (plate,routeid, departuretime) references voyage

Detailed Schemas

staff

Relational Model: staff(tckno, name, salary, phone, homeaddress, startdate)
Functonal Dependencies: tckno -> name, salary, phone, homeaddress, startdate
Candidate Keys: {(tckno)}
Normal Form: BCNF
Table Definition:
create table staff (
tckno char(8) primary key,
name varchar(50) not null,
salary numeric() not null,
phone varchar(15) not null,
homeaddress varchar(100) not null,
startdate varchar(10) not null
);

system_user

Relational Model: systemuser(tckno, password)
Functional Dependencies: tckno -> password
Candidate Keys: {(tckno)}
Normal Form: BCNF
Table Definition:
create table systemuser (
tckno char(8) primary key,
password varchar(20) not null,
foreign key (tck_no) references staff
);

manager

Relational Model: manager(tckno, position)
Functional Dependencies: tckno -> position
Candidate Keys: {(tckno)}
Normal Form: BCNF
Table Definition:
create table manager (
tckno char(11) primary key,
position varchar(40),
foreign key (tck_no) references staff
);

sales_person

Relational Model: salesperson(tckno, officecity, officeaddress)
Functional Dependencies: tckno -> officecity, officeaddress
Candidate Keys: {(tckno)}
Normal Form: BCNF
Table Definition:
create table salesperson (
tckno char(11) primary key,
officecity varchar(20) not null,
officeaddress varchar(100) not null,
foreign key (tckno) references staff,
foreign key (officecity, officeaddress) references sales_office
);

terminal_agent

Relational Model: terminalagent(tckno, terminalcity, terminaladdress)
Functional Dependencies: tckno -> terminalcity, terminaladdress
Candidate Keys: {(tckno)}
Normal Form: BCNF
Table Definition:
create table terminalagent (
tckno char(11) primary key,
terminalcity varchar(20) not null,
terminaladdress varchar(100) not null,
foreign key (tckno) references staff,
foreign key (officecity, officeaddress) references salesoffice
);

sales_office

Relational Model: salesoffice(city, address, phone)
Functional Dependencies: city, address -> phone
Candidate Keys: {(city, address)}
Normal Form: BCNF
Table Definition:
create table salesoffice (
city varchar(20) primary key,
address varchar(100) not null,
phone varchar(15) not null
);

driver

Relational Model: driver(tckno, licenseno)
Functional Dependencies: tckno -> licenseno
Candidate Keys: {(tckno)}
Normal Form: BCNF
Table Definition:
create table driver (
tckno char(11) primarykey,
licenseno char(7) not null
foreign key (tck_no) references staff,
);

rent

Relational Model: rent(plate, starttime, endtime, price, rentedfrom)
Functional Dependencies: plate, starttime -> endtime, price, rentedfrom
Candidate Keys: {(plate, starttime)}
Normal Form: BCNF
Table Definition:
create table rent (
plate varchar(10) primary key,
starttime varchar(10) not null,
endtime varchar(10),
price numeric(4,2) not null,
rentedfrom varchar(20) not null,
foreign key (plate) references bus
);

rented_driver

Relational Model: renteddriver(tckno, plate, starttime)
Functional Dependencies:
Candidate Keys: {(tckno, plate, starttime)}
Normal Form: BCNF
Table Definition:
create table renteddriver (
tckno char(11) primary key,
plate varchar(10) primary key,
starttime varchar(10) primary key,
foreign key (tckno) references driver,
foreign key (plate, starttime) references rent
);

assistant

Relational Model: assistant(tckno, rank)
Functional Dependencies: tckno -> rank
Candidate Keys: {(tckno)}
Normal Form: BCNF
Table Definition:
create table assistant (
tckno char(11) primary key,
rank varchar(20),
foreign key (tck_no) references staff
);

rented_assistant

Relational Model: rentedassistant(plate, starttime, tckno)
Functional Dependencies: -
Candidate Keys: {(plate, starttime, tckno)}
Normal Form: BCNF
Table Definition:
create table rentedassistant (
plate varchar(10) primary key,
starttime varchar(10) primary key,
tckno char(11) primary key,
foreign key (plate, starttime) references rent,
foreign key (tckno) references assistant
);

customer

Relational Model: customer(tckno, name, surname, dateofbirth, gender)
Functional Dependencies: tchno -> name, surname, dateofbirth, gender
Candidate Keys: {(tckno)}
Normal Form: BCNF
Table Definition:
create table customer (
tckno char(10) primary key,
name varchar(35) not null,
surname varchar(15) not null,
dateofbirth varchar(10),
gender char(1)
);

rented_by

Relational Model: rentedby(tckno, plate, starttime)
Functional Dependencies: -
Candidate Keys: {(tckno, plate, starttime)}
Normal Form: BCNF
Table Definition:
create table rentedby(
tckno char(11) primary key,
plate varchar(10) primary key,
starttime varchar(10) primary key,
foreign key (tck_no) references customer,
foreign key (plate) references bus,
);

garage

Relational Model: garage(city, address, phone, capacity)
Functional Dependencies: city, address -> phone, capacity
Candidate Keys: {(city, address)}
Normal Form: BCNF
Table Definition:
create table garage (
city varchar(20) primary key,
address varchar(100) primary key,
phone varchar(15) not null,
);

is_at

Relational Model: isat(city, address, plate)
Functional Dependencies: -
Candidate Keys: {(city, address, plate)}
Normal Form: BCNF
Table Definition:
create table isat (
city varchar(20) primary key,
address varchar(100) primary key,
plate varchar(10) primary key,
foreign key (city, address) references garage,
foreign key (plate) references bus
);

bus_type

Relational Model: bustype(brand, model, year, layout, passengercapacity, storagecapacity, height, width, length)
Functional Dependencies: brand, model, year -> layout, passengercapacity, storagecapacity, height, width, length
Candidate Keys: {(brand, model, year)}
Normal Form: BCNF
Table Definition:
create table bustype (
brand varchar(20) primary key,
model varchar(20) primary key,
year char(4) primary key,
layout smallint not null,
passengercapacity smallint not null,
storage_capacity numeric(3,2) not null,
height numeric(1,2),
width numeric(1,2),
length numeric(2,1)
);

bustype_feature

Relational Model: bustypefeature(brand, model, year, featurename)
Functional Dependencies: -
Candidate Keys: {(brand, model, year, featurename)}
Normal Form: BCNF
Table Definition:
create table bustypefeature (
brand varchar(20) primary key,
model varchar(20) primary key,
year char(4) primary key,
featurename varchar(30) primary key,
foreign key (brand, model, year) references busttype,
);

terminal

Relational Model: terminal(city, address, phone, capacity)
Functional Dependencies: city, address -> phone, capacity
Candidate Keys: {(city, address)}
Normal Form: BCNF
Table Definition:
create table terminal (
city varchar(20) primary key,
address varchar(100) primary key,
phone varchar(15) not null,
capacity smallint not null
);

bus

Relational Model: bus(plate, brand, model, year, roamingdistance, startdate, isoperational)
Functional Dependencies: plate -> brand, model, year, raomingdistance, startdate, isoperational
Candidate Keys: {(plate)}
Normal Form: BCNF
Table Definition:
create table bus (
plate varchar(10) primary key,
brand varchar(20) primary key,
model varchar(20) primary key,
year char(4) primary key,
roamingdistance int default 0 not null,
startdate varchar(10) not null,
isoperational char(1) not null,
foreign key (brand, model, year) references busttype
);

route

Relational Model: route(routeid, departcity, departaddress, arrivecity, arriveaddress, estimatedduration, distance, numberofbreaks)
Functional Dependencies: routeid -> departcity, departaddress, arrivecity, arriveaddress, estimatedduration, distance, numberofbreaks)
Candidate Keys: {(routeid)}
Normal Form: BCNF
Table Definition:
create table route (
routeid char(12) primary key,
departcity varchar(20) not null,
departaddress varchar(100) not null,
arrivecity varchar(20) not null,
arriveaddress varchar(100) not null,
estimatedduration int,
distance int not null,
numberofbreaks smallint default 0 not null,
foreign key (departcity, departaddress) references terminal,
foreign key (arrivecity, arrive_address) references terminal
);

stopover

Relational Model: stopover(city, address, routeid)
Functional Dependencies: -
Candidate Keys: {(city, address, routeid)}
Normal Form: BCNF
Table Definition:
create table stopover (
city varchar(20) primary key,
address varchar(100) primary key,
routeid char(12) primary key,
foreign key (routeid) references route,
foreign key (city, address) references terminal
);

service_area

Relational Model: servicearea(address)
Functional Dependencies: -
Candidate Keys: {(address)}
Normal Form: BCNF
Table Definition:
create table service_area (
address varchar(100) primary key
);

break

Relational Model: break(routeid, address, duration)
Functional Dependencies: routeid, address -> duration
Candidate Keys: {(routeid, address)}
Normal Form: BCNF
Table Definition:
create table break (
routeid char(12) primary key,
address varchar(100) primary key,
duration int not null,
foreign key (routeid) references route,
foreign key (address) references servisearea
);

voyage

Relational Model: voyage(plate, routeid, departuretime, arrivaltime, price, occupiedseats)
Functional Dependencies: plate, routeid, departuretime -> arrivaltime, price
Candidate Keys: {(plate, routeid, departuretime)}
Normal Form: BCNF
Table Definition:
create table voyage (
plate varchar(10) primary key
routeid char(12) primary key
departuretime varchar(10) primary key
arrivaltime varchar(10),
price numeric(3,2) not null,
occupiedseats int not null,
foreign key (plate) references bus,
foreign key (routeid) references route
);

reservation

Relational Model: reservation(tckno, plate, routeid, departuretime, seat, time, price)
Functional Dependencies: tckno, plate, routeid, seat -> time, price
Candidate Keys: {(tckno, plate, routeid, seat)}
Normal Form: BCNF
Table Definition:
create table reservation (
tckno char(11) primary key,
plate varchar(10) primary key,
routeid char(12) primary key,
departuretime varchar(10) primary key,
seat char(2) primary key,
time varchar(10) not null,
price numeric(3,2) not null,
foreign key (tckno) references customer,
foreign key (plate,routeid, departure_time) references voyage
);

associated_driver

Relational Model: associateddriver(tckno, plate, routeid, departuretime)
Functional Dependencies: -
Candidate Keys: {(tckno, plate, routeid, departuretime
Normal Form: BCNF
Table Definition:
create table associateddriver (
tckno char(11) primary key,
plate varchar(10) primary key,
routeid char(12) primary key,
departuretime varchar(10) primary key,
foreign key (tckno) references customer,
foreign key (plate,routeid, departuretime) references voyage
);

associated_assistant

Relational Model: associatedassistant(tckno, plate, routeid, departuretime)
Functional Dependencies: -
Candidate Keys: {(tckno, plate, routeid, departuretime)}
Normal Form: BCNF
Table Definition:
create table associatedassistant (
tckno char(11) primary key,
plate varchar(10) primary key,
routeid char(12) primary key,
departuretime varchar(10) primary key,
foreign key (tckno) references customer,
foreign key (plate,routeid, departuretime) references voyage
);

ticket

Relational Model: ticket(tckno, plate, routeid, departuretime, seat, paymenttype,paymenttime, price)
Functional Dependencies: tckno, plate, routeid, departuretime, seat -> paymenttype, paymenttime, price
Candidate Keys: {(tckno, plate, routeid, departuretime, seat)}
Normal Form: BCNF
Table Definition:
create table ticket (
tckno char(11) primary key,
plate varchar(10) primary key,
routeid char(12) primary key,
departuretime varchar(10) primary key,
seat char(2) primary key,
paymenttype varchar(5) not null,
paymenttime varchar(1o) not null,
price numeric(3,2) not null,
foreign key (tckno) references customer,
foreign key (plate,routeid, departure_time) references voyage
);
 

Functional Dependencies and Normalization of Tables

All functional dependencies are shown in section 2.2. Detailed Schemas. All the relations are already in BCNF form. There is no need for decomposition. 

Functional Components

Use Cases/ Scenarios

Customer Operations

• Customers can view scheduled voyages.
  
• Customers can view seat status of each voyage.
  
• Customers can make reservations for empty seats of voyages online.
  
• Customers can buy tickets for empty seats of voyages online.
  
• Customers can cancel their reservations for voyages online.
  
• Customers can rent buses for specific time intervals.
  
• Customers can rent buses with or without bus crew.
  

Salesperson Operations

• Salespeople can view list of tickets and reservations.
  
• Salespeople can view scheduled voyages.
  
• Salespeople can view details (seat information, reserved, arrival and departure times) of any voyage.
  
• Salespeople can sale tickets to customers.
  
• Salespeople can apply discounts on standard ticket prices.
  
• Salespeople can make reservations for customers.
  
• Salespeople can cancel reservations.
  
• Salespeople can refund tickets.
  

Terminal Agent Operations

• Terminal agents can view scheduled voyages along with their seat status.
  
• Terminal agents can enter arrival times of the buses.
  
• Terminal agents can cancel voyages with zero passengers.
  

Manager Operations

• Managers can arrange (add/remove/change) voyages.
  
• Managers can register/unregister buses to the system.
  
• Managers can create/remove bus types.
  
• Managers can create routes.
  
• Managers can set standard prices for specific route-bus pairs.
  
• Managers can lease buses.
  
• Managers can adjust salaries of salespeople, terminal agents, drivers and assistants.
  
• Managers can hire/fire salespeople, terminal agents, drivers and assistants.
  
• Managers can update staff info.
  
• Managers can register/unregister terminals, branches and service areas.
  
• Managers can query average passenger load factors of routes.
  

Algorithms

Modifying Entities

Manager can add, remove and edit entities such as busses, terminals, routes, staff etc. These function first check if the current user has the manager privileges and display an error if it does not. An input form is display for an add function. Input is validated in both client-side and server-side. If input is valid, new rows are added to the corresponding tables. A list of existing entities are display in a table for a remove and edit functions. Items selected for removal are deleted from database, or an edit form is displayed for edit functions.

Creating Voyage

Manager can create voyages using existing busses and routes. The input form for creating voyages have a timetable. Manager can create a single voyage as well as multiple voyages in a single step. Validation step determines the schedule conflicts and displays related errors.

Making Reservation

Salesperson (in sales-office) and customer (on the internet) can make reservations for voyages. Voyage and seat options are displayed in the reservation form. User cannot make reservation for a sold or reserved seat.

Purchasing/Selling Ticket

A ticket is either sold by a salesperson or purchased by a customer online. Either way, the inputs and ticket creation are the same. A ticket can be purchased directly, or a reservation can be converted to a ticket by paying the fee. If a new ticket is being created, a similar form to the reservation form is displayed and filled. If customer is buying ticket for a reservation, this step is omitted. In the last step, the payment is done.

Canceling Reservation

Salesperson and customer can list and cancel reservations. Salesperson has right to cancel any reservation whereas customer can only cancel his/her reservations.

Renting Bus

Busses can be rented from the sale-offices. Hence, only salesperson has the ability to rent a bus. During the process, salesperson selects the bus and time range for renting. If the selected bus has a voyage anytime during the time range, renting cannot be done and an error is displayed. Renting fee is entered by the salesperson.

Data Structures

Built-in MySQL data structures will be used. There is no need for additional data types.
 

User Interface Design and Corresponding SQL Statements

Login

Input: @tck_no, @password
Process: User provides his tc identity number and password. System checks if a corresponding record exists. If it does, then the user is authorized. Otherwise an error screen is displayed.

SQL Statements:

SELECT *  
FROM systemuser  
WHERE tck_no = @tck_no  
  AND password=@password  

Bus Rental

Input: @endtime, @price, @rentedfrom
Process: Only a salesperson can make rental operation. There is no customer interface for this purpose. Salesperson asks for list of available buses. System shows a list of all buses that are currently in one of the garages (not travelling). Salesperson clicks “rent” and enters customer information through another form.

SQL Statements:

SELECT plate,  
       model,  
       brand,  
       city,  
       address  
FROM bus,  
     is_at  
WHERE is_operational = 'operational'  
  AND bus.plate = is_at.plate  
variable passed: @plate  
INSERT INTO rent  
VALUES(@plate,  
       getCurrentDate(),  
       @end_time,  
       @price,  
       @rented_from)  

Manager Dashboard

Input: -
Process: System shows various information about active voyages, busses, garages and active rentals.

SQL Statements:

List voyages:

SELECT v.departure_time,  
       v.arrival_time,  
       r.depart_ciy,  
       r.depart_address,  
       r.arrive_city,  
       r.arrive_address,  
       b.model  
FROM voyage v,  
     bus b,  
     route r  
WHERE v.plate=b.plate  
  AND v.route_id = r.id  
ORDER BY v.departure_time  

List buses:

SELECT b.plate,  
       b.model,  
       i.city,  
       i.address  
FROM bus b, is_at i,  
WHERE b.plate=i.plate  
  OR (b.plate = v.plate  
      AND v.arrival_time IS NULL)  

List garages:

SELECT g.city,  
       g.address,  
       count(i.plate)  
FROM garage g,  
     is_at i  
WHERE i.cty=g.city  
  AND i.address = g.address  
GROUP BY i.city,  
         i.address  

List rentals:

SELECT c.name,  
       r.start_time,  
       r.end_time,  
       b.model,  
FROM bus b,  
     rent r,  
     rented_by,  
     customer c  
WHERE r.plate=b.plate  
  AND rented_by.plate = r.plate  
  AND r.start_time = rented_by.start_time rented_by.tck_no = c.tck_no  

List Voyages:

Input: @depcity, @arrcity, @dep_time
Process: Salesperson enters departure city, arrival city and a date. System shows appropriate voyages.

SQL Statements:

SELECT r.depart_city,  
       r.depart_address,  
       r.arrive_city,  
       r.arrive_address,  
       v.departure_time,  
       v.arrival_time,  
       v.occupied_seats,  
       bt.passanger_capacity b.model,  
FROM bus b,  
     route r,  
     voyage v,  
     bustype bt  
WHERE r.id = v.route_id  
  AND v.plate = b.plate  
  AND bt.brand = b.brand  
  AND bt.model = b.model  
  AND bt.year = b.year  
  AND r.depart_city=@dep_city  
  AND r.arrive_city = @arr_city  
  AND v.departure_time = @dep_time  

List Reservations

Input: @time
Process: Salesperson wants to list all the reservations on a specific day. He enters the day. Sytem lists all the reservations mad efor that particular day. Later salesperson may filter these results by name. Salesperson can cancel a reservation by clicking the button. System passes required variables to cancel query.

SQL Statements:

SELECT c.name,  
    c.tck_no,  
       c.surname,  
       res.price,  
    res.seat,  
    ro.id,  
       ro.depart_city,  
       ro.arrive_city,  
    v.plate  
FROM customer c,  
     reservation res,  
     route ro,  
     voyage v  
WHERE res.tck_no = c.tck_no  
  AND res.plate = v.plate  
  AND res.route_id = v.route_id  
  AND res.departure_time = v.departure_time  
  AND v.route_id = ro.id  
  AND res.time=@time  

Cancel:

DELETE  
FROM reservation  
WHERE tck_no=@tck_no  
  AND plate=@plate  
  AND route_id=@route_id  
  AND departure_time=@dtime  
  AND seat=@seat  

Create Ticket

Input: @tckno,@plate,@routeid, @departuretime, @seat, @paymenttype, @price, @name, @surname, @dateofbirth, @gender
Process: Salesperson chose a voyage before this step. All voyage, bus and route information was passed from previous step. In this step salesperson provides tckno, seat, paymenttype, name, surname, dateofbirth, gender inputs.

SQL Statements:

INSERT INTO ticket  
VALUES(@tck_no,  
       @plate,  
       @route_id,  
       @departure_time,  
       @seat,  
       @payment_type,  
       getCurrentTime(),  
       @price)  

INSERT INTO customer(@tck_no, @name, @surname, @dateofbirth, @gender)
*customer table are protected from having duplicates by a trigger

Make Reservation

Input: @tckno,@plate,@routeid, @departuretime, @seat, @paymenttype, @price, @name, @surname, @dateofbirth, @gender
Process: Salesperson chose a voyage before this step. All voyage, bus and route information was passed from previous step. In this step salesperson provides tck_no, seat, name, surname, dateofbirth, gender inputs.

SQL Statements:

INSERT INTO reservation  
VALUES(@tck_no,  
       @plate,  
       @route_id,  
       @departure_time,  
       @seat,  
       getCurrentTime(),  
       @price)  

INSERT INTO customer(@tck_no, @name, @surname, @dateofbirth, @gender)
*customer table are protected from having duplicates by a trigger

Edit Bus

Input: @plate, @type, @is_operational
Process: Manager chose a bus to edit at previous step. Plate information was passed to this step from the previous one. He enters new information and system updates the bus info.

SQL Statements:

UPDATE bus  
SET is_operational=@is_operational,  
    type=@type  
WHERE plate=@plate  

Edit/Remove Bus Type

Input: @model,@brand, @layout,@storagecapacity, @passangercapacity,@height, @width,@length,@year
Process: Manager wants to list bus types. System makes a query to list all the bus types. System makes another query to retrieve features for bus types for each bus type and combine the information. Manager later can filter bus types by model, brand or year. Delete operation is only valid when no bus is of that bus type. This restriction is granted by an assertion which will be introduced in the next section of this document. Editing a bus type is done by another form after clicking “edit” here. Required information is passed to next form.

SQL Statements:

SELECT brand,  
       model,  
       year,  
       layout,  
       passanger_capacity  
FROM bustype  

SELECT feature_name features  
FROM bustype_feature  
WHERE brand=@brand  
  AND model=@model  
  AND YEAR=@year  

DELETE  
    FROM bustype  
    WHERE model=@model  
      AND brand=@brand  
      AND year=@year  

UPDATE bustype  
    SET model=@model,  
        brand=@brand,  
        layout=@layout,  
        storage_capacity=@storage_capacity,  
        passanger_capacity=@passanger_capacity,  
        height=@height,  
        width=@width,  
        length=@length ,  
        year=@year  

Terminal Agent Dashboard

Input: @time, @plate, @departuretime, @arrivaltime
Process: Terminal agent is responsible for entering arrival and departure times of buses. System shows a list of arriving buses. Select statement for listing is the same as salesperson’s. Update of departure times is done via another form which is accessible from this screen by clicking “edit” button. In that screen, terminal agent can enter arrival time.

SQL Statements:

UPDATE voyage  
SET arrival_time=@arrival_time  
WHERE plate=@plate  
  AND route_id = @route_id  
  AND departure_time=@departure_time  

Customer Dashboard

Input: @departurecity, @arrivalcity, @departure_date
Process: Customer wants to buy a ticket. He provides departure, arrival cities and a date. System shows available voyages.

SQL Statements:

SELECT r.depart_city,  
       r.depart_address,  
       r.arrive_city,  
       r.arrive_address,  
       v.departure_time,  
       v.arrival_time,  
       b.model,  
       bt.passanger_capacity,  
       v.occupied_seats  
FROM bus b,  
     bustype bt,  
     route r,  
     voyage v  
WHERE r.id = v.route_id  
  AND bt.model = b.model  
  AND bt.brand=b.brand  
  AND bt.year=b.year  
  AND v.plate = b.plate  
  AND r.depart_city=@departure_city  
  AND r.arrive_city = @arrival_city  
  AND v.departure_time = @departure_time  
  AND v.occupied_seats<bt.passanger_capacity  

Advanced Database Components

Views

Note: Only direct user of the database is the front-end application, hence views actually are not needed in this case. But for educational purposes, we provide views as actors of use-cases are direct users of the database.

• Terminal agent updates arrival and departure times of voyages and knows the plate of the bus beforehand. Hence, terminal agent does not need to have access to other attributes of voyage table.

  CREATE VIEW voyage_times AS  
  SELECT plate,  
         departure_time,  
         arrival_time  
  FROM voyage  
  

• Finding phone number of a terminal is an action that a customer can perform. But customer must not have access to capacity information of the terminal. This view is read-only.

  CREATE VIEW terminal_phone AS  
  SELECT city,  
         address,  
         telephone  
  FROM terminal  
  

• Employees may want to retrieve phone numbers of other employees from the database. For example, a salesperson can query phone number of a terminal agent, but the salesperson should not be able to see the salary of the terminal agent.

  CREATE VIEW staff_phone AS  
  SELECT tck_no,  
         name,  
         phone  
  FROM staff  
  

• Salesperson can see busses for renting them, but busses that are not operational are not needed. The salesperson does also not need to see roaming distance and start time of a bus.

  CREATE VIEW operational_busses AS  
  SELECT plate,  
         brand,  
         model,  
         year  
  FROM bus  
  WHERE is_operational = TRUE  
  

Stored Procedures

• Calculating passenger load factors
  Registration and authentication procedures
  
• Counting the number of busses at each garage
  
• Calculating incomes from tickets sold and expenses due to employee salaries and bus costs over travel distances
  
• Calculating deviations of voyage durations with respect to estimated durations and determining new estimates
   
  

Reports

• Total Salary Bill

  SELECT sum(salary)  
  FROM staff  
  

• Usage Rates of Busses (total travel distance over time)

  SELECT plate,  
         roaming_distance/(CURRENT_DATE() - start_date)  
  FROM bus  
  ORDER BY roaming_distance/(CURRENT_DATE() - start_date) DESC  
  

• Passenger Load Factors of Routes

  SELECT avg(V.occupied_seats / B.capacity)  
  FROM voyage AS V NATURAL  
  LEFT OUTER JOIN bus NATURAL  
  LEFT OUTER JOIN bus_type AS B  
  WHERE V.route_id = @routeID  
  

• Customers with their total spendings(traveling) in decreasing order

  SELECT tck_no,  
         sum(price) AS total_spending  
  FROM customer  
  NATURAL JOIN ticket  
  GROUP BY tck_no  
  ORDER BY total_spending DESC  
  

• Customers with their total spendings(renting) in decreasing order

  SELECT tck_no,  
         sum(price) AS total_spending  
  FROM customer  
  NATURAL JOIN ticket  
  GROUP BY tck_no  
  ORDER BY total_spending DESC  
  

Triggers

• On insert operation into ticket table, corresponding reservation row (if it exist) is deleted.
• On insert operation into voyage table, roaming_distance of associated bus is increased by the distance of the route.
• On delete operation from voyage table, roaming_distance of associated bus is decreased by the distance of the route.
• On insert operation into ticket table, occupied_seats of associated voyage is increased.
• On delete operation from ticket table, occupied_seats of associated voyage is decreased.

Constraints

• All tck_no attributes are unique Turkish Identification Numbers (TIN) with 11 digits. The attribute cannot get a value with less or more digits. If the employee does not have a TIN, an invalid TIN with 11 digits that is unique to that employee must be used.
• license_no attribute of driver table must be a valid Turkish Driver's License Number with 7 digits.
• endtime attribute of rent table must have a value of date that comes after value of starttime.
• height, width, length attributes of bustype table, roaming_distance in bus, and distance in route have the units of meter.
• In route table, departcity and departaddress pair cannot be the same with arrivecity and arriveaddress pair.
• password attribute of systemuser table can have a value at least 8 characters long.
• All price, distance, capacity and duration attributes must have a non-negative value.
• When a voyage is being created or a bus is being rented, is_operational attribute of the associated bus must be true.   # Implementation Plan

We will use MySQL Server as database management system for our project. We are planning to use PHP, Code Igniter and JavaScript for software and user interface parts of our project. CodeIgniter is a PHP framework which is optimized for MVC use.