= 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 скрипта за креирање и бришење на табелите ===

[wiki:ddlScript.sql DDL скрипта]

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

[wiki:dmlScript.sql DML скрипта]

=== Релациски дијаграм изваден од DBeaver ===
[[Image(DBeaverExportVer2.png)]]