database project 2
1
Project 2
In this assignment you will be completing the creation of most of the tables for the Online Vehicle Sales (OVS), Inc. online transaction processing (OLTP) database based on the supplied ERD, and fully populating all the tables you create. You will also be performing a number of queries against the populated tables.
Your OLTP tables will be populated with a large number of rows since this is intended to simulate a large number of transactions which will make the data warehouse you’ll create in later assignments more realistic.
In Assignment #1 you already created the CUSTOMERS, VEHICLES, and SALES tables based on the ERD, and populated them with a few rows of data. In this assignment you will create the FINANCING_PLANS, SALES_FINANCINGS, and SALESPERSONS tables and fully populate them. You will not be creating the other tables in the ERD.
You will be making up your own data and in many cases you will need to reuse the same customer, vehicle, and financing plan data for your sales.
For all six tables, unless otherwise specified, you must include all necessary integrity constraints including primary keys, foreign keys (except to tables not created), CHECK constraints, UNIQUE constraints, and NOT NULL constraints. It is good practice, but not required, to explicitly name your constraints.
You can create your 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. Do NOT submit your SQL script files. Doing so may result in confusion and will result in lost points.
If you are using SQL*Plus put all your 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 a SPOOL file you need to show all your SQL and results. For a screen snapshot file you need to show at least the first 10 INSERT statements and all other statements. You must show the results of executing your SQL statements right after the SQL statements. Failure to do so will result in lost points.
Do NOT submit additional files as this only complicates the grading, and will result in lost points.
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) Create the FINANCING_PLANS table and populate it with at least 5 (five) rows of data. Show all the SQL you used and the results returned by Oracle from executing your SQL after each SQL statement.
2) Add more rows as necessary to your VEHICLES table so that you have at least 50 (fifty) rows of data total. Execute a “SELECT COUNT(*) FROM vehicles” statement after you have added all your new
2
data. Show the INSERT SQL statements you used and the SELECT COUNT(*) statement and the results returned by Oracle from executing all your SQL after each SQL statement.
3) Create the SALESPERSONS table and populate it with at least 10 (ten) rows of data. You do not need to worry about a foreign key to the DEALERSHIPS table since you won’t be creating that table. Show all the SQL you used and the results returned by Oracle from executing your SQL after each SQL statement.
4) Add more rows as necessary to your CUSTOMERS table so that you have at least 100 (one hundred) rows of data total. Execute a “SELECT COUNT(*) FROM customers” statement after you have added all your new data. Show the INSERT SQL statements you used and the SELECT COUNT(*) statement and the results returned by Oracle from executing all your SQL after each SQL statement.
5) Add more rows as necessary to your SALES table so that you have at least 200 (two hundred) rows of data total. Execute a “SELECT COUNT(*) FROM sales” statement after you have added all your new data. Show the INSERT SQL statements you used and the SELECT COUNT(*) statement and the results returned by Oracle from executing all your SQL after each SQL statement.
6) Create the SALES_FINANCINGS table and populate it with at least 200 (two hundred) rows of data (i.e. one per sale). Execute a “SELECT COUNT(*) FROM sales_financings” statement after you have added all your new data. Show the INSERT SQL statements you used and the SELECT COUNT(*) statement and the results returned by Oracle from executing all your SQL after each SQL statement.
7) Via a single SELECT query display the total count of sales, and the lowest, average, and highest sale price. Show the SQL statement you used and the results returned by Oracle from executing your SQL statement right after the statement.
8) Via a single SELECT query display all customer IDs, names, and zip codes of customers having purchased 2 or more vehicles. Show the SQL statement you used and the results returned by Oracle from executing your SQL statement right after the statement.
9) Via a single SELECT query display the institution and loan type of the financing plan that was used for the most sales. Show the SQL statement you used and the results returned by Oracle from executing your SQL statement right after the statement.
10) Via a single SELECT query display the make of the vehicle involved in the most sales and all customers having purchased a vehicle of that make. Show the SQL statement you used and the results returned by Oracle from executing your SQL statement right after the statement.
11) Via a single SELECT query display the total count of sales, by model and then by zip code, with the highest values first. Show the SQL statement you used and the results returned by Oracle from executing your SQL statement right after the statement.
Your submission MUST be in a single text, Word, or PDF file with all steps numbered and in order.
3
Project 2 grading rubric
Attribute Meets Does Not Meet
CREATE TABLE SQL statements
15 points
Uses an SQL script file. Creates the FINANCING_PLANS, SALES_FINANCINGS, and SALESPERSONS tables. Includes all necessary integrity constraints including primary keys, foreign keys, CHECK constraints, UNIQUE constraints, and NOT NULL constraints. 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 FINANCING_PLANS, SALES_FINANCINGS, and SALESPERSONS tables. Does not include all necessary integrity constraints including primary keys, foreign keys, CHECK constraints, UNIQUE constraints, and NOT NULL constraints. Does not use an Oracle RDBMS. All SQL statements are not syntactically correct or execute without error.
INSERT SQL statements 35 points
Populates the FINANCING_PLANS table with at least 5 (five) rows.
Populates your VEHICLES table with at least 50 (fifty) rows.
Populates your SALESPERSONS table with at least 10 (ten) rows.
Add more rows as necessary to your CUSTOMERS table so that you have at least 100 (one hundred) rows of data total.
Adds more rows as necessary to your SALES table so that you have at least 200 (two hundred) rows of data total.
Populates the SALES_FINANCINGS table with at least 200 (two hundred) rows of data (i.e. one per sale).
All SQL statements are syntactically
0 points
Does not populate the FINANCING_PLANS table with at least 5 (five) rows.
Does not populate your VEHICLES table with at least 50 (fifty) rows.
Does not populate your SALESPERSONS table with at least 10 (ten) rows.
Does not add more rows as necessary to your CUSTOMERS table so that you have at least 100 (one hundred) rows of data total.
Does not add more rows as necessary to your SALES table so that you have at least 200 (two hundred) rows of data total.
Does not populates the SALES_FINANCINGS table with at least 200 (two hundred) rows of
4
correct and execute without error.
data (i.e. one per sale).
All SQL statements are not syntactically correct or execute without error.
SELECT SQL statements 35 points
Via a single SELECT query, display the total count of sales, and the lowest, average, and highest sale price.
Via a single SELECT query, display all customer IDs, names, and zip codes of customers having purchased 2 or more vehicles.
Via a single SELECT query, display the institution and loan type of the financing plan that was used for the most sales.
Via a single SELECT query, display the make of the vehicle involved in the most sales and all customers having purchased a vehicle of that make.
Via a single SELECT query, display the total count of sales, by model and then by zip code, with the highest values first.
All SQL statements are syntactically correct and execute without error.
0 points
Does not display the total count of sales, and the lowest, average, and highest sale price via a single SELECT query.
Does not display all customer IDs, names, and zip codes of customers having purchased 2 or more vehicles Via a single SELECT query.
Does not display the institution and loan type of the financing plan that was used for the most sales Via a single SELECT query
Does not display the make of the vehicle involved in the most sales and all customers having purchased a vehicle of that make Via a single SELECT query
Does not display the total count of sales, by model and then by zip code, with the highest values first Via a single SELECT query
All SQL statements are not syntactically correct or execute without error.
SQL script file and SPOOL file 15 points
Submits either an SQL*Plus SPOOL file or screen snapshots of the output if using SQL Developer or another GUI. 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
0 points
Does not submit either an SQL*Plus SPOOL file or screen snapshots of the output if using SQL Developer or another GUI. Does not demonstrate DROP TABLE, CREATE TABLE, and ALTER TABLE SQL statements as they executed and the Oracle responses. Does not demonstrate INSERT
5
the Oracle responses. Displays the contents of all tables from SELECT * FROM tablename; statements. Displays all single SELECT statements queries. Does NOT submit a SQL script file. Includes a SET ECHO ON SQL*Plus statement in your SQL script file to ensure that all the SQL that is executed is displayed in your SPOOL file.
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. Submits a SQL script file. Does not Include a SET ECHO ON SQL*Plus statement in your SQL script file to ensure that all the SQL that is executed is displayed in your SPOOL file.