Relational Design for OPLMS

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. USER (User_ID, First_Name, last_Name, Email, Password))
   • 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)

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

DDL скрипта

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

DML скрипта

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