Question: /* use these commands if you want to replace an existing version of Transit database USE master GO DROP DATABASE transit GO / / Homework

/* use these commands if you want to replace an existing version of Transit database

USE master

DROP DATABASE transit

Homework #1

Create a database with the following tables, correctly identifying the data type for each field and

implementing constraints as described. Primary keys and relationships will be added in the next assignment.

CREATE DATABASE Transit

USE Transit

Operators(Seniority Number, First Name, Last Name, Hire Date). All fields are required.

Seniority number is four digits. Hire date must be less than tomorrow.

CREATE TABLE Operators

(

SeniorityNumber char(4) NOT NULL

CONSTRAINT ck_Operators_Seniority

CHECK (SeniorityNumber LIKE '[0-9][0-9][0-9][0-9]'),

FirstName varchar(25) NOT NULL,

LastName varchar(35) NOT NULL,

HireDate smalldatetime

CONSTRAINT ck_Operators_HireDate CHECK (HireDate <=Getdate())

)

Trips(Route Number, Start Location, Start Time, End Location, End Time, Effective Date)

All fields are required. End Time must be greater than Start Time. Effective Date must be

on or after January 1, 2000.

CREATE TABLE Trips

(

RouteNumber varchar(4) NOT NULL,

StartLocation varchar(50) NOT NULL,

StartTime time NOT NULL,

EndLocation varchar(50) NOT NULL,

EndTime time NOT NULL,

EffectiveDate smalldatetime NOT NULL

CHECK (EffectiveDate >= cast('1/1/2000' as smalldatetime)),

CONSTRAINT ck_Trips_StartEnd CHECK (EndTime > StartTime)

)

Vehicle(Manufacturer, Model, Model Year, Purchase Date). Default value for Manufacturer is

'Gillig'; default value for Model Year is the current year. Model year must be less than or equal

to the current year.

CREATE TABLE Vehicles

(

Manufacturer varchar(50)

DEFAULT 'Gillig',

Model varchar(50),

ModelYear int

DEFAULT DatePart(yyyy,GetDate())

CHECK (ModelYear <= DatePart(yyyy,GetDate())),

alternative using CHAR(4)

ModelYear char(4)

DEFAULT cast(DatePart(yyyy,GetDate()) AS char(4))

CHECK (cast(ModelYear as int) <= Datepart(yyyy,GetDate())),

PurchaseDate smalldatetime

)

Create a database with the following tables, correctly identifying the data type for each field and

implementing constraints as described. Primary keys and relationships will be added in the next assignment.

/* homework #2 */

Use the ALTER command to add primary keys to the Operators,

Vehicles and Trips tables created in Homework #1. The primary

key for each table should be based on an identity field.

ALTER TABLE operators

ADD OperatorID int IDENTITY --Primary Key

ALTER TABLE Operators

ADD CONSTRAINT pkOperators Primary key (OperatorID)

ALTER TABLE Vehicles

ADD VehicleID int IDENTITY Primary Key

ALTER TABLE Trips

ADD TripID int IDENTITY Primary key

Run Table: Each operator can have 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.

CREATE TABLE Runs

(

RunID int IDENTITY NOT NULL Primary Key,

OperatorID int NOT NULL REFERENCES Operators,

BidDate date NOT NULL

CONSTRAINT ckRunBidDate CHECK

(biddate <= dateadd(mm,6,getdate())) --getdate() + 180

)

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 (there are two foreign keys).

All fields are required.

CREATE TABLE Schedules

(

ScheduleID int IDENTITY Primary Key,

RunID int NOT NULL,

VehicleID int NOT NULL,

CONSTRAINT fk_Schedules_Runs FOREIGN KEY (RunID)

REFERENCES Runs(RunID),

CONSTRAINT fk_Schedules_Vehicles FOREIGN KEY (VehicleID)

REFERENCES Vehicles

)

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.

ALTER TABLE Trips

ADD ScheduleID int NULL REFERENCES Schedules

ALTER TABLE Trips

ADD CONSTRAINT ckTripsStartTime CHECK

(StartTime BETWEEN cast('05:30 am' as time) AND cast('11:50 pm' as time))

--(StartTime >= '5:30 am' and StartTime <= '11:50 pm')

/* homework #3 - editing data */

USE Transit

-- insert operators

INSERT INTO Operators(SeniorityNumber, FirstName, LastName, HireDate)

VALUES('0001', 'David', 'Letterman', '1/1/01')

INSERT INTO Operators(SeniorityNumber, FirstName, LastName, HireDate)

VALUES('0002', 'Tony', 'Bennet', '1/10/01')

INSERT INTO Operators(SeniorityNumber, FirstName, LastName, HireDate)

VALUES('0003', 'Shirley', 'Temple','2/15/02')

INSERT INTO Operators(SeniorityNumber, FirstName, LastName, HireDate)

VALUES('0004', 'John', 'Adams', '6/15/02')

INSERT INTO Operators(SeniorityNumber, FirstName, LastName, HireDate)

VALUES('0005', 'Tanya', 'Tucker', '8/19/03')

INSERT INTO Operators(SeniorityNumber, FirstName, LastName, HireDate)

VALUES('0006', 'Peter', 'Parker', '5/5/04')

-- insert vehicles

INSERT INTO Vehicles(Manufacturer, Model, ModelYear, PurchaseDate)

VALUES('Gillig', '40 foot', '1998', '3/20/98')

INSERT INTO Vehicles(Manufacturer, Model, ModelYear, PurchaseDate)

VALUES('Gillig', '40 foot', '1998', '3/20/98')

INSERT INTO Vehicles(Manufacturer, Model, ModelYear, PurchaseDate)

VALUES('Gillig', '40 foot', '1998', '3/20/98')

INSERT INTO Vehicles(Manufacturer, Model, ModelYear, PurchaseDate)

VALUES('Gillig', '40 foot', '1998', '3/20/98')

INSERT INTO Vehicles(Manufacturer, Model, ModelYear, PurchaseDate)

VALUES('Gillig', 'Phantom', '1992', '11/15/02')

INSERT INTO Vehicles(Manufacturer, Model, ModelYear, PurchaseDate)

VALUES('Gillig', 'Phantom', '1992', '11/15/02')

-- insert runs

INSERT INTO Runs(OperatorID, BidDate)

VALUES(1, '9/1/04')

INSERT INTO Runs(OperatorID, BidDate)

VALUES(2, '9/1/04')

INSERT INTO Runs(OperatorID, BidDate)

VALUES(3, '9/1/04')

INSERT INTO Runs(OperatorID, BidDate)

VALUES(4, '9/1/04')

INSERT INTO Runs(OperatorID, BidDate)

VALUES(5, '9/1/04')

INSERT INTO Runs(OperatorID, BidDate)

VALUES(1, '1/1/05')

INSERT INTO Runs(OperatorID, BidDate)

VALUES(2, '1/1/05')

INSERT INTO Runs(OperatorID, BidDate)

VALUES(3, '1/1/05')

INSERT INTO Runs(OperatorID, BidDate)

VALUES(4, '1/1/05')

INSERT INTO Runs(OperatorID, BidDate)

VALUES(5, '1/1/05')

INSERT INTO Runs(OperatorID, BidDate)

VALUES(6, '1/1/05')

-- insert schedules

INSERT INTO Schedules(RunID, VehicleID)

VALUES(1, 6)

INSERT INTO Schedules(RunID, VehicleID)

VALUES(2, 5)

INSERT INTO Schedules(RunID, VehicleID)

VALUES(3, 2)

INSERT INTO Schedules(RunID, VehicleID)

VALUES(4, 3)

INSERT INTO Schedules(RunID, VehicleID)

VALUES(5, 4)

INSERT INTO Schedules(RunID, VehicleID)

VALUES(6, 1)

INSERT INTO Schedules(RunID, VehicleID)

VALUES(7, 2)

INSERT INTO Schedules(RunID, VehicleID)

VALUES(8, 3)

INSERT INTO Schedules(RunID, VehicleID)

VALUES(9, 4)

INSERT INTO Schedules(RunID, VehicleID)

VALUES(10, 5)

INSERT INTO Schedules(RunID, VehicleID)

VALUES(11, 6)

-- insert trips