Submission: Through the webpage of course in CW system.

Project Description:

A

In this project, you must design a database management system for the train ticket sales system. Your design needs to include: the ERD diagram, the relational model, the relational algebra expressions as well as the corresponding SQL commands for each report. Please note that the final project is a group work. Each working group can include at most three students. Each member of a working team need to submit its work separately via the CW system. You should consider the following details in your design:

Each tractor $($locomotive$)$ can only move $13$ passenger wagons or $6$ freight wagons $($for car transport$).$

The ticket sales system is generally set up for each tourist center, meaning that this system

must be able to be assigned to multiple centers. It should be noted that each tourist center needs to be discriminated from the other by a unique code. The commission amount of each center should be set by the system administrator and its

contract number

Each train has the following specifications that must be set by the system manager before

selling the ticket:

Wagon section:

Men's passenger wagon

Women's passenger wagon

Family passenger wagon

Special freight wagon for transporting cars

Route settings:

The system administrator must adjust the date of departure based on the capacity of the wagons

$$ The number of coupes in each wagon must be adjusted by the manager, but the number of seats in each coupe is defined according to the standard.

Normal coupe: $6$ seats; price of each seat M; the total price of the coupe is $6-$M

Royal Coupe: $4$ seats; price of each seat N; the total price of the coupe is $4-$N

Ticket sales settings:

The ticket sales system should calculate the passenger's age automatically based on his. The cost of each passenger ticket according to his$/$her age, has the following rates:

Travelers $12$ years and more, full ticket

Travelers under $12$ years, half$-$price ticket

The designed system should be able to provide the following reports:

$1.$ A report of the names of passengers, wagons, coupes, gender

$2.$ Report of vehicles delivered for transportation including owner name, license plate number, passenger name, wagon, coupe

$3.$ A report on the number of filled wagons and filled coupes

$4.$ Report of empty seats: seat number, coupe name, wagon name, gender assigned to the wagon

$5.$ Different financial reports such as:

a$.$ Ticket sales

b$.$ Commissions allocated to each tourist center

c$.$ Number of passengers by gender plus costs

d$.$ Time of departure, number of wagons and name of train

$6.$ A report on the number of tractors required to carry the wagons of a train