SQL practice help ! Implementing Relationships Use the ALTER command to add prim

Question

SQL practice help !

Implementing Relationships

Use the ALTER command to add primary keys to the Operators, Vehicles and Trips tables. The primary key for each table should be based on an identity field.

Create two new tables that are used to create relationships with the existing tables (use the diagram below to illustrate the relationships):

* Run Table: One operator has many work assignments, called runs. Each run needs an identifier, and belongs to an operator; use an identity field for the run identifier, and include a field to serve as a foreign key for the operator table. All fields are required. The Bid Date field cannot be more than six months in the future.

* Schedule Table: A schedule identifies the vehicle that will perform each trip, with each schedule entry belonging to a run. Create a table that has an identity field as a primary key, and a foreign key to identify the run the schedule belongs to, and the vehicle performing that schedule. All fields are required.

Modify the Trip table: Add a field to identify the schedule that the trip belongs to; this field is not required. Add a new constraint that Start time must fall between 5:30am and 11:50pm.

Explanation / Answer

====================================================================================================

## Below are the alter query to add primary key for Operators, Vehicles and Trips table.

ALTER table Operators

ADD COLUMN operator_id char(12) not null primary key;

ALTER table Vehicles

ADD COLUMN vehicle_id char(12) not null primary key;

ALTER table Trips

ADD COLUMN trip_id char(12) not null primary key;

==================================================================================================

## Below is the create query for Run table

## This table has foreign key relationship with Operators table and is join with operator_id.

## This table has bid_date column with will accept only next 6 months date

create table Run (

run_id char(12) not null primary key,

operator_id char(12) not null

REFERENCES Operators(operator_id),

bid_date DATETIME CHECK(bid_date >= DATEADD(MONTH, 6, current_timestamp))

);

==================================================================================================

## Below is the create query for Schedule table

## This table has foreign key relationship with Vehicles table and is join with vehicle_id.

create table Schedules (

schedule_id char(12) not null primary key,

vehicle_id char(12) not null

REFERENCES Vehicles(vehicle_id)

);

==================================================================================================

## Below is the Alter query for Trips table

## This table has foreign key relationship with Schedules table and is join with schedule_id.

## This table has start_time column which accept time between 5:30 am till 11:50 pm.

ALTER table Trips

ADD COLUMN schedule_id char(12)

REFERENCES Schedule(schedule_id),

ADD COLUMN start_time DATETIME CHECK( start_time >= "05:30:00" or start_time <="23:50:00") ;

=======================================================================================================

Note: Help us improve our solution quality.Please rate this solution by pressing like button and if you have any queries please comment in the comment section

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

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