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

Ознаки

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

Табели

Client (telephone_number, name, last_name)
Motorcycle (registration, chassis_number, year, model, telephone_number *Client)
Service (service_id, service_date, total_price, telephone_number *Client, registration *Motorcycle)
Mechanic (embg, name, last_name)
Service_Mechanic (service_id *Service, mechanic_embg *Mechanic)
Item (item_id, price, tax, description, unit_of_measurement)
Service_Item (service_id *Service, item_id *Item , number_of_units, total_price)
ServiceInvoice (invoice_number, date, photo_copy, recipient *Client, service_id *Service)
FiscalBill (fiscal_id, datetime, total_price, ddv, edb, description, issuer *Client, invoice_number *ServiceInvoice)

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

-- Create database
CREATE DATABASE motorcycle_service;

-- Connect to database
\c motorcycle_service;

-- Create tables
CREATE TABLE Client (
    telephone_number VARCHAR PRIMARY KEY,
    name VARCHAR NOT NULL,
    last_name VARCHAR NOT NULL
);

CREATE TABLE Motorcycle (
    registration VARCHAR PRIMARY KEY,
    chassis_number VARCHAR NOT NULL,
    year DATE NOT NULL,
    model VARCHAR NOT NULL,
    telephone_number VARCHAR NOT NULL,
    FOREIGN KEY (telephone_number) REFERENCES Client(telephone_number)
);

CREATE TABLE Service (
    service_id SERIAL PRIMARY KEY,
    service_date DATE NOT NULL,
    total_price NUMERIC NOT NULL,
    telephone_number VARCHAR NOT NULL,
    registration VARCHAR NOT NULL,
    FOREIGN KEY (telephone_number) REFERENCES Client(telephone_number),
    FOREIGN KEY (registration) REFERENCES Motorcycle(registration)
);

CREATE TABLE Mechanic (
    embg NUMERIC PRIMARY KEY,
    name VARCHAR NOT NULL,
    last_name VARCHAR NOT NULL
);

CREATE TABLE Service_Mechanic (
    service_id INTEGER,
    mechanic_embg NUMERIC,
    PRIMARY KEY (service_id, mechanic_embg),
    FOREIGN KEY (service_id) REFERENCES Service(service_id),
    FOREIGN KEY (mechanic_embg) REFERENCES Mechanic(embg)
);

CREATE TABLE ServiceInvoice (
    invoice_number SERIAL PRIMARY KEY,
    date DATE NOT NULL,
    photo_copy VARCHAR NOT NULL,
    recipient VARCHAR NOT NULL,
    service_id INTEGER UNIQUE NOT NULL,
    FOREIGN KEY (recipient) REFERENCES Client(telephone_number),
    FOREIGN KEY (service_id) REFERENCES Service(service_id)
);

CREATE TABLE FiscalBill (
    fiscal_id SERIAL PRIMARY KEY,
    datetime TIMESTAMP NOT NULL,
    total_price NUMERIC NOT NULL,
    ddv NUMERIC NOT NULL,
    edb VARCHAR NOT NULL,
    description VARCHAR,
    issuer VARCHAR NOT NULL,
    invoice_number INTEGER NOT NULL,
    FOREIGN KEY (issuer) REFERENCES Client(telephone_number),
    FOREIGN KEY (invoice_number) REFERENCES ServiceInvoice(invoice_number)
);

CREATE TABLE Item (
    item_id SERIAL PRIMARY KEY,
    price NUMERIC NOT NULL,
    tax NUMERIC NOT NULL,
    description VARCHAR,
    unit_of_measurement VARCHAR
);

CREATE TABLE Service_Item (
    service_id INTEGER,
    item_id INTEGER,
    number_of_units NUMERIC NOT NULL,
    total_price NUMERIC NOT NULL,
    PRIMARY KEY (service_id, item_id),
    FOREIGN KEY (service_id) REFERENCES Service(service_id),
    FOREIGN KEY (item_id) REFERENCES Item(item_id)
);

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

-- Insert Clients
INSERT INTO Client (telephone_number, name, last_name) VALUES
    ('+38970123456', 'John', 'Doe'),
    ('+38971234567', 'Jane', 'Smith'),
    ('+38972345678', 'Bob', 'Johnson');

-- Insert Motorcycles
INSERT INTO Motorcycle (registration, chassis_number, year, model, telephone_number) VALUES
    ('SK-1234-AB', 'CH123456789', '2020-01-01', 'Honda CBR600RR', '+38970123456'),
    ('SK-5678-CD', 'CH987654321', '2019-01-01', 'Yamaha R1', '+38971234567'),
    ('SK-9012-EF', 'CH456789123', '2021-01-01', 'Kawasaki Ninja', '+38970123456');

-- Insert Mechanics
INSERT INTO Mechanic (embg, name, last_name) VALUES
    (1234567890123, 'Mike', 'Wilson'),
    (2345678901234, 'Sarah', 'Brown'),
    (3456789012345, 'Tom', 'Davis');

-- Insert Items
INSERT INTO Item (item_id, description, unit_of_measurement, price, tax) VALUES
    (1, 'Oil Filter', 'piece', 15.00, 1.50),
    (2, 'Engine Oil', 'liter', 10.00, 1.00),
    (3, 'Air Filter', 'piece', 25.00, 2.50);

-- Insert Services
INSERT INTO Service (service_date, total_price, telephone_number, registration) VALUES
    ('2024-01-05', 150.00, '+38970123456', 'SK-1234-AB'),
    ('2024-01-06', 200.00, '+38971234567', 'SK-5678-CD'),
    ('2024-01-07', 175.00, '+38970123456', 'SK-9012-EF');

-- Insert Service_Mechanic relationships
INSERT INTO Service_Mechanic (service_id, mechanic_embg) VALUES
    (1, 1234567890123),
    (1, 2345678901234),
    (2, 2345678901234),
    (3, 3456789012345);

-- Insert Service_Item relationships
INSERT INTO Service_Item (service_id, item_id, number_of_units, total_price) VALUES
    (1, 1, 1, 15.00),
    (1, 2, 4, 40.00),
    (2, 3, 1, 25.00),
    (2, 2, 5, 50.00),
    (3, 1, 1, 15.00);

-- Insert ServiceInvoices
INSERT INTO ServiceInvoice (date, photo_copy, recipient, service_id) VALUES
    ('2024-01-05', 'invoice1.pdf', '+38970123456', 1),
    ('2024-01-06', 'invoice2.pdf', '+38971234567', 2),
    ('2024-01-07', 'invoice3.pdf', '+38970123456', 3);

-- Insert FiscalBills
INSERT INTO FiscalBill (datetime, issuer, description, total_price, ddv, edb, invoice_number) VALUES
    ('2024-01-05 14:30:00', '+38970123456', 'Regular service', 150.00, 27.00, 'EDB123456', 1),
    ('2024-01-06 15:45:00', '+38971234567', 'Full service', 200.00, 36.00, 'EDB234567', 2),
    ('2024-01-07 16:15:00', '+38970123456', 'Oil change', 175.00, 31.50, 'EDB345678', 3);