Changes between Initial Version and Version 1 of dmlScript.sql

Timestamp:

12/21/25 01:17:59 (10 days ago)

Author:

235018

Comment:

--

dmlScript.sql

@@ +1 @@
+{{{#!sql
+-- PRODUCT
+INSERT INTO product (price, availability, weight, width_x_length_x_depth, aprox_production_time, description, delivery_cost) VALUES
+(700, 50, 2.5, '30x20x10', 10, 'Handmade wooden chair with oak wood', '50'),
+(150, 10, 0.2, '20x20x15', 2, 'Heart crochet', '0'),
+(199, 100, 0.75, '40x40x1', 14, 'Decorative wall hanging made with beads', '0');
+
+-- IMAGE
+INSERT INTO image (product_code, image) VALUES
+(1, 'chair.jpg'),
+(2, 'crochet.jpg'),
+(3, 'wall_hanging.jpg');
+
+
+-- COLOR
+INSERT INTO color (product_code, color) VALUES
+(1, 'Brown'),
+(2, 'Blue'),
+(2, 'Red'),
+(2, 'Yellow'),
+(2, 'Green'),
+(3, 'White and Pink'),
+(3, 'Black and Gold'),
+(3, 'Orange, Green and Purpule);
+
+
+-- STORE
+INSERT INTO store (name, date_of_founding, physical_address, store_email, rating) VALUES
+('WoodCraft Skopje', '2015-03-12', 'st.Ilindenska 45, Skopje 1000, Macedonia', 'contact@woodcraft.mk', 4.6),
+('Fox Crochets', '2023-06-01', 'st.Kej Makedonija 12, Ohrid 6000, Macedonia', 'ohrid@woodcraft.mk', 4.8);
+
+
+-- 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');
+
+
+-- PERMISSIONS
+INSERT INTO permissions (personal_SSN, type, authorisation) VALUES
+('1234567890123', 'BOSS', 'FULL'),
+('9876543210987', 'EMPLOYEE', 'LIMITED'),
+('4567891234567', 'BOSS', 'FULL');
+
+
+-- BOSS
+INSERT INTO boss (boss_SSN) VALUES
+('1234567890123'),
+('4567891234567');
+
+
+-- EMPLOYEES
+INSERT INTO employees (employee_SSN, date_of_hire) VALUES
+('9876543210987', '2019-09-01');
+
+
+-- CLIENT
+INSERT INTO client (first_name, last_name, email, password) VALUES
+('Ivan', 'Stojanov', 'ivan@gmail.com', 'ivanpass'),
+('Marija', 'Kostova', 'marija@yahoo.com', 'marijapass');
+
+
+-- DELIVERY_ADDRESS
+INSERT INTO delivery_address (client_ID, address) VALUES
+(1, 'st.Partizanska 10, Skopje 1000, Macedonia'),
+(2, 'st.Turisticka 5, Bitola 7000, Macedonia');
+
+
+-- ORDER
+INSERT INTO "order" (order_num, client_ID, quantity, status, last_date_mod, payment_method, discount) VALUES
+(1, 1, 2, 'placed order', '2025-12-01 10:15:00', 'credit card ****6750', 0.0),
+(2, 1, 2, 'packaging', '2025-12-10 18:00:00', 'PayPal account *******', 0.0),
+(3, 2, 1, 'delivered', '2025-12-02 14:30:00', 'cash', 4.0);
+
+
+-- REPORT
+INSERT INTO report (date, store_ID, overall_profit, sales_trend, marketing_growth, owner_signature) VALUES
+('2024-11-30 23:59:59', 1, 125000.00, 'Increasing', 'Stable growth', 'M.Petrovski'),
+('2024-11-30 23:59:59', 2, 98000.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', 1, '2024-11-01', 12500.00),
+('2024-11-30 23:59:59', 2, '2024-11-01', 8000.00);
+
+
+-- REQUEST
+INSERT INTO request (request_num, date_and_time, problem, notes_of_communication, costumer_satisfaction) VALUES
+(1, '2024-12-03 11:20:00', 'Late delivery', 'Apologized and offered discount', 4.0),
+(2, '2024-12-04 09:10:00', 'Military discount', 'Discount approved', 5.0);
+
+
+-- MAKES_REQUEST
+INSERT INTO makes_request (client_ID, request_num) VALUES
+(1, 1),
+(2, 2);
+
+
+-- ANSWERS
+INSERT INTO answers (request_num, personal_SSN) VALUES
+(1, '4567891234567'),
+(2, '1234567890123');
+
+-- FOR_STORE
+INSERT INTO for_store (request_num, store_ID) VALUES
+(1, 2),
+(2, 1);
+
+-- REVIEW
+INSERT INTO review (order_num, comment, rating, last_mod_date) VALUES
+(1, 'Great quality, slightly late delivery', 4.0, '2024-12-05 18:00:00');
+
+
+-- CHANGE
+INSERT INTO change (date_and_time, product_code, changes) VALUES
+('2024-11-10 09:00:00', 1, 'Updated production time'),
+('2024-11-12 15:30:00', 2, 'Added new color');
+
+
+-- WORKS_IN_STORE
+INSERT INTO works_in_store (personal_SSN, store_ID) VALUES
+('1234567890123', 1),
+('9876543210987', 1),
+('4567891234567', 2);
+
+
+-- WORKED
+INSERT INTO worked (personal_SSN, report_date, store_ID, wage, pay_method, total_hours, week) VALUES
+('1234567890123', '2025-11-30 23:59:59', 1, 75, 'hourly', 48, '2025-11-24 - 2025-11-30'),
+('9876543210987', '2025-11-30 23:59:59', 1, 75, 'hourly', 38, '2025-11-24 - 2025-11-30'),
+('4567891234567', '2025-11-30 23:59:59', 2, 450, 'weekly', 52, '2025-11-24 - 2025-11-30');
+
+
+-- SELLS
+INSERT INTO sells (product_code, store_ID, discount) VALUES
+(1, 1, 0.0),
+(2, 2, 0.0),
+(3, 2, 0.5);
+
+
+-- INCLUDES
+INSERT INTO includes (order_num, product_code) VALUES
+(1, 1),
+(2, 3),
+(3, 2);
+
+
+-- APPROVES
+INSERT INTO approves (boss_SSN, report_date, store_ID, owner_signature) VALUES
+('1234567890123', '2025-12-01 09:56:30', 1, 'M.Petrovski'),
+('4567891234567', '2025-12-03 13:06:12', 2, 'S.Vaneva');
+
+
+-- EXCHANGES_DATE
+INSERT INTO exchanges_date (report_date, store_ID, monthly_profit, date, sales, damages) VALUES
+('2024-11-30 23:59:59', 1, 38750.00, '2024-12-01 08:00:00', 52, 750),
+('2024-11-30 23:59:59', 2, 26150, '2024-12-01 08:00:00', 40, NULL);
+}}};