1. Construct a clean and concise ER diagram for the RRS database. List your assumptions and clearly indicate the cardinality mappings.

Part 2: SQL Queries

 

Write SQL queries:

1. Input a passenger's last name and first name and retrieve all trains they are booked on.
2. Input the Day and list the passengers travelling on that day with confirmed tickets.
3. User input the age of the passenger (50 to 60) and display the train information (Train Number, Train Name, Source and Destination) and passenger information (Name, Address, Category, ticket status) of passengers who are between the ages of 50 to 60.
4. List train name, day and number of passenger on that train.
5. Enter a train name and retrieve all the passengers with confirmed status traveling in that train.
6. List passengers that are waitlisted including the name of the train.
7. List passengers that have '605' phone area code in descending order.
8. List name of passengers that are traveling on Thursdays in ascending order.
Scenario:

You are hired by a Railway Company, you are asked to design a system which provides information to passengers about the trains available on the bases of source and destination, other information like status of the train, status of passenger ticket, how many seats are available etc.

Suppose you are given the following requirements for a simple database for the Railway Reservation System:

This system helps to maintain the records of different trains, the train's status, and passengers.

• Each Train has Train number, train name, source, destination, fair of Premium Ticket, fair for general ticket and weekdays on which train is available.
• Train status includes date information for which the ticket was booked, total number of seats available and number of seats already occupied.
• Passenger has name, age, address of the passenger, status of reservation (if confirmed or waitlist), category of the ticket booked.

 

Note: As the system is very large and is not feasible to develop therefore there are some assumptions that need to be considered, for example:

• Only two categories of tickets are available: Premium and General Ticket
• The total number of tickets can be booked in each category (Premium and General) is 10
• Number of tickets in waiting list is 2
• Total Number of trains are 5
• Any stops made by a train before its destination and their bookings are not considered.

Answer rating: 100% (QA)

There are multiple questions being asked here involving both ER diagram construction and SQL query writing for a Railway Reservation System RRS 1 Cons... View the full answer