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, product_code*, 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, week, 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), store_ID* (REPORT), owner_signature)

27. EXCHANGES_DATA (report_date* (REPORT), store_ID* (STORE), monthly_profit, date, sales, damages)

DDL script for creation and deletion of tables

DDL скрипта

DML script for inserting data in the tables

DML скрипта

Relational diagram made in DBeaver