ERModel Final version

Diagram

Data requirements

ENTITIES

PRODUCT
Represents every product is currently selling by a store in the marketplace.
Candidate keys:

• code – surrogate numeric identifier (primary key).

Attributes:

• code – surrogare numeric identifier, required. Auto-generated primary key.
• price – numeric, required.
• availability – numeric, required.
• weight – numeric, required.
• width_X_length_X_depth – text, required.
• approx_production_time – numeric, required. Expressed in days.
• description – text, required.
• cathegory_id - text, required, foreign key from category entity.
• image – multi-valued, media, required.
• color – multi-valued, text, required.

---

category
Represents all the categories of products there are on the marketplace. The primary category is the only one with the parent_category_id being the same as the id attribute.
Candidate keys:

• id - surrogare numeric identifier (primary key).

Attributes:

• id - surrogare numeric identifier, required. Auto-generated primary key.
• name - text, required.
• parent_category_id - surrogate numeric identifier, required.

---

PERSONAL
Represents each individual member of the personnel in the store.
Candidate keys:

• id – surrogate numeric identifier (primary key).

Attributes:

• name – complex, text, required.
• first_name – text, required.
• last_name – text, required.
• SSN – numeric, required.
• email – text, required.
• password – hash value, required.
• permissions – complex, multi-valued, text, required.
• type – text, required.
• authorisation – text, required.

---

BOSS
Represents the boss/supervisor role. Inherited from PERSONAL.
Candidate keys:

• boss_ID – inherited primary key.

---

EMPLOYEES
Represents employees. Inherited from PERSONAL.
Candidate keys:

• employee_ID – inherited primary key.

Attributes:

• date_of_hire – date, required.

---

STORE
Represents a store registered in the marketplace system.
Candidate keys:

• store_ID – surrogate numeric identifier (primary key).

Attributes:

• store_ID – numeric, required. Auto-generated primary key.
• name – text, required.
• date_of_founding – date, required.
• physical_address – text, required.
• store_email – text, required.
• rating – numeric, optional.

---

CLIENT
Represents customers who are registered users for the marketplace.
Candidate keys:

• client_ID – surrogate numeric identifier (primary key).

Attributes:

• name – complex, text, required.
• first_name – text, required.
• last_name – text, required.
• email – text, required.
• password – hash value, required.
• delivery_address – complex, multi-valued text, required.
• address - text, required.
• city - text, required.
• postcode - text, required.
• country - text, required.
• is_default - boolean, required.

---

REQUEST
Represents customer requests or inquiries.
Candidate keys:

• request_num – surrogate numeric identifier (primary key).

Attributes:

• date_and_time – timestamp, required.
• problem – text, required.
• notes_of_communication – text, optional.
• customer_satisfaction – text, optional.

---

CHANGE
Represents changes made to products or systems.
Candidate keys: Composite key: (date_and_time, product_code).
Attributes:

• date_and_time – timestamp, required. Primary key.
• product_code - numeric, required.
• changes – text, required.

---

ORDER
Represents customer orders.
Candidate keys:

• order_num – surrogate numeric identifier (primary key).

Attributes:

• order_num – numeric, required. Auto-generated primary key.
• client_ID - numeric, required. Foreign key from CLIENT.
• quantity – numeric, required.
• status – text, required.
• last_modified_date – timestamp, required.
• payment_method – text, required.
• discount – numeric, optional.

---

REVIEW
Represents reviews for orders.
Candidate keys:

• order_num - surrogate numeric identifier (foreign primary key).

Attributes:

• comment – text, required.
• rating – numeric, required.
• last_modified_date – timestamp, required.

---

REFUND
Represents a refund/return for orders.
Candidate keys:

• refund_id - surrogate numeric identifier (primary key).
• order_num - surrogate numeric identifier (foreign partial key).

Attributes:

• reason – text, required.
• amount – numeric, required.
• status – text, required.

---

REPORT
Represents business reports and analytics.
Candidate keys:

• date – date identifier (partial key).
• store_ID – surrogate numeric identifier (foreign partial key), gotten from STORE entity.

Attributes:

• overall_profit – numeric, required. Profit of the store from date of founding.
• sales_trend – text, optional.
• marketing_growth – text, optional.
• owner_signature – text, required.
• monthly_profit – complex, multi-valued, numeric, required.
• month_and_year – date, required.
• profit – numeric, required.

---

RELATIONS

works_in_store (PERSONAL – STORE)
Represents personnel working in stores.
Keys: Composite key: (personal_ID, store_ID).

---

worked (PERSONAL – REPORT)
Tracks personnel work reports.
Keys: Composite key: (personal_ID, report_date, store_ID).
Attributes:

• wage – numeric, required.
• pay_method – text, required.
• working_hours – numeric,complex, required.
• week – numeric, required.
• total_week – numeric, required.

---

makes_request (CLIENT – REQUEST)
Represents clients making requests.
Keys: Composite key: (client_ID, request_num).

---

answers (REQUEST – PERSONAL)
Represents personnel answering requests.
Keys: Composite key: (request_num, personal_ID).

---

makes_change (PERSONAL – CHANGE)
Represents personnel making changes to products when they have a certain permission.
Keys: Composite key: (personal_id, date_and_time, product_code).

---

exchanges_data (REPORT – STORE)
Represents data exchange between reports and stores.
Keys: Composite key: (report_date, store_ID).
Attributes:

• monthly_profit – numeric, required.
• date - timestamp, optional.
• sales – numeric, required.
• damages – numeric, required.

---

sells (PRODUCT – STORE)
Represents products being sold in stores.
Keys: Composite key: (product_codecode, store_ID).
Attributes:

• discount – numeric, optional.

---

approves (REPORT – BOSS)
Represents bosses approving reports.
Keys: Composite key: (report_date, store_ID, boss_ID).
Attributes:

• owner_signature - media, optional.

---

includes (PRODUCT – ORDER)
Represents products included in orders.
Keys: Composite key: (product_code, order_num).

---

for_store (REQUEST – STORE)
Represents requests made for specific stores.
Keys: Composite key: (request_num, store_ID).

---

ERModel History

First submitted on 09.12.2025

Edited on 17.12.2025

Edited on 20.12.2025

Edited on 18.09.2026

Last edited on 19.09.2026