Database management systems Project

The purpose of this project is to give students an opportunity to put the theory of database modeling, implementation and application design into practice. Students will choose a project and develop its database.

Task $1$: Project proposal $[2$ marks $]$

Write a proposal for your project. It should include the following:

A cover page outlining the title of your project, the full names of the group members $($with Student IDs and section$),$ your assigned Group Number.

A background of the project or application being created. This should also include a description of the problem.

Project objectives

Identification of the data requirements

Distribution of duties for the project. List the names of each group member and what are their primary roles.

Task $2$: Database design and implementation $[4$ marks$]$

A$.$ Database design

A list of entities $($tables$)$ that have been identified to solve the identified problem

The conceptual data model $($ERD$)$

The relational mapping data model $($Database Schema$)$

Groups may use a specific modeling tool such as MS Visio, LucidChart, or drawing tools available in MS Word.

B$.$ Database implementation

Implement the Physical Data Model. Write the code used to create the tables, screenshots from your created tables.

Task $3$: Database manipulation, Final report and presentation $[4$ marks $]$

Database Manipulation

Design some views to show the important report in the system.

Write the required code to manipulate the system

Due Date $30$ Nevember $2024$

Practical scenario of the train system

Description:

The train system serves the major cities in the Kingdom. The system manages information about customers, employees, trains, trips, and tickets. We have $5$ trains operating between several cities, and each train has several daily trips. Customers and employees are registered and linked to the system, and customers can purchase tickets for trips.

Number of customers and employees:

$$ Customers: $1000$ customers registered in the system.

$$ Employees: $50$ employees $(30$ working as train crew, and $20$ in ticket offices$).$

Number of trains and daily trips:

$$ Number of trains: $5$ trains:

$$ Riyadh $-$ Jeddah train $($fast $-$ capacity $200$ passengers$).$

$$ Riyadh $-$ Dammam train $($fast $-$ capacity $150$ passengers$).$

$$ Jeddah $-$ Medina train $($local $-$ capacity $100$ passengers$).$

$$ Riyadh $-$ Mecca train $($fast $-$ capacity $180$ passengers$).$

$$ Riyadh $-$ Qassim train $($local $-$ capacity $120$ passengers$).$

$$ Number of daily trips for each train:

$$ Each train has two outbound and two return trips daily.

Ticket prices:

$$ The price is determined based on the distance between cities:

$$ Riyadh $-$ Jeddah: $300$ riyals.

$$ Riyadh $-$ Dammam: $150$ riyals.

$$ Jeddah $-$ Medina: $100$ riyals.

$$ Riyadh $-$ Mecca: $250$ riyals.

$$ Riyadh $-$ Qassim: $120$ riyals.

$10\%$ tax is added.

Suggested times:

Each train has two outbound and two return trips daily:

$$ Riyadh $-$ Jeddah:

$$ Outbound: $8$:$00$ am and $4$:$00$ pm$.$

$$ Return: $12$:$00$ pm and $8$:$00$ pm$.$

$$ Riyadh $-$ Dammam:

$$ Outbound: $7$:$00$ am and $3$:$00$ pm$.$

$$ Return: $11$:$00$ am and $7$:$00$ pm$.$

And so on for the rest of the lines.

Tables Details:

$1.$ Train Schedule:

TrainID TrainName Capacity Type

$1$ Riyadh $-$ Jeddah $200$ Fast

$2$ Riyadh $-$ Dammam $150$ Fast

$2.$ Employee Schedule:

EmployeeID Name Role TrainID

$1$ Ahmed Train Driver $1$

$2$ Mohammed Host $1$

$3.$ Customer Schedule:

CustomerID Name PhoneNumber Email

$1$ Fatima $0501234567$ fatima@email.com

$2$ Khalid $0559876543$ khalid@email.com

$4.$ Trip Schedule:

TripID TrainID DepartureTime ArrivalTime From To

$1108$:$0012$:$00$ Riyadh Jeddah

$2116$:$0020$:$00$ Riyadh Jeddah

$5.$ Ticket Schedule:

TicketID CustomerID TripID Price SeatNumber

$111330$ A$12$

$221330$ B$14$

Scenario uses:

$1.$ Query to calculate daily revenue:

$$ We calculate the total daily ticket prices.

$2.$ Query to determine completed trips:

$$ If all seats are sold.

$3.$ Report showing daily trip times for each train.