Loading...

Messages

Proposals

Stuck in your homework and missing deadline? Get urgent help in $10/Page with 24 hours deadline

Get Urgent Writing Help In Your Essays, Assignments, Homeworks, Dissertation, Thesis Or Coursework & Achieve A+ Grades.

Privacy Guaranteed - 100% Plagiarism Free Writing - Free Turnitin Report - Professional And Experienced Writers - 24/7 Online Support

What is star schema in sql

20/10/2021 Client: muhammad11 Deadline: 2 Day

Create The DEALERSHIPS Star Schema Dimension Table Via SQL

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.

sales.

Homework is Completed By:

Writer Writer Name Amount Client Comments & Rating
Instant Homework Helper

ONLINE

Instant Homework Helper

$36

She helped me in last minute in a very reasonable price. She is a lifesaver, I got A+ grade in my homework, I will surely hire her again for my next assignments, Thumbs Up!

Order & Get This Solution Within 3 Hours in $25/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 3 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 6 Hours in $20/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 6 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 12 Hours in $15/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 12 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

6 writers have sent their proposals to do this homework:

Top Grade Essay
WRITING LAND
Top Writing Guru
Smart Accountants
Supreme Essay Writer
Finance Master
Writer Writer Name Offer Chat
Top Grade Essay

ONLINE

Top Grade Essay

I am an academic and research writer with having an MBA degree in business and finance. I have written many business reports on several topics and am well aware of all academic referencing styles.

$28 Chat With Writer
WRITING LAND

ONLINE

WRITING LAND

After reading your project details, I feel myself as the best option for you to fulfill this project with 100 percent perfection.

$23 Chat With Writer
Top Writing Guru

ONLINE

Top Writing Guru

Being a Ph.D. in the Business field, I have been doing academic writing for the past 7 years and have a good command over writing research papers, essay, dissertations and all kinds of academic writing and proofreading.

$26 Chat With Writer
Smart Accountants

ONLINE

Smart Accountants

This project is my strength and I can fulfill your requirements properly within your given deadline. I always give plagiarism-free work to my clients at very competitive prices.

$41 Chat With Writer
Supreme Essay Writer

ONLINE

Supreme Essay Writer

I have written research reports, assignments, thesis, research proposals, and dissertations for different level students and on different subjects.

$21 Chat With Writer
Finance Master

ONLINE

Finance Master

Being a Ph.D. in the Business field, I have been doing academic writing for the past 7 years and have a good command over writing research papers, essay, dissertations and all kinds of academic writing and proofreading.

$49 Chat With Writer

Let our expert academic writers to help you in achieving a+ grades in your homework, assignment, quiz or exam.

Similar Homework Questions

Rci canal boats england - Chapter Application Paper - Fair verona romeo juliet - Tv commercial script english - Zask international medical supply inc - Jure leskovec net worth - Female perspectives about slavery as offered by Jacobs and Truth - Picasso girl before a mirror meaning - Barry o brien quiz question answer - The wall street journal classroom edition chapter 1 answers - Assignment 5-Fast Scrabble Words - Margaret beeson billings mt - Hsc extension english syllabus - Need help in homework BI - Mit sloan interview invites - Uts bonus points scheme - 5 day push pull workout pdf - Vce scaling report 2019 - Walmart porter 5 forces - Conk hair watsons go to birmingham - Assignment: Evidence-Based Project - R6 - Proposing a data gathering strategy at aerotech inc - Suppose your original problem statement for this dilemma was - 9 1 unun wiring - How to calculate meals per labor hour - The key theme in humanistic therapy is - 0.16 rounded to the nearest tenth - Sits surrey ac uk - Wordsworth graphic organizer b - Big assignment - BUS 322 Week 3 Assignment 1 What Makes ____ the Best Place to Work and Why? - Practical application of cloud computing - Questions and answers to bus 599 strategic management peregrine exam - Or does it explode - Interactive advent calendar ks2 - How many grams in an ounce of 14 karat gold - Stopping distance activity worksheet answers physics classroom - Kim - The leadership grid used the two leader behaviors which were called: - 1800 mm in cm - Assumptions in truss analysis - Examples of literary devices - Www hazelmeremc co uk - The audiology department at randall clinic offers - Explain what is meant by locus of control - 150 as a product of prime factors - How did planck solve the ultraviolet catastrophe - Myers briggs type indicator mbti classification - Linkworks trial balance - LeadingGlobalandDiverseCultures_Assessment1 - Problem Set 7 - Fundamentals of Nursing - Three courses - Chemistry matter and change chapter 9 study guide answers - Problem solution presentation topics - At penn foster expanded academic asap is - Ieee 829 standard for test documentation - Pros and cons of dsm diagnosis - 100 positive response due tonight by 10:30 - The financial detective 2016 case study solution - How long did johnny cash spend in prison - Space headway and time headway - The patterson operation case study - Contribution of sir philip sidney - The short run in macroeconomic analysis is a period - Social control theory - Fnsacc501 answers - Under armour sustainability report 2017 - What is the payback period statistic - Law discussion questions - Too big to fail full movie - Arm mounted hydraulic jaws - What is the slowest type of mass movement - Cinnamon and warfarin interaction - Smoke signals where do you live - Ina assitalia insurance students - Food handling course chisholm - Order 2123789: person I admire - Humanistic personality analysis - Database system project proposal - Lion king comparison hakuna matata - Unisuper unique superannuation identifier - Bodily fluid clean up procedure - Taylormade r540 xd driver non conforming - Dr kevin kalwerisky consultant clinic - Ethics - Tell me about your educational background - Why shaving mirror is concave - Did jim and laura buy a car conclusion - Organisation Behavior - COM 201: The Power of Effective Speaking. - Little eaton village hall - L98a2 cadet gp rifle - Harvela investments ltd v royal trust co of canada - Leeds beckett university part time language courses - Principles of management chapter 5 ppt - One sentence summary examples - Im not scared book - Manufacturing process planning ppt