Create the dealerships star schema dimension table via sql

CMIS-420 Project #2 (added Step #0 on 11/24/14)

In this assignment you will be creating a data warehouse database for Online Vehicle Sales (OVS), Inc.  The data warehouse is comprised of 4 dimension tables and a fact table based on the Project #2 Star Schema diagram posted in LEO’s Week #6 area.  Your OLTP database tables and star schema tables will reside in the same Oracle schema.

You can perform this assignment based on a database on Nova or any other Oracle system you wish, but you must use the Oracle RDBMS.

You should use one or more SQL script files to complete this assignment.  Your script files should contain all your SQL and PL/SQL code.  Do NOT submit your SQL script files.  Doing so may result in confusion and will result in lost points.

Everything for this assignment must be in a single file.  If you are using SQL*Plus you must put all your SQL, PL/SQL, and results together in a single SPOOL file.  If you are using SQL Developer or other GUI, put all your screen snapshots in a single file for both your SQL statements and PL/SQL as they executed and the results.  Failure to include all your SQL, PL/SQL, and all your Oracle execution results along with them will result in lost points.

Do NOT submit additional files as this only complicates the grading, and will result in lost points.

The specific assignment steps are listed below.  In order to earn full credit you must keep your steps in the order shown, number your steps, and put everything in a single file.

Your FINANCING_PLANS table should already be created and populated.  Your Plan_ID primary key is the Plan_Code column.  Don’t worry about changing this to Plan_Code or changing any other column names you already have.

0)     Create the DEALERSHIPS star schema dimension table via SQL.  Add at least 2 rows of data via INSERT statement(s).  After populating your DEALERSHIPS table execute a “SELECT * FROM dealerships;” SQL statement to display the entire contents.  Show all your SQL code for this step and the Oracle results from executing it. 

1)     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 table’s foreign key to reference this new table name.  For the Vehicle_Code primary key column 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. 

2)     Create the TIMES star schema dimension table via SQL.  This is “TIME” table in the star schema figure.  The Sale_Day primary key column values should be all dates from your first sale date through and including your last sale date from your SALES table.  The Day_Type values should be ‘Weekday’, ‘Weekend’, or ‘Holiday’ (this trumps Weekday and Weekend).  Set all occurrences of the following days for your date range to be holidays: New Year’s Day, Martin Luther King Jr’s Birthday, President’s Day, Memorial Day, 4th of July, Labor Day, Columbus Day, Veterans Day, Thanksgiving, and Christmas.  Use a PL/SQL block to populate the TIMES table.  After populating your TIMES table execute the SQL statement “SELECT day_type, COUNT(*),MIN(sale_day),MAX(sale_day) FROM time GROUP BY day_type ORDER BY day_type” to show the summarized contents of your table.  Show all your SQL and PL/SQL code for this step and the results.

3)     Create the SALES_FACTS star schema fact table via SQL.  Ensure that you have declared foreign keys of Sale_Day, Vehicle_Code, Plan_Code, and Dealer_ID to reference your TIMES, VEHICLES, FINANCING_PLANS, and DEALERSHIPS tables, respectfully.  Ensure that you have a primary key for the SALES_FACTS table that is a composite of the Sale_Day, Vehicle_Code, Plan_Code, and Dealer_ID columns.  Do a DESC (i.e. DESCRIBE) of your SALES_FACTS table after it’s created.

4)     Using PL/SQL populate the SALES_FACTS table.  One way to do this is to use four nested cursor loops to get every possible combination of the dimension tables’ primary keys and then the total vehicles sold and gross sales amount for each combination.  If these values for Total_Vehicles_Sold and Gross_Sales_Amount for a combination are zero then don’t INSERT a row into the SALES_FACT table.  Only insert rows for combinations of the four foreign key columns where there were some vehicles sold. Another approach besides nested cursor loops is to use a single INSERT statement with a GROUP BY clause.  After populating your SALES_FACTS table execute the query “SELECT COUNT(*) FROM sales_facts;” to show the row count.  Also execute the query “SELECT SUM(vehicles_sold) FROM sales_facts;” to ensure that you have included all of your 200 or more sales.


Calculate your paper price
Pages (550 words)
Approximate price: -

Why Choose Us

Quality Papers

We value our clients. For this reason, we ensure that each paper is written carefully as per the instructions provided by the client. Our editing team also checks all the papers to ensure that they have been completed as per the expectations.

Professional Academic Writers

Over the years, our Acme Homework has managed to secure the most qualified, reliable and experienced team of writers. The company has also ensured continued training and development of the team members to ensure that it keep up with the rising Academic Trends.

Affordable Prices

Our prices are fairly priced in such a way that ensures affordability. Additionally, you can get a free price quotation by clicking on the "Place Order" button.

On-Time delivery

We pay strict attention on deadlines. For this reason, we ensure that all papers are submitted earlier, even before the deadline indicated by the customer. For this reason, the client can go through the work and review everything.

100% Originality

At Essay USA, all papers are plagiarism-free as they are written from scratch. We have taken strict measures to ensure that there is no similarity on all papers and that citations are included as per the standards set.

Customer Support 24/7

Our support team is readily available to provide any guidance/help on our platform at any time of the day/night. Feel free to contact us via the Chat window or support email:

Try it now!

Calculate the price of your order

We'll send you the first draft for approval by at
Total price:

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Our Services

Essay USA has stood as the world’s leading custom essay writing services providers. Once you enter all the details in the order form under the place order button, the rest is up to us.


Essay Writing Services

At Essay USA, we prioritize on all aspects that bring about a good grade such as impeccable grammar, proper structure, zero-plagiarism and conformance to guidelines. Our experienced team of writers will help you completed your essays and other assignments.


Admission and Business Papers

Be assured that you’ll definitely get accepted to the Master’s level program at any university once you enter all the details in the order form. We won’t leave you here; we will also help you secure a good position in your aspired workplace by creating an outstanding resume or portfolio once you place an order.


Editing and Proofreading

Our skilled editing and writing team will help you restructure you paper, paraphrase, correct grammar and replace plagiarized sections on your paper just on time. The service is geared toward eliminating any mistakes and rather enhancing better quality.


Technical papers

We have writers in almost all fields including the most technical fields. You don’t have to worry about the complexity of your paper. Simply enter as much details as possible in the place order section.