Changes between Version 2 and Version 3 of dmlScript.sql


Ignore:
Timestamp:
12/21/25 22:25:14 (9 days ago)
Author:
235018
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • dmlScript.sql

    v2 v3  
    11{{{#!sql
    22-- PRODUCT
    3 INSERT INTO product (price, availability, weight, width_x_length_x_depth, aprox_production_time, description, delivery_cost) VALUES
    4 (700, 50, 2.5, '30x20x10', 10, 'Handmade wooden chair with oak wood', 50),
    5 (150, 10, 0.2, '20x20x15', 2, 'Heart crochet', 0),
    6 (199, 100, 0.75, '40x40x1', 14, 'Decorative wall hanging made with beads', 0);
     3INSERT INTO product (product_code, price, availability, weight, width_x_length_x_depth, aprox_production_time, description, delivery_cost) VALUES
     4(00100001, 700, 50, 2.5, '30x20x10', 10, 'Handmade wooden chair with oak wood', 50),
     5(00200001, 150, 10, 0.2, '20x20x15', 2, 'Heart crochet', 0),
     6(00200002, 199, 100, 0.75, '40x40x1', 14, 'Decorative wall hanging made with beads', 0);
    77
    88-- IMAGE
    99INSERT INTO image (product_code, image) VALUES
    10 (1, 'chair.jpg'),
    11 (2, 'crochet.jpg'),
    12 (3, 'wall_hanging.jpg');
     10(00100001, 'chair.jpg'),
     11(00200001, 'crochet-heart-red.jpg'),
     12(00200001, 'crochet-heart-blue.jpg'),
     13(00200002, 'wall_hanging.jpg');
    1314
    1415
    1516-- COLOR
    1617INSERT INTO color (product_code, color) VALUES
    17 (1, 'Brown'),
    18 (2, 'Blue'),
    19 (2, 'Red'),
    20 (2, 'Yellow'),
    21 (2, 'Green'),
    22 (3, 'White and Pink'),
    23 (3, 'Black and Gold'),
    24 (3, 'Orange, Green and Purpule);
     18(00100001, 'Brown'),
     19(00200001, 'Blue'),
     20(00200001, 'Red'),
     21(00200001, 'Yellow'),
     22(00200001, 'Green'),
     23(00200002, 'White and Pink'),
     24(00200002, 'Black and Gold'),
     25(00200002, 'Orange, Green and Purpule);
    2526
    2627
    2728-- STORE
    28 INSERT INTO store (name, date_of_founding, physical_address, store_email, rating) VALUES
    29 ('WoodCraft Skopje', '2015-03-12', 'st.Ilindenska 45, Skopje 1000, Macedonia', 'contact@woodcraft.mk', 4.6),
    30 ('Fox Crochets', '2023-06-01', 'st.Kej Makedonija 12, Ohrid 6000, Macedonia', 'ohrid@woodcraft.mk', 4.8);
     29INSERT INTO store (store_id, name, date_of_founding, physical_address, store_email, rating) VALUES
     30(001, 'WoodCraft Skopje', '2015-03-12', 'st.Ilindenska 45, Skopje 1000, Macedonia', 'contact@woodcraft.mk', 4.6),
     31(002, 'Fox Crochets', '2023-06-01', 'st.Kej Makedonija 12, Ohrid 6000, Macedonia', 'ohrid@woodcraft.mk', 4.8);
    3132
    3233
    3334-- PERSONAL
    34 INSERT INTO personal (ssn, first_name, last_name, email, password) VALUES
     35INSERT INTO personal (ssn, first_name, last_name, email, password) VALUES   
    3536('1234567890123', 'Marko', 'Petrovski', 'marko@woodcraft.mk', 'Havr6njs09$hdgs'),
    3637('9876543210987', 'Antonio', 'Trajkovski', 'antonio@woodcraft.mk', 'GKm78BHs&vJHA'),
    … …  
    4041-- PERMISSIONS
    4142INSERT INTO permissions (personal_SSN, type, authorisation) VALUES
    42 ('1234567890123', 'BOSS', 'FULL'),
    43 ('9876543210987', 'EMPLOYEE', 'LIMITED'),
    44 ('4567891234567', 'BOSS', 'FULL');
     43('1234567890123', 'BOSS', 'admin'),
     44('9876543210987', 'EMPLOYEE', 'M.Petrovski'),
     45('4567891234567', 'BOSS', 'admin');
    4546
    4647
    … …  
    5758
    5859-- CLIENT
    59 INSERT INTO client (first_name, last_name, email, password) VALUES
    60 ('Ivan', 'Stojanov', 'ivan@gmail.com', 'ivanpass'),
    61 ('Marija', 'Kostova', 'marija@yahoo.com', 'marijapass');
     60INSERT INTO client (client_ID, first_name, last_name, email, password) VALUES
     61(1, 'Ivan', 'Stojanov', 'ivan@gmail.com', 'hkh689gvgsh%hd'),     -- hash value of the passwords
     62(2, 'Marija', 'Kostova', 'marija@yahoo.com', 'PJdbbh334$djk-hs'),    -- hash value of the passwords
     63(3, 'Antoneta', 'Mariovska', 'mariovskaantoneta@finki.ukim.mk', '*bxhc6cbsd3@xh');   -- hash value of the password
    6264
    6365
    … …  
    6567INSERT INTO delivery_address (client_ID, address) VALUES
    6668(1, 'st.Partizanska 10, Skopje 1000, Macedonia'),
    67 (2, 'st.Turisticka 5, Bitola 7000, Macedonia');
     69(2, 'st.Turisticka 5, Bitola 7000, Macedonia'),
     70(3, 'st.32 4, s.Cucer-Sandevo, Skopje, Macedonia');
    6871
    6972
    7073-- ORDER
    71 INSERT INTO "order" (order_num, client_ID, quantity, status, last_date_mod, payment_method, discount) VALUES
    72 (1, 1, 2, 'placed order', '2025-12-01 10:15:00', 'credit card ****6750', 0.0),
    73 (2, 1, 2, 'packaging', '2025-12-10 18:00:00', 'PayPal account *******', 0.0),
    74 (3, 2, 1, 'delivered', '2025-12-02 14:30:00', 'cash', 4.0);
     74INSERT INTO "order" (order_num, client_ID, status, last_date_mod, payment_method, discount) VALUES
     75(0022025000001, 1, 'placed order', '2025-12-01 10:15:00', 'credit card ****6750', 0.0),
     76(0022025000002, 1, 'packaging', '2025-12-10 18:00:00', 'PayPal account *******', 0.0),
     77(0012025000001, 2, 'delivered', '2025-12-02 14:30:00', 'cash', 4.0);
    7578
    7679
    7780-- REPORT
    7881INSERT INTO report (date, store_ID, overall_profit, sales_trend, marketing_growth, owner_signature) VALUES
    79 ('2024-11-30 23:59:59', 1, 125000.00, 'Increasing', 'Stable growth', 'M.Petrovski'),
    80 ('2024-11-30 23:59:59', 2, 98000.00, 'Stable', 'Moderate growth', 'S.Vaneva');
     82('2024-11-30 23:59:59', 001, 125000.00, 'Increasing', 'Stable growth', 'M.Petrovski'),
     83('2024-11-30 23:59:59', 002, 98000.00, 'Stable', 'Moderate growth', 'S.Vaneva');
    8184
    8285
    8386-- MONTHLY_PROFIT
    8487INSERT INTO monthly_profit (report_date, store_ID, month_and_year, profit) VALUES
    85 ('2024-11-30 23:59:59', 1, '2024-11-01', 12500.00),
    86 ('2024-11-30 23:59:59', 2, '2024-11-01', 8000.00);
     88('2024-11-30 23:59:59', 001, '2024-11-01', 12500.00),
     89('2024-11-30 23:59:59', 002, '2024-11-01', 8000.00);
    8790
    8891
    8992-- REQUEST
    9093INSERT INTO request (request_num, date_and_time, problem, notes_of_communication, costumer_satisfaction) VALUES
    91 (1, '2024-12-03 11:20:00', 'Late delivery', 'Apologized and offered discount', 4.0),
    92 (2, '2024-12-04 09:10:00', 'Military discount', 'Discount approved', 5.0);
     94(001112025001, '2024-11-03 11:20:00', 'Late delivery', 'Apologized and offered discount', 4.0),
     95(002122025001, '2024-12-04 09:10:00', 'Military discount', 'Discount approved', 5.0);
    9396
    9497
    9598-- MAKES_REQUEST
    9699INSERT INTO makes_request (client_ID, request_num) VALUES
    97 (1, 1),
    98 (2, 2);
     100(3, '002122025001'),
     101(2, '001112025001');
    99102
    100103
    101104-- ANSWERS
    102105INSERT INTO answers (request_num, personal_SSN) VALUES
    103 (1, '4567891234567'),
    104 (2, '1234567890123');
     106('001112025001', '4567891234567'),
     107('002122025001', '1234567890123');
    105108
    106109-- FOR_STORE
    107110INSERT INTO for_store (request_num, store_ID) VALUES
    108 (1, 2),
    109 (2, 1);
     111('002122025001', 002),
     112('001112025001', 001);
    110113
    111114-- REVIEW
    112115INSERT INTO review (order_num, comment, rating, last_mod_date) VALUES
    113 (1, 'Great quality, slightly late delivery', 4.0, '2024-12-05 18:00:00');
     116('0012025000001', 'Great quality, slightly late delivery', 4.0, '2024-12-05 18:00:00');
    114117
    115118
    116119-- CHANGE
    117120INSERT INTO change (date_and_time, product_code, changes) VALUES
    118 ('2024-11-10 09:00:00', 1, 'Updated production time'),
    119 ('2024-11-12 15:30:00', 2, 'Added new color');
     121('2024-11-10 09:00:00', 00100001, 'FROM aprox_production_time=14 TO aprox_production_time=10'),
     122('2024-11-12 15:30:00', 00200001, 'Added new color');
    120123
    121124
    122125-- WORKS_IN_STORE
    123126INSERT INTO works_in_store (personal_SSN, store_ID) VALUES
    124 ('1234567890123', 1),
    125 ('9876543210987', 1),
    126 ('4567891234567', 2);
     127('1234567890123', 001),
     128('9876543210987', 001),
     129('4567891234567', 002);
    127130
    128131
    129132-- WORKED
    130133INSERT INTO worked (personal_SSN, report_date, store_ID, wage, pay_method, total_hours, week) VALUES
    131 ('1234567890123', '2025-11-30 23:59:59', 1, 75, 'hourly', 48, '2025-11-24 - 2025-11-30'),
    132 ('9876543210987', '2025-11-30 23:59:59', 1, 75, 'hourly', 38, '2025-11-24 - 2025-11-30'),
    133 ('4567891234567', '2025-11-30 23:59:59', 2, 450, 'weekly', 52, '2025-11-24 - 2025-11-30');
     134('1234567890123', '2025-11-30 23:59:59', 001, 75, 'hourly', 48, '2025-11-24 - 2025-11-30'),
     135('9876543210987', '2025-11-30 23:59:59', 001, 75, 'hourly', 38, '2025-11-24 - 2025-11-30'),
     136('4567891234567', '2025-11-30 23:59:59', 002, 450, 'weekly', 52, '2025-11-24 - 2025-11-30');
    134137
    135138
    136139-- SELLS
    137140INSERT INTO sells (product_code, store_ID, discount) VALUES
    138 (1, 1, 0.0),
    139 (2, 2, 0.0),
    140 (3, 2, 0.5);
     141(00100001, 001, 0.0),
     142(00200001, 002, 0.0),
     143(00200002, 002, 0.5);
    141144
    142145
    143146-- INCLUDES
    144147INSERT INTO includes (order_num, product_code) VALUES
    145 (1, 1),
    146 (2, 3),
    147 (3, 2);
     148('0012025000001', '00100001'),
     149('0022025000001', '00200002'),
     150('0022025000002', '00200001');
    148151
    149152
    150153-- APPROVES
    151154INSERT INTO approves (boss_SSN, report_date, store_ID, owner_signature) VALUES
    152 ('1234567890123', '2025-12-01 09:56:30', 1, 'M.Petrovski'),
    153 ('4567891234567', '2025-12-03 13:06:12', 2, 'S.Vaneva');
     155('1234567890123', '2025-12-01 09:56:30', 001, 'M.Petrovski'),
     156('4567891234567', '2025-12-03 13:06:12', 002, 'S.Vaneva');
    154157
    155158
    156159-- EXCHANGES_DATE
    157160INSERT INTO exchanges_date (report_date, store_ID, monthly_profit, date, sales, damages) VALUES
    158 ('2024-11-30 23:59:59', 1, 38750.00, '2024-12-01 08:00:00', 52, 750),
    159 ('2024-11-30 23:59:59', 2, 26150, '2024-12-01 08:00:00', 40, NULL);
     161('2024-11-30 23:59:59', 001, 38750.00, '2024-12-01 08:00:00', 52, 750),
     162('2024-11-30 23:59:59', 002, 26150, '2024-12-01 08:00:00', 40, NULL);
    160163}}};