ITDMA$0$ Project $3$ Specification $2021|$ V$3\mathrm{.}0$ Page $2$ of $6$

Section A

Question $1120$ Marks

A South African Flight Booking Agency wants to develop its online booking system to afford its

clients a self$-$service facility. The system will allow customers to check and reserve flights as and

when required. Customers will be able to view flights based on dates, times, prices, and

departure and arrival points. Any customer will be able to view flights on the system but only

registered customers$/$members can make a reservation.

On registration, a customer is requested to provide a South African ID or passport number as a

login ID$,$ as well as their e$-$mail address, telephone number, names, and an optional postal

address. Customers with an invalid e$-$mail address, telephone number and$/$or ID will be rejected.

The customer is also required to specify the class of flight when booking and ensure that

information such as dates and times, and places of departure and arrival are correct. The system

keeps track of flights by their names, dates and destinations. Flights are marked by flight

number, capacity, name, class and availability. There are three flight classes: $1$st $($executive

class$),2$nd $($business class$)$ and $3$rd $($economy class$).$

A ticket can only be reserved for available flights. Each client$/$customer can only reserve one

ticket per journey and there can be more than one passenger per ticket. The system keeps track

of flights by flight numbers, customers$$ names, dates and destinations. The system will allocate a

status of registered and not registered to a customer when an ID number is entered. A booking is

made using the customer$$s name, flight number and date, while an optional discount is available

for seniors and minors. Based on their ID number, any customer over $65$ years is considered a

senior citizen and anyone under $16$ years of age is a minor. Minors may not have ID numbers,

which would make it impossible to secure a discount when booking online. Customers are also

given a choice of payment options.

Specifications

Create an ER Diagram for the scenario above.

Create a database.

Create the tables for the scenario above. $($Ensure to use constraints such as CHECK and other

validation constraints in your tables to avoid losing marks.$)$

ITDMA$0$ Project $3$ Specification $2021|$ V$3\mathrm{.}0$ Page $3$ of $6$

Insert sample data into the tables you created above.

Create the following views:

$$ vw$\_$Flight $$ All flights appearing on the website, their availability and prices per

reservation.

$$ vw$\_$AffordableFlight $$ Select the top three flights with unchanged reservation and booking

price.

$$ vw$\_$SeniorUsers $-$ Select all senior users who qualify for a discount based on their age.

$$ vw$\_$OnboardCustomers $$ Select all member names as well as the total number of classes

that each member will attend. HINT: Use the COUNT function.

Create the following stored procedures:

$$ sp$\_$AddNewFlights $$ Insert new flights as they become available.

$$ sp$\_$UpdateFlyingTimes $$ Update the flying times as requested by customers based on

demand.

$$ sp$\_$DeleteTicket $$ Delete a ticket record. A ticket may only be deleted when it has been used

or its date has expired.

$$ sp$\_$Report $$ Print the details of a specified flight class and each member$$s name and contact

number assigned to the class. Your report$$s output should have exactly the same format as

shown below:

FLIGHT BOOKING REPORT:

$\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_$

Book ID: $3$

customer name: Janne $[$Date: Oct $222017]$

flight number: $4$ Journey: One way

No$.$ Customer Name Flight Price

$\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_$

$1.$ Jannie R$2500$

$2.$ Calvin R$1200$

$3.$ Siphiwo R$12000$

Use a counter to print the $$No$.$ column values, and ensure that the column values are properly

aligned.

ITDMA$0$ Project $3$ Specification $2021|$ V$3\mathrm{.}0$ Page $4$ of $6$

Create $2($two$)$ after triggers on the tables you have created.

Create Indexes on the tables you created.

Create a zipped backup of your database and hand it in with your script files