🚘 CoreTech AutoCare – PL/SQL Database Schema & Procedures

📑 Overview

This Oracle SQL-based database system supports the operations of a vehicle maintenance service company. It includes robust functionality to manage customers, vehicles, appointments, inventory, system users, and reporting dashboards.

---

🔄 Sequences

CREATE SEQUENCE seq_user START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE seq_customer START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE seq_vehicle START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE seq_mechanic START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE seq_appointment START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE seq_invoice START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE seq_payment START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE seq_log START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE seq_item START WITH 1 INCREMENT BY 1;

---

👥 SystemUser Table & Procedures

Table Structure

SystemUser(
  UserID INT PRIMARY KEY,
  UserName VARCHAR2(50),
  PhoneNo NUMBER(10),
  EmailID VARCHAR2(100),
  Password VARCHAR2(255),
  Role VARCHAR2(20) CHECK (Role IN ('Admin', 'Sales Representative'))
)

Procedures

• GET_USER_PASSWORD_ROLE
• ADD_SYSTEM_USER
• UPDATE_USER_BY_USERNAME
• DELETE_USER_BY_USERNAME
• GET_ALL_SYSTEM_USERS

---

👤 Customer Table & Procedures

Table Structure

Customer(
  CustomerID INT PRIMARY KEY,
  CustomerName VARCHAR2(50),
  PhoneNo NUMBER(10),
  EmailID VARCHAR2(100) UNIQUE,
  Address VARCHAR2(255)
)

Procedures

• InsertCustomer
• UpdateCustomer
• DeleteCustomer
• SearchCustomer

---

🚗 Vehicle Table & Procedures

Table Structure

Vehicle(
  VehicleID INT PRIMARY KEY,
  CustomerID INT,
  Make VARCHAR2(100),
  Model VARCHAR2(100),
  Year NUMBER(4),
  VIN CHAR(17) UNIQUE,
  ServiceHistory VARCHAR2(255),
  FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
)

Procedures

• InsertVehicle
• UpdateVehicle
• DeleteVehicleByVIN
• GetVehicleByVIN
• GetVehicleByID
• GetVehiclesByEmail
• GetAllVehicles

Functions

• GetCustomerIdByEmail
• GetEmailByCustomerId

---

🧑‍🔧 Mechanic Table & Procedures

Table Structure

Mechanic(
  MechanicID INT PRIMARY KEY,
  MechanicName VARCHAR2(50),
  Expertise VARCHAR2(255),
  PhoneNo NUMBER(10),
  EmailID VARCHAR2(100) UNIQUE
)

Procedures

• InsertSampleMechanics
• get_all_mechanics

---

📅 ServiceAppointment Table & Procedures

Table Structure

ServiceAppointment(
  AppointmentID INT PRIMARY KEY,
  VehicleID INT,
  MechanicID INT,
  UserID INT,
  ServiceType VARCHAR2(100),
  ServiceDate DATE,
  ServiceStatus VARCHAR2(20),
  FOREIGN KEY (VehicleID) REFERENCES Vehicle(VehicleID),
  FOREIGN KEY (MechanicID) REFERENCES Mechanic(MechanicID),
  FOREIGN KEY (UserID) REFERENCES SystemUser(UserID)
)

Procedures

• InsertAppointment
• GetAllAppointments
• Get_All_ServiceAppointments
• update_appointment_by_id
• delete_appointment_by_id
• get_appointment_by_id
• GetVehicleIdByVIN

View

• AppointmentDashboardView

---

🧾 Invoice & Payment Tables

Invoice Table

Invoice(
  InvoiceID INT PRIMARY KEY,
  AppointmentID INT,
  Amount NUMBER(8,2),
  PaymentStatus VARCHAR2(20),
  GeneratedDate DATE,
  FOREIGN KEY (AppointmentID) REFERENCES ServiceAppointment(AppointmentID)
)

Payment Table

Payment(
  PaymentID INT PRIMARY KEY,
  InvoiceID INT,
  PaymentMethod VARCHAR2(20),
  AmountPaid NUMBER(8,2),
  PaymentDate DATE,
  FOREIGN KEY (InvoiceID) REFERENCES Invoice(InvoiceID)
)

View

• FullInvoiceView

Procedure

• GetInvoiceDetails

---

📦 Inventory & ServiceInventory Tables

Inventory Table

Inventory(
  ItemID INT PRIMARY KEY,
  ItemName VARCHAR2(50),
  Quantity INT,
  Price NUMBER(8,2),
  MinStockLevel INT,
  UpdatedDate DATE
)

ServiceInventory Table

ServiceInventory(
  AppointmentID INT,
  ItemID INT,
  Quantity INT DEFAULT 1,
  PRIMARY KEY (AppointmentID, ItemID),
  FOREIGN KEY (AppointmentID) REFERENCES ServiceAppointment(AppointmentID),
  FOREIGN KEY (ItemID) REFERENCES Inventory(ItemID)
)

Procedures

• Add_Service_Inventory
• Get_Inventory_Summary

---

📊 Reports & Dashboard Procedures

Monthly Reports

• Get_Monthly_Service_Report
• GetTodayCustomerCount
• GetUpcomingCustomerToday

Admin Dashboard

• GetDashboardAppointments
• Get_All_ServiceAppointments

---

🧪 Sample Cursor Testing (Inventory Summary)

SET SERVEROUTPUT ON;
DECLARE
    inv_cursor SYS_REFCURSOR;
    v_itemname Inventory.ItemName%TYPE;
    v_quantity Inventory.Quantity%TYPE;
    v_minstock Inventory.MinStockLevel%TYPE;
BEGIN
    Get_Inventory_Summary(inv_cursor);

    LOOP
        FETCH inv_cursor INTO v_itemname, v_quantity, v_minstock;
        EXIT WHEN inv_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Item: ' || v_itemname || ', Qty: ' || v_quantity || ', MinStock: ' || v_minstock);
    END LOOP;

    CLOSE inv_cursor;
END;

---

📌 Notes

• Passwords are encrypted using bcrypt.
• Case-insensitive updates & deletes for usernames.
• Custom exceptions using RAISE_APPLICATION_ERROR are included for more meaningful error handling.
• Modular design allows integration with a JavaFX front-end and Spring Boot backend.

---

---

🧪 Sample Tests for Procedures (Excluding Login)

🔧 Insert Customer

BEGIN
    InsertCustomer('John Doe', '9876543210', '[email protected]', '123 Maple Street');
END;

---

🔄 Update Customer

BEGIN
    UpdateCustomer('John Doe', '9876543210', '[email protected]', '456 Oak Avenue');
END;

---

❌ Delete Customer

BEGIN
    DeleteCustomer('[email protected]');
END;

---

🔍 Search Customer

DECLARE
    v_name VARCHAR2(100);
    v_phone VARCHAR2(20);
    v_address VARCHAR2(255);
BEGIN
    SearchCustomer(v_name, '[email protected]', v_phone, v_address);
    DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
    DBMS_OUTPUT.PUT_LINE('Phone: ' || v_phone);
    DBMS_OUTPUT.PUT_LINE('Address: ' || v_address);
END;

---

🚗 Insert Vehicle

BEGIN
    InsertVehicle(1, 'Toyota', 'Corolla', 2020, '1HGCM82633A123456', 'Oil Change');
END;

---

🧾 Add Service Inventory

BEGIN
    Add_Service_Inventory(1, 1, 2); -- AppointmentID, ItemID, Quantity
END;

---

📅 Insert Appointment

BEGIN
    InsertAppointment(1, 1, 1, 'Engine Check', SYSDATE, 'Scheduled');
END;

---

📊 Get Monthly Service Report

DECLARE
    service_cursor SYS_REFCURSOR;
    v_customer_name Customer.CustomerName%TYPE;
    v_vehicle_name VARCHAR2(200);
    v_service_type ServiceAppointment.ServiceType%TYPE;
BEGIN
    Get_Monthly_Service_Report(service_cursor);

    LOOP
        FETCH service_cursor INTO v_customer_name, v_vehicle_name, v_service_type;
        EXIT WHEN service_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Customer: ' || v_customer_name || ', Vehicle: ' || v_vehicle_name || ', Service: ' || v_service_type);
    END LOOP;

    CLOSE service_cursor;
END;