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

Timestamp:

12/28/25 01:24:55 (3 days ago)

Author:

235018

Comment:

--

dmlScript-with-help-of-AI.sql

@@ +1 @@
+{{{#!sql
+-- CATEGORY
+INSERT INTO category (name, parent_category_id) VALUES
+('Furniture', NULL),
+('Home Decor', NULL),
+('Gifts', NULL),
+('Chairs', 1),
+('Tables', 1),
+('Wall Decor', 2),
+('Textiles', 3),
+('Crochet', 7),
+('Beadwork', 7);
+
+-- PRODUCT 
+INSERT INTO product (code, price, availability, weight, width_x_length_x_depth, aprox_production_time, description, cathegory_id) VALUES
+('00100001', 700.00, 50, 2.50, '30x20x10', 10, 'Handmade wooden chair with oak wood', 4),
+('00200001', 150.00, 10, 0.20, '20x20x15', 2, 'Heart crochet decoration', 8),
+('00200002', 199.00, 100, 0.75, '40x40x1', 14, 'Decorative wall hanging made with beads', 9),
+('00100002', 1200.00, 15, 15.00, '180x90x75', 21, 'Solid oak dining table', 5),
+('00200003', 89.99, 25, 0.50, '60x30x2', 7, 'Macrame wall hanging', 6);
+
+-- IMAGE (multiple images per product allowed)
+INSERT INTO image (product_code, image) VALUES
+('00100001', 'chair_front.jpg'),
+('00100001', 'chair_side.jpg'),
+('00100001', 'chair_detail.jpg'),
+('00200001', 'crochet-heart-red.jpg'),
+('00200001', 'crochet-heart-blue.jpg'),
+('00200002', 'wall_hanging_pink.jpg'),
+('00200002', 'wall_hanging_gold.jpg'),
+('00100002', 'dining_table_oak.jpg'),
+('00200003', 'macrame_wall_hanging.jpg');
+
+-- COLOR
+INSERT INTO color (product_code, color) VALUES
+('00100001', 'Brown'),
+('00100001', 'Walnut'),
+('00100001', 'Ebony'),
+('00200001', 'Blue'),
+('00200001', 'Red'),
+('00200001', 'Yellow'),
+('00200001', 'Green'),
+('00200002', 'White and Pink'),
+('00200002', 'Black and Gold'),
+('00200002', 'Orange, Green and Purple'),
+('00100002', 'Natural Oak'),
+('00100002', 'Dark Stain'),
+('00200003', 'Natural'),
+('00200003', 'White');
+
+-- STORE
+INSERT INTO store (store_id, name, date_of_founding, physical_address, store_email, rating) VALUES
+('001', 'WoodCraft Skopje', '2015-03-12', 'st.Ilindenska 45, Skopje 1000, Macedonia', 'contact@woodcraft.mk', 4.6),
+('002', 'Fox Crochets', '2023-06-01', 'st.Kej Makedonija 12, Ohrid 6000, Macedonia', 'ohrid@foxcrochets.mk', 4.8),
+('003', 'Artisan Collective', '2020-01-15', 'st.Goce Delcev 78, Bitola 7000, Macedonia', 'info@artisancollective.mk', 4.5);
+
+-- PERSONAL
+INSERT INTO personal (ssn, first_name, last_name, email, password) VALUES    
+('1234567890123', 'Marko', 'Petrovski', 'marko@woodcraft.mk', 'Havr6njs09$hdgs'),
+('9876543210987', 'Antonio', 'Trajkovski', 'antonio@woodcraft.mk', 'GKm78BHs&vJHA'),
+('4567891234567', 'Sara', 'Vaneva', 's.vaneva@foxcrochets.mk', 'hY69Btvhs90'),
+('7891234567890', 'Elena', 'Nikolovska', 'elena@artisancollective.mk', 'Pp45@kjsdD'),
+('3216549870123', 'Dimitar', 'Risteski', 'dimitar@foxcrochets.mk', 'Ls92#hjsDf');
+
+-- PERMISSIONS
+INSERT INTO permissions (personal_SSN, type, authorisation) VALUES
+('1234567890123', 'BOSS', 'admin'),
+('9876543210987', 'EMPLOYEE', '1234567890123'),
+('4567891234567', 'BOSS', 'admin'),
+('7891234567890', 'MANAGER', '3216549870123'),
+('3216549870123', 'BOSS', 'admin');
+
+-- BOSS
+INSERT INTO boss (boss_SSN) VALUES
+('1234567890123'),
+('4567891234567'),
+('3216549870123');
+
+-- EMPLOYEES
+INSERT INTO employees (employee_SSN, date_of_hire) VALUES
+('9876543210987', '2019-09-01'),
+('7891234567890', '2020-02-15');
+
+-- CLIENT
+INSERT INTO client (client_id, first_name, last_name, email, password) VALUES
+(1001, 'Ivan', 'Stojanov', 'ivan@gmail.com', 'hkh689gvgsh%hd'),
+(1002, 'Marija', 'Kostova', 'marija@yahoo.com', 'PJdbbh334$djk-hs'),
+(1003, 'Antoneta', 'Mariovska', 'mariovskaantoneta@finki.ukim.mk', '*bxhc6cbsd3@xh'),
+(1004, 'Petar', 'Dimitrievski', 'petar.d@hotmail.com', 'Xh78$jsdK12'),
+(1006, 'Ana', 'Stojanova', 'ana.stojanova@gmail.com', 'An89#kjsdFg');
+
+-- DELIVERY_ADDRESS
+INSERT INTO delivery_address (client_ID, address, city, postcode, country, is_default) VALUES
+(1001, 'st.Partizanska 10', 'Skopje', '1000', 'Macedonia', TRUE),
+(1002, 'st.Turisticka 5', 'Bitola', '7000', 'Macedonia', TRUE),
+(1003, 'st.32 br.4', 'Cucer-Sandevo', '1011', 'Macedonia', TRUE),
+(1004, 'st.Bul.Kuzman Josifovski Pitu 15', 'Skopje', '1000', 'Macedonia', TRUE),
+(1006, 'st.Makedonska Brigada 22', 'Ohrid', '6000', 'Macedonia', TRUE),
+(1003, 'Campus Dormitory, Room 305', 'Skopje', '1000', 'Macedonia', FALSE); -- Second address for client 3
+
+-- ORDER (updated with correct constraints)
+INSERT INTO "order" (order_num, client_ID, status, last_date_mod, payment_method, discount) VALUES
+('002202500001', 1001, 'placed order', '2025-12-01 10:15:00', 'credit card ****6750', 0.00),
+('002202500002', 1001, 'being processed', '2025-12-10 18:00:00', 'PayPal account user123', 0.00),
+('001202500001', 1002, 'delivered', '2025-12-02 14:30:00', 'cash', 4.00),
+('003202500001', 1003, 'shipping', '2025-12-05 09:45:00', 'credit card ****1234', 10.00),
+('002202500003', 1004, 'canceled', '2025-12-03 16:20:00', 'credit card ****9876', 0.00),
+('001202500002', 1006, 'delivered', '2025-12-08 11:30:00', 'bank transfer', 5.50);
+
+-- REPORT
+INSERT INTO report (date, store_ID, overall_profit, sales_trend, marketing_growth, owner_signature) VALUES
+('2024-11-30 23:59:59', '001', 125000.00, 'Increasing', 'Stable growth', 'M.Petrovski'),
+('2024-11-30 23:59:59', '002', 98000.00, 'Stable', 'Moderate growth', 'S.Vaneva'),
+('2024-11-30 23:59:59', '003', 75000.00, 'Growing', 'Rapid growth', 'D.Ristevski'),
+('2024-12-31 23:59:59', '001', 135000.00, 'Increasing', 'Good growth', 'M.Petrovski'),
+('2024-12-31 23:59:59', '002', 105000.00, 'Stable', 'Moderate growth', 'S.Vaneva');
+
+-- MONTHLY_PROFIT
+INSERT INTO monthly_profit (report_date, store_ID, month_and_year profit) VALUES
+('2024-11-30 23:59:59', '001', 'November 2024', 12500.00),
+('2024-11-30 23:59:59', '002', 'November 2024', 8000.00),
+('2024-11-30 23:59:59', '003', 'November 2024', 6500.00),
+('2024-12-31 23:59:59', '001', 'December 2024', 14500.00),
+('2024-12-31 23:59:59', '002', 'December 2024', 9000.00);
+
+-- REQUEST
+INSERT INTO request (request_num, date_and_time, problem, notes_of_communication, customer_satisfaction) VALUES
+('00111202500101', '2024-11-03 11:20:00', 'Late delivery', 'Apologized and offered discount', 4.0),
+('00212202500102', '2024-12-04 09:10:00', 'Military discount inquiry', 'Discount approved for active duty personnel', 5.0),
+('00312202500101', '2024-12-10 14:30:00', 'Product damage during shipping', 'Replacement sent, offered refund option', 3.5),
+('00112202500201', '2024-12-15 16:45:00', 'Custom order request', 'Discussed custom design options', 4.5);
+
+-- MAKES_REQUEST 
+INSERT INTO makes_request (client_ID, request_num) VALUES
+(1003, '00312202500101'),
+(1002, '00111202500101'),
+(1001, '00212202500102'),
+(1004, '00112202500201');
+
+-- ANSWERS
+INSERT INTO answers (request_num, personal_SSN) VALUES
+('00111202500101', '4567891234567'),
+('00212202500102', '1234567890123'),
+('00312202500101', '7891234567890'),
+('00112202500201', '9876543210987');
+
+-- FOR_STORE      (Probably not needed as you can get it from the first 3 digits of the request number)
+INSERT INTO for_store (request_num, store_ID) VALUES
+('00212202500102', '002'),
+('00111202500101', '001'),
+('00312202500101', '003'),
+('00112202500201', '001');
+
+-- REVIEW
+INSERT INTO review (order_num, comment, rating, last_mod_date) VALUES
+('001202500001', 'Great quality, slightly late delivery', 4.0, '2024-12-05 18:00:00'),
+('001202500002', 'Beautiful craftsmanship, exactly as pictured', 5.0, '2024-12-10 10:30:00'),
+('003202500001', '', 4.5, '2024-12-12 14:15:00');
+
+-- CHANGE
+INSERT INTO "change" (date_and_time, product_code, changes) VALUES
+('2024-11-10 09:00:00', '00100001', 'FROM aprox_production_time=14 TO aprox_production_time=10'),
+('2024-11-12 15:30:00', '00200001', 'Added new color options: Purple, Pink'),
+('2024-12-01 11:00:00', '00100002', 'Price increased from 1100 to 1200 due to material costs'),
+('2024-12-05 14:20:00', '00200002', 'Production time reduced from 16 to 14 days');
+
+-- MAKES_CHANGE
+INSERT INTO makes_change (personal_SSN, change_date_time, product_code) VALUES
+('1234567890123', '2024-11-10 09:00:00', '00100001'),
+('4567891234567', '2024-11-12 15:30:00', '00200001'),
+('7891234567890', '2024-12-01 11:00:00', '00100002'),
+('9876543210987', '2024-12-05 14:20:00', '00200002');
+
+-- WORKS_IN_STORE
+INSERT INTO works_in_store (personal_SSN, store_ID) VALUES
+('1234567890123', '001'),
+('9876543210987', '001'),
+('4567891234567', '002'),
+('7891234567890', '003'),
+('3216549870123', '002'),
+('9876543210987', '003'); -- Employee works in multiple stores, probably need to have employee ID
+
+-- WORKED
+INSERT INTO worked (personal_SSN, report_date, store_ID, wage, pay_method, total_hours, week) VALUES
+('1234567890123', '2024-11-30 23:59:59', '001', 75, 'hourly', 48, '2024-11-24 - 2024-11-30'),
+('9876543210987', '2024-11-30 23:59:59', '001', 75, 'hourly', 38, '2024-11-24 - 2024-11-30'),
+('4567891234567', '2024-11-30 23:59:59', '002', 10000, 'monthly', 52, '2024-11-24 - 2024-11-30'),
+('7891234567890', '2024-11-30 23:59:59', '003', 65, 'hourly', 42, '2024-11-24 - 2024-11-30'),
+('3216549870123', '2024-11-30 23:59:59', '002', 60, 'hourly', 40, '2024-11-24 - 2024-11-30');
+
+-- SELLS
+INSERT INTO sells (product_code, store_ID, discount) VALUES
+('00100001', '001', 0.0),
+('00100002', '001', 5.0),
+('00200001', '002', 0.0),
+('00200002', '002', 0.5),
+('00200002', '003', 0.3),
+('00200003', '003', 0.0),
+('00200001', '003', 0.2);
+
+-- INCLUDES
+INSERT INTO includes (order_num, product_code) VALUES
+('001202500001', '00100001'),
+('002202500001', '00200002'),
+('002202500002', '00200001'),
+('003202500001', '00200002'),
+('003202500001', '00200003'),
+('001202500002', '00100001'),
+('001202500002', '00200001');
+
+-- APPROVES
+INSERT INTO approves (boss_SSN, report_date, store_ID, owner_signature) VALUES
+('1234567890123', '2024-11-30 23:59:59', '001', 'M.Petrovski'),
+('4567891234567', '2024-11-30 23:59:59', '002', 'S.Vaneva'),
+('1234567890123', '2024-12-31 23:59:59', '001', 'M.Petrovski'),
+('4567891234567', '2024-12-31 23:59:59', '002', 'S.Vaneva');
+
+-- EXCHANGES_DATA 
+INSERT INTO exchanges_data (report_date, store_ID, monthly_profit, date, sales, damages) VALUES        -- Report_id???
+('2024-11-30 23:59:59', '001', 38750.00, '2024-11-30 20:00:00', 52, 750.00),
+('2024-11-30 23:59:59', '002', 26150.00, '2024-11-30 20:00:00', 40, 0.00),
+('2024-11-30 23:59:59', '003', 19500.00, '2024-11-30 20:00:00', 35, 250.00),
+('2024-12-31 23:59:59', '001', 41250.00, '2025-12-31 20:00:00', 58, 1200.00),
+('2024-12-31 23:59:59', '002', 28500.00, '2025-12-31 20:00:00', 45, 500.00);
+
+-- REFUND
+INSERT INTO refund (order_num, amount, reason, status) VALUES
+('002202500003', 199.00, 'Customer changed mind before shipping', 'processed'),
+('001202500001', 50.00, 'Partial refund for late delivery', 'approved'),
+('003202500001', 89.99, 'One item damaged during shipping', 'pending'),
+('001202500002', 75.00, 'Price adjustment after promotion', 'declined');
+
+
+}}}