IMAT5103 - Railway Management System: Database Systems and Design
VerifiedAdded on 2023/04/20
|12
|699
|373
Project
AI Summary
This project report demonstrates the design and implementation of a database system for a railway management system. It covers the conceptual database design, including business rules for storing train, employee, station, and booking details. The logical database design outlines the tables (Customer, Station, Train, Booking) with primary and foreign keys. The report includes Oracle DBMS SQL implementation for creating tables, indexes, and populating data. Several SQL queries are provided to retrieve information such as counting bookings for a specific train or customer, selecting booking and customer details, summing the number of seats, and counting bookings at specific times. Desklib provides access to this and other solved assignments for students.

Running head: DATABASE SYSTSEM AND DESIGN
Database systems and design
Name of the Student:
Name of the University:
Author Note
Database systems and design
Name of the Student:
Name of the University:
Author Note
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser

1
DATABASE SYSTEM AND DESIGN
Stage 1: Scenario and Conceptual Database Design Task
1.1: Selection of the case upon which the database design and implementation is to be
based
The demonstration of a railway management system is provided in this report. Storing
the data of a railway system is very important and one such arbitrary railway system has been
considered for this report. The train details are to be stored in the system along with the
stations details. This would help the customers in having the details of the trains and the
station so that they would be able to select their desired location. In addition to this the details
of the customers are also to be stored in the system. The bookings would done for the system
are also to be recorded in the system so that the details of the railway system are maintained
in the database.
DATABASE SYSTEM AND DESIGN
Stage 1: Scenario and Conceptual Database Design Task
1.1: Selection of the case upon which the database design and implementation is to be
based
The demonstration of a railway management system is provided in this report. Storing
the data of a railway system is very important and one such arbitrary railway system has been
considered for this report. The train details are to be stored in the system along with the
stations details. This would help the customers in having the details of the trains and the
station so that they would be able to select their desired location. In addition to this the details
of the customers are also to be stored in the system. The bookings would done for the system
are also to be recorded in the system so that the details of the railway system are maintained
in the database.

2
DATABASE SYSTEM AND DESIGN
Task 1.2: Provide a conceptual database design for your scenario & the list of enterprise
rules being modelled
Business Rules
The set of rules defined below are required for the development of the database of the
railway management system:
The details of the train are to be stored in the system including the Train Number.
DATABASE SYSTEM AND DESIGN
Task 1.2: Provide a conceptual database design for your scenario & the list of enterprise
rules being modelled
Business Rules
The set of rules defined below are required for the development of the database of the
railway management system:
The details of the train are to be stored in the system including the Train Number.
⊘ This is a preview!⊘
Do you want full access?
Subscribe today to unlock all pages.

Trusted by 1+ million students worldwide

3
DATABASE SYSTEM AND DESIGN
The details of the Employees are to be stored in the database.
The details of the station are stored in the system identified by their unique id.
The booking details are to store in the database.
Stage 2: Logical Database Design and Oracle SQL Implementation/querying
Task 2.1: Provide a Logical Database Design for your scenario
Customer (CustomerID (pk), CustomerName)
Station (StationID (pk), StationName)
Train (TrainID (pk), TrainName)
Booking (BookingID (pk), TrainID (fk), SStation (fk), EStation (fk), CustomerID (fk),
NumberOfSeats, Time)
Task 2.2 Create the tables using Oracle DBMS
Database tables
Customer
Station
DATABASE SYSTEM AND DESIGN
The details of the Employees are to be stored in the database.
The details of the station are stored in the system identified by their unique id.
The booking details are to store in the database.
Stage 2: Logical Database Design and Oracle SQL Implementation/querying
Task 2.1: Provide a Logical Database Design for your scenario
Customer (CustomerID (pk), CustomerName)
Station (StationID (pk), StationName)
Train (TrainID (pk), TrainName)
Booking (BookingID (pk), TrainID (fk), SStation (fk), EStation (fk), CustomerID (fk),
NumberOfSeats, Time)
Task 2.2 Create the tables using Oracle DBMS
Database tables
Customer
Station
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser

4
DATABASE SYSTEM AND DESIGN
Train
Booking
Task 2.3: Create the four most useful indexes on your tables
Customer
DATABASE SYSTEM AND DESIGN
Train
Booking
Task 2.3: Create the four most useful indexes on your tables
Customer

5
DATABASE SYSTEM AND DESIGN
Station
Train
Booking
Task 2.4: Data Population
Customers
DATABASE SYSTEM AND DESIGN
Station
Train
Booking
Task 2.4: Data Population
Customers
⊘ This is a preview!⊘
Do you want full access?
Subscribe today to unlock all pages.

Trusted by 1+ million students worldwide

6
DATABASE SYSTEM AND DESIGN
Station
Trains
DATABASE SYSTEM AND DESIGN
Station
Trains
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser

7
DATABASE SYSTEM AND DESIGN
Booking
Task 2.5: SQL Query writing
Query 1
Select COUNT(P17243905BOOKING.BOOKINGID)
From P17243905BOOKING
DATABASE SYSTEM AND DESIGN
Booking
Task 2.5: SQL Query writing
Query 1
Select COUNT(P17243905BOOKING.BOOKINGID)
From P17243905BOOKING

8
DATABASE SYSTEM AND DESIGN
Inner Join P17243905TRAIN On P17243905TRAIN.TRAINNUMBER =
P17243905BOOKING.TRAINID
Where P17243905TRAIN.TRAINNAME = 'Express';
Query 2
Select COUNT(P17243905BOOKING.BOOKINGID)
From P17243905BOOKING
Inner Join P17243905CUSTOMER On P17243905CUSTOMER.CUSTOMERID =
P17243905BOOKING.CUSTOMERID
Where P17243905CUSTOMER.CUSTOMERNAME = 'Jerome Prince';
Query 3
Select P17243905BOOKING.*, P17243905CUSTOMER.*
From P17243905BOOKING
Inner Join P17243905CUSTOMER On P17243905CUSTOMER.CUSTOMERID =
P17243905BOOKING.CUSTOMERID
Where P17243905CUSTOMER.CUSTOMERNAME = 'Jerome Prince';
DATABASE SYSTEM AND DESIGN
Inner Join P17243905TRAIN On P17243905TRAIN.TRAINNUMBER =
P17243905BOOKING.TRAINID
Where P17243905TRAIN.TRAINNAME = 'Express';
Query 2
Select COUNT(P17243905BOOKING.BOOKINGID)
From P17243905BOOKING
Inner Join P17243905CUSTOMER On P17243905CUSTOMER.CUSTOMERID =
P17243905BOOKING.CUSTOMERID
Where P17243905CUSTOMER.CUSTOMERNAME = 'Jerome Prince';
Query 3
Select P17243905BOOKING.*, P17243905CUSTOMER.*
From P17243905BOOKING
Inner Join P17243905CUSTOMER On P17243905CUSTOMER.CUSTOMERID =
P17243905BOOKING.CUSTOMERID
Where P17243905CUSTOMER.CUSTOMERNAME = 'Jerome Prince';
⊘ This is a preview!⊘
Do you want full access?
Subscribe today to unlock all pages.

Trusted by 1+ million students worldwide

9
DATABASE SYSTEM AND DESIGN
Query 4
Select SUM(P17243905BOOKING.NUMBEROFSEATS)
From P17243905BOOKING;
Query 5
Select COUNT(P17243905BOOKING.BOOKINGID)
From P17243905BOOKING
Where P17243905BOOKING.TIME = '02:00 PM';
Query 6
Select COUNT(P17243905BOOKING.BOOKINGID)
From P17243905BOOKING
Where P17243905BOOKING.TIME = '4:00 PM';
DATABASE SYSTEM AND DESIGN
Query 4
Select SUM(P17243905BOOKING.NUMBEROFSEATS)
From P17243905BOOKING;
Query 5
Select COUNT(P17243905BOOKING.BOOKINGID)
From P17243905BOOKING
Where P17243905BOOKING.TIME = '02:00 PM';
Query 6
Select COUNT(P17243905BOOKING.BOOKINGID)
From P17243905BOOKING
Where P17243905BOOKING.TIME = '4:00 PM';
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser

10
DATABASE SYSTEM AND DESIGN
DATABASE SYSTEM AND DESIGN

11
DATABASE SYSTEM AND DESIGN
Bibliography
Lee, S., Tewolde, G. and Kwon, J., 2014, March. Design and implementation of vehicle
tracking system using GPS/GSM/GPRS technology and smartphone application. In Internet
of Things (WF-IoT), 2014 IEEE World Forum on (pp. 353-358). IEEE.
Pfaff, B., Pettit, J., Koponen, T., Jackson, E., Zhou, A., Rajahalme, J., Gross, J., Wang, A.,
Stringer, J., Shelar, P. and Amidon, K., 2015. The design and implementation of open
vswitch. In 12th {USENIX} Symposium on Networked Systems Design and Implementation
({NSDI} 15) (pp. 117-130).
DATABASE SYSTEM AND DESIGN
Bibliography
Lee, S., Tewolde, G. and Kwon, J., 2014, March. Design and implementation of vehicle
tracking system using GPS/GSM/GPRS technology and smartphone application. In Internet
of Things (WF-IoT), 2014 IEEE World Forum on (pp. 353-358). IEEE.
Pfaff, B., Pettit, J., Koponen, T., Jackson, E., Zhou, A., Rajahalme, J., Gross, J., Wang, A.,
Stringer, J., Shelar, P. and Amidon, K., 2015. The design and implementation of open
vswitch. In 12th {USENIX} Symposium on Networked Systems Design and Implementation
({NSDI} 15) (pp. 117-130).
⊘ This is a preview!⊘
Do you want full access?
Subscribe today to unlock all pages.

Trusted by 1+ million students worldwide
1 out of 12
Related Documents

Your All-in-One AI-Powered Toolkit for Academic Success.
+13062052269
info@desklib.com
Available 24*7 on WhatsApp / Email
Unlock your academic potential
Copyright © 2020–2025 A2Z Services. All Rights Reserved. Developed and managed by ZUCOL.