Changes between Initial Version and Version 1 of ЛогичкиФизичкиДизајн

Timestamp:

01/08/25 23:10:31 (6 days ago)

Author:

221550

Comment:

--

ЛогичкиФизичкиДизајн

@@ +1 @@
+== Релациска шема (со мапирачка трансформација)
+
+=== Ознаки
+
+ - Примарните клучеви се означени со 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);
+}}}