Changes between Initial Version and Version 1 of LogicalPhysicalDesign

Timestamp:

12/22/25 15:48:24 (6 days ago)

Author:

221296

Comment:

--

LogicalPhysicalDesign

@@ +1 @@
+= 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)]]