DATABASE 1

Use the CREATE TABLE command and the INSERT command to create the following database.For each field, pick the most appropriate data type for it. While creating the PRODUCT table, add a CHECK constraint to ensure that values of the PROD_PRICE($) are greater than zero. (4 points for each table = 8 points)

Table Name: PRODUCT

Primary Key: PROD_CODE

Foreign Key: VEND_CODE

PROD_CODE	PROD_DESCRIPT	PROD_PRICE($)	PROD_ON_HAND	VEND_CODE
001278-AB	Claw Hammer	12.95	23	232
123-21UUY	Chain Saw	189.49	4	235
QER-34256	Sledge Hammer	18.63	6	231
SRE-657OG	Rat-Tail File	2.99	15	232
77x-3245Q	Steel Tape	6.79	8	235

Table Name: VENDOR

Primary Key: VEND_CODE

VEND_CODE	VEND_CONTACT	VEND_AREACODE	VEND_PHONE
230	Shelly K.	608	555-1234
231	James Johnson	615	123-4536
232	Annelise Crystal	608	224-8134
233	Candice Wallace	904	342-6567
234	Arthur Jones	615	123-3324
235	Henry Ortozo	615	899-3425

After creating the two tables, use the ALTER TABLE command to add a new column Address to the Vendor table. Then use the UPDATE command to set the address of each vendor according to the following table: (2 points)

VEND_CODE	Address
230	450 Stone,Houston,TX
231	975 Fire Oak,Humble,TX
232	638 Voss,Houston,TX
233	291 Berry,Bellaire,TX
234	5631 Rice,Houston,TX
235	980 Dallas,Houston,TX

Hand in:

1. The two CREATE TABLE commands for the two tables.
2. The ALTER TABLE command to add the Address column

Screen shots of the content of both tables. To list the content of PRODUCT table, use SELECT * FROM PRODUCT and to list the content of the VENDOR table, use SELECT * FROM VENDOR