You have been asked to design a database management system for a commuter rail s

Question

You have been asked to design a database management system for a commuter rail system. The entities and attributes for the database management system are provided by the customer as following:

Train

Train No

Typical Crew

Engine

Engine No

Manufacturer

Type

Maintenance Intervals (there are more than 1 size of these)

Purchase Date

Manufacture Date

Years In Service

Car

Car No

Manufacturer

Capacity

Seat Types (there can be more than 1 of these)

Purchase Date

Manufacture Date

Years In Service

Crew

Crew Member ID

Name

Start Date

Years Of Service

You have also been provided with some additional information:

A train must have at least one engine and one car. It may also have more than 1 engine and more than 1 car.

A train must have at least one crew member and may have many crew members.

Management would like to keep track of the dates that an engine, crew member and car are assigned to a train.

An engine may be assigned to 1 or more trains, but not on the same dates

A car may be assigned to 1 or more trains, but not on the same dates

A crew member may be assigned to more than 1 train in the same day.

Explanation / Answer

We need to create the tables as like below to design database management system for above request

create table train (
Train_No           varchar2(100)    not null
,Typical_Crew           varchar2(100)    not null
,Typical_Crew_Assign_Date   Date             not null  
,Engine_No           varchar2(100)    not null
,Engine_Assign_Date       Date        not null
,Car_No               varchar2(100)    not null
,Car_Assign_Date       Date            not null
,CONSTRAINT engine_uniue UNIQUE (Engine_No,Engine_Assign_Date) // it satsfies an engine may be assigned to 1 or more trains, but not on the same dates
,CONSTRAINT car_uniue UNIQUE (car_No,car_Assign_Date) //it satisfies car may be assigned to 1 or more trains, but not on the same dates
);

create table Engine (
Engine_No       varchar2(100) not null
,Manufacturer       varchar2(100)
,Type           varchar2(100)
,Maintenance_Intervals    varchar2(100)
,Purchase_Date       date
,Manufacture_Date   date
,Years_In_Service   number
);

create table Car (
Car_No           varchar2(100) not null
,Manufacturer       varchar2(100)
,Capacity       varchar2(100)
,Seat_Types        varchar2(100)
,Purchase_Date       date
,Manufacture_Date   date
Years_In_Service   number
);

create table Crew (
Crew_Member_ID       varchar2(100) not null
,Name           varchar2(100)
,Start_Date       date
,Years_Of_Service   number
);

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.