SQL & DDL: Truck Simulator DDL.2.txt

-- Customer
CREATE TABLE Customer (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    CustomerSurName VARCHAR(100),
    Email VARCHAR(100),
    Address VARCHAR(255),
    CustomerContact VARCHAR(20)
);

-- Employee
CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(100),
    EmployeeSurName VARCHAR(100),
    Position VARCHAR(100),
    Department VARCHAR(100)
);

-- Product
CREATE TABLE Product (
    ProductID INT PRIMARY KEY,
    Model VARCHAR(100),
    Price DECIMAL(10,2),
    LicensePlate VARCHAR(50),
    Status ENUM('available', 'sold', 'rented') NOT NULL
);

-- Truck
CREATE TABLE Truck (
    ProductID INT PRIMARY KEY,
    HP INT,
    FOREIGN KEY (ProductID) REFERENCES Product(ProductID)
);

-- Trailer
CREATE TABLE Trailer (
    ProductID INT PRIMARY KEY,
    Capacity DECIMAL(10,2),
    FOREIGN KEY (ProductID) REFERENCES Product(ProductID)
);

-- Procurement
CREATE TABLE Procurement (
    TransactionID INT PRIMARY KEY,
    EmployeeID INT,
    CustomerID INT,
    ProductID INT,
    ProcurementDate DATE,
    Quantity INT,
    FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID),
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),
    FOREIGN KEY (ProductID) REFERENCES Product(ProductID)
);

-- T_Type
CREATE TABLE T_Type (
    TransactionID INT PRIMARY KEY,
    Type ENUM('Rent', 'Buy') NOT NULL,
    Duration INT, -- за rent
    MonthlyPay DECIMAL(10,2), -- за rent
    TotalPrice DECIMAL(10,2), -- за buy
    FOREIGN KEY (TransactionID) REFERENCES Procurement(TransactionID)
);

-- Customer Feedback
CREATE TABLE CustomerFeedback (
    FeedbackID INT PRIMARY KEY,
    CustomerID INT,
    ProductID INT,
    Rating INT CHECK (Rating BETWEEN 1 AND 5),
    Comment TEXT,
    FeedbackDate DATE,
    TransactionID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),
    FOREIGN KEY (ProductID) REFERENCES Product(ProductID),
    FOREIGN KEY (TransactionID) REFERENCES Procurement(TransactionID)
);

-- Maintenance
CREATE TABLE Maintenance (
    MainID INT PRIMARY KEY,
    EmployeeID INT,
    ProductID INT,
    MainDate DATE,
    FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID),
    FOREIGN KEY (ProductID) REFERENCES Product(ProductID)
);

-- Views (Customer views product)
CREATE TABLE Views (
    CustomerID INT,
    ProductID INT,
    PRIMARY KEY (CustomerID, ProductID),
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),
    FOREIGN KEY (ProductID) REFERENCES Product(ProductID)
);