🚗 Car Dealership Database Management System

A comprehensive PostgreSQL database system for managing car dealerships, built with Node.js and PGlite. This project demonstrates advanced database operations including table creation, data manipulation, relationships, and complex queries for a multi-dealership car sales business.

📋 Table of Contents

• Features
• Database Schema
• Prerequisites
• Installation
• Usage
• Database Structure
• Sample Data
• SQL Operations
• Project Structure
• Contributing
• License

🌟 Features

• Multi-Dealership Management: Track multiple dealership locations across different states
• Staff Management: Manage employees across different roles (CEO, Salesperson, Mechanic, etc.)
• Inventory Tracking: Comprehensive car inventory with condition ratings and pricing
• Sales Records: Track sold cars with seller information and sale prices
• Data Relationships: Fully normalized database with foreign key constraints
• CRUD Operations: Complete Create, Read, Update, Delete functionality
• Sample Data: Pre-populated with realistic dealership data for 70+ vintage and classic cars
• In-Memory Database: Uses PGlite for fast, serverless PostgreSQL operations

🗄️ Database Schema

The system consists of four main tables with the following relationships:

dealerships (1) ←→ (many) staff
dealerships (1) ←→ (many) cars
cars (1) ←→ (1) sold_cars
staff (1) ←→ (many) sold_cars

📋 Prerequisites

Before running this project, make sure you have:

• Node.js (version 18 or higher)
• npm (comes with Node.js)

🚀 Installation

1. Clone the repository

   git clone https://github.com/bakadja/postgresql.git
   cd postgresql

2. Install dependencies

   npm install

3. Run the application

   npm start
   # or
   npm run dev
   # or
   node index.js

💻 Usage

The application automatically executes the following sequence:

1. Database Setup: Creates all necessary database tables
2. Data Population: Populates tables with sample dealership and car data
3. CRUD Operations: Demonstrates various database operations
4. Table Modifications: Adds constraints and foreign key relationships
5. Custom Queries: Executes queries from query.sql file
6. Results Display: Shows query results in a formatted console table

Running Custom Queries

To execute your own queries:

1. Edit the query.sql file with your SQL commands
2. Run the application:

   npm start

The results will be displayed in your console as a formatted table.

🏗️ Database Structure

Tables Overview

dealerships

Stores information about dealership locations.

Column	Type	Constraints	Description
id	SERIAL	PRIMARY KEY	Unique dealership identifier
city	TEXT	NOT NULL	Dealership city location
state	CHAR(2)	NOT NULL	Two-letter state code
established	DATE	NOT NULL	Date dealership was established

staff

Manages employee information for each dealership.

Column	Type	Constraints	Description
id	SERIAL	PRIMARY KEY	Unique staff identifier
dealership_id	INTEGER	NOT NULL, FOREIGN KEY	Reference to dealership
name	TEXT	NOT NULL	Employee full name
role	TEXT	NOT NULL	Employee job role

cars

Comprehensive car inventory across all dealerships.

Column	Type	Constraints	Description
id	SERIAL	PRIMARY KEY	Unique car identifier
brand	TEXT	NOT NULL	Car manufacturer
model	TEXT	NOT NULL	Car model name
year	INTEGER	NOT NULL	Manufacturing year
price	INTEGER	NOT NULL	Listed price in USD
color	TEXT	NOT NULL	Car color
condition	INTEGER	NOT NULL (0-10 scale)	Car condition rating
sold	BOOLEAN	NOT NULL	Sale status
dealership_id	INTEGER	NOT NULL, FOREIGN KEY	Reference to dealership

sold_cars

Records of completed car sales.

Column	Type	Constraints	Description
id	SERIAL	PRIMARY KEY	Unique sale identifier
cars_id	INTEGER	NOT NULL, FOREIGN KEY	Reference to sold car
seller	INTEGER	NOT NULL, FOREIGN KEY	Reference to selling staff
sold_date	DATE	NOT NULL	Date of sale
sold_price	INTEGER	NOT NULL	Final sale price in USD

📊 Sample Data

The database comes pre-populated with:

• 4 Dealerships across different states (IL, GA, MI, PA)
• 15+ Staff Members with various roles including:
  • CEO, Accountant, HR Officer
  • Salespersons, Mechanics
  • Data Administrator
• 75+ Classic and Vintage Cars from premium brands
• 14+ Sales Records with complete transaction details

Featured Car Brands

Luxury & Sports: Aston Martin, Ferrari, Lamborghini, Rolls-Royce, Bentley, Maserati
German Engineering: BMW, Mercedes-Benz, Porsche, Volkswagen
American Classics: Ford, Chevrolet, Dodge, AMC, Pontiac, Lincoln
British Heritage: Jaguar, Lotus, Triumph
Italian Style: Alfa Romeo, Fiat
Japanese Legends: Toyota, Nissan

🔧 SQL Operations

The project demonstrates comprehensive SQL operations:

CRUD Operations

• Create: Insert new dealerships, staff, and car inventory
• Read: Complex queries with joins, aggregations, and filtering
• Update: Modify car prices, conditions, and sales status
• Delete: Remove records based on various conditions

Advanced Database Features

• Foreign Key Relationships: Ensures data integrity across tables
• Table Alterations: Dynamic schema modifications and constraint additions
• Data Validation: Enforces business rules through database constraints
• Complex Queries: Multi-table joins and analytical operations

Example Query Patterns

-- Find all unsold luxury cars over $100,000
SELECT c.brand, c.model, c.year, c.price, d.city, d.state
FROM cars c
JOIN dealerships d ON c.dealership_id = d.id
WHERE c.sold = FALSE AND c.price > 100000
ORDER BY c.price DESC;

-- Get sales performance by dealership
SELECT d.city, d.state, COUNT(sc.id) as total_sales, 
       AVG(sc.sold_price) as avg_sale_price
FROM dealerships d
LEFT JOIN cars c ON d.id = c.dealership_id
LEFT JOIN sold_cars sc ON c.id = sc.cars_id
GROUP BY d.id, d.city, d.state
ORDER BY total_sales DESC;

📁 Project Structure

postgresql/
├── index.js                 # Main application entry point
├── create-tables.sql        # Database schema creation
├── populate-tables.sql      # Initial dealership and staff data
├── insert-cars-data.sql     # Classic car inventory data
├── insert-new-data.sql      # Additional data and sales records
├── crud-operations.sql      # CRUD operation demonstrations
├── alter-table.sql          # Schema modifications and constraints
├── query.sql               # Custom queries (edit this file)
├── package.json            # Node.js project configuration
├── LICENSE                 # MIT license file
├── .gitignore             # Git ignore rules
├── .gitattributes         # Git attributes configuration
└── README.md              # Project documentation

Key Components

• Database Layer: SQL files containing schema, data, and operations
• Application Layer: Node.js runtime with PGlite integration
• Configuration: Package management and environment setup

🤝 Contributing

Contributions are welcome! Here's how you can help:

Getting Started

1. Fork the Project
2. Create your Feature Branch (git checkout -b feature/AmazingFeature)
3. Commit your Changes (git commit -m 'Add some AmazingFeature')
4. Push to the Branch (git push origin feature/AmazingFeature)
5. Open a Pull Request

Areas for Contribution

• Database Schema: Enhance table structures or add new entities
• Sample Data: Add more realistic dealership and car data
• Query Examples: Contribute useful SQL query patterns
• Documentation: Improve README or add code comments
• Testing: Add unit tests for database operations

Guidelines

• Follow existing code style and conventions
• Add appropriate comments to SQL files
• Update README if adding new features
• Test your changes thoroughly

📜 License

This project is licensed under the MIT License - see the LICENSE file for details.

MIT License Summary

• ✅ Commercial use
• ✅ Modification
• ✅ Distribution
• ✅ Private use
• ❌ Liability
• ❌ Warranty

---

Built with ❤️ using Node.js and PGlite

Report Bug • Request Feature • View Demo