Erd For project Sql

# Enterprise Management System Database

## Car Rental System Documentation

---

# 1. Project Overview

This project is a **Car Rental Management System Database** designed for a medium-sized enterprise that manages car rentals, customers, employees, reservations, payments, maintenance, and insurance.

The system is implemented using SQL Server and demonstrates full database lifecycle design including:

* ERD design

* Relational schema mapping

* Database implementation

* Advanced SQL queries

* Stored procedures, triggers, and functions

---

# 2. Objectives

The main objectives of this system are:

* Manage car rental operations efficiently

* Store and organize customer and employee data

* Track reservations, rentals, and payments

* Handle vehicle maintenance and insurance records

* Provide business insights using SQL queries

* Ensure data integrity using constraints and relationships

---

# 3. Database Design (ERD Summary)

The system consists of the following entities:

### Main Entities:

* Branch

* CarCategory

* Customer

* Employee

* Car

* Reservation

* Rental

* Payment

* Maintenance

* Insurance

---

# 4. Relationships

The system includes:

### One-to-Many Relationships:

* Branch → Employee

* Branch → Car

* Customer → Rental

* Car → Rental

### Many-to-Many (resolved via Rental):

* Customer ↔ Car (through Rental table)

### One-to-One (logical relationship):

* Rental → Payment

---

# 5. Tables Structure Summary

Each table includes:

* Primary Keys (PK)

* Foreign Keys (FK)

* Constraints (NOT NULL, CHECK where needed)

### Example:

**Car Table:**

* CarID (PK)

* PlateNumber

* Brand

* Model

* PricePerDay

* Status

* BranchID (FK)

* CategoryID (FK)

---

# 6. Normalization

The database is normalized up to **3NF**:

### 1NF:

* All attributes are atomic

### 2NF:

* No partial dependency (composite keys handled properly)

### 3NF:

* No transitive dependencies

* Data is stored in separate related tables

---

# 7. Data Insertion

The system includes realistic sample data:

* 5 Branches

* 5 Car Categories

* 30 Customers

* 30 Employees

* 30 Cars

* 30 Reservations

* 30 Rentals

* 30 Payments

* 30 Maintenance records

* 30 Insurance records

---

# 8. SQL Features Implemented

## 8.1 Basic SQL

* CREATE DATABASE

* CREATE TABLE

* INSERT INTO

## 8.2 Constraints

* PRIMARY KEY

* FOREIGN KEY

* NOT NULL

* CHECK (optional improvement)

---

# 9. Advanced SQL Queries

## 9.1 JOIN Queries

* Customer + Rental + Car

* Branch + Car + Rental

* Customer + Payment + Rental

* Car + Category + Rental

* Employee + Rental + Car

## 9.2 Aggregate Functions

* SUM (Total Revenue)

* AVG (Average rental cost)

* COUNT (Number of rentals per customer)

* GROUP BY & HAVING

## 9.3 Subqueries

* Highest rental cost

* Customers above average rental cost

* Count rentals per customer

## 9.4 Correlated Subquery

* Each customer with number of rentals

---

# 10. Views

A view was created to simplify reporting:

### RentalDetails View

Shows:

* Customer name

* Car brand

* Rental dates

* Total cost

---

# 11. Stored Procedures

### RentCar1 Procedure

Used to insert a new rental record automatically.

---

# 12. Triggers

### Trigger 1: UpdateCarStatus

* Automatically updates car status to "Rented" when a rental is created

### Trigger 2: CalculateTotalCost

* Automatically calculates rental cost based on duration and price per day

---

# 13. Functions

### CalculateRentalCost Function

* Calculates total rental cost based on:

* Start date

* End date

* Price per day

---

# 14. Transactions

A transaction was implemented to ensure:

* Rental insertion

* Payment insertion

* Rollback in case of error

This ensures **data consistency and integrity**.

---

# 15. Indexing (Performance Optimization)

Indexes improve query performance:

* Index on Rental(CustomerID)

* Index on Payment(RentalID)

---

# 16. Business Insights

The system can generate:

* Total revenue from payments

* Most active customers

* Most rented cars

* Branch performance comparison

---

# 17. Conclusion

The Car Rental Database System is a fully functional relational database that demonstrates:

* Real-world business modeling

* Strong relational design

* Advanced SQL implementation

* Automation using triggers and procedures

It is suitable for enterprise-level car rental management systems.