Question

Draw an E-R diagram pls

Required Data
The data items required for the train reservation database can be classified into six categories:
trains, stations, train schedules, reservations, customers and employees.
The above classification does not imply any particular table arrangement. You are responsible
for arranging the data items into tables, determining the relationships among tables and identifying
the key attributes. Finally, you should specify and enforce integrity constraints on the data,
including referential integrity constraints.
You will first create an E-R diagram of your online railway reservation system before developing
your relational model. Details of this assignment will be forthcoming.
2.1 Train Data
Your system should store information about trains. Every train has a unique four-digit number id.
2.1 Station Data
A station has a unique id, a name, the name of the city this station is, and the state.
2.3 Train Schedule Data
The basic information related to this data is:
1. Transit line name (e.g. Northeast Corridor)
2. Train
3. Origin
4. Destination
5. Stops
6. Departure datetime
7. Arrival datetime
8. Travel time
9. Fare
All trains perform a specific route based on a unique transit line throughout the day. Depending
on the transit line, the trains start from a specific origin and arrive at a specific destination station.
For example, the Northeast Corridor transit line has origin the New York Penn station and
destination the Trenton station. Every transit line starts from its origin on a specific time and arrives
at its destination at another specific time. It also has a number of stops, with each stop having its
own arrival and departure times. For example, the Northeast Corridor line with train #3806 starts
at 3:48am from Trenton, and arrives at NY Penn Station at 5:21am with a total travel time of 93
minutes. While performing this route it stops to 10 stations which are Princeton, New Brunswick,
Edison, Metuchen, …, etc. and each of these stations have their own departure/arrival time. A
route has also an associated fare which depends on the type of the route (if it is one way, or round
trip), as well as fare discounts for children, seniors and disabled.. You can assume that every transit
line has a fixed fare from origin to destination and then the individual stops have a
fare=fare/number of stops in this line. For example, the Northeast Corridor line (which goes from
Trenton to NY Penn) has a fare of $50. Then if we assume that it has 10 stops in between (e.g.
Trenton, Princeton, New Brunswick, Edison, Metuchen, etc.), the fare between two consecutive
stops would be $50/10 = $5, between two stops (e.g. NB-> Metuchen) $10 etc. Children have a
discount of 25%, seniors 35% and disabled 50%. Round trip tickets have double fare.
2.4 Reservation Data
This category of data should include the following items:
1. Reservation Number
2. Date
3. Passenger
4. Total Fare
A reservation has a unique number and is for a single passenger. Each reservation has an
associated origin station, destination station and transit line name (along with its train number),
departure date and time. A reservation also has the following attributes: total fare and the date
when reservation was made. For example, Mr. John Smith makes a reservation on 7/25/2020 for
the Northeast Corridor train #2345 that goes from New Brunswick station on 8/10/2020 11:00am
to New York Penn Station 8/10/2020 11:59am and costs $80.
2.5 Customer Data
The items required for this category include:
1. Last Name
2. First Name
3. E-mail Address
4. Username
5. Password
A customer may partake in any number of reservation transactions. Associated with each account
is a reservation portfolio, indicating which reservations are held in that account (past and future).
2.6 Employee Data
This category of data should include the following:
1. Social Security #
2. Last Name
3. First Name
4. Username
5. Password

Answer 1

Have made a few assumptions there as in the case of Passenger info and employee info.

Considering the employee as someone from the tech team.

Hope this draft version gives a rough idea.