Changes between Initial Version and Version 1 of P2

Timestamp:

12/20/25 20:00:24 (10 days ago)

Author:

235018

Comment:

--

P2

@@ +1 @@
+= 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}}}__**, {{{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))
+
+17. **REVIEW** (**__{{{order_num*}}}__** (ORDER), {{{comment}}}, {{{rating}}}, {{{last_mod_date}}})
+
+18. **CHANGE** (**__{{{date_and_time}}}__**, **{{{changes_made}}}**, ''{{{previous_version}}}'', ''{{{new_version}}}'')
+
+19. **MAKES_CHANGE** (**__{{{personal_SSN*}}}__** (PERSONAL), **__{{{change_date_time*}}}__** (CHANGE))
+
+20. **MADE_ON** (**__{{{product_code*}}}__** (PRODUCT), **__{{{change_date_time*}}}__** (CHANGE))
+
+21. **WORKS_IN_STORE** (**__{{{personal_SSN*}}}__** (PERSONAL), **__{{{store_ID*}}}__** (STORE))
+
+22. **WORKED** (**__{{{personal_SSN*}}}__** (PERSONAL), **__{{{report_date*}}}__** (REPORT),  **__{{{store_ID*}}}__** (REPORT), {{{wage}}},  {{{pay_method}}},  **{{{working_hours}}}**, ''{{{total}}}'', ''{{{total_week}}}'')
+
+23. **SELLS** (**__{{{product_code*}}}__** (PRODUCT), **__{{{store_ID*}}}__** (STORE), {{{discount}}})
+
+24. **INCLUDES** (**__{{{order_num*}}}__** (ORDER), **__{{{product_code*}}}__** (PRODUCT))
+
+25. **APPROVES** (**__{{{boss_SSN*}}}__** (BOSS), **__{{{report_date*}}}__** (REPORT), {{{owner_signature}}}, **__{{{store_ID*}}}__** (REPORT))
+
+26. **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 скрипта за креирање и бришење на табелите ===
+
+[wiki:ddlScript.sql DDL скрипта]
+
+=== DML скрипта за полнење на табелите со податоци ===
+
+[wiki:dmlScript.sql DML скрипта]
+
+=== Релациски дијаграм изваден од DBeaver ===
+[[Image(DBeaverExportVer2.png)]]