INS3282 • CAPSTONE PROJECT II

TUTORIAL 4 – SYSTEM DESIGN

Project: Inventory & Demand Decision Support System
ACTIVITY 2 – ERD, NORMALIZATION, AND DATA DICTIONARY

Goal: create a consistent data model that supports the inventory workflow, its controls (RBAC, PO approval/locking,
FEFO, reorder alerts), and the reports required by Admin and Manager (steps 11–14 of the Tutorial 4 guide).

1. Core Entities and Primary Keys (Step 11)

Thirteen entities were identified directly from the requirement baseline and the three user roles' workflows
(login/RBAC, master data, purchasing, receiving, counting, alerting, auditing, feedback). Every entity has a single-
column or composite surrogate primary key:

Traced to
Entity Primary Key Business Purpose
(FR/BR/NFR)
Defines the fixed RBAC role set (Admin, Manager, FR-SYS-01, BR-19,
ROLES role_id
Store Staff) that every user is assigned to. NFR-03
Stores login accounts and links each staff member FR-ADM-02, FR-
USERS user_id
to exactly one role. ADM-03, NFR-03
Groups products that share replenishment FR-ADM-04, FR-
CATEGORIES category_id
behaviour and holds the category-level default rule. ADM-05, BR-01
Master data for vendors, including lead time used FR-ADM-01, FR-
SUPPLIERS supplier_id
in PO planning. MGR-11
Core SKU master record; one product belongs to BR-01, BR-16, FR-
PRODUCTS product_id
one category and one primary supplier. STF-01
FR-MGR-05/06, FR-
Header record of a replenishment order: who
PURCHASE_ORDERS po_id ADM-06, BR-07,
created it, who approved it, its status.
BR-20
po_id + product_id Junction/line-item entity — one row per product FR-MGR-04, BR-06,
PO_DETAILS
(composite) ordered on a PO. BR-08/09/10
One row per physical delivery batch of a product, FR-STF-06, FR-STF-
INVENTORY_BATCHES batch_id
carrying its own expiry date. 12 (FEFO)
System-generated low-stock / stock-out-risk FR-SYS-02, BR-04,
STOCK_ALERTS alert_id
notifications per product. BR-13, FR-MGR-12
STOCK_COUNT_SESSI Header record of one physical stock-count exercise FR-STF-09, FR-
session_id
ONS performed by a staff member. ADM-09
session_id +
STOCK_COUNT_DETAI Junction/line-item entity — one row per product
product_id FR-STF-04, BR-14
LS counted within a session.
(composite)
Immutable record of every sensitive action for FR-SYS-03, FR-
AUDIT_LOGS log_id
traceability and review. ADM-07, NFR-09
CUSTOMER_FEEDBAC Complaint/feedback entries linked to a specific out-
feedback_id FR-STF-11
K of-stock product.

2. Resolving Repeating Groups and Many-to-Many Relationships

(Step 12)

2.1 Purchase-order lines → PO_DETAILS junction

A Purchase Order can contain many products, and the same product can appear on many purchase orders — a
classic many-to-many relationship. Storing the product list as repeating columns on PURCHASE_ORDERS
(product1_id, qty1, product2_id, qty2, …) would violate 1NF and cap the number of line items. Instead we apply the
same Order–OrderItem–Product pattern taught in the facilitator check:

Classic pattern This project

Order PURCHASE_ORDERS (header: supplier, creator, approver, status, total)
PO_DETAILS (junction: composite key po_id + product_id, plus line-specific
OrderItem
ordered_qty / received_qty)
Product PRODUCTS
PO_DETAILS also carries attributes that belong to neither parent — the ordered quantity (which the Manager may
override, BR-06) and the received quantity (entered by Store Staff and cross-checked against the order, BR-08–BR-
10). These line-specific facts are exactly why a junction table is needed instead of a simple lookup relationship.

2.2 Stock-count lines → STOCK_COUNT_DETAILS junction

The same many-to-many shape recurs for physical stock counts: one count session covers many products, and one
product is counted across many sessions over time. STOCK_COUNT_SESSIONS plays the role of "Order",
STOCK_COUNT_DETAILS plays "OrderItem" (composite key session_id + product_id, holding system_qty /
actual_qty / variance), and PRODUCTS is again the shared "Product" entity (FR-STF-04, BR-14).

2.3 Repeating batches → INVENTORY_BATCHES

A single product is received in multiple deliveries over time, each with its own expiry date — another repeating
group if it were modelled as columns on PRODUCTS. INVENTORY_BATCHES normalizes this into one row per
delivery (batch_id as PK, product_id as FK), which is also what makes FEFO picking possible: the query simply orders
a product's batches by expiry_date ascending (FR-STF-12).
Facilitator check: a diagram shows what connects (PURCHASE_ORDERS – PO_DETAILS – PRODUCTS and
STOCK_COUNT_SESSIONS – STOCK_COUNT_DETAILS – PRODUCTS); this section explains why those two junction tables
exist — they resolve M:N relationships and hold line-specific facts that cannot live on either parent.

3. Normalization Check – 1NF, 2NF, 3NF (Step 13)

Each entity was checked in turn. “Pass” means the rule holds without exception; “Deliberate exception” marks a
documented, justified denormalization rather than a design defect.

1NF (atomic 2NF (no partial 3NF (no transitive

Entity Notes
values) dep.) dep.)
Simple lookup table; no derived or
ROLES Pass Pass Pass
repeating attributes.
role_id is a foreign key, not a
USERS Pass Pass Pass
transitive attribute of user data.
default_safety_stock /
default_reorder_point are the
CATEGORIES Pass Pass Pass
category's own attributes, not copies
of another row.
SUPPLIERS Pass Pass Pass No derived columns.
unit_price, min/max/safety/reorder
PRODUCTS Pass Pass Pass values all depend only on product_id,
not on category_id or supplier_id.
 1NF (atomic 2NF (no partial 3NF (no transitive
Entity Notes
values) dep.) dep.)
total_amount is derivable from
PO_DETAILS (a child table) rather than
from another column of the same
Deliberate
PURCHASE_ORDERS Pass Pass row, so it is not a classic transitive
exception
dependency — it is a cached
aggregate. Documented as
denormalization below.
ordered_qty and received_qty depend
on the whole (po_id, product_id) pair,
PO_DETAILS Pass Pass – verified Pass
not on po_id or product_id alone →
no partial dependency.
Splitting batches into rows (instead of
batch1_qty, batch2_qty... columns on
INVENTORY_BATCHES Pass Pass Pass
PRODUCTS) is what makes this table
1NF-compliant.
STOCK_ALERTS Pass Pass Pass No derived columns.
total_variance_rate is a cached
STOCK_COUNT_SESSIO Deliberate aggregate of STOCK_COUNT_DETAILS.
Pass Pass
NS exception Documented as denormalization
below.
system_qty/actual_qty depend on the
composite key; variance is a stored
STOCK_COUNT_DETAIL Deliberate
Pass Pass – verified computed column (system_qty −
S exception
actual_qty). Documented as
denormalization below.
table_name/record_id describe the
AUDIT_LOGS Pass Pass Pass logged event itself, not a transitive
fact about user_id.
CUSTOMER_FEEDBACK Pass Pass Pass No derived columns.

3.1 Deliberate denormalization — reason and refresh rule

● PURCHASE_ORDERS.total_amount — cached sum of PO_DETAILS.ordered_qty × PRODUCTS.unit_price.
Reason: avoids a join + aggregate every time the PO list/dashboard renders, protecting the NFR-02 “load ≤
3s” target. Refresh rule: recalculated whenever a PO_DETAILS row for that po_id is inserted, updated, or
deleted.
● STOCK_COUNT_SESSIONS.total_variance_rate — cached aggregate of STOCK_COUNT_DETAILS.variance for
that session. Reason: lets Admin's discrepancy-history report (FR-ADM-09) list sessions without re-
aggregating every detail row. Refresh rule: recalculated once, when the session is marked complete.
● STOCK_COUNT_DETAILS.variance — stored as system_qty − actual_qty instead of computed on read.
Reason: enables direct SQL sort/filter for the discrepancy and priority alert lists (BR-13, BR-14). Refresh
rule: recalculated whenever system_qty or actual_qty on that row changes.
● CATEGORIES.default_safety_stock / default_reorder_point vs. PRODUCTS.safety_stock / reorder_point —
not row duplication but a template/override pattern: category defaults are copied into a new product at
creation time and may then be overridden per product (Admin-only, BR-16). Keeping both lets an Admin
reset a product to its category baseline and lets category-level policy evolve independently of already-
customized products (FR-ADM-04).
No other transitive or partial dependencies were found; all remaining non-key attributes depend on the whole
primary key of their own table and nothing else.
4. Entity-Relationship Diagram
The ERD below implements the entities, keys, and junction tables described in Sections 1–2. Cardinalities: ROLES 1–
N USERS; CATEGORIES 1–N PRODUCTS; SUPPLIERS 1–N PRODUCTS and 1–N PURCHASE_ORDERS; USERS 1–N
PURCHASE_ORDERS (as creator and, separately, as approver), 1–N STOCK_COUNT_SESSIONS, 1–N AUDIT_LOGS, 1–
N CUSTOMER_FEEDBACK; PRODUCTS 1–N INVENTORY_BATCHES, STOCK_ALERTS, PO_DETAILS,
STOCK_COUNT_DETAILS, CUSTOMER_FEEDBACK; PURCHASE_ORDERS 1–N PO_DETAILS; STOCK_COUNT_SESSIONS
1–N STOCK_COUNT_DETAILS.

Figure 1. Inventory & Demand Decision Support System – full ERD (13 entities, 2 junction tables).
5. Data Dictionary (Step 14)
Field type, key/required status, and validation / business meaning for every entity. FK targets and the requirement
IDs they enforce are noted in the meaning column so each field stays traceable to the baseline (NFR-09).

Entity Field Type Key / Required Validation / Business Meaning

ROLES role_id int PK Auto-increment. Uniquely identifies a role.
Enum-style value: "Admin" / "Manager" / "Store
role_name string(50) Required, Unique Staff". Drives menu and route access (FR-SYS-
01).
USERS user_id int PK Auto-increment.
username string(50) Required, Unique Login identifier (FR-SYS-01).
Stores a salted hash only — plaintext is never
password_hash string(255) Required
persisted (NFR-03).
Determines the functions the user may access
FK -> ROLES.role_id,
role_id int (FR-ADM-03, BR-19); an invalid/locked role
Required
blocks login (FR-ADM-02).
CATEGORIES category_id int PK Auto-increment.
E.g. "Fresh Food", "Beverage", "Non-Food" (FR-
category_name string(100) Required, Unique
ADM-05).
default_safety_stoc Baseline value copied into a new product's
int Required, >= 0
k safety_stock at creation (FR-ADM-04, BR-05).
default_reorder_po Baseline value copied into a new product's
int Required, >= 0
int reorder_point at creation (FR-ADM-04).
SUPPLIERS supplier_id int PK Auto-increment.
supplier_name string(150) Required Legal / trading name.

contact_info string(255) Optional Phone and/or email of the vendor contact.

Days between PO submission and expected
lead_time_days int Required, >= 0 delivery; shown on the supplier profile and PO-
creation screen (FR-MGR-11).
PRODUCTS product_id int PK Auto-increment.
sku_code string(50) Required, Unique Unique stock-keeping unit code (BR-01).

product_name string(150) Required Display name.

Used to compute PO line value and
unit_price decimal(10,2) Required, >= 0
PURCHASE_ORDERS.total_amount.
FK -> CATEGORIES, Every product belongs to exactly one category
category_id int
Required (BR-01).
FK -> SUPPLIERS,
supplier_id int Primary supplying vendor for this SKU.
Required
Lower operational bound used in stock-level
min_stock int Required, >= 0
reporting.
Required, > Upper operational bound; caps reorder-
max_stock int
min_stock quantity suggestions.
Required, >= 0, Buffer stock; product-level override of the
safety_stock int
Admin-only edit category default (BR-16).
Required, >= 0, Threshold that triggers STOCK_ALERTS when
reorder_point int
Admin-only edit current stock <= this value (BR-04, FR-MGR-03).
 Entity Field Type Key / Required Validation / Business Meaning
PURCHASE_ORDE
po_id int PK Auto-increment.
RS
FK -> SUPPLIERS,
supplier_id int Vendor the order is placed with.
Required
Must resolve to a user whose role is Manager
creator_id int FK -> USERS, Required
(FR-MGR-05).
Populated only when a user with role Admin
approver_id int FK -> USERS, Nullable approves the order (FR-ADM-06, BR-07); NULL
while Draft/Pending.
Enum: Draft / Pending / Approved / Rejected /
status string(20) Required Delivered. Governs the PO-locking state
machine (BR-20).
Cached sum of PO_DETAILS (ordered_qty x
total_amount decimal(12,2) Required
unit_price) — see §3 denormalization note.
Timestamp of PO creation, used for status-
created_at datetime Required, auto-set
history reporting (FR-MGR-06).
PO_DETAILS PK (composite), FK ->
po_id int Identifies the parent order.
(junction) PURCHASE_ORDERS
Identifies the ordered product; together with
PK (composite), FK ->
product_id int po_id forms the composite key so the same
PRODUCTS
product cannot appear twice on one PO.
System-suggested quantity, editable by the
ordered_qty int Required, > 0 Manager before submission; every change is
logged (FR-MGR-04, BR-06).
Set by Store Staff during receiving; cross-
checked against ordered_qty and any mismatch
received_qty int Required, default 0
is flagged as a discrepancy (FR-STF-05/07, BR-
08/09/10).
INVENTORY_BAT
batch_id int PK Auto-increment. One row per physical delivery.
CHES
FK -> PRODUCTS,
product_id int Product this batch belongs to.
Required
received_date date Required Date stock-in was confirmed (FR-STF-06).
Used to rank batches for FEFO picking — the
Optional (NULL for
expiry_date date batch with the earliest expiry is suggested first
non-perishables)
(FR-STF-12).
Remaining quantity in this specific batch; sum
current_quantity int Required, >= 0 across a product's batches = its system stock
level.
STOCK_ALERTS alert_id int PK Auto-increment.
FK -> PRODUCTS,
product_id int Product the alert refers to.
Required
E.g. "Low Stock", "Reorder Point", "Stock-out
alert_type string(30) Required
Risk" (FR-SYS-02, BR-04, BR-13, FR-MGR-12).
Used to measure alert latency against the
created_at datetime Required, auto-set
acceptance criteria of FR-SYS-02 / FR-MGR-12.
Required, default Cleared once the Manager actions the alert (e.g.
is_resolved boolean
false a PO is created).
 Entity Field Type Key / Required Validation / Business Meaning
STOCK_COUNT_S
session_id int PK Auto-increment.
ESSIONS
Store Staff who performed the count (FR-STF-
user_id int FK -> USERS, Required
09).
counted_at datetime Required Timestamp the session was completed.
Cached aggregate variance % across the session
total_variance_rate decimal(5,2) Required — see §3 denormalization note; supports the
Admin discrepancy-history view (FR-ADM-09).
STOCK_COUNT_ PK (composite), FK ->
DETAILS session_id int STOCK_COUNT_SESSI Identifies the parent count session.
(junction) ONS
Identifies the counted product; composite key
PK (composite), FK ->
product_id int prevents duplicate counts of the same product
PRODUCTS
in one session.
System-recorded quantity at the moment of
system_qty int Required, >= 0
counting.
Physically counted quantity, entered by Store
actual_qty int Required, >= 0
Staff (FR-STF-04).
Stored/computed = Cached so the discrepancy list can be
variance int system_qty − sorted/filtered in SQL without recomputation
actual_qty (BR-13, BR-14).
AUDIT_LOGS log_id int PK Auto-increment.
Actor who performed the sensitive action (FR-
user_id int FK -> USERS, Required
SYS-03).
E.g. "PO_APPROVED",
action_taken string(100) Required
"REORDER_RULE_CHANGED" (FR-ADM-07).
Target table of the action; makes the log
table_name string(50) Required
filterable by entity (FR-ADM-07).
record_id int Required Primary-key value of the affected record.
When the action occurred; supports date-range
timestamp datetime Required, auto-set
filtering (FR-ADM-07).
CUSTOMER_FEE
feedback_id int PK Auto-increment.
DBACK
FK -> PRODUCTS, Links the feedback to the specific out-of-stock
product_id int
Required product (FR-STF-11).
staff_id int FK -> USERS, Required Store Staff who logged the complaint.

comment string(500) Optional Free-text detail of the complaint.

created_at datetime Required, auto-set Time the feedback was recorded.

6. Traceability and Quality-Gate Check

● Every Must-have FR that touches data (FR-SYS-01–03, FR-ADM-01–06, FR-MGR-01–06/12, FR-STF-01–09)
maps to at least one entity/field above — satisfying the guide's quality-gate item “every Must-have
requirement appears in the data model.”
● Keys, relationships, and status values are explicit: every table has a stated PK, every FK names its parent
table, and enumerated fields (status, alert_type, role_name) list their allowed values.
 ● Security/authorization boundaries are visible at the data level: password_hash is never stored in plaintext
(NFR-03), role_id gates USERS, and only Admin-authored rows may edit PRODUCTS.safety_stock /
reorder_point (BR-16) or PURCHASE_ORDERS.approver_id (BR-07).
● PO locking (BR-20) is representable purely through PURCHASE_ORDERS.status transitions (Pending →
Approved/Rejected), with the application layer disabling edits to PO_DETAILS while status = Pending.
● FEFO (FR-STF-12) and the Top-10 stock-out risk list (FR-MGR-12) are both computable from existing
columns (INVENTORY_BATCHES.expiry_date; INVENTORY_BATCHES.current_quantity vs. recent
PO_DETAILS/sales velocity) without further schema changes.
Facilitator check satisfied: the diagram (Section 4) shows what connects; Sections 2–3 explain why — including why
PO_DETAILS and STOCK_COUNT_DETAILS exist as junction tables, following the Order–OrderItem–Product pattern.