Car Warehouse Database System

Project Overview

The Car Warehouse Database System is a relational database designed to manage the operations of a model car and vehicle retail business in Pakistan.
It facilitates efficient inventory management, order processing, and customer relationship management by tracking offices, employees, customers, products, orders, and warehouses.

Objectives

• Schema Design: Create a normalized schema (3NF) to eliminate data redundancy.
• Implementation: Deploy the database in SQL Server with realistic sample data.
• Operations: Develop SQL queries, stored procedures, and triggers for core business logic.
• Integrity: Ensure referential integrity using primary and foreign key constraints.

Tech Stack

• Database: Microsoft SQL Server (2019 or later)
• Management Tool: SQL Server Management Studio (SSMS)
• Frontend: Java GUI developed in IntelliJ IDEA
• Version Control: GitHub

Database Schema

The system consists of seven core tables:

1. offices: Stores location and contact details for business branches.
2. employees: Manages staff records and hierarchy.
3. customers: Tracks client information and credit limits.
4. products: Inventory of model vehicles including stock levels and pricing.
5. orders: Records of customer purchases and shipping status.
6. orderdetails: Line items for each order linking products and quantities.
7. warehouses: Monitors storage locations and their current capacity.

Features

• CRUD Operations: Full support for creating, reading, updating, and deleting records.
• Stored Procedures:
  • GetCustomerOrderSummary: Generates summaries of customer activity.
  • UpdateProductStock: Manages inventory adjustments.
• Automated Triggers:
  • UpdateWarehouseCapacity: Automatically adjusts capacity percentages.
  • UpdateCustomerCreditLimit: Updates limits based on business rules.

Getting Started

1. Database Setup: Execute the CarWarehouseDB.sql script in SSMS to create the tables and populate sample data.
2. Queries: Use Queries.sql to run various operations and procedures.
3. Frontend: Open the Java project in IntelliJ IDEA to run the GUI.

Contributors

• Author Name: Muhammad Ahmad Shafique
• Course: Database Systems