Relational Design for Handcraft Marketplace

Notation

• Primary keys are bolded and underlined.
• Foreign keys are marked with * at the end of their name and the referenced entity is written in parentheses.
• Complex attributes are bolded, and their containing attributes are following the, made italic.
• Multivalued attributes have their own table

Tables

1. PRODUCT (code, price, availability, dimensions, weight, width_X_length_X_depth, aprox_production_time, description)
   • image (multi-valued attribute, see table COLORS)
   • color (multi-valued attribute, see table IMAGE)

2. IMAGE (product_code* (PRODUCT), image)

3. COLOR (product_code* (PRODUCT), color)

4. STORE (store_ID, name, date_of_founding, physical_address, store_email, rating)

5. PERSONAL (SSN, name, first_name, last_name, email, password)
   • permissions (complex, multi-valued attribute, see table PERMISSIONS)

6. PERMISSIONS (personal_SSN* (PERSONAL), type, authorisation)

7. BOSS (boss_SSN* (PERSONAL))

8. EMPLOYEES (employee_SSN* (PERSONAL), date_of_hire)

9. CLIENT (client_ID, name, first_name, last_name, email, password)
   • delivery_address (multi-valued attribute, see table DELIVERY_ADDRESS)

10. DELIVERY_ADDRESS (client_ID (CLIENT), address)

11. ORDER (order_num,client_ID* (CLIENT), quantity, status, last_date_mod, payment_method, discount)

12. REPORT (date, store_ID* (STORE), overall_profit, sales_trend, marketing_growth, owner_signature)
    • monthly_profit (multi-valued attribute, see table MONTHLY_PROFIT)

13. MONTHLY_PROFIT (report_date* (REPORT), store_ID* (REPORT), month_and_year, profit)

14. REQUEST (request_num, date_and_time, problem, notes_of_communication, costumer_satisfaction)

15. MAKES_REQUEST (client_ID* (CLIENT), order_num* (ORDER))

16. ANSWERS (request_num* (REQUEST), personal_SSN* (PERSONAL))

17. FOR_STORE (request_num* (REQUEST), store_ID* (STORE))

18. REVIEW (order_num* (ORDER), comment, rating, last_mod_date)

19. CHANGE (date_and_time, changes_made)

20. MAKES_CHANGE (personal_SSN* (PERSONAL), change_date_time* (CHANGE))

21. MADE_ON (product_code* (PRODUCT), change_date_time* (CHANGE))

22. WORKS_IN_STORE (personal_SSN* (PERSONAL), store_ID* (STORE))

23. WORKED (personal_SSN* (PERSONAL), report_date* (REPORT), store_ID* (REPORT), wage, pay_method, working_hours, total, total_week)

24. SELLS (product_code* (PRODUCT), store_ID* (STORE), discount)

25. INCLUDES (order_num* (ORDER), product_code* (PRODUCT))

26. APPROVES (boss_SSN* (BOSS), report_date* (REPORT), owner_signature, store_ID* (REPORT))

27. EXCHANGES_DATA (report_date* (REPORT), store_ID* (STORE), monthly_profit, date, sales, damages, store_ID* (REPORT))
    • store_ID is also added as a regular attribute because it is being included in the report. This is necessary as reports made by different stores, on the same day need to be differentiated.

DDL скрипта за креирање и бришење на табелите

DDL скрипта

DML скрипта за полнење на табелите со податоци

DML скрипта

Релациски дијаграм изваден од DBeaver