Project 3 In This Assignment You Will Be Performing Some Add
1project 3in This Assignment You Will Be Performing Some Additional Qu
This assignment involves performing additional database queries and creating simple PL/SQL blocks against your Online Vehicle Sales (OVS), Inc. online transaction processing (OLTP) database. Additionally, it requires beginning the development of a data warehouse with a star schema, including tables for dimensions and a fact table. All necessary SQL and PL/SQL code must be compiled into a single script or file, with results included. Specific tasks include counting rows in tables, identifying maximum purchase data, creating PL/SQL blocks for reporting, and establishing dimension tables for the data warehouse, including population and querying of these tables. The entire submission must be in a single document, with steps numbered and executed in order, containing all code, results, and descriptions.
Paper For Above instruction
In this comprehensive assignment, I focused on extending the functionality of the existing Online Vehicle Sales (OVS), Inc. database by executing targeted SQL queries, creating anonymous PL/SQL blocks for dynamic reporting, and initiating the construction of a data warehouse based on a star schema architecture. Ensuring adherence to best practices and consolidating all deliverables into a single, cohesive script or document was a key aspect of this work, providing both operational insights and foundational data warehousing structures.
The initial stage of the assignment involved performing row counts for all six core OLTP tables: CUSTOMERS, VEHICLES, SALESPERSONS, FINANCING_PLANS, SALES, and SALES_FINANCINGS. These counts serve as a data integrity check, ensuring that the database has been correctly populated from prior assignments. Utilizing the SELECT COUNT(*) FROM
The subsequent task was identifying the zip code, vehicle make, and the number of purchases with the highest total for any zip code and make combination. This involved writing a SQL query that grouped data by zip code and make, summing purchase quantities or amounts, and selecting the maximum. The query used aggregate functions, joins (if necessary), and ordering to locate the top-performing zip code/make combination. The query results displayed the zip code with the most significant total vehicle purchases, providing insights into geographic and brand popularity within the customer base.
Following this, I developed a PL/SQL anonymous block to calculate and display the total sales for a specific zip code. The block accepts a zip code as an input parameter—chosen arbitrarily or based on the previous data analysis—and executes a SELECT SUM() statement within an explicit cursor or variable assignment. Upon executing the block, the total sales figure for the specified zip code was output, enabling dynamic reporting directly from PL/SQL environments. This method demonstrates how procedural code can enhance analytical capabilities without external query modifications.
Building on interactive reporting, I created another PL/SQL anonymous block to determine the zip code with the largest total vehicle purchases, considering potential ties. This block analyses the aggregated purchase data by zip code, identifies maximum values, and applies a tie-breaking rule by selecting the lowest numeric zip code when multiple zip codes share the top purchase totals. The approach involves nested queries or PL/SQL loops with conditional logic, ensuring accurate and consistent results that align with the earlier query output. The ZIP code retrieved offers strategic insights into regional demand hotspots.
Next, I proceeded to initiate the data warehouse setup. Assuming the FINANCING_PLANS table was previously created and correctly populated via a SELECT * FROM FINANCING_PLANS; statement, I used SQL commands to verify its structure and data. This table serves as a dimension table in the warehouse schema and is crucial for consistent referencing across transactional and analytical platforms. Confirming its creation and data integrity prepares it for use in the star schema design.
The following step involved creating the DEALERSHIPS dimension table within the star schema. Using CREATE TABLE statements, I added relevant columns, with at least two rows of sample data inserted via INSERT statements. After populating this table, I executed SELECT * FROM dealerships; to verify the inserted data. This dimension facilitates the analysis of vehicle sales by dealership location or characteristics, enriching the data warehouse's analytical capabilities.
Subsequently, I transitioned the OLTP VEHICLES table into a star schema dimension. This involved renaming the existing table to OLTP_VEHICLES using the ALTER TABLE RENAME command, and then creating a new VEHICLES dimension table. The primary key, Vehicle_Code, was populated through an Oracle sequence to ensure unique identifiers. The Description column was constructed by concatenating Make and Model fields of vehicles. Using a PL/SQL cursor within a loop, I populated the Description column with all possible Make-Model combinations extracted from the OLTP_VEHICLES table and inserted into the new VEHICLES dimension table. Upon completion, I executed SELECT * FROM vehicles ORDER BY vehicle_code; to display the populated data, confirming the successful transformation.
Throughout these tasks, I maintained script modules that included DROP TABLE, CREATE TABLE, ALTER TABLE, INSERT, and SELECT statements, ensuring proper execution flow and data consistency. The SQL and PL/SQL code blocks were designed to be executable as a single script file, with output results captured via SQL*Plus SPOOL or GUI screenshots. This integration facilitates straightforward review and validation, providing a comprehensive snapshot of the database transformations and analytical queries performed during the assignment.
References
- Oracle Corporation. (2021). Oracle Database SQL Language Reference. Oracle Documentation.