Use the SQL Server Management Studio to complete this lab. a. Create a SQL script file...

  

Transcribed Image Text:

Use the SQL Server Management Studio to complete this lab. a. Create a SQL script file named Car Inspection_DB_Script Your_initials.SQL (John Doe would name it Car Inspeciton_DB_Script JD.SQL). When executed this script is to create a database named Car Inspection Your Initials that contains the objects described in this document. 2. Upload and submit the Car Inspection_DB_Script Your Initials.SQL file before the due date. Car Inspection_Your_Initials Design Specifications Facility FacilityIDNo: integer IDENTITY Name: varchar(40) NOT NULL Street: varchar(40) NOT NULL City: varchar(30) NOT NULL County: varchar(30) NOT NULL Zip: varchar(9) NOT NULL Vehicle VIN: char(17) NOT NULL PlateNo: varchar(10) NOT NULL State: char(2) NOT NULL Make: varchar(18) NULL YearManufactured: integer NOT NULL Inspection InspectionNo: integer IDENTITY InspectDate: datetime NOT NULL OdometerReading: decimal(9.2) NOT NULL Issued CertNo:char(12) NULL InspectorCertNo: char(12) NULL VIN: char(17) NOT NULL (FK) FacilityIDNo: integer NOT NULL (FK) ProblemMaster Ok ProblemCode: char(4) NOT NULL Description: varchar(80) NOT NULL Rejectable: char(1) NOT NULL ActionToTake: varchar(80) NULL InspectionProblem Inspection ProblemNo: integer IDENTITY Reject char(1) NOT NULL Tire Pulled: char(4) NULL RepairOrderNo: integer NULL InspectionNo: integer NOT NULL (FK) ProblemCode: char(4) NOT NULL (FK) Additional Design Specifications Facility table FacilityIDNo- In addition to setting it up as a primary key, define it as an integer IDENTITY column. Vehicle table State - Use a check constraint to limit the values allowed (only allow valid two position state codes). Inspection table InspectionNo - In addition to setting it up as a primary key, define it as an integer IDENTITY column. VIN- Use foreign key constraint (references) to enforce the referential integrity. FacilityIDNO - Use foreign key constraint (references) to enforce the referential integrity. OdometerReading - Use a check constraint to limit the range of Odometer Reading values (allow zero to one million). InspectionProblem table Inspection Problem No - in addition to setting it up as a primary key, define it as an integer IDENTITY column. Inspection No - Use foreign key constraint (references) to enforce the referential integrity. Problem Code - Use foreign key constraint (references) to enforce the referential integrity. TirePulled - Use a check constraint to limit the values allowed (only allow FL, FR, RL, RR). Reject - Use a check constraint to limit the values allowed (only allow P, F). ProblemMaster table Rejectable - Use a check constraint to limit the values allowed (only allow Y, N). Use the SQL Server Management Studio to complete this lab. a. Create a SQL script file named Car Inspection_DB_Script Your_initials.SQL (John Doe would name it Car Inspeciton_DB_Script JD.SQL). When executed this script is to create a database named Car Inspection Your Initials that contains the objects described in this document. 2. Upload and submit the Car Inspection_DB_Script Your Initials.SQL file before the due date. Car Inspection_Your_Initials Design Specifications Facility FacilityIDNo: integer IDENTITY Name: varchar(40) NOT NULL Street: varchar(40) NOT NULL City: varchar(30) NOT NULL County: varchar(30) NOT NULL Zip: varchar(9) NOT NULL Vehicle VIN: char(17) NOT NULL PlateNo: varchar(10) NOT NULL State: char(2) NOT NULL Make: varchar(18) NULL YearManufactured: integer NOT NULL Inspection InspectionNo: integer IDENTITY InspectDate: datetime NOT NULL OdometerReading: decimal(9.2) NOT NULL Issued CertNo:char(12) NULL InspectorCertNo: char(12) NULL VIN: char(17) NOT NULL (FK) FacilityIDNo: integer NOT NULL (FK) ProblemMaster Ok ProblemCode: char(4) NOT NULL Description: varchar(80) NOT NULL Rejectable: char(1) NOT NULL ActionToTake: varchar(80) NULL InspectionProblem Inspection ProblemNo: integer IDENTITY Reject char(1) NOT NULL Tire Pulled: char(4) NULL RepairOrderNo: integer NULL InspectionNo: integer NOT NULL (FK) ProblemCode: char(4) NOT NULL (FK) Additional Design Specifications Facility table FacilityIDNo- In addition to setting it up as a primary key, define it as an integer IDENTITY column. Vehicle table State - Use a check constraint to limit the values allowed (only allow valid two position state codes). Inspection table InspectionNo - In addition to setting it up as a primary key, define it as an integer IDENTITY column. VIN- Use foreign key constraint (references) to enforce the referential integrity. FacilityIDNO - Use foreign key constraint (references) to enforce the referential integrity. OdometerReading - Use a check constraint to limit the range of Odometer Reading values (allow zero to one million). InspectionProblem table Inspection Problem No - in addition to setting it up as a primary key, define it as an integer IDENTITY column. Inspection No - Use foreign key constraint (references) to enforce the referential integrity. Problem Code - Use foreign key constraint (references) to enforce the referential integrity. TirePulled - Use a check constraint to limit the values allowed (only allow FL, FR, RL, RR). Reject - Use a check constraint to limit the values allowed (only allow P, F). ProblemMaster table Rejectable - Use a check constraint to limit the values allowed (only allow Y, N).

Answer rating: 100% (QA)

Have created all the tables and exported the sql file Attaching that MySQL dump 1013 Distrib 8019 for macos1015 x8664 Host localhost Database Car InspectionDBJD Server version 8019 40101 SET OLDCHARAC... View the full answer