BusDriver.png Write a full script to create a new SQL Server database called Bus

Question

BusDriver.png 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. The script file must be saved and submitted as Ass10_BusDriver4703.sql. 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

Driver B river BusModel ROM 8 ModelCode Name Age ModelName BusNo TotalSeats Bus B BusNo Mileage Model

Explanation / Answer

SQL script to to create database ‘BusDriver4703’ and tables namely ‘BusModel’, ‘Bus’, ‘Driver’.

--Creates database BusDriver4703

CREATE DATABASE BusDriver4703;

GO

--Use the created database

USE BusDriver4703;

GO

--Creating table BusModel with ModelCode declared as primary key and applying constrain on model     --code and totalseats.

CREATE TABLE BusModel

(

               ModelCode                        CHAR(2) PRIMARY KEY,

               ModelName                      VARCHAR(15),

               TotalSeats                          INT,

               CONSTRAINT check_model CHECK (ModelCode IN (‘LG’, ‘MD’, ‘MN’, ‘SP’, ‘ST’)),

               CONSTRAINT check_seat CHECK (TotalSeat BETWEEN 20 AND 50)

);

--Creating table Bus with BusNo as primary key and foreign key Model which references BusModel table. --Two check constraints are created one on BusNo and another on Mileage

CREATE TABLE Bus

(

               BusNo                                 CHAR(3) PRIMARY KEY,

               Mileage                              INT,

               Model                                 CHAR(2),

               FOREIGN KEY (Model) REFERENCES BusModel(ModelCode)

ON DELETE SET NULL

ON UPDATE CASCADE,

               CONSTRAINT check_bus CHECK (BusNo like ‘U%’),

               CONSTRAINT check_mileage (Mileage BETWEEN 1 AND 350000)

);

--Creating table Driver with DriverID as primary key and BusNo as foreign key which references BusNo    --of Bus table. One check constraint is created on Age.

CREATE TABLE Driver

(

               DriverID                              INT PRIMARY KEY,

               Name                                  VARCHAR(60) NOT NULL,

               Age                                      INT NOT NULL,

               BusNo                                 CHAR(3),

               FOREIGN KEY (BusNo) REFERENCES Bus (BusNo)

ON UPDATE CASCADE

ON DELETE SET NULL,

               CONSTRAINT check_age CHECK (Age BETWEEN 25 AND 60)

);

Related Questions

Navigate

• Browse (All)
• Browse B
• Subjects

---

---

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