Here Is a database structure of a simple Train route management system: Train Ro
ID: 645774 • Letter: H
Question
Here Is a database structure of a simple Train route management system:
Train Route Database:
Route_ID, Fare, Stop_ID, StopName, Schedule_ID, Departure_Date, Arrival_Date, Train_ID, Train_Capacity, Driver_ID, DriverName.
Part 1: Deliverables: ERD, Tables.
Convert the Train Route Database to an equivalent collection of tables that are in third normal form,and Draw the appropriate Entity Relationship Diagram (ERD).
The Entities in your ERD must include the corresponding attributes.
The tables must have names and column names.
Hints: - Some of the tables you would consider: Driver, Train, Schedule, Route, Stop.
A Train can be associated with one or many train drivers.
A driver can drive one train.
A schedule is handled by a Train.
A Train can have many schedules.
A route has multiple train schedules.
A route has many stops.
A stop can belong to many routes.
Part2: Deliverables:
All Code for CREATING tables, insert statements and queries.
1) Using your Oracle Accounts(SQL), Create all tables from Parti.
2) Insert 10 drivers to your Driver table.
3) Insert 5 trains to you Train table.
4) Insert 10 schedules to your Schedule table.
5) Insert 5 routs to your Route table.
6) Insert 10 stops to your Stop table.
7) Using a query, list all drivers in your Driver table.
8) Using a query, list all trains in your Train table.
9) Using a query, list all routes in your Route table.
10) Using a query, list all stops in you Stop table.
11) Using a query, list all schedules in your Schedule table
12) Using a query, list all trains that are driven by the first Driver from question 7.
13) Using a query, find how many trains are driven by the second from question 7.
14) Using a query, find how many trains you have in your Train table.
15) Using a query, list all stops that belong to your first route from question 9.
16) Using a query, find how many stops belong to your second route from question 9.
17) Using a query, find the train that the first driver from question 7 is driving.
18) Using a query, find all drivers associated with Train 2 from question8.
19) Using a query, find all schedules associated with train 3 from question8.
20) Using a query, find how many schedules associated with train 3 from question8.
Explanation / Answer
TRAINROUTE DATABASE HAVE 5 TABLE:DRIVER01,TRAIN01,SCHEDULE01,ROUTE01,STOP01.
///////////////////////////////////////////////////////////////////////////
SQL> CREATE TABLE DRIVER01
(
DRIVERID NUMBER(20),
DRIVERNAME VARCHAR2(20));
Table Created
SQL> INSERT INTO DRIVER01(DRIVERID,DRIVERNAME)
VALUES(99,ANAND);
1 row created
SQL> INSERT INTO DRIVER01(DRIVERID,DRIVERNAME)
VALUES(501,BALA);
1 row created
:
:
:
:
create 10 rows.
SQL> CREATE TABLE TRAIN01
(
TRAINID NUMBER(20),
TRAINCAPACITY NUMBER(20)
DRIVERID NUMBER(20));
Table Created
SQL> INSERT INTO TRAIN01(TRAINID,TRAINCAPACITY,DRIVERID)
VALUES(666,1000,99);
1 row created
SQL> INSERT INTO TRAIN01(ROUTEID,FARE,STOPID,STOPNAME,SCHEDULEID,DEPARTUREDATE,ARRIVALDATE,TRAINID,TRAINCAPACITY,DRIVERID,DRIVERNAME)
VALUES(1051,5555,501);
1 row created
:
:
:
:
create 5 rows.
SQL> CREATE TABLE SCHEDULE01
(
SCHEDULEID NUMBER(20),
DEPARTUREDATE DATE,
ARRIVALDATE DATE);
Table Created
SQL> INSERT INTO SCHEDULE01(SCHEDULEID,DEPARTUREDATE,ARRIVALDATE)
VALUES(101,'26-03-2015','27-03-2015');
1 row created
SQL> INSERT INTO SCHEDULE01(SCHEDULEID,DEPARTUREDATE,ARRIVALDATE)
VALUES(901,'27-03-2015','29-03-2015');
1 row created
:
:
:
:
create 10 rows.
SQL> CREATE TABLE ROUTE01
(
ROUTEID NUMBER(20),
FARE NUMBER(10));
Table Created
SQL> INSERT INTO ROUTE01(ROUTEID,FARE)
VALUES(101,99);
1 row created
SQL> INSERT INTO ROUTE01(ROUTEID,FARE)
VALUES(102,450);
1 row created
:
:
:
:
create 5 rows.
SQL> CREATE TABLE STOP01
(
STOPID NUMBER(20),
STOPNAME VARCHAR2(20));
Table Created
SQL> INSERT INTO STOP01(STOPID,STOPNAME)
VALUES(01,'TRIVANDRAM-CHENNAI');
1 row created
SQL> INSERT INTO STOP01(STOPID,STOPNAME)
VALUES(49,'CHENNAI-MUMBAI');
1 row created
:
:
:
:
create 10 rows.
SQL> SELECT *FROM DRIVER01
SQL> SELECT *FROM TRAIN01
SQL> SELECT *FROM ROUTE01
SQL> SELECT *FROM STOP01
SQL> SELECT *FROM SCHEDULE01
SQL> SELECT TRAINID FROM TRAIN01 WHERE DRIVERID='99'
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.