Project 3
Need Help Writing an Essay?
Tell us about your assignment and we will find the best writer for your paper
Write My Essay For MeIn this assignment you will be performing some additional queries against your Online Vehicle Sales (OVS), Inc. online transaction processing (OLTP) database and also creating some simple anonymous PL/SQL blocks.
You will also begin the creation of a data warehouse database for Online Vehicle Sales (OVS), Inc. The full data warehouse is comprised of 4 dimension tables and a fact table based on the 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.
This assignment is based on the fully populated tables from Homework #2 so it assumes you’ve completed all work for that assignment.
You can perform this assignment based on a database on UGMC’s Virtual Lab Broker.
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.
Here are the specific assignment steps. In order to earn full credit you must keep your steps in order, number your steps, and put everything in a single file.
1) Execute SELECT COUNT(*) FROM <table_name>; statements for all 6 of your OVS, Inc. OLTP tables. You should have at least the following counts: CUSTOMERS table – 100 rows, VEHICLES table – 50 rows, SALESPERSONS table – 10 rows, FINANCING_PLANS – 5 rows, SALES table – 200 rows, and SALES_FINANCINGS table – 200 rows.
2) Via a single SELECT query display the zip code, make, and count with the largest total car purchases for a zip code and make combination (there may be a tie with two or more). Show the SQL statement you used and the results returned by Oracle from executing your SQL statement right after the statement.
3) Develop a PL/SQL anonymous block that displays the total sales for a zip code for a specific zip code. You may use any of your zip codes you wish. Show the PL/SQL statements in your block, the actual execution of your block, and the results returned.
4) Develop a PL/SQL anonymous block that displays the zip code with the largest total car purchases. Since there can be a tie with two or more zip codes, ensure that the lowest numeric zip code is displayed. Show the PL/SQL statements in your block, the actual execution of your block, and the results returned.
5) This step begins the creation of your data warehouse. Ensure that your FINANCING_PLANS table has already been created and populated via a “SELECT * FROM financing_plans;” SQL query. This table is used by both your OLTP database and serves as a dimension table in the star schema of your data warehouse database. 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.
6) 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.
7) 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.
Your submission MUST include one or more .sql files. Please name them with your last name, project, and order to run them. For example: Smith_Project_2_Part_A.sql. Also include drop scripts to un-do the scripts. For example: Smith_Project_2_Part_A_drop.sql.
Project 3 grading rubric
|
Attribute |
Meets |
Does Not Meet |
|
CREATE TABLE SQL statements |
35 points Uses an SQL script file. Creates the DEALERSHIPS star schema dimension table. Creates the VEHICLES star schema dimension table. Changes 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. Uses an Oracle sequence to populate the Vehicle_Code values. Uses all concatenated combinations of Make and Model of vehicles for the Description column. Uses an Oracle RDBMS. All SQL statements are syntactically correct and execute without error. |
0 points Does not use an SQL script file. Does not create the DEALERSHIPS star schema dimension table. Does not create the VEHICLES star schema dimension table. Does not change your existing OLTP VEHICLES table to OLTP_VEHICLES via the SQL RENAME command or change your SALES table’s foreign key to reference this new table name. Does not use an Oracle sequence to populate the Vehicle_Code values. Does not use all concatenated combinations of Make and Model of vehicles for the Description column. Does not use an Oracle RDBMS. All SQL statements are not syntactically correct or execute without error. |
|
INSERT SQL statements |
10 points Adds at least 2 rows of data via INSERT statement(s) to the DEALERSHIPS Star schema table. All SQL statements are syntactically correct and execute without error. |
0 points Does not add at least 2 rows of data via INSERT statement(s) to the DEALERSHIPS Star schema table. All SQL statements are not syntactically correct or execute without error. |
|
SELECT SQL statements |
5 points Executes SELECT COUNT(*) FROM <table_name>; for all OLT tables resulting in expected counts. Via a single SELECT query display the zip code, make, and count with the largest total car purchases for a zip code and make combination. Ensures that your FINANCING_PLANS table has already been created and populated via a “SELECT * FROM financing_plans. After populating your DEALERSHIPS table execute a “SELECT * FROM dealerships;” After populating your VEHICLES table execute a “SELECT * FROM vehicles ORDER BY vehicle_code”. All SQL statements are syntactically correct and execute without error. |
0 points Does not execute SELECT COUNT(*) FROM <table_name>; for all OLT tables resulting in expected counts. Does not, via a single SELECT query, display the zip code, make, and count with the largest total car purchases for a zip code and make combination. Does not ensure that your FINANCING_PLANS table has already been created and populated via a “SELECT * FROM financing_plans. Does not, After populating your DEALERSHIPS table execute a “SELECT * FROM dealerships;” Does not, after populating your VEHICLES table execute a “SELECT * FROM vehicles ORDER BY vehicle_code”. All SQL statements are syntactically correct and execute without error. All SQL statements are not syntactically correct or execute without error. |
|
PL/SQL anonymous blocks |
40 points Develops a PL/SQL anonymous block that displays the total sales for a zip code for a specific zip code. Develops a PL/SQL anonymous block that displays the zip code with the largest total car purchases. Ensures that the lowest numeric zip code is displayed. Uses 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. |
0 points Does not develop a PL/SQL anonymous block that displays the total sales for a zip code for a specific zip code. Does not develop a PL/SQL anonymous block that displays the zip code with the largest total car purchases. Does not ensure that the lowest numeric zip code is displayed. Does not 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. |
|
SQL script file(s) |
10 points Submits SQL script file(s). Demonstrates DROP TABLE, CREATE TABLE, and ALTER TABLE SQL statements as they executed and the Oracle responses. Demonstrates INSERT SQL statements as they executed and the Oracle responses. Displays the contents of all tables from SELECT * FROM tablename; statements. Displays all single SELECT statements queries. Displays all PL/SQL code and execution. |
0 points Does not submits SQL script file(s). Does not demonstrate DROP TABLE, CREATE TABLE, and ALTER TABLE SQL statements as they executed and the Oracle responses. Does not demonstrate INSERT SQL statements as they executed and the Oracle responses. Does not display the contents of all tables from SELECT * FROM tablename; statements. Does not display all single SELECT statements queries. Does not display all PL/SQL code and execution. |
Let our team of professional writers take care of your essay for you! We provide quality and plagiarism free academic papers written from scratch. Sit back, relax, and leave the writing to us! Meet some of our best research paper writing experts. We obey strict privacy policies to secure every byte of information between you and us.
ORDER ORIGINAL ANSWERS WRITTEN FROM SCRATCH
