CIS 411w spring 2017 problem set 14

1.Create three tables based on the entity diagrams, and descriptions below:

describe sales;

Name Null? Type

----------------------------------------- -------- ----------------------------

SALE_ID NOT NULL NUMBER(7)

CHANNEL_ID NUMBER(7)

PRODUCT_ID NUMBER(7)

QUANTITY NUMBER(7)

describe products;

Name Null? Type

----------------------------------------- -------- ----------------------------

PRODUCT_ID NOT NULL NUMBER(7)

PRODUCT_DESC VARCHAR2(20)

PRICE NUMBER(9,2)

describe channels;

Name Null? Type

----------------------------------------- -------- ----------------------------

CHANNEL_ID NOT NULL NUMBER(7)

CHANNEL_DESC VARCHAR2(20)

Copy and paste the SQL commands to create the tables into this assignment

There are two foreign keys: channel_id and product_id in the SALES table.

Copy and paste the SQL commands to create the foreign keys into this assignment.

There are three primary keys in the SALES, PRODUCTS and CHANNELS tables.

Copy and paste the SQL commands to create the primary keys into this assignment.

To get data into your SALES, PRODUCTS and CHANNELS tables, create insert statements from the data below:

CHANNELS data:

CHANNEL_ID CHANNEL_DESC

---------- --------------------

1 FEDEX

4 UPS

2 STORE319

3 USPS

PRODUCTS data:

PRODUCT_ID PRODUCT_DESC PRICE

---------- -------------------- ----------

1 USB phone charger 19.99

2 Coaxial Cable 99.99

3 Power cable 19.99

SALES data:

SALE_ID CHANNEL_ID PRODUCT_ID QUANTITY

---------- ---------- ---------- ----------

1 1 1 100

2 2 2 200

3 3 3 500

Copy and paste the SQL INSERT commands into this assignment.

Create a UNIQUE INDEX on the table PRODUCTS using the columns: product_id and product_desc. That is, create one UNIQUE INDEX that references the two columns.

https://www.techonthenet.com/oracle/indexes.php

Copy and paste the SQL commands to create the index into this assignment.

Create a BITMAP INDEX on the table SALES and the columns channel_id and product_id.

Copy and paste the SQL commands to create the index into this assignment.

Create a VIEW of a SQL select statements that selects the sale_id, product_desc,price and channel_desc from the SALES, PRODUCTS and CHANNELS tables. The SALES table is joined to the PRODUCTS table using the product_id column found in both tables.

https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_8004.htm#SQLRF01504

Copy and paste the SQL commands to create the view into this assignment.

8.Submit your work to the Problem Set 14 Drop Box for credit.