Additional Constraints for the Projects Tables

$$ Categories Table

o Add a new column CategoryCode NUMBER$(6,0)$

$$ Unique Constraint

o CategoryName NOT NULL

o You can just add the columns to your existing script, in the real world

we would use the ALTER TABLE command we saw last week to add the

new column

$\backslash$infty ReportsTo

$$

$$ Product Table

o Check constraints

$$ UnitPrice column greater than or equal to $0$

$$ ReorderLevel greater than or equal to $0$

$$ UnitsInStock greater than or equal to $0$

$$ UnitsOnOrder greater than or equal to $0$

o ProductName NOT NULL

$$ OrderDetails

o Check constraints

$$ Discount greater than or equal to $0$ and less than or equal $1($used

as a percentage, $\mathrm{.}05=5\%)$

$$ Quantity greater than $0$

$$ UnitPrice greater than or equal to $0$

o ProductID NOT NULL

o UnitPrice NOT NULL

o Quantity NOT NULL

o Discount NOT NULL

$$ Customers

o CompanyName NOT NULL

o Add a new column called Email VARCHAR$2(50)$

$$ Unique Constraint

$$ Employees

o Add a new columns SIN CHAR$(9)$

$$ Unique Constraint

o LastName NOT NULL

o FirstName NOT NULL

$$ Suppliers

o CompanyName NOT NULL

$$ Shippers

o CompanyName NOT NULL

By adding these to your script you should end up with $26$ constraints $($does not include

the NOT NULL constraints$)$ on your tables:

o $8$ Primary Key

o $8$ Foreign Key

o $3$ Unique Key

o $7$ Check