This document provides an overview of database systems and design. It covers the selection of a case for database design, conceptual database design, enterprise rules, logical database design, Oracle SQL implementation and querying, and data population. The document also includes SQL query examples and a bibliography.
Contribute Materials
Your contribution can guide someone’s learning journey. Share your
documents today.
Running head: DATABASE SYSTSEM AND DESIGN Database systems and design Name of the Student: Name of the University: Author Note
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
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.
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.
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
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
8 DATABASE SYSTEM AND DESIGN InnerJoinP17243905TRAINOnP17243905TRAIN.TRAINNUMBER= P17243905BOOKING.TRAINID Where P17243905TRAIN.TRAINNAME = 'Express'; Query 2 Select COUNT(P17243905BOOKING.BOOKINGID) From P17243905BOOKING InnerJoinP17243905CUSTOMEROnP17243905CUSTOMER.CUSTOMERID= P17243905BOOKING.CUSTOMERID Where P17243905CUSTOMER.CUSTOMERNAME = 'Jerome Prince'; Query 3 Select P17243905BOOKING.*, P17243905CUSTOMER.* From P17243905BOOKING InnerJoinP17243905CUSTOMEROnP17243905CUSTOMER.CUSTOMERID= P17243905BOOKING.CUSTOMERID Where P17243905CUSTOMER.CUSTOMERNAME = 'Jerome Prince';
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';
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
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).