Showing Page:
1/3
Create the VEHICLES star schema dimension table via SQL.
Change your existing OLTP VEHICLES table to
OLTP_VEHICLES via the SQL RENAME command and
change your SALES tables foreign key to reference this
new table name. For the Vehicle_Code primary key colum n
use an Oracle sequence to populate the values. For the
Description column use all concatenated combinations of
Make and Model of vehicles you have. Use a PL/SQL block
to populate the Description column by SELECTing the
combinations from your OLTP_VEHICLES table and then
INSERTing the combinations into your new VEHICLES
table, which would best be performed via a cursor in a
loop. After populating your VEHICLES table execute a
\"SELECT * FROM vehicles ORDER BY vehicle_code\" SQL
statement to display the entire contents. Show all your
SQL and PL/SQL code for this step and the Oracle results
from executing it. (I\'m needing help with using the
sequence to populate the description and using the cursor
in a loop!!) Star Schema for OVS, Inc. Data Warehouse
VEHICLES Vehicle Code (PK) SALES FACTS Description
TIME Sale_Day (FK) Vehicle_Code (FK) Plan_Code (FK)
Dealer_ID (FK) Vehicles Sold Gross Sales Amt Sale_Day
(PK) Day_Type DEALERSHIPS PK of a fact a single fact
Dealer ID (PK) Location Region_ID calculated values for
each fact Street Address City State Zip Code Phone Sq Ft
Opened Date Manager District ID FINANCING PLANS Plan
Code (PK) Loan_Type Institution Nam e -oan Perc_ Rate
Min Down Max Loan Amount Max Term
Showing Page:
2/3
Solution
1. CREATE TABLE VEHICLES ( VECHILE_CODE
VARCHAR2(50) PRIMARY KEY, DESCRIPTION
VARCHAR2(100));
2. RENAME TABLE OLTPVEHICLES TO OLTP_VEHICLES;
3. ALTER TABLE SALES DROP CONSTRAINT
VEHICLE_CODE;
4. ALTER TABLE SALES ADD CONSTRAINT
VEHICLES.VEHICLE_CODE ENABLE NOVALIDATE;
5. CREATE SEQUENCE VEHICLE_SEQUENCE
start with 1
increment by 1
minvalue 1
maxvalue 10000;
6. DECLARE DESC VARCHAR2;
CURSOR v_data is
select description
from OLTP_VEHICLES;
BEGIN
Showing Page:
3/3
open v_data;
loop
fetch vdata into Desc;
Exit when v_data % notfound;
Insert into VEHICLES(vechile_code,description)
values(vehicles_sequence.Nextval, desc);
END LOOP;
CLOSE v_data;
COMMIT;
END;
7. SELECT * FROM VEHICLES ORDER BY VEHICLE_CODE;

Unformatted Attachment Preview

Name: Description: ...
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.
Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4