PS14 1. Create three tables based on the entity diagrams, and descriptions below
ID: 3817078 • Letter: P
Question
PS14
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
SALES table:
...............
CREATE TABLE SALES (
SALE_ID NUMBER(7) NOT NULL,
CHANNEL_ID NUMBER(7),
PRODUCT_ID NUMBER(7),
QUANTITY NUMBER(7),
PRIMARY KEY (SALE_ID )
);
PRODUCTS table:
.................
CREATE TABLE PRODUCTS (
PRODUCT_ID NUMBER(7) NOT NULL,
PRODUCT_DESC VARCHAR2(20)
PRICE NUMBER(9,2),
PRIMARY KEY (PRODUCT_ID )
);
CHANNELS table:
.................
CREATE TABLE CHANNELS (
CHANNEL_ID NUMBER(7) NOT NULL,
CHANNEL_DESC VARCHAR2(20),
PRIMARY KEY (CHANNEL_ID )
);
................................................................
Adding foreign keys:
ALTER TABLE SALES
ADD FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCTS(PRODUCT_ID);
ADD FOREIGN KEY (CHANNEL_ID) REFERENCES CHANNELS(CHANNEL_ID);
..................
Inserting data:
CHANNELS data:
..........
INSERT INTO CHANNELS VALUES (1, "FEDEX");
INSERT INTO CHANNELS VALUES (4, "UPS");
INSERT INTO CHANNELS VALUES (2, "STORE319");
INSERT INTO CHANNELS VALUES (3, "USPS");
PRODUCTS data:
...........
INSERT INTO PRODUCTS VALUES (1, "USB phone charger", 19.99);
INSERT INTO PRODUCTS VALUES (2, "Coaxial Cable", 99.99);
INSERT INTO PRODUCTS VALUES (3, "Power cable", 19.99);
.........
SALES data:
........
INSERT INTO SALES VALUES (1, 1, 1, 100);
INSERT INTO SALES VALUES (2, 2, 2, 200);
INSERT INTO SALES VALUES (3, 3, 3, 500);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.