ITC 222 Assignment 7

Creating and altering tables

Automart has decided to implement a new inspection process where they go to each location at random times. They want to add the results of these inspections to the database. This involves adding three tables. One look up table that contains the various types of infractions or problems. One for the inspection date and location and another for the details of each inspection

---

The Problems

Create the following tables. Each table should be in the Schema "Employee".

InspectionCategory

It will contain the column CategoryID which will be an identity starting at 1 and incrementing by ones. This column will also be the primary key. The next column will be CategoryName which will provide the descriptive name of the category such as "cleanliness" or " waiting area" etc.

Inspection

This table will contain an InspectionID which is the primary key and and identity starting with 1 and incrementing by 1s. It will contain an InspectionDate and a LocationID which is a foreign key relating to the table location.

Inspection Detail

This table will contain the columns InspectionDetailID, an Identity and primary key, InspectionID, a foreign key, CategoryID a foreign key related to InspectionCategory and a Description column which will contain xml.

---

We also are going to make some changes to existing tables

Alter the Customer.RegisteredCustomer table to make emails unique. Do the same for passwords.

Also the CustomerSurvey table has check constraint on the column "rating" that limits it to a number between 1 and 10. The constraint name is CK__CustomerS__Ratin__25869641. Drop it and add a new constraint that limits it to numbers between 1 and 5.

We are also going to correct a mistake in the current database. The vehicleID in Employee.VehicleService should be a foreign key related to the VehicleID in Customer.Vehicle. Add the Foreign Key constraint to correct this oversite.

---

What I am looking for

I want to see the code for the three tables and the alter tables. I am especially looking at the primary key and foreign key constraints for those tables