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


Ignore:
Timestamp:
01/29/25 23:47:38 (3 weeks ago)
Author:
221550
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

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

    v1 v2  
    99=== Табели
    1010
    11 ''Client''  (**__telephone_number__**, name, last_name)\\
    12 ''Motorcycle''  (**__registration__**, chassis_number, year, model, **telephone_number** *Client)\\
    13 ''Service''  (**__service_id__**, service_date, total_price, **telephone_number** *Client, **registration** *Motorcycle)\\
    14 ''Mechanic''  (**__embg__**, name, last_name)\\
    15 ''Service_Mechanic''  (**__service_id *Service, mechanic_embg *Mechanic__**)\\
    16 ''Item''  (**__item_id__**, price, tax, description, unit_of_measurement)\\
    17 ''Service_Item''  (**__service_id *Service, item_id *Item __**, number_of_units, total_price)\\
    18 ''!ServiceInvoice''  (**__invoice_number__**, date, photo_copy, **recipient** *Client, **service_id** *Service)\\
    19 ''!FiscalBill''  (**__fiscal_id__**, datetime, total_price, ddv, edb, description, **issuer** *Client, **invoice_number** *ServiceInvoice)\\
    20 
    21 
     11''Clients'' (**__Id__**, !FirstName, !LastName, !PhoneNumber)\\
     12''Motorcycles'' (**__Id__**, Model, Year, !ChassisNumber, Registration, Kilometers, '''!ClientId''' *Clients)\\
     13''Mechanics'' (**__EMBG__**, !FirstName, !LastName)\\
     14''!ServiceItems'' (**__Id__**, Description, !MeasurementUnit, !PricePerUnit, Tax)\\
     15''Services'' (**__Id__**, !DateOfService, !LaborCost, !TotalPrice, '''!MotorcycleId''' *Motorcycles)\\
     16''!ServiceAssignments'' (**__ServiceId__** *Services, **__MechanicEMBG__** *Mechanics, **__ServiceItemId__** *!ServiceItems, !UnitsUsed)\\
     17''!ServiceInvoices'' (**__Id__**, !IssueDate, !ImagePath, '''!ServiceId''' *Services)\\
     18''!FiscalBills'' (**__Id__**, !IssueDateTime, Description, DDV, EDB, '''!ServiceInvoiceId''' *!ServiceInvoices)\\
    2219
    2320
    … …  
    3128\c motorcycle_service;
    3229
    33 -- Create tables
    34 CREATE TABLE Client (
    35     telephone_number VARCHAR PRIMARY KEY,
    36     name VARCHAR NOT NULL,
    37     last_name VARCHAR NOT NULL
     30CREATE TABLE Clients (
     31    Id SERIAL PRIMARY KEY,
     32    FirstName VARCHAR(50) NOT NULL,
     33    LastName VARCHAR(50),
     34    PhoneNumber VARCHAR(20)
    3835);
    3936
    40 CREATE TABLE Motorcycle (
    41     registration VARCHAR PRIMARY KEY,
    42     chassis_number VARCHAR NOT NULL,
    43     year DATE NOT NULL,
    44     model VARCHAR NOT NULL,
    45     telephone_number VARCHAR NOT NULL,
    46     FOREIGN KEY (telephone_number) REFERENCES Client(telephone_number)
     37CREATE TABLE Mechanics (
     38    EMBG VARCHAR(13) PRIMARY KEY,
     39    FirstName VARCHAR(50) NOT NULL,
     40    LastName VARCHAR(50) NOT NULL
    4741);
    4842
    49 CREATE TABLE Service (
    50     service_id SERIAL PRIMARY KEY,
    51     service_date DATE NOT NULL,
    52     total_price NUMERIC NOT NULL,
    53     telephone_number VARCHAR NOT NULL,
    54     registration VARCHAR NOT NULL,
    55     FOREIGN KEY (telephone_number) REFERENCES Client(telephone_number),
    56     FOREIGN KEY (registration) REFERENCES Motorcycle(registration)
     43CREATE TABLE Motorcycles (
     44    Id SERIAL PRIMARY KEY,
     45    ClientId INT NOT NULL REFERENCES Clients(Id) ON DELETE CASCADE,
     46    Model VARCHAR(50) NOT NULL,
     47    Year INT,
     48    ChassisNumber VARCHAR(50) UNIQUE,
     49    Registration VARCHAR(20),
     50    Kilometers INT
    5751);
    5852
    59 CREATE TABLE Mechanic (
    60     embg NUMERIC PRIMARY KEY,
    61     name VARCHAR NOT NULL,
    62     last_name VARCHAR NOT NULL
     53CREATE TABLE ServiceItems (
     54    Id SERIAL PRIMARY KEY,
     55    Description TEXT NOT NULL,
     56    MeasurementUnit VARCHAR(20) NOT NULL,
     57    PricePerUnit DECIMAL(10,2) NOT NULL,
     58    Tax DECIMAL(5,2) NOT NULL
    6359);
    6460
    65 CREATE TABLE Service_Mechanic (
    66     service_id INTEGER,
    67     mechanic_embg NUMERIC,
    68     PRIMARY KEY (service_id, mechanic_embg),
    69     FOREIGN KEY (service_id) REFERENCES Service(service_id),
    70     FOREIGN KEY (mechanic_embg) REFERENCES Mechanic(embg)
     61-- Price has to be calculated by the program due to PostgreSQL limitations.
     62CREATE TABLE Services (
     63    Id SERIAL PRIMARY KEY,
     64    MotorcycleId INT NOT NULL REFERENCES Motorcycles(Id) ON DELETE CASCADE,
     65    DateOfService DATE NOT NULL,
     66    LaborCost DECIMAL(10,2) NOT NULL,
     67    TotalPrice DECIMAL(10,2) NOT NULL  -- Manually calculated by app
    7168);
    7269
    73 CREATE TABLE ServiceInvoice (
    74     invoice_number SERIAL PRIMARY KEY,
    75     date DATE NOT NULL,
    76     photo_copy VARCHAR NOT NULL,
    77     recipient VARCHAR NOT NULL,
    78     service_id INTEGER UNIQUE NOT NULL,
    79     FOREIGN KEY (recipient) REFERENCES Client(telephone_number),
    80     FOREIGN KEY (service_id) REFERENCES Service(service_id)
     70CREATE TABLE ServiceAssignments (
     71    ServiceId INT REFERENCES Services(Id) ON DELETE CASCADE,
     72    MechanicEMBG VARCHAR(13) REFERENCES Mechanics(EMBG) ON DELETE CASCADE,
     73    ServiceItemId INT REFERENCES ServiceItems(Id) ON DELETE CASCADE,
     74    UnitsUsed DECIMAL(10,2) NOT NULL,
     75    PRIMARY KEY (ServiceId, MechanicEMBG, ServiceItemId)
    8176);
    8277
    83 CREATE TABLE FiscalBill (
    84     fiscal_id SERIAL PRIMARY KEY,
    85     datetime TIMESTAMP NOT NULL,
    86     total_price NUMERIC NOT NULL,
    87     ddv NUMERIC NOT NULL,
    88     edb VARCHAR NOT NULL,
    89     description VARCHAR,
    90     issuer VARCHAR NOT NULL,
    91     invoice_number INTEGER NOT NULL,
    92     FOREIGN KEY (issuer) REFERENCES Client(telephone_number),
    93     FOREIGN KEY (invoice_number) REFERENCES ServiceInvoice(invoice_number)
     78CREATE TABLE ServiceInvoices (
     79    Id SERIAL PRIMARY KEY,
     80    ServiceId INT UNIQUE NOT NULL REFERENCES Services(Id) ON DELETE CASCADE,
     81    IssueDate TIMESTAMP NOT NULL DEFAULT NOW(),
     82    ImagePath TEXT NOT NULL
    9483);
    9584
    96 CREATE TABLE Item (
    97     item_id SERIAL PRIMARY KEY,
    98     price NUMERIC NOT NULL,
    99     tax NUMERIC NOT NULL,
    100     description VARCHAR,
    101     unit_of_measurement VARCHAR
    102 );
    103 
    104 CREATE TABLE Service_Item (
    105     service_id INTEGER,
    106     item_id INTEGER,
    107     number_of_units NUMERIC NOT NULL,
    108     total_price NUMERIC NOT NULL,
    109     PRIMARY KEY (service_id, item_id),
    110     FOREIGN KEY (service_id) REFERENCES Service(service_id),
    111     FOREIGN KEY (item_id) REFERENCES Item(item_id)
     85CREATE TABLE FiscalBills (
     86    Id SERIAL PRIMARY KEY,
     87    ServiceInvoiceId INT UNIQUE NOT NULL REFERENCES ServiceInvoices(Id) ON DELETE CASCADE,
     88    IssueDateTime TIMESTAMP NOT NULL DEFAULT NOW(),
     89    Description TEXT,
     90    DDV DECIMAL(5,2) NOT NULL,
     91    EDB VARCHAR(15) NOT NULL
    11292);
    11393}}}
    … …  
    11797{{{
    11898-- Insert Clients
    119 INSERT INTO Client (telephone_number, name, last_name) VALUES
    120     ('+38970123456', 'John', 'Doe'),
    121     ('+38971234567', 'Jane', 'Smith'),
    122     ('+38972345678', 'Bob', 'Johnson');
     99INSERT INTO Clients (FirstName, LastName, PhoneNumber)
     100VALUES
     101    ('John', 'Doe', '555-1234'),
     102    ('Jane', 'Smith', '555-5678');
     103
     104-- Insert Mechanics
     105INSERT INTO Mechanics (EMBG, FirstName, LastName)
     106VALUES
     107    ('1234567890123', 'Mike', 'Johnson'),
     108    ('9876543210987', 'Sarah', 'Williams');
    123109
    124110-- Insert Motorcycles
    125 INSERT INTO Motorcycle (registration, chassis_number, year, model, telephone_number) VALUES
    126     ('SK-1234-AB', 'CH123456789', '2020-01-01', 'Honda CBR600RR', '+38970123456'),
    127     ('SK-5678-CD', 'CH987654321', '2019-01-01', 'Yamaha R1', '+38971234567'),
    128     ('SK-9012-EF', 'CH456789123', '2021-01-01', 'Kawasaki Ninja', '+38970123456');
     111INSERT INTO Motorcycles (ClientId, Model, Year, ChassisNumber, Registration, Kilometers)
     112VALUES
     113    (1, 'Harley-Davidson Street Glide', 2020, '1HDRS12345', 'ABC-123', 15000),
     114    (2, 'Honda CBR600RR', 2022, '2HNDR67890', 'XYZ-789', 5000);
    129115
    130 -- Insert Mechanics
    131 INSERT INTO Mechanic (embg, name, last_name) VALUES
    132     (1234567890123, 'Mike', 'Wilson'),
    133     (2345678901234, 'Sarah', 'Brown'),
    134     (3456789012345, 'Tom', 'Davis');
     116-- Insert Service Items
     117INSERT INTO ServiceItems (Description, MeasurementUnit, PricePerUnit, Tax)
     118VALUES
     119    ('Engine Oil', 'Liter', 10.50, 0.18),
     120    ('Oil Filter', 'Piece', 15.00, 0.18),
     121    ('Brake Pads', 'Set', 45.00, 0.18);
    135122
    136 -- Insert Items
    137 INSERT INTO Item (item_id, description, unit_of_measurement, price, tax) VALUES
    138     (1, 'Oil Filter', 'piece', 15.00, 1.50),
    139     (2, 'Engine Oil', 'liter', 10.00, 1.00),
    140     (3, 'Air Filter', 'piece', 25.00, 2.50);
     123-- Insert Services (with app-calculated totals)
     124INSERT INTO Services (MotorcycleId, DateOfService, LaborCost, TotalPrice)
     125VALUES
     126    (1, '2023-10-01', 50.00, 111.07),  -- Calculated by app: (3.5*10.50*1.18) + (1*15.00*1.18) + 50
     127    (2, '2023-10-02', 75.00, 131.40);  -- Calculated by app: (2*45.00*1.18) + 75
    141128
    142 -- Insert Services
    143 INSERT INTO Service (service_date, total_price, telephone_number, registration) VALUES
    144     ('2024-01-05', 150.00, '+38970123456', 'SK-1234-AB'),
    145     ('2024-01-06', 200.00, '+38971234567', 'SK-5678-CD'),
    146     ('2024-01-07', 175.00, '+38970123456', 'SK-9012-EF');
     129-- Insert Service Assignments
     130INSERT INTO ServiceAssignments (ServiceId, MechanicEMBG, ServiceItemId, UnitsUsed)
     131VALUES
     132    (1, '1234567890123', 1, 3.5),
     133    (1, '1234567890123', 2, 1.0),
     134    (2, '9876543210987', 3, 2.0);
    147135
    148 -- Insert Service_Mechanic relationships
    149 INSERT INTO Service_Mechanic (service_id, mechanic_embg) VALUES
    150     (1, 1234567890123),
    151     (1, 2345678901234),
    152     (2, 2345678901234),
    153     (3, 3456789012345);
     136-- Insert Service Invoices
     137INSERT INTO ServiceInvoices (ServiceId, ImagePath)
     138VALUES
     139    (1, '/invoices/service1.png'),
     140    (2, '/invoices/service2.png');
    154141
    155 -- Insert Service_Item relationships
    156 INSERT INTO Service_Item (service_id, item_id, number_of_units, total_price) VALUES
    157     (1, 1, 1, 15.00),
    158     (1, 2, 4, 40.00),
    159     (2, 3, 1, 25.00),
    160     (2, 2, 5, 50.00),
    161     (3, 1, 1, 15.00);
     142-- Insert Fiscal Bills
     143INSERT INTO FiscalBills (ServiceInvoiceId, Description, DDV, EDB)
     144VALUES
     145    (1, 'Full service package', 0.18, '123456789012345'),
     146    (2, 'Brake system maintenance', 0.18, '123456789012345');
     147}}}
    162148
    163 -- Insert ServiceInvoices
    164 INSERT INTO ServiceInvoice (date, photo_copy, recipient, service_id) VALUES
    165     ('2024-01-05', 'invoice1.pdf', '+38970123456', 1),
    166     ('2024-01-06', 'invoice2.pdf', '+38971234567', 2),
    167     ('2024-01-07', 'invoice3.pdf', '+38970123456', 3);
    168149
    169 -- Insert FiscalBills
    170 INSERT INTO FiscalBill (datetime, issuer, description, total_price, ddv, edb, invoice_number) VALUES
    171     ('2024-01-05 14:30:00', '+38970123456', 'Regular service', 150.00, 27.00, 'EDB123456', 1),
    172     ('2024-01-06 15:45:00', '+38971234567', 'Full service', 200.00, 36.00, 'EDB234567', 2),
    173     ('2024-01-07 16:15:00', '+38970123456', 'Oil change', 175.00, 31.50, 'EDB345678', 3);
    174 }}}