Changes between Initial Version and Version 1 of ERModel


Ignore:
Timestamp:
07/13/26 01:39:34 (13 days ago)
Author:
235018
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • ERModel

    v1 v1  
     1== ERModel Current version ==
     2== Diagram ==
     3[[Image(Handcrafts-storeDB-Edit1.3.png)]]
     4
     5== Data requirements ==
     6=== ENTITIES ===
     7
     8{{{PRODUCT}}}
     9[[BR]]
     10Represents every product is currently selling by a store in the marketplace.
     11[[BR]]
     12'''[[span(style=color: #9A0000, Candidate keys:)]]'''
     13* {{{code}}} – surrogate numeric identifier (primary key).
     14[[BR]]
     15'''[[span(style=color: #9A0000, Attributes:)]]'''
     16* {{{code}}} – numeric, required. Auto-generated primary key.
     17* {{{price}}} – numeric, required.
     18* {{{availability}}} – numeric, required.
     19* {{{description}}} – text, required.
     20* {{{images}}} – multi-valued, media, required.
     21* {{{dimensions}}} – complex, text, required.
     22* {{{weight}}} – numeric, required.
     23* {{{width_X_length_X_depth}}} – text, required.
     24* {{{colors}}} – multi-valued, text, required.
     25* {{{approx_production_time}}} – numeric, required. Expressed in days.
     26[[BR]]
     27----
     28{{{PERSONAL}}}
     29[[BR]]
     30Represents each individual member of the personnel in the store.
     31[[BR]]
     32'''[[span(style=color: #9A0000, Candidate keys:)]]'''
     33* {{{SSN}}} – numeric identifier (primary key).
     34[[BR]]
     35'''[[span(style=color: #9A0000, Attributes:)]]'''
     36* {{{SSN}}} – numeric, required. Primary key.
     37* {{{name}}} – complex, text, required.
     38* {{{first_name}}} – text, required.
     39* {{{last_name}}} – text, required.
     40* {{{middle_name}}} – text, optional.
     41* {{{email}}} – text, required.
     42* {{{password}}} – hash value, required.
     43* {{{permissions}}} – complex, multi-valued, text, required.
     44* {{{type}}} – text, required.
     45* {{{authorisation}}} – text, required.
     46[[BR]]
     47----
     48{{{BOSS}}}
     49[[BR]]
     50Represents the boss/supervisor role. Inherited from PERSONAL.
     51[[BR]]
     52'''[[span(style=color: #9A0000, Candidate keys:)]]'''
     53* {{{SSN}}} – inherited primary key.
     54* {{{signature}}} - boss' signature for authorising Reports and Permissions.
     55[[BR]]
     56----
     57{{{EMPLOYEES}}}
     58[[BR]]
     59Represents employees. Inherited from PERSONAL.
     60[[BR]]
     61'''[[span(style=color: #9A0000, Candidate keys:)]]'''
     62* {{{SSN}}} – inherited primary key.
     63[[BR]]
     64'''[[span(style=color: #9A0000, Attributes:)]]'''
     65* {{{date_of_hire}}} – date, required.
     66[[BR]]
     67----
     68{{{STORE}}}
     69[[BR]]
     70Represents a store registered in the marketplace system.
     71[[BR]]
     72'''[[span(style=color: #9A0000, Candidate keys:)]]'''
     73* {{{store_ID}}} – surrogate numeric identifier (primary key).
     74[[BR]]
     75'''[[span(style=color: #9A0000, Attributes:)]]'''
     76* {{{store_ID}}} – numeric, required. Auto-generated primary key.
     77* {{{name}}} – text, required.
     78* {{{date_of_founding}}} – date, required.
     79* {{{physical_address}}} – text, required.
     80* {{{store_email}}} – text, required.
     81* {{{rating}}} – numeric, optional.
     82[[BR]]
     83----
     84{{{CLIENT}}}
     85[[BR]]
     86Represents customers who are registered users for the marketplace.
     87[[BR]]
     88'''[[span(style=color: #9A0000, Candidate keys:)]]'''
     89* {{{client_ID}}} – surrogate numeric identifier (primary key).
     90[[BR]]
     91'''[[span(style=color: #9A0000, Attributes:)]]'''
     92* {{{client_ID}}} – numeric, required. Auto-generated primary key.
     93* {{{name}}} – complex, text, required.
     94* {{{first_name}}} – text, required.
     95* {{{last_name}}} – text, required.
     96* {{{email}}} – text, required.
     97* {{{password}}} – hash value, required.
     98* {{{delivery_address}}} – multi-valued text, required.
     99[[BR]]
     100----
     101{{{REQUEST}}}
     102[[BR]]
     103Represents customer requests or inquiries.
     104[[BR]]
     105'''[[span(style=color: #9A0000, Candidate keys:)]]'''
     106* {{{request_num}}} – surrogate numeric identifier (primary key).
     107[[BR]]
     108'''[[span(style=color: #9A0000, Attributes:)]]'''
     109* {{{request_num}}} – numeric, required. Auto-generated primary key.
     110* {{{date_and_time}}} – timestamp, required.
     111* {{{problem}}} – text, required.
     112* {{{notes_of_communication}}} – text, optional.
     113* {{{customer_satisfaction}}} – text, optional.
     114[[BR]]
     115----
     116{{{CHANGE}}}
     117[[BR]]
     118Represents changes made to products or systems.
     119[[BR]]
     120'''[[span(style=color: #9A0000, Candidate keys:)]]'''
     121* {{{date_and_time}}} – timestamp identifier (primary key).
     122[[BR]]
     123'''[[span(style=color: #9A0000, Attributes:)]]'''
     124* {{{date_and_time}}} – timestamp, required. Primary key.
     125* {{{product_code}}} - numeric, required.
     126* {{{changes_made}}} – text, required.
     127[[BR]]CASCADE
     128----
     129{{{ORDER}}}
     130[[BR]]
     131Represents customer orders.
     132[[BR]]
     133'''[[span(style=color: #9A0000, Candidate keys:)]]'''
     134* {{{order_num}}} – surrogate numeric identifier (primary key).
     135[[BR]]
     136'''[[span(style=color: #9A0000, Attributes:)]]'''
     137* {{{order_num}}} – numeric, required. Auto-generated primary key.
     138* {{{quantity}}} – numeric, required.
     139* {{{status}}} – text, required.
     140* {{{last_modified_date}}} – timestamp, required.
     141* {{{payment_method}}} – text, required.
     142* {{{discount}}} – numeric, optional.
     143[[BR]]
     144----
     145{{{REPORT}}}
     146[[BR]]
     147Represents business reports and analytics.
     148[[BR]]
     149'''[[span(style=color: #9A0000, Candidate keys:)]]'''
     150* {{{date}}} – date identifier (partial key).
     151* {{{store_ID}}} – surrogate numeric identifier (foreign partial key), gotten from STORE entity.
     152[[BR]]
     153'''[[span(style=color: #9A0000, Attributes:)]]'''
     154* {{{date}}} – date, required. Partial primary key.
     155* {{{store_ID}}} – numeric, required. Foreign partial key.
     156* {{{overall_profit}}} – numeric, required. Profit of the store from date of founding.
     157* {{{monthly_profit}}} – complex, multi-valued, numeric, required.
     158* {{{month_and_year}}} – text, required.
     159* {{{profit}}} – numeric, required.
     160* {{{sales_trend}}} – text, optional.
     161* {{{marketing_growth}}} – text, optional.
     162* {{{owner_signature}}} – text, required.
     163[[BR]]
     164----
     165
     166=== RELATIONS ===
     167
     168{{{review (ORDER)}}}
     169[[BR]]
     170Represents reviews for orders.
     171[[BR]]
     172'''[[span(style=color: #9A0000, Keys:)]]'''
     173({{{order_num}}})
     174[[BR]]
     175'''[[span(style=color: #9A0000, Attributes:)]]'''
     176* {{{comment}}} – text, required.
     177* {{{rating}}} – numeric, required.
     178* {{{last_modified_date}}} – timestamp, required.
     179[[BR]]
     180----
     181{{{works_in_store (PERSONAL – STORE)}}}
     182[[BR]]
     183Represents personnel working in stores.
     184[[BR]]
     185'''[[span(style=color: #9A0000, Keys:)]]'''
     186Composite key: ({{{SSN}}}, {{{store_ID}}}).
     187[[BR]]
     188----
     189{{{worked (PERSONAL – REPORT)}}}
     190[[BR]]
     191Tracks personnel work reports.
     192[[BR]]
     193'''[[span(style=color: #9A0000, Keys:)]]'''
     194Composite key: ({{{SSN}}}, {{{date}}}, {{{store_ID}}}).
     195[[BR]]
     196'''[[span(style=color: #9A0000, Attributes:)]]'''
     197* {{{week}}} – numeric, required.
     198* {{{total_week}}} – numeric, required. Total hours worked that certain week.
     199* {{{pay_method}}} – text, required.
     200* {{{wage}}} – numeric, required.
     201* {{{working_hours}}} – numeric, required.
     202[[BR]]
     203----
     204{{{make_request (CLIENT – REQUEST)}}}
     205[[BR]]
     206Represents clients making requests.
     207[[BR]]
     208'''[[span(style=color: #9A0000, Keys:)]]'''
     209Composite key: ({{{client_ID}}}, {{{request_num}}}).
     210[[BR]]
     211----
     212{{{answers (REQUEST – PERSONAL)}}}
     213[[BR]]
     214Represents personnel answering requests.
     215[[BR]]
     216'''[[span(style=color: #9A0000, Keys:)]]'''
     217Composite key: ({{{request_num}}}, {{{SSN}}}).
     218[[BR]]
     219----
     220{{{makes_change (PERSONAL – CHANGE)}}}
     221[[BR]]
     222Represents personnel making changes to products when they have a certain permission.
     223[[BR]]
     224'''[[span(style=color: #9A0000, Keys:)]]'''
     225Composite key: ({{{SSN}}}, {{{date_and_time}}}).
     226[[BR]]
     227'''[[span(style=color: #9A0000, Attributes:)]]'''
     228* {{{permission}}} – complex, text, required.
     229* {{{type}}} – text, required.
     230* {{{authorisation}}} – text, required.
     231[[BR]]
     232----
     233{{{made_on (PRODUCT – CHANGE)}}}
     234[[BR]]
     235Represents changes made on products.
     236[[BR]]
     237'''[[span(style=color: #9A0000, Keys:)]]'''
     238Composite key: ({{{code}}}, {{{date_and_time}}}).
     239[[BR]]
     240----
     241{{{exchanges_data (REPORT – STORE)}}}
     242[[BR]]
     243Represents data exchange between reports and stores.
     244[[BR]]
     245'''[[span(style=color: #9A0000, Keys:)]]'''
     246Composite key: ({{{date}}}, {{{store_ID}}}).
     247[[BR]]
     248'''[[span(style=color: #9A0000, Attributes:)]]'''
     249* {{{store_ID}}} - numeric, required.
     250* {{{monthly_profit}}} – numeric, required.
     251* {{{sales}}} – numeric, required.
     252* {{{damages}}} – numeric, required.
     253[[BR]]
     254----
     255{{{sells (PRODUCT – STORE)}}}
     256[[BR]]
     257Represents products being sold in stores.
     258[[BR]]
     259'''[[span(style=color: #9A0000, Keys:)]]'''
     260Composite key: ({{{code}}}, {{{store_ID}}}).
     261[[BR]]
     262'''[[span(style=color: #9A0000, Attributes:)]]'''
     263* {{{quantity}}} – numeric, required.
     264* {{{discount}}} – numeric, optional.
     265[[BR]]
     266----
     267{{{approves (REPORT – BOSS)}}}
     268[[BR]]
     269Represents bosses approving reports.
     270[[BR]]
     271'''[[span(style=color: #9A0000, Keys:)]]'''
     272Composite key: ({{{date}}}, {{{SSN}}}).
     273[[BR]]
     274----
     275{{{includes (PRODUCT – ORDER)}}}
     276[[BR]]
     277Represents products included in orders.
     278[[BR]]
     279'''[[span(style=color: #9A0000, Keys:)]]'''
     280Composite key: ({{{code}}}, {{{order_num}}}).
     281[[BR]]
     282----
     283{{{makes_order (CLIENT – ORDER)}}}
     284[[BR]]
     285Represents clients making orders.
     286[[BR]]
     287'''[[span(style=color: #9A0000, Keys:)]]'''
     288Composite key: ({{{client_ID}}}, {{{order_num}}}).
     289[[BR]]
     290----
     291{{{for_store (REQUEST – STORE)}}}
     292[[BR]]
     293Represents requests made for specific stores.
     294[[BR]]
     295'''[[span(style=color: #9A0000, Keys:)]]'''
     296Composite key: ({{{request_num}}}, {{{store_ID}}}).
     297[[BR]]
     298----
     299
     300== ERModel History ==
     301First submitted on 09.12.2025
     302
     303Edited on 17.12.2025
     304
     305Last edit on 20.12.2025