CIS 411w spring 2017 problem set 14 1. Create three tables based on the entity d
ID: 3828245 • Letter: C
Question
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.
Explanation / Answer
1. Create table Product (PRODUCT_ID Numeric (7) NOT NULL ,PRIMARY KEY,PRODUCT_DESC VARCHAR2(20),PRICE Numeric(9,2))
2. Create table Channels(CHANNEL_ID Numeric(7) NOT NULL PRIMARY KEY,
CHANNEL_DESC VARCHAR2(20))
3. Create table Sales(SALE_ID NUMBER(7) NOT NULL PRIMARY KEY ,
CHANNEL_ID Numeric (7),PRODUCT_ID Numeric(7),
QUANTITY Numeric(7),
PRODUCT_ID Numeric (7) REFERENCES Product (PRODUCT_ID ),
CHANNEL_ID Numeric(7) REFERENCES Channels (CHANNEL_ID ) )
4. Inserting data Into channels table:
Insert Into channels values (1,’ FEDEX’)
Insert Into channels values (4,’UPS’)
Insert Into channels values (2,’ STORE319’)
Insert Into channels values ( 3,’USPS’)
5. Inserting data into product table
Insert Into Product values ( 1 ,’USB phone charger’,19.99)
Insert Into Product values ( 2 ,’Coaxial Cable’, 99.99)
Insert Into Product values ( 3,’ Power cable’,19.99)
6. Inserting data into sales table:
Insert Into Product values ( 1,1,1,100)
Insert Into Product values (2, 2, 2 , 200)
Insert Into Product values ( 3 , 3, 3 , 500)
7. Creating unique index on products table:
CREATE UNIQUE INDEX Prd_Index
ON Product (PRODUCT_ID ,PRODUCT_DESC );
8.Creating bitmap index on sales table:
CREATE BITMAP INDEX Sal_bitindex
ON Sales(CHANNEL_ID , PRODUCT_ID);
9. Creating a view
CREATE VIEW SalesData
AS
Select A.sale_id,B.product_desc,C.channel_desc from Sales A join Products B on A.product_id=B.product_id join Channels C on A. CHANNEL_ID=C.CHANNEL_ID
Query a view
Select * from SalesData