Релациска шема (со мапирачка трансформација)

Ознаки

• Примарните клучеви се означени со bold и underline
• Надворешните клучеви се означени со *

Табели

Clients (Id, FirstName, LastName, PhoneNumber)
Motorcycles (Id, Model, Year, ChassisNumber, Registration, Kilometers, ClientId *Clients)
Mechanics (EMBG, FirstName, LastName)
ServiceItems (Id, Description, MeasurementUnit, PricePerUnit, Tax)
Services (Id, DateOfService, LaborCost, TotalPrice, MotorcycleId *Motorcycles)
ServiceAssignments (ServiceId *Services, MechanicEMBG *Mechanics, ServiceItemId *ServiceItems, UnitsUsed)
ServiceInvoices (Id, IssueDate, ImagePath, ServiceId *Services)
FiscalBills (Id, IssueDateTime, Description, DDV, EDB, ServiceInvoiceId *ServiceInvoices)

DDL скрипта за бришење на табелите и креирање на табелите

-- Create database
CREATE DATABASE motorcycle_service;

-- Connect to database
\c motorcycle_service;

CREATE TABLE Clients (
    Id SERIAL PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50),
    PhoneNumber VARCHAR(20)
);

CREATE TABLE Mechanics (
    EMBG VARCHAR(13) PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL
);

CREATE TABLE Motorcycles (
    Id SERIAL PRIMARY KEY,
    ClientId INT NOT NULL REFERENCES Clients(Id) ON DELETE CASCADE,
    Model VARCHAR(50) NOT NULL,
    Year INT,
    ChassisNumber VARCHAR(50) UNIQUE,
    Registration VARCHAR(20),
    Kilometers INT
);

CREATE TABLE ServiceItems (
    Id SERIAL PRIMARY KEY,
    Description TEXT NOT NULL,
    MeasurementUnit VARCHAR(20) NOT NULL,
    PricePerUnit DECIMAL(10,2) NOT NULL,
    Tax DECIMAL(5,2) NOT NULL
);

-- Price has to be calculated by the program due to PostgreSQL limitations.
CREATE TABLE Services (
    Id SERIAL PRIMARY KEY,
    MotorcycleId INT NOT NULL REFERENCES Motorcycles(Id) ON DELETE CASCADE,
    DateOfService DATE NOT NULL,
    LaborCost DECIMAL(10,2) NOT NULL,
    TotalPrice DECIMAL(10,2) NOT NULL  -- Manually calculated by app
);

CREATE TABLE ServiceAssignments (
    ServiceId INT REFERENCES Services(Id) ON DELETE CASCADE,
    MechanicEMBG VARCHAR(13) REFERENCES Mechanics(EMBG) ON DELETE CASCADE,
    ServiceItemId INT REFERENCES ServiceItems(Id) ON DELETE CASCADE,
    UnitsUsed DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (ServiceId, MechanicEMBG, ServiceItemId)
);

CREATE TABLE ServiceInvoices (
    Id SERIAL PRIMARY KEY,
    ServiceId INT UNIQUE NOT NULL REFERENCES Services(Id) ON DELETE CASCADE,
    IssueDate TIMESTAMP NOT NULL DEFAULT NOW(),
    ImagePath TEXT NOT NULL
);

CREATE TABLE FiscalBills (
    Id SERIAL PRIMARY KEY,
    ServiceInvoiceId INT UNIQUE NOT NULL REFERENCES ServiceInvoices(Id) ON DELETE CASCADE,
    IssueDateTime TIMESTAMP NOT NULL DEFAULT NOW(),
    Description TEXT,
    DDV DECIMAL(5,2) NOT NULL,
    EDB VARCHAR(15) NOT NULL
);

DML скрипта за полнење на табелите со податоци

-- Insert Clients
INSERT INTO Clients (FirstName, LastName, PhoneNumber)
VALUES 
    ('John', 'Doe', '555-1234'),
    ('Jane', 'Smith', '555-5678');

-- Insert Mechanics
INSERT INTO Mechanics (EMBG, FirstName, LastName)
VALUES 
    ('1234567890123', 'Mike', 'Johnson'),
    ('9876543210987', 'Sarah', 'Williams');

-- Insert Motorcycles
INSERT INTO Motorcycles (ClientId, Model, Year, ChassisNumber, Registration, Kilometers)
VALUES 
    (1, 'Harley-Davidson Street Glide', 2020, '1HDRS12345', 'ABC-123', 15000),
    (2, 'Honda CBR600RR', 2022, '2HNDR67890', 'XYZ-789', 5000);

-- Insert Service Items
INSERT INTO ServiceItems (Description, MeasurementUnit, PricePerUnit, Tax)
VALUES 
    ('Engine Oil', 'Liter', 10.50, 0.18),
    ('Oil Filter', 'Piece', 15.00, 0.18),
    ('Brake Pads', 'Set', 45.00, 0.18);

-- Insert Services (with app-calculated totals)
INSERT INTO Services (MotorcycleId, DateOfService, LaborCost, TotalPrice)
VALUES 
    (1, '2023-10-01', 50.00, 111.07),  -- Calculated by app: (3.5*10.50*1.18) + (1*15.00*1.18) + 50
    (2, '2023-10-02', 75.00, 131.40);  -- Calculated by app: (2*45.00*1.18) + 75

-- Insert Service Assignments
INSERT INTO ServiceAssignments (ServiceId, MechanicEMBG, ServiceItemId, UnitsUsed)
VALUES 
    (1, '1234567890123', 1, 3.5),
    (1, '1234567890123', 2, 1.0),
    (2, '9876543210987', 3, 2.0);

-- Insert Service Invoices
INSERT INTO ServiceInvoices (ServiceId, ImagePath)
VALUES 
    (1, '/invoices/service1.png'),
    (2, '/invoices/service2.png');

-- Insert Fiscal Bills
INSERT INTO FiscalBills (ServiceInvoiceId, Description, DDV, EDB)
VALUES 
    (1, 'Full service package', 0.18, '123456789012345'),
    (2, 'Brake system maintenance', 0.18, '123456789012345');