Create Vehicle Table With VIN And OS ID

Create Table Vehicles Vin Varchar210 Os Id Varchar210

Create Table Vehicles Vin Varchar210 Os Id Varchar210

CREATE TABLE VEHICLES (VIN VARCHAR2(10), OS_ID VARCHAR2(10), TRADE_ID VARCHAR2(10), TYPE VARCHAR2(12), MODEL VARCHAR2(15), MAKE VARCHAR2(15), WHERE_FROM VARCHAR2(15), WHOLESALE_COST VARCHAR2(15), CONSTRAINT VEHICLES_PK PRIMARY KEY (VIN));

Drop Table SALESPERSONS;

CREATE TABLE SALEPERSONS (SALESPERSON_ID VARCHAR2(10), TITLE VARCHAR2(12), FIRST_NAME VARCHAR2(15), LAST_NAME VARCHAR2(15), MIDDLE_NAME VARCHAR2(15), HIRE_DATE DATE, DEALER_ID VARCHAR2(10), CONSTRAINT SALEPERSONS_PK PRIMARY KEY (SALESPERSON_ID) );

Insert into SALEPERSONS (SALESPERSON_ID, TITLE, FIRST_NAME, LAST_NAME, HIRE_DATE, DEALER_ID) Values ('SP001', 'MR', 'Tracy', 'McGrady', TO_DATE('01/01/2023 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'D1');

Insert into SALEPERSONS (SALESPERSON_ID, TITLE, FIRST_NAME, LAST_NAME, HIRE_DATE, DEALER_ID) Values ('SP002', 'MR', 'Vince', 'CarterR', TO_DATE('01/11/2023 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'D2');

Insert into SALEPERSONS (SALESPERSON_ID, TITLE, FIRST_NAME, LAST_NAME, HIRE_DATE, DEALER_ID) Values ('SP003', 'Ms', 'Mona', 'Lisa', TO_DATE('12/01/2022 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'D3');

Insert into SALEPERSONS (SALESPERSON_ID, TITLE, FIRST_NAME, LAST_NAME, HIRE_DATE, DEALER_ID) Values ('SP004', 'Ms', 'Madline', 'Lisa', TO_DATE('12/01/2022 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'D4');

Insert into SALEPERSONS (SALESPERSON_ID, TITLE, FIRST_NAME, LAST_NAME, HIRE_DATE, DEALER_ID) Values ('SP005', 'Mr', 'Vince', 'McMahon', TO_DATE('01/01/2023 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'D5');

Insert into SALEPERSONS (SALESPERSON_ID, TITLE, FIRST_NAME, LAST_NAME, HIRE_DATE, DEALER_ID) Values ('SP006', 'Mr', 'Albert', 'Camo', TO_DATE('12/01/2022 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'D6');

Insert into SALEPERSONS (SALESPERSON_ID, TITLE, FIRST_NAME, LAST_NAME, HIRE_DATE, DEALER_ID) Values ('SP007', 'Ms', 'Kaley', 'CouCou', TO_DATE('12/01/2022 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'D7');

Insert into SALEPERSONS (SALESPERSON_ID, TITLE, FIRST_NAME, LAST_NAME, HIRE_DATE, DEALER_ID) Values ('SP008', 'Ms.', 'Stephanie', 'McMahon', TO_DATE('01/01/2023 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'D8');

Insert into SALEPERSONS (SALESPERSON_ID, TITLE, FIRST_NAME, LAST_NAME, HIRE_DATE, DEALER_ID) Values ('SP009', 'Mr', 'Mario', 'Gutze', TO_DATE('01/01/2023 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'D9');

Insert into SALEPERSONS (SALESPERSON_ID, TITLE, FIRST_NAME, LAST_NAME, HIRE_DATE, DEALER_ID) Values ('SP010', 'Mr', 'Albert', 'Bourne', TO_DATE('01/01/2023 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'D10');

Create Table SALES (SALE_ID VARCHAR2(10), CUST_ID VARCHAR2(10), VIN VARCHAR2(10), SALESPERSON_ID VARCHAR2(10), VEHICLE_STATUS VARCHAR2(10), SALE_DATE DATE, MILEAGE FLOAT, GROSS_SALE_PRICE FLOAT);

Drop Table FINANCING_PLANS;

Create Table FINANCING_PLANS (FPID VARCHAR2(12), INSTITUTION VARCHAR2(12), PERCENTAGE VARCHAR2(12), LOAN_TYPE VARCHAR2(12), MAX_TERM INTEGER, MIN_DOWN FLOAT, MAX_LOAN_AMT FLOAT, CONSTRAINT FINANCING_PLANS_PK PRIMARY KEY (FPID));

Insert into FINANCING_PLANS (FPID, INSTITUTION, PERCENTAGE, LOAN_TYPE, MAX_TERM, MIN_DOWN, MAX_LOAN_AMT) Values ('FP0001', 'BANK', '8', 'CONSUMER', 4, 3500, 50000);

Insert into FINANCING_PLANS (FPID, INSTITUTION, PERCENTAGE, LOAN_TYPE, MAX_TERM, MIN_DOWN, MAX_LOAN_AMT) Values ('FP0002', 'BANK', '5', 'CONSUMER', 5, 1000, 20000);

Insert into FINANCING_PLANS (FPID, INSTITUTION, PERCENTAGE, LOAN_TYPE, MAX_TERM, MIN_DOWN, MAX_LOAN_AMT) Values ('FP0003', 'BANK', '9', 'CONSUMER', 6, 3300, 45000);

Insert into FINANCING_PLANS (FPID, INSTITUTION, PERCENTAGE, LOAN_TYPE, MAX_TERM, MIN_DOWN, MAX_LOAN_AMT) Values ('FP0004', 'BANK', '9', 'CONSUMER', 7, 3300, 45000);

Insert into FINANCING_PLANS (FPID, INSTITUTION, PERCENTAGE, LOAN_TYPE, MAX_TERM, MIN_DOWN, MAX_LOAN_AMT) Values ('FP0005', 'BANK', '9', 'CONSUMER', 7, 6500, 65000);

Drop Table CUSTOMERS;

Create Table CUSTOMERS (CUST_ID VARCHAR2(10), FIRST_NAME VARCHAR2(15), LAST_NAME VARCHAR2(15), MIDDLE_NAME VARCHAR2(15), STREET_ADDRESS VARCHAR2(30), CITY VARCHAR2(15), STATE VARCHAR2(20), ZIP VARCHAR2(5), CONSTRAINT CUSTOMERS_PK PRIMARY KEY (CUST_ID));

Sample Paper For Above instruction

The comprehensive development and management of an online vehicle sales database involve multiple phases, including data analysis, querying, scripting, and data warehouse creation. This paper demonstrates these processes, emphasizing the operations performed on the OLTP system and the initial steps of constructing a data warehouse schema to support analytical needs.

Introduction

The primary objective of this project is to conduct a series of structured SQL and PL/SQL exercises over the existing OLTP database, and to initiate the development of the associated star schema data warehouse for Online Vehicle Sales (OVS), Inc. The foundation of this work resides in the fully populated tables from previous homework, ensuring that the data used for counting, querying, and schema creation is representative of real operational data.

Data Counting and Basic Queries

The first step involves verifying the data volume in essential tables using COUNT(*) queries. For the Customers table, a target of 100 rows ensures sufficient customer coverage. The Vehicles table should contain 50 entries, representing the vehicle inventory, while the Salesperson table should have 10 records, each representing a sales agent. The Financing Plans table should encompass 5 distinct financing options, and the Sales table should record 200 transactions, with an equivalent number of financing entries in the Sales Financings table, indicating the transaction volume.

Executing these count queries provides an internal check of the dataset's completeness, yielding expected results that confirm the data's integrity and volume. These steps are critical in preparing for more complex queries and schema development.

Analytical Queries

The next analytical query identifies the zip code and vehicle make combination with the largest total car purchases. This aggregate analysis helps identify high-traffic sales regions and the most popular vehicle makes within those regions. The SQL query employs GROUP BY clauses along with ORDER BY and FETCH FIRST or ROWNUM techniques to extract the top combination.

Additionally, a PL/SQL anonymous block computes the total sales for a specific zip code. This block accepts a zip code as input, calculates the total purchase amount for that region, and outputs the result. Another similar block identifies the zip code with the largest total car purchases, handling ties by selecting the lowest numeric zip code. These procedures demonstrate practical automation for sales reporting.

Data Warehouse Schema Design

The latter part of this project involves designing star schema components for analytical reporting. The existing financing plans table serves as a dimension table, linking to fact tables through foreign keys, with the primary key stored in the FPID column.

A new dealer dimension table is created with at least two records, providing geographic and corporate data necessary for regional analysis. Following this, the Vehicles dimension table is distinctively created: the OLTP Vehicles table is renamed to OLTP_VEHICLES, and a new Vehicles dimension table is populated with concatenated make and model descriptions for each vehicle, utilizing an Oracle sequence to generate unique vehicle codes.

The script employs PL/SQL cursors to iterate over existing vehicle data, constructing description strings and inserting them into the new dimension table. The foreign key in the Sales table is also modified to refer to the new Vehicles dimension, ensuring referential integrity across the data model.

Conclusion

This assignment illustrates the critical steps in both operational database management and the foundational design of a data warehouse schema. Successfully executing the counting queries, developing auto-generating codes and descriptions, and establishing star schema dimensions underpins sophisticated analytics and reporting for enterprise vehicle sales management. These processes ensure data consistency, enable region and product performance analysis, and prepare the database environment for future business intelligence applications.

References

• Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.