Write a full script to create a new SQL Server database called BusDriver4703 and

Question

Write a full script to create a new SQL Server database called BusDriver4703 and three tables as shown in the figure above, including BusModel, Bus, and Driver. Columns and primary key of each table and two relationships are also defined in the above figure.

In order to receive full credit, your script must be able to be executed as a whole to create the above database and tables with no errors. *It's important you include a GO and USE command right after the database is created and before your first CREATE TABLE statement. See p.361 for an example of script that creates the AP database and its tables.

For the purpose of this assignment, all relationships must be implemented such that (1) when a primary key value is updated, the foreign key that references that value is updated automatically, and (2) if a primary key value is referenced by a foreign key table, the row with this primary key value cannot be deleted, i.e., no action.

Column specifications are given below. All columns default to NULL unless a primary key or NOT NULL constraint is specified.

Driver table

DriverID: int

Name: varchar(60), not null

Age: int and must between 25 and 60, not null

BusNo: char(3)

Bus table

BusNo: char(3), must begin with a letter 'U'

Mileage: int, must be positive and below 350,000

Model: char(2)

BusModel table

ModelCode: char(2), values are limited to 'LG', 'MD', 'MN', 'SP', 'ST' only

ModelName: varchar(15)

TotalSeats: int, must be at least 20 but no more than 50

Explanation / Answer

create database BusDriver4703

USE BusDriver4703
GO
CREATE TABLE DRIVER
(
DriverID int PRIMARY KEY,
Name varchar(60) not null,
Age int NOT NULL,
BusNo char(3),
CHECK(AGE BETWEEN 25 AND 60)
);

USE BusDriver4703
GO
CREATE TABLE BUS
(
BusNo char(3) PRIMARY KEY,
Mileage int,
Model char(2),
CHECK(BusNo LIKE 'U%'),
CHECK(Mileage BETWEEN O AND 350,000)
);

USE BusDriver4703
GO
CREATE TABLE BUSMODEL
(
ModelCode char(2) PRIMARY KEY,
ModelName varchar(15),
TotalSeats int ,
CHECK(ModelCode IN('LG', 'MD', 'MN', 'SP', 'ST')),
CHECK (TotalSeats BETWEEN 20 AND 50)
);

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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