ERModel Current version

Diagram

Data requirements

ENTITIES

Product
Represents every product that the store is currently selling. It is needed to make it easier for costumers to order what they desire, keep track of sales,profit and stock, marketing,...
Candidate keys:

• code – surrogate numeric identifier (chosen primary key).
• image – natural key, must be unique, but can change in the future.

code is chosen as PK because it is stable, short, and independent of business rules.
Attributes:

• code – numeric, required. Auto-generated positive integer; primary key.
• price – numeric, required. Set by the personal. Can be repeated and changed.
• availability – numeric, required. Keeps track of the remaining copies of the product.
• description – text, required. Free-text that describes the product.
• image – media, required. Helps easily identify and distinguish the products. Can be changed.
• dimensions – combination of 2 text values: weight(important for calculating delivery)in grams and a value that includes width, length and depth of the product as text, required. Heps for calculating delivery costs upon ordering.
• color – text, required. Can have multiple possible values : "BLUE", "RED", "YELLOW", "GREEN", "VIOLET",... It indicates in which colors the product is available.
• aprox_production_time – numeric, required. Automatically set when the product is created. Helps upon managing orders, informing the costumer of arrival time, scheduling delivery,ext... Shown in days.

---

Permission
Represents the possible actions that members of the personal can take, tracks the actions of each member of the personnel over the products and their communication with the clients.
Candidate keys:

• permission_id – surrogate numeric identifier (primary key).

No realistic natural key exists that is stable and unique, so we only use permission_id.
Attributes:

• permission_id – numeric, required. Auto-generated primary key.
• description – text, required. Allowed values like {ACTIVE, RESERVED, SOLD}.

---

Personal
Represents each individual member of the personal in the system. Needed to keep record of the personal information of each of the members, the permissions that they have been granted, hours that they have worked,ext... It is given as a single entity that later separates into the different roles of the personal.
Candidate keys:

• SSN – A unique number of each induvidual that doesn't tend to change (primary key).
• email - Natural key that is unique, but could be deleted and reused later, which doesn't make it optimal.

SSN is chosen as PK for simplicity and stability.
Attributes:

• SSN – numeric, required. Unique and government provieded (primary key).
• name – complex text, required. Consists of the first, middle and last name of the person.
• email – text, required. Unique, provided by the employee.
• password – hash value, required. Unique, secure value to keep record of the user's password for authenticating the user upon logging. It is kept as a hash value for the safety of the user and the system.

---

Boss
Represents the owner of the store It is kept as a single entity to better manage their permissions, access and tracking over the employees and the system's data. Inherited from Personal. It doesn't represent one specific person, but a level of user in the system. For example, the active shareholders would have the role of a Boss as they would have full access over the system.
Candidate keys:

• SSN – numeric, required. Unique and government provieded (primary key). Uses same key as the previous entity because it is inherited.

---

EMPLOYEES
Abstract entity representing all staff members (generalization root). The entity is split into concrete roles (MANAGERS, STORE ATTENDANTS). Inherits common personnel attributes from Personal. Needed for HR, access control and activity tracking.
Candidate keys: SSN – government-provided unique identifier (inherited primary key). email – natural key, unique across employees.
Attributes: SSN – numeric, required. Primary key shared with Personal. name – complex text, required. Full name. email – text, required. Employee email. first name / last name – text, required. password – hash, required. For authentication where applicable.

---

STORE ATTENDANTS
Represents store-level staff who handle day-to-day store activities (sales, stocking, customer service). Inherits from EMPLOYEES/Personal.
Candidate keys: SSN – inherited identifier (primary key).
Attributes: SSN – numeric, required. Inherited primary key. working hous – multi-valued (or repeated) text/numeric — records shifts / hours worked (used for scheduling). wage – numeric, optional. Pay rate for the attendant. promotion date – date, optional (if an attendant gets promoted).

---

MANAGERS
Represents employees with managerial responsibilities. Inherits from EMPLOYEES/Personal. Used to manage stores, supervise staff and make higher-level decisions.
Candidate keys: SSN – inherited surrogate/natural identifier (primary key).
Attributes: SSN – numeric, required. Inherited primary key. promotion date – date, optional. When the employee was promoted to manager. supervisor_SSN – numeric, optional. SSN of the manager's supervisor (if applicable). wage – numeric, optional. Current salary/wage of the manager.

---

STORE
Represents a physical or logical store location (or the company store) that holds stock and generates sales. Used to track inventory distribution, store-level statistics and profit.
Candidate keys: store_ID – surrogate numeric identifier (primary key). store_ID used as PK because it uniquely identifies a store instance and is stable.
Attributes: store_ID – numeric, required. Auto-generated primary key. statistics – text or JSON, optional. Aggregated store metrics used by analytics. profit – numeric, optional. Store-level profit figure for reporting. last mod. date – timestamp, optional. Last update timestamp for store data.

---

CLIENT
Represents each customer who buys from the store or places orders. Needed to track orders, deliveries, communication, and customer history.
Candidate keys: client_ID – surrogate numeric identifier (chosen primary key). email – natural key, usually unique but can change or be reused. client_ID is chosen as PK because it is stable, auto-generated and independent of user-controlled data.
Attributes: client_ID – numeric, required. Auto-generated positive integer; primary key. name – complex text, required. Business or display name of the client (if any). first name – text, required. Client's first name. last name – text, required. Client's last name. email – text, required. Used for login and communication; should be unique. password – hash value, required. Stored hashed for authentication. delivery address – multi-valued text, required. One or more delivery addresses for shipments. last mod. date – timestamp, optional. Tracks last update of the client record. status – text, optional. Current customer status (e.g., ACTIVE, BLOCKED).

---

ANALYTICS SYSTEM
Represents the analytics/reporting subsystem that processes sales, inventory and customer data to produce statistics and insights. It is a logical entity used for reporting and access control.
Candidate keys: (no natural key) – single system instance identified by internal id if needed.
Attributes: statistics – text/JSON, optional. Aggregated metrics and reports. quantity – numeric, optional. Aggregated counts used in reports.

---

RELATIONS

granted (Personal – Permission)
Represents which permissions each employee has been granted. Needed to control system access, operations allowed, and to ensure accountability. Modeled as M–N: one person can have many permissions; each permission type can be assigned to many personnel.
Keys: Composite key: (SSN, permission_id).
Attributes: This relationship has no additional attributes beyond the foreign keys.

---

manages_product (Personal – Product – Permission)
Represents that a member of the personnel has the permission to perform actions on products such as adding, deleting, or updating them. This is needed to ensure that only authorized employees can modify the product catalog and maintain store safety and integrity. Modeled as M–N between Personal and Product, but each operation is conditioned by a Permission.
Keys: Composite key: (SSN, code, permission_id).
Attributes: No extra attributes beyond the three foreign keys.

---

worked (Personal – WeeklyWork)
Represents how many hours a member of the personnel has worked during each week, and allows calculation of total past working hours. Needed for payroll, scheduling, and labor tracking. Modeled as 1–N: each personal member has many weekly records.
Keys: Composite key: (SSN, week, year).
Attributes: hours_worked – numeric, required. total_hours_before – numeric, optional. week – numeric, required. year – numeric, required.

---

supervises (Boss – Employees)
Represents that a Boss is able to look at the Employees' activity in the company structure. Needed to track the permission of the owner to supervise ALL employees(managers and store attendants)and to reflect managerial hierarchy. Modeled as N–N: N Bosses can supervise multiple Employees; each Employee has all the objects of the Boss entity as a superior supervision.
Keys: Both sides identified by SSN. Relationship uniqueness pair: (boss_SSN, employee_SSN).
Attributes: No attributes other than the foreign key boss_SSN.

---

supervises (Managers – Store Atendants)
Represents that a Manager oversees one or more Store Attendants in the store structure. Needed to track who supervises whom and to reflect managerial hierarchy. Modeled as 1–N: one Manager supervises multiple Store Attendants; each Store Attendant has exactly one Manager.
Keys: Both sides identified by SSN. Relationship uniqueness pair: (manager_SSN, stAttendant_SSN).
Attributes: No attributes other than the foreign key manager_SSN.

---

has_access (Boss/Managers – AnalyticsSystem)
Represents that Bosses and Managers have access to the analytics system. Needed to restrict access to sensitive business metrics, store performance, and decision-making tools. Modeled as M–N because multiple managers and bosses can access the system, and the system recognizes multiple authorized users.
Keys: Composite key: (SSN, analytics_ID).
Attributes: No additional attributes; access level is implied by the employee’s role.

---

works_in (Employees – Store)
Connects each employee to the store they are assigned to. Needed for shift scheduling, stock handling, supervisor assignment and internal organization. Modeled as N–1: multiple employees can work in the same store; each employee belongs to only one store at a time.
Keys: Identified by SSN. Relationship-level uniqueness: (store_ID, SSN).
Attributes: Implemented via foreign key store_ID.

---

past_work (Managers – Store)
Represents the stores in which a manager has worked previously. Needed for human resources management, career tracking, and for understanding experience level. Modeled as M–N: a manager may have worked in multiple stores; a store may have had multiple managers over the years.
Keys: Composite key: (manager_SSN, store_ID).
Attributes: start_date – date, optional. end_date – date, optional.

---

has_in_stock (Store – Product)
Represents which store possesses which products and in what quantity. Needed to track inventory per location and manage transfers, sales and stock alerts. Modeled as M–N: a store can hold many products; a product can be stored in many store locations.
Keys: Composite key: (store_ID, code).
Attributes: quantity – numeric, required. Amount of the product stored at the given store.

---

places_order (Client – Product)
Represents that a client orders a specific product. Needed to manage sales, delivery scheduling, order history, and revenue tracking. Modeled as M–N: a client can order many products; each product can be ordered by many clients.
Keys: Composite key: (client_ID, code, order_date).
Attributes: order_date – timestamp, required. quantity – numeric, required. delivery_address – text, required.

---

logs_into (Client – AnalyticsSystem)
Represents that a client logs into the analytics page. Although clients normally do not access analytics, this relationship formally captures login attempts or special access privileges in certain cases. Modeled as M–N because a client may log in multiple times, and the system logs multiple users.
Keys: Composite key: (client_ID, analytics_ID, timestamp).
Attributes: timestamp – datetime, required. Time of login event.

---

ERModel History

First submitted on 12.09.2025