Database Systems Project $($PHASE $1)$ High$-$Level Requirements

Phase $1$ Report Due: November $03,2024$

In this project, you will develop a database system for the Saudi Railways. Your finished product

should contain information about trains, schedules, staff$/$ employees and customers.

Each project group should go through the following steps in Phase $1$ of the project:

$1.$ Identify and describe constraints, including business related, e$.$g$.,$ one person can only have a

maximum of $5$ reservations at a time; and technical constraints, such as primary keys, foreign

keys, check constraints, and not null constraints, for the tables and attributes, etc.

$2.$ Design the database following an EER approach.

$3.$ Find out how the most recent version of your database implements the concept of triggers and

then create at least one trigger for your database.

$4.$ Populate the database by using SQL insert statements or with some GUI interface such as

PhpMySQLAdmin in case you are using MySQL$.$

The list of requirements with constraints$/$business rules are as follows:

a$)$ A railway system has trains travelling from a city $($origin$)$ to a city $($destination$)$ through

connecting stations and tracks. You can assume for simplicity that only one track exists

between any two stations. All the tracks put together form a connected graph.

$,($ b$)$ Trains have an ID and an Arabic and English name such as HHR$100$

qi$$r al$-$aramayn as$-$sar$).$

c$)$ Train schedules keep a record of the time a train passes through each station on its route.

You can assume for simplicity that each train reaches its destination on the same day and

that every train with same train ID runs once every day. A sequence number of the

stations in the route of a train is maintained that shows the route ordered by sequence

number.

d$)$ Passenger booking consists of unique reservation number, train, date, from$-$station, to

station, coach $($economy or business class$),$ seat number and passenger name. Also, the

luggage details are maintained for each passenger. Passengers are required to upload their

identification document during the reservation process.

e$)$ Dependent$/$Family Member details are maintained for a passenger. These members are

given a $25\%$ discount on total ticket amount for each dependent when passengers travel

with family or dependents.

f$)$ Loyalty Miles system is also maintained for registered passengers who travel frequently.

A further discount is given on the ticket amount for each travel for three loyalty classes

based on miles travelled: Green $(5\%$ discount after $10$K miles travelled$),$ Silver $(10\%$

discount after $50$K miles travelled$)$ and Gold $(25\%$ discount after $100$K miles travelled$).$

g$)$ A payment gateway for the ticket $($s$)$ allows the payments to be made with a VAT of

$\mathrm{.}15\%$

h$)$ A waiting list is maintained for passengers who did not get a seat on the preferred train.

The reservation is temporary $($until a given date$)$ and is made permanent if the payment is

made before that date or cancelled after that date if payment is not made. Cancellation

charges of $15\%$ are applied on all reservations that are cancelled after the payment is

made.

i$)$ The system should be able to retrieve information like summary of passengers on any

train, waiting list for a given train, list of trains on a given day etc. The exact list of

reports will be given in the Phase $2$ of the project description.

We have three main actors in our system:

$$ Passenger: Mainly responsible for creating, modifying and requesting cancellation of

reservations.

$$ Staff: Staff will use the system for creating, modifying, cancelling the reservations,

maintain train details, refund the payment in the event of cancellation etc.

$$ System: Mainly responsible for sending notifications as reminders, canceled

reservations, confirming payments etc.

Phase $1$ Deliverables

A detailed report $($pdf$)$ which contains:

$$ Cover page $($Title$,$ Group Number, IDs & Names, Date$)$

$$ The report must include the following

$$ All constraints and business rules identified. Clearly state all your

assumptions.

$$ Detailed conceptual and logical design with EER and Relational

Schema.

$$ All the tools and resources that you used.

$$ A table which lists the tasks done by each group member.