CS 336 -- Principles of Information and Data Management
Fall 2024
Requirements Specification for the Database Programming Project
Introduction
In this project, you will design and implement a relational database system to support the operations of an online railway booking system. You will use HTML for the user interface, MySQL for the database server, and Java, Javascript, and JDBC for connectivity between the user interface and database server.
You will have to install your own web server that will host your web application (Tomcat) as well as a MySQL server. Everything will be local. Many resources will be provided about how to do everything so don’t worry.
You are to work in teams of four.
Project Specification
As you probably know, there is a multitude of online railway booking systems on the web. The basic idea behind your on-line railway booking system is that it will allow customers to use the web to browse/search the contents of your database (at least that part you want the customer to see) and to make train reservations over the web. It should also allow users to query the database for available train schedules (direct or indirect) between a pair of cities for a given date and "approximate" time.
Your database system must be based on the specifications and requirements that follow.
1 System Users
The users of your system will be the customers (passengers) that use your system to make a reservation, customer representatives who provide customer-related services, and the site's manager/admin. You should assume that the computer knowledge of the users is limited, and thus your system must be easy to access and operate.
2 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
3 User-Level Functionality
3.1 Manager/Admin-Level Functionality
The manager should be able to:
• Add, Edit and Delete information for a customer representative
• Obtain a sales report per month (total revenue per month)
• Produce a list of reservations by transit line or by customer name
• Produce the total revenue generated by a particular transit line, or customer
• Determine which customer generated most total revenue
• Produce a list of the 5 most active transit lines (most reservations per month)
3.2 Customer-Representative-Level Functionality
Customer Representatives should be thought of as reservation agents and should be able to:
• Edit and Delete information for train schedules
• Reply to customer questions
• Produce a list of all customers who have reservations on a given transit line and date
• Produce a list of all schedules for a given station (both as origin and as destination) (e.g. list of train schedules that have New Brunswick as origin, or list of train schedules that have NB as destination)
3.3 Customer-Level Functionality
Customers should be thought of as online train ticket buyers and should be able to easily browse your online travel reservation system on the web, search for train schedules given an origin, destination and date, and make train reservations. In particular, they should be able to make the following types of reservations:
• One-Way
• Round-Trip
A customer should also be able to cancel an existing reservation and they should be able to retrieve the following information:
• A history of all current and past reservations they have made
• Travel itinerary for a given reservation
A customer should also be able to ask questions to the customer service, where the customer representatives should be able to reply (like a forum functionality).
4 User Access Control
Your database system should provide controlled access to the data by distinguishing between the different types of users: manager/admin, customer representatives, and customers.
• Customer Representatives should not be able to perform manager-level transactions.
• A customer should not be allowed access to other customers' account information, or to any employee information.
5 User Interface
HTML and its successors provide facilities for creating pop-up and pull-down menus, value lists, input/output forms, labels and customized reports. You should make use of all of these capabilities, and in the process come up with a system that caters to users with only limited computer knowledge. The information you provide to customers should look professional and inviting.
Implementation requirements
The system is to be accessed through a web interface, programmed in jsp, accessing a mySQL database. Details of the technical set up, will be covered in a video lecture that will be posted on Canvas. In order to be fair to everyone in the class, please do not use frameworks or other tools that make programming easier. (If we had time to teach everyone such a framework, we would.)