ITSC 3160Oracle LiveSQLLucidchartSQLDatabase Design

Dealership Inventory & Sales Analysis

A full database design and implementation project for a car dealership, built as part of Database Design & Implementation (ITSC 3160). The system manages vehicles, customers, employees, manufacturers, and transactions in a normalized relational database deployed on Oracle LiveSQL.

my role

On a team of four, I was responsible for logical diagram implementation and data entry, translating the conceptual ER model into a fully normalized logical schema and populating all six tables with realistic seed data for testing.

overview

The database handles day-to-day dealership operations: customer browsing, vehicle listing, transaction processing, and employee management. Six core entities are linked through foreign key constraints enforcing referential integrity across the full dataset. The schema satisfies 1NF, 2NF, and 3NF normalization throughout.

entity relationship diagram

Single bar = One (mandatory)

Crow's foot = Many

Line = Relationship

All relationships are One-to-Many (1:N)

logical diagram

PKPrimary Key: unique row identifier

FKForeign Key: references another table's PK

Dashed line = FK → PK reference

normalization

1NF

All tables have atomic values, no repeating groups or comma-separated fields.

2NF

All non-key attributes fully depend on the entire primary key.

3NF

No transitive dependencies, e.g. MSRP lives in Models, not Vehicles.

sample queries

All vehicles by manufacturer

SQL

SELECT v.VIN, mo.Model_Name, v.Year, v.Color, v.Price
FROM Vehicles v
JOIN Models mo ON v.Model_ID = mo.Model_ID
JOIN Manufacturers ma ON mo.ManufacturerID = ma.ID
WHERE ma.Name = 'Porsche';

Inventory vehicles by model

SQL

SELECT v.VIN, mo.Model_Name, v.Year, v.Color, v.Price
FROM Inventory v
JOIN Models mo ON v.Model_ID = mo.Model_ID
WHERE mo.Model_Name LIKE 'Macan%';

Full transaction details

SQL

SELECT t.ID, c.Name AS Customer, e.Name AS Employee,
       v.VIN, mo.Model_Name, v.Color, t.Value
FROM Transactions t
JOIN Customers c  ON t.CustomerID = c.ID
JOIN Employees e  ON t.EmployeeID = e.ID
JOIN Vehicles v   ON t.VIN = v.VIN
JOIN Models mo    ON v.Model_ID = mo.Model_ID;

Transactions for customer 'Richard' with red vehicle

SQL

SELECT t.ID, c.Name, v.VIN, mo.Model_Name, v.Color, t.Value
FROM TransactionDetails t
JOIN Customers c ON t.CustomerID = c.ID
WHERE c.Name LIKE '%Richard%'
AND v.Color = 'Red';

Query Results

All Porsche Macan vehicles

VIN	Model	Year	Color	Price
WP1AA2A57LLB125	Porsche Macan	2020	Black	$60,000.12
WP1AA2A57LLB123	Porsche Macan	2019	Blue	$49,887.12
WP1AA2A57LLB125	Porsche Macan	2021	Orange	$52,000.12
WP1AA2A57LLB123	Porsche Macan	2021	Silver	$53,000.12
WP1AA2A57LLB125	Porsche Macan	2019	White	$61,545.12
WP1AA2A57LLB123	Porsche Macan	2020	Black	$42,753.12
WP1AA2A57LLB125	Porsche Macan	2022	Turquoise	$87,000.12

Transaction details

Transaction ID	Customer	VIN	Model	Color	Value	Employee
251209066	Alex Anderson	WP1AA2A57LLB123	Porsche Macan	Red	$42,000.00	Sarah Johnson
271206396	Alex Kolcaryk	WP1AA2A57LLB125	Porsche Macan	Black	$60,000.12	Gracie Del
261206366	Richard Jackson	WP1AA2A57LLB123	Porsche Macan	Red	$42,000.00	Michael Brown
266302465	Robert Jamison	WP1AA2A57LLB123	Porsche Macan	Blue	$49,887.12	John Carter

Download Full Report