Homework #3

Due: see Syllabus

Total: 20 Points

What to submit:

Submit SQL commands used to answer Part 1, Part 2, and Part 3. Make sure that your SQL script runs without any errors. Submit screen shots of running commands for Part 2. Submit answers to Part 4 along with explanation of the system response to your command (so in case of error, explain why the error happened, in case the insert works, explain why it worked).

Part 1 (3 Points): Creating the database:

Create the following tables. The underlined bold column names are the primary keys. Make sure to specify the primary and foreign key constraints in yourcreate table statements.

customer: (cus_code:integer, cus_lname:varchar(20), cus_fname:varchar(20), cus_initial:char, cus_areacode:integer,cus_phone:integer).

invoice: (inv_number:integer, cus_code:integer, inv_date:date,

foreign key cus_code references customer(cus_code))

line: (inv_number:integer, prod_code:integer ,line_units:integer,

foreign key (inv_number) references Invoice(inv_number),

foreign key (prod_code) references Product (prod_code) )

product:(prod_code:integer, prod_desc:varchar(50), prod_price:integer, prod_quant:integer,vend_code:integer,

foreign key (vend_code) referenecs Vendor(vend_code))

vendor:(vend_code:integer,vend_name:varchar(30),vend_contact:varchar(30),vend_areacode:integer,vend_phone:integer)

Part 2 (3 Points): Inserting data:

Insert the following data in the tables using insert into statements:

customer:

10010, Ramas, Paul, A, 615, 8442573

10011, Dunne, Leona, K, 713, 8941238

10012, Smith, Kathy, W, 615, 8942285

10013, Olowski, Paul,F, 615, 2221672

10014, Orlando, Myron, NULL, 615, 2971228

invoice:

1001, 10011, 2018-01-03

1002, 10014, 2016-08-04

1003, 10012, 2017-03-20

1004, 10014, 2018-01-13

line:

1001, 12321, 1

1001, 65781, 3

1003, 83456, 2

1002, 34256, 6

1003, 12321, 5

1002, 12321, 6

1001, 83456, 3

product:

12321, hammer, 189 ,20, 232

65781, chain, 12, 45, 235

34256, tape, 35, 60, 235

83456, saw, 165, 15, 236

12333, hanger, 200 ,10, 232

vendor:

232, Bryson, Smith, 615, 2233234

235, SuperLoo, Anderson, 615, 2158995

236, Brett, Manko, 612, 3458695

Part 3 (9 Points): SQL Queries

Write SQL statements to answer each of the following questions.

List the Product Code, Description, and Quantity for all products, sorted by Description.

List the invoice number and invoice date for all invoices of customer Kathy Smith.

List the product code and product quantity for products in invoice number 1001, with line_units 3.

List all product description and product price supplied by vendor whose vendor contact name is Anderson.

Produce a list of product description, vendor name, and vendor phone for all products with quantity greater than 60.

For each product bought by a customer, list product description, product price and customers last name.

Part 4 (5 Points): Checking entity and referential integrity:

Write an SQL statement to do each of the following tasks in the given order (try 1, 2, 3, 4, than 5). Explain whether the statement is correctly executed or not, if it does not execute correctly, explain why it did not.

Insert the following entry in CUSTOMER

10011, Juan, Rodriguez, J, 612, 7788776

Insert the following entry in INVOICE

1005, 10012, 2017-11-30

Insert the following entry in PRODUCT

12321, nail, 9, 23, 236

Insert the following entry to the VENDOR table

231,Adam, Eric, 615, 2158995

Insert the following entry in PRODUCT

12322, coil, 189, 20, 237

What to submit: Submit SQL commands used to answer Part 1, Part 2, and Part 3. Make sure that your SQL script runs without any errors. Submit screen shots of running c for Part 2. Submit answers to Part 4 along with explanation of the system response to your command (so in case of error, explain why the error happened, in case the insert works, explain why it worked). Part 1 (3 Points): Creating the database: Create the following tables. The underlined bold column names are the primary keys. Make sure to specify the primary and foreign key constraints in your create table statements. 1. customer: (cus codeinteger cus Jnamexarchar(20). cus.fhamevarchax(20). 2. invoice: foreign key cus.code references customer(cus.code)) ne: foreign key (inv^number) references Invoice( mV.numbet foreign key (prod code) references Product (prodcode)) roduct: prod quant-integeruend.codeinteger, foreign key (vendcode) refereuecs Vendor(vendcode)) 5. vendor:(vend code integervend_name:varchar(30),vend_contact:varchar(30), vend_areacode:integer,vend phone:integer)