Changes between Version 1 and Version 2 of dmlScript-with-help-of-AI.sql


Ignore:
Timestamp:
12/30/25 14:12:13 (15 hours ago)
Author:
235018
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • dmlScript-with-help-of-AI.sql

    v1 v2  
    11{{{#!sql
    22-- CATEGORY
    3 INSERT INTO category (name, parent_category_id) VALUES
    4 ('Furniture', NULL),
    5 ('Home Decor', NULL),
    6 ('Gifts', NULL),
    7 ('Chairs', 1),
    8 ('Tables', 1),
    9 ('Wall Decor', 2),
    10 ('Textiles', 3),
    11 ('Crochet', 7),
    12 ('Beadwork', 7);
     3INSERT INTO category (id, name, parent_category_id) VALUES
     4(1, 'Furniture', NULL),
     5(2, 'Home Decor', NULL),
     6(3, 'Gifts', NULL),
     7(4, 'Chairs', 1),
     8(5, 'Tables', 1),
     9(6, 'Wall Decor', 2),
     10(7, 'Textiles', 3),
     11(8, 'Crochet', 3),
     12(9, 'Beadwork', 3);
     13
     14
     15-- STORE
     16INSERT INTO store (store_id, name, date_of_founding, physical_address, store_email, rating) VALUES
     17('001', 'WoodCraft Skopje', '2015-03-12', 'st.Ilindenska 45, Skopje 1000, Macedonia', 'contact@woodcraft.mk', 4.6),
     18('002', 'Fox Crochets', '2023-06-01', 'st.Kej Makedonija 12, Ohrid 6000, Macedonia', 'ohrid@foxcrochets.mk', 4.8),
     19('003', 'Artisan Collective', '2020-01-15', 'st.Goce Delcev 78, Bitola 7000, Macedonia', 'info@artisancollective.mk', 4.5);
     20
    1321
    1422-- PRODUCT
    15 INSERT INTO product (code, price, availability, weight, width_x_length_x_depth, aprox_production_time, description, cathegory_id) VALUES
     23INSERT INTO product (code, price, availability, weight, width_x_length_x_depth, aprox_production_time, description, category_id) VALUES
    1624('00100001', 700.00, 50, 2.50, '30x20x10', 10, 'Handmade wooden chair with oak wood', 4),
    1725('00200001', 150.00, 10, 0.20, '20x20x15', 2, 'Heart crochet decoration', 8),
    1826('00200002', 199.00, 100, 0.75, '40x40x1', 14, 'Decorative wall hanging made with beads', 9),
    19 ('00100002', 1200.00, 15, 15.00, '180x90x75', 21, 'Solid oak dining table', 5),
     27('00100002', 1200.00, 15, 10.00, '180x90x75', 21, 'Solid oak dining table', 5),
    2028('00200003', 89.99, 25, 0.50, '60x30x2', 7, 'Macrame wall hanging', 6);
    2129
    … …  
    4957('00200003', 'White');
    5058
    51 -- STORE
    52 INSERT INTO store (store_id, name, date_of_founding, physical_address, store_email, rating) VALUES
    53 ('001', 'WoodCraft Skopje', '2015-03-12', 'st.Ilindenska 45, Skopje 1000, Macedonia', 'contact@woodcraft.mk', 4.6),
    54 ('002', 'Fox Crochets', '2023-06-01', 'st.Kej Makedonija 12, Ohrid 6000, Macedonia', 'ohrid@foxcrochets.mk', 4.8),
    55 ('003', 'Artisan Collective', '2020-01-15', 'st.Goce Delcev 78, Bitola 7000, Macedonia', 'info@artisancollective.mk', 4.5);
    5659
    5760-- PERSONAL
    58 INSERT INTO personal (ssn, first_name, last_name, email, password) VALUES   
    59 ('1234567890123', 'Marko', 'Petrovski', 'marko@woodcraft.mk', 'Havr6njs09$hdgs'),
    60 ('9876543210987', 'Antonio', 'Trajkovski', 'antonio@woodcraft.mk', 'GKm78BHs&vJHA'),
    61 ('4567891234567', 'Sara', 'Vaneva', 's.vaneva@foxcrochets.mk', 'hY69Btvhs90'),
    62 ('7891234567890', 'Elena', 'Nikolovska', 'elena@artisancollective.mk', 'Pp45@kjsdD'),
    63 ('3216549870123', 'Dimitar', 'Risteski', 'dimitar@foxcrochets.mk', 'Ls92#hjsDf');
     61INSERT INTO personal (id, first_name, last_name, ssn, email, password) VALUES   
     62('0010001', 'Marko', 'Petrovski', '1234567890123', 'marko@woodcraft.mk', 'Havr6njs09$hdgs'),
     63('0010002', 'Antonio', 'Trajkovski', '9876543210987', 'antonio@woodcraft.mk', 'GKm78BHs&vJHA'),
     64('0020001', 'Sara', 'Vaneva', '4567891234567', 's.vaneva@foxcrochets.mk', 'hY69Btvhs90'),
     65('0020002', 'Elena', 'Nikolovska', '7891234567890', 'elena@artisancollective.mk', 'Pp45@kjsdD'),
     66('0030001',  'Dimitar', 'Risteski', '3216549870123','dimitar@foxcrochets.mk', 'Ls92#hjsDf');
    6467
    6568-- PERMISSIONS
    66 INSERT INTO permissions (personal_SSN, type, authorisation) VALUES
    67 ('1234567890123', 'BOSS', 'admin'),
    68 ('9876543210987', 'EMPLOYEE', '1234567890123'),
    69 ('4567891234567', 'BOSS', 'admin'),
    70 ('7891234567890', 'MANAGER', '3216549870123'),
    71 ('3216549870123', 'BOSS', 'admin');
     69INSERT INTO permissions (personal_ID, type, authorisation) VALUES
     70('0010001', 'BOSS', 'admin'),
     71('0010002', 'EMPLOYEE', 'M.Petrovski'),
     72('0020001', 'BOSS', 'admin'),
     73('0020002', 'MANAGER', 'S.Vaneva'),
     74('0030001', 'BOSS', 'admin');
    7275
    7376-- BOSS
    74 INSERT INTO boss (boss_SSN) VALUES
    75 ('1234567890123'),
    76 ('4567891234567'),
    77 ('3216549870123');
     77INSERT INTO boss (boss_ID, signature) VALUES
     78('0010001', 'M.Petrovski'),
     79('0020001', 'S.Vaneva'),
     80('0030001', 'D.Ristevski');
    7881
    7982-- EMPLOYEES
    80 INSERT INTO employees (employee_SSN, date_of_hire) VALUES
    81 ('9876543210987', '2019-09-01'),
    82 ('7891234567890', '2020-02-15');
     83INSERT INTO employees (employee_ID, date_of_hire) VALUES
     84('0010002', '2021-09-01'),
     85('0020002', '2023-02-15');
    8386
    8487-- CLIENT
    … …  
    99102(1003, 'Campus Dormitory, Room 305', 'Skopje', '1000', 'Macedonia', FALSE); -- Second address for client 3
    100103
    101 -- ORDER (updated with correct constraints)
     104-- ORDER
    102105INSERT INTO "order" (order_num, client_ID, status, last_date_mod, payment_method, discount) VALUES
    103106('002202500001', 1001, 'placed order', '2025-12-01 10:15:00', 'credit card ****6750', 0.00),
    … …  
    139142
    140143-- ANSWERS
    141 INSERT INTO answers (request_num, personal_SSN) VALUES
    142 ('00111202500101', '4567891234567'),
    143 ('00212202500102', '1234567890123'),
    144 ('00312202500101', '7891234567890'),
    145 ('00112202500201', '9876543210987');
     144INSERT INTO answers (request_num, personal_ID) VALUES
     145('00111202500101', '0010001'),
     146('00212202500102', '0020001'),
     147('00312202500101', '0030001'),
     148('00112202500201', '0010002');
    146149
    147150-- FOR_STORE      (Probably not needed as you can get it from the first 3 digits of the request number)
    … …  
    166169
    167170-- MAKES_CHANGE
    168 INSERT INTO makes_change (personal_SSN, change_date_time, product_code) VALUES
    169 ('1234567890123', '2024-11-10 09:00:00', '00100001'),
    170 ('4567891234567', '2024-11-12 15:30:00', '00200001'),
    171 ('7891234567890', '2024-12-01 11:00:00', '00100002'),
    172 ('9876543210987', '2024-12-05 14:20:00', '00200002');
     171INSERT INTO makes_change (personal_ID, change_date_time, product_code) VALUES
     172('0010002', '2024-11-10 09:00:00', '00100001'),
     173('0020001', '2024-11-12 15:30:00', '00200001'),
     174('0010001', '2024-12-01 11:00:00', '00100002'),
     175('0020001', '2024-12-05 14:20:00', '00200002');
    173176
    174177-- WORKS_IN_STORE
    175 INSERT INTO works_in_store (personal_SSN, store_ID) VALUES
    176 ('1234567890123', '001'),
    177 ('9876543210987', '001'),
    178 ('4567891234567', '002'),
    179 ('7891234567890', '003'),
    180 ('3216549870123', '002'),
    181 ('9876543210987', '003'); -- Employee works in multiple stores, probably need to have employee ID
     178INSERT INTO works_in_store (personal_ID, store_ID) VALUES
     179('0010001', '001'),
     180('0010002', '001'),
     181('0020001', '002'),
     182('0030001', '003'),
     183('0020001', '002');
    182184
    183185-- WORKED
    184186INSERT INTO worked (personal_SSN, report_date, store_ID, wage, pay_method, total_hours, week) VALUES
    185 ('1234567890123', '2024-11-30 23:59:59', '001', 75, 'hourly', 48, '2024-11-24 - 2024-11-30'),
    186 ('9876543210987', '2024-11-30 23:59:59', '001', 75, 'hourly', 38, '2024-11-24 - 2024-11-30'),
    187 ('4567891234567', '2024-11-30 23:59:59', '002', 10000, 'monthly', 52, '2024-11-24 - 2024-11-30'),
    188 ('7891234567890', '2024-11-30 23:59:59', '003', 65, 'hourly', 42, '2024-11-24 - 2024-11-30'),
    189 ('3216549870123', '2024-11-30 23:59:59', '002', 60, 'hourly', 40, '2024-11-24 - 2024-11-30');
     187('0010001', '2024-11-30 23:59:59', '001', 75, 'hourly', 48, '2024-11-24 - 2024-11-30'),
     188('0010002', '2024-11-30 23:59:59', '001', 75, 'hourly', 38, '2024-11-24 - 2024-11-30'),
     189('0020001', '2024-11-30 23:59:59', '002', 10000, 'monthly', 52, '2024-11-24 - 2024-11-30'),
     190('0030001', '2024-11-30 23:59:59', '003', 65, 'hourly', 42, '2024-11-24 - 2024-11-30'),
     191('0020002', '2024-11-30 23:59:59', '002', 60, 'hourly', 40, '2024-11-24 - 2024-11-30');
    190192
    191193-- SELLS
    … …  
    210212
    211213-- APPROVES
    212 INSERT INTO approves (boss_SSN, report_date, store_ID, owner_signature) VALUES
    213 ('1234567890123', '2024-11-30 23:59:59', '001', 'M.Petrovski'),
    214 ('4567891234567', '2024-11-30 23:59:59', '002', 'S.Vaneva'),
    215 ('1234567890123', '2024-12-31 23:59:59', '001', 'M.Petrovski'),
    216 ('4567891234567', '2024-12-31 23:59:59', '002', 'S.Vaneva');
     214INSERT INTO approves (boss_ID, report_date, store_ID, owner_signature) VALUES
     215('0010001', '2024-11-30 23:59:59', '001', 'M.Petrovski'),
     216('0020001', '2024-11-30 23:59:59', '002', 'S.Vaneva'),
     217('0010001', '2024-12-31 23:59:59', '001', 'M.Petrovski'),
     218('0020001', '2024-12-31 23:59:59', '002', 'S.Vaneva');
    217219
    218220-- EXCHANGES_DATA
    219 INSERT INTO exchanges_data (report_date, store_ID, monthly_profit, date, sales, damages) VALUES        -- Report_id???
     221INSERT INTO exchanges_data (report_date, store_ID, monthly_profit, date, sales, damages) VALUES       
    220222('2024-11-30 23:59:59', '001', 38750.00, '2024-11-30 20:00:00', 52, 750.00),
    221223('2024-11-30 23:59:59', '002', 26150.00, '2024-11-30 20:00:00', 40, 0.00),