Relational Database Design and SQL

Using Microsoft SQL Server, answer the questions listed below. You can complete your exercise using either Management Studio or Query Editor.

a) Create a database called EXERCISE1630.

b) Create a user defined data type called KEY_ID for all the primary key columns containing integer values to ensure the same data type, length and null ability.

c) Create the following tables (see image for column information):

CUSTOMER INVOICE LINE PRODUCT VENDOR

d) Add all the primary keys.

e) Add all the foreign keys.

f) The default value for the VEND_PROVINCE column in the VENDOR table is the value BC.

g) The default value for the INV_DATE column in the INVOICE table is todays date - GETDATE().

h) Add a check constraint to the PROD_UNIT_PRICE column in the PRODUCT table to ensure the price entered is greater than or equal to 10.00.

i) The default value for the CUST_BALANCE column in the CUSTOMER table is the value 0.

j) The default value for the VEND_CITY column in the VENDOR table is the value Burnaby.

Table Layout CUSTOMER Data Type Length Column Name Null Values CUST NUMBER (PK) INT User-defined Data Type No CUST LAST NAME VARCHAR 30 No 20 No VARCHAR CUST FIRST NAME CHAR 10 Yes CUST PHONE MONE No CUST BALANCE INVOICE Data Type Length Column Name Null Values User-defined Data Type No INV NUMBER (PK) INT CUST NUMBER (FK) INT User-defined Data Type No DATE DATETIME Yes LINE Data Type Length Column Name Null Values INV NUMBER (PK) INT User-defined Data Type No No LINE NUMBER (PK) SMALLINT No PROD CODE (FK VARCHAR 10 LINE UNIT PRICE MONEY No PRODUCT Data Type Length Column Name Null Values No LPROD CODE (PK) VARCHAR 10 VARCHAR Yes PROD DESCRIPTION No PROD QTY IN STOCK INT No PROD UNIT PRICE MONEY User-defined Data Type No VEND NUMBER (FK) INT VENDOR Data Type Length Column Name Null Values VEND NUMBER (PK) INT User-defined Data Type No VARCHAR 40 No VEND NAME 25 Yes VEND ADDRESS VARCHAR VEND CIT VARCHAR Yes CHAR 2 Yes VEND PROVINCE VEND POSTAL CODE CHAR 6 Yes CHAR 10 Yes VEND PHONE