8.4 Data, Logic Development, and Analytics

8.4 Data, Logic Development, and Analytics

The NCEES FE Industrial and Systems specification places data, logic development, and analytics alongside linear programming and stochastic modeling inside the Modeling and Quantitative Analysis knowledge area. That grouping is deliberate: an optimization model, a queuing study, and a control chart are all worthless if the underlying data is mis-typed, duplicated, or pulled from the wrong join. This section covers the data structures, logic constructs, algorithmic reasoning, and analytics vocabulary an industrial engineer is expected to command.

---

1. Data Types and Measurement Scales

Before any statistic is computed, the engineer must classify the measurement scale of each field, because the scale determines which mathematical operations are meaningful.

Exam Watchout: Only interval and ratio data support an arithmetic mean. Averaging ordinal satisfaction codes (1 = poor, 5 = excellent) to report a "3.7 average" assumes equal spacing that the scale does not provide. Report the median or the top-two-box percentage instead.

Structured, Semi-Structured, and Unstructured Data

• Structured: Fixed schema in rows and columns (ERP transaction tables, MES cycle-time logs, PLC tag histories). Directly queryable.
• Semi-Structured: Self-describing but schema-flexible (JSON payloads from IIoT sensors, XML EDI documents, log files). Requires parsing before analysis.
• Unstructured: No inherent record structure (inspection photographs, maintenance technician free-text notes, thermal imagery). Requires feature extraction or natural-language processing.

Data Quality Dimensions

Six dimensions are audited before a data set is trusted for engineering decisions: accuracy (matches physical reality), completeness (no missing required fields), consistency (same fact agrees across systems), timeliness (current enough for the decision), validity (conforms to the defined domain and format), and uniqueness (no duplicate records). A single duplicated work order inflates throughput statistics; a missing scrap code silently biases a Pareto chart.

2. Relational Databases

Nearly all manufacturing execution, ERP, quality, and maintenance systems sit on relational databases. The relational model organizes data into two-dimensional tables (relations).

                Relational Terminology Map
  ┌───────────────────────────────────────────────────────────┐
  │ TABLE (Relation)  =  a named two-dimensional data set     │
  │   ROW (Tuple / Record)  =  one real-world instance        │
  │   COLUMN (Attribute / Field)  =  one property             │
  │   DOMAIN  =  the legal value set for a column             │
  │   CARDINALITY = number of rows;  DEGREE = number of columns│
  └───────────────────────────────────────────────────────────┘

Keys

• Primary Key (PK): The attribute (or minimal combination of attributes) that uniquely identifies each row. A primary key must be unique and never null. Example: part_number in a PARTS table.
• Candidate Key: Any attribute set that could serve as the primary key. One candidate is chosen as the PK; the rest become alternate keys.
• Composite Key: A primary key built from two or more columns, common in transaction tables (e.g., work_order_id + operation_seq).
• Foreign Key (FK): An attribute in one table whose values must exist as primary-key values in another table. Foreign keys implement the relationships between tables and enforce referential integrity — the database rejects a routing row that references a nonexistent part number.

Relationship Cardinality

• One-to-many (1:N): One SUPPLIER supplies many PARTS. The FK lives on the "many" side.
• Many-to-many (M:N): Many PARTS are used on many PRODUCTS. Relational databases cannot store M:N directly; an associative (junction) table such as BILL_OF_MATERIALS holds a composite key of both parent keys plus the quantity-per attribute.
• One-to-one (1:1): Rare; usually a table split for security or sparse attributes.

Normalization

Normalization is the systematic decomposition of tables to eliminate redundant storage and the three update anomalies: insertion (cannot record a new supplier until it ships a part), update (changing an address in one row and not another), and deletion (removing the last order for a part erases the part's description).

Third normal form is the practical target for transactional systems. Analytical reporting warehouses deliberately denormalize into star schemas (a central fact table surrounded by dimension tables) to trade storage for query speed.

Query Logic (SQL Semantics)

An FE candidate is not asked to write production SQL, but is expected to read the logic of a query:

• SELECT chooses columns (a projection).
• WHERE filters rows (a selection), applied before grouping.
• JOIN ... ON combines tables on matching key values. An inner join keeps only matching rows; a left outer join keeps every row of the left table and inserts nulls where no match exists.
• GROUP BY collapses rows into groups and applies aggregate functions (COUNT, SUM, AVG, MIN, MAX).
• HAVING filters groups, applied after aggregation. Filtering an aggregate in WHERE is the classic logic error.

3. Logic Development: Flowcharts, Decision Tables, and Pseudocode

Flowchart Symbols (ANSI/ISO 5807)

Program and process logic is documented with a standard symbol set. Note that these are program flowchart symbols and differ from the ASME process chart symbols used in methods engineering.

            Reorder-Point Check: Flowchart Logic
                     ( START )
                         |
              [ Read on-hand qty Q, ROP R ]
                         |
                      < Q <= R ? >
                     /            \
                 Yes /              \ No
                    |                |
      [ Release replenishment ]   [ No action ]
                    |                |
                    +--------+-------+
                             |
                          ( STOP )

The Three Control Structures

Any algorithm, no matter how complex, is built from exactly three structures (the structured-programming theorem):

1. Sequence: Statements execute one after another.
2. Selection (branching): IF condition THEN ... ELSE ..., or a multi-way CASE.
3. Iteration (looping): WHILE condition DO ... (test first, may execute zero times) or FOR i = 1 TO n (definite count).

Two loop errors dominate exam questions: the infinite loop (the loop variable is never advanced or the exit condition can never become false) and the off-by-one error (looping 1 TO n-1 when n items must be processed).

Boolean Logic and Decision Tables

Selection conditions are Boolean expressions combined with AND ($\cap$), OR ($\cup$), and NOT. De Morgan's laws are the standard simplification tool:

$\overline{A \cap B} = \bar{A} \cup \bar{B} \qquad \overline{A \cup B} = \bar{A} \cap \bar{B}$

A decision table enumerates every combination of $k$ Boolean conditions ($2^k$ rules) against the actions to be taken. Decision tables are preferred over nested IF statements when the number of conditions exceeds three, because they make missing and contradictory rules visible.

Pseudocode

Pseudocode expresses the same logic in language-independent statements with explicit indentation. Example — computing the reorder point from a demand history array:

INPUT  d[1..n], L, z
sum <- 0
FOR i = 1 TO n
    sum <- sum + d[i]
END FOR
dbar <- sum / n
ss   <- 0
FOR i = 1 TO n
    ss <- ss + (d[i] - dbar)^2
END FOR
sigma <- SQRT( ss / (n - 1) )
ROP   <- dbar * L + z * sigma * SQRT(L)
OUTPUT ROP

4. Algorithms and Computational Complexity

Big-O Notation

Big-O describes the asymptotic worst-case growth rate of an algorithm's operation count as the input size $n$ grows. Constant factors and lower-order terms are dropped: an algorithm requiring $5n^2 + 300n + 900$ operations is $O(n^2)$.

The practical dividing line is between polynomial-time algorithms (tractable) and exponential-time enumeration (intractable). This is exactly why the facility layout Quadratic Assignment Problem, the Traveling Salesman Problem, and integer programming rely on heuristics rather than exhaustive search.

Searching

• Linear (sequential) search scans records one at a time. Worst case $n$ comparisons, average $n/2$. Works on unsorted data.
• Binary search requires the data be sorted. It compares the target with the middle element and discards half the remaining range each iteration, so the range after $k$ probes is at most $n/2^k$ and the search terminates when that falls below 1. Maximum comparisons: $C_{\max} = \lfloor \log_2 n \rfloor + 1 = \lceil \log_2 (n + 1) \rceil$ Doubling the file size adds exactly one comparison — the practical reason inventory master files are indexed.

Greedy vs. Exhaustive vs. Heuristic Search

• Greedy: Take the locally best step at every stage (Nearest Neighbor routing, Largest Candidate line balancing). Fast, never guaranteed optimal.
• Exhaustive / exact: Evaluate every feasible combination (complete enumeration, branch and bound with full exploration). Guarantees optimality, explodes combinatorially.
• Metaheuristic: Structured randomized search that escapes local optima (simulated annealing, genetic algorithms, tabu search). Used where the exact model is intractable and greedy answers are too poor.

5. Data Science Techniques for Industrial Systems

Supervised vs. Unsupervised Learning

                     Analytics Task Taxonomy
                              |
        +---------------------+---------------------+
        |                                           |
   SUPERVISED (labeled response y exists)      UNSUPERVISED (no labels)
        |                                           |
  +-----+------+                            +-------+--------+
  |            |                            |                |
Regression  Classification              Clustering     Dimension Reduction
(continuous y) (categorical y)          (k-means,      (PCA, factor
                                        hierarchical)   analysis)
Ex: predict    Ex: pass/fail from       Ex: group SKUs  Ex: collapse 40
tool life from inline sensor traces     by demand       sensor channels
feed and speed                          signature       into 3 components

• Regression predicts a continuous response — the same least-squares machinery covered in the statistics chapter, extended to many predictors.
• Classification predicts a categorical label. Common industrial classifiers are logistic regression, decision trees, random forests, and support vector machines.
• Clustering (notably k-means, which iteratively assigns points to the nearest of $k$ centroids and then recomputes those centroids) groups similar records without any labels. It underpins part-family formation for cellular manufacturing and SKU segmentation for slotting.
• Dimension reduction (principal component analysis) compresses many correlated sensor channels into a few orthogonal components that explain most of the variance.

Analytics Maturity Ladder

1. Descriptive: What happened? Dashboards, Pareto charts, run charts.
2. Diagnostic: Why did it happen? Drill-down, correlation, root cause analysis.
3. Predictive: What will happen? Regression forecasts, remaining-useful-life models.
4. Prescriptive: What should we do? Optimization and simulation feeding a recommended action.

Evaluating a Classifier: The Confusion Matrix

A binary classifier is scored against known truth in a $2 \times 2$ confusion matrix:

$\text{Accuracy} = \frac{TP + TN}{TP + TN + FP + FN} \qquad \text{Precision} = \frac{TP}{TP + FP} \qquad \text{Recall (Sensitivity)} = \frac{TP}{TP + FN}$

$F_1 = \frac{2 \times \text{Precision} \times \text{Recall}}{\text{Precision} + \text{Recall}}$

The Accuracy Trap in Manufacturing: When only 1% of parts are defective, a model that predicts "good" for every part achieves 99% accuracy while catching zero defects. On rare-event quality problems, judge the model on recall (escapes prevented) and precision (false alarms avoided), never on raw accuracy — the same base-rate reasoning used with Bayes' theorem for inspection systems.

Overfitting and Validation

A model tuned until it reproduces every wiggle in the historical data has memorized noise and will forecast poorly. Guard against this by splitting the data into training, validation (tuning), and test (final, untouched) partitions, or by k-fold cross-validation. A large gap between training performance and test performance is the diagnostic signature of overfitting.

6. Step-by-Step Worked Engineering Examples

Worked Example 8.4.1: Normalization and a Join-Plus-Aggregate Query

Problem: A plant records scrap in one flat spreadsheet:

1. Identify the normalization violation and decompose the data into 3NF tables.
2. State the query logic that reports total scrap quantity by supplier city.

Solution:

1. Diagnose the violation. The table is in 1NF (all cells atomic) and its key is work_order. However, part_desc depends on part_no, and supplier_city depends on supplier_id — both are non-key attributes determining other non-key attributes. These are transitive dependencies, so the table violates 3NF. The symptom is visible in the data: "Cast housing" and "Toledo" are stored twice, so a city correction applied to only one row leaves the database inconsistent.

2. Decompose into 3NF:

   • SUPPLIER(supplier_id PK, supplier_city)
   • PART(part_no PK, part_desc, supplier_id FK -> SUPPLIER)
   • SCRAP(work_order PK, part_no FK -> PART, scrap_qty) Each fact is now stored exactly once, and referential integrity blocks a scrap record that names a nonexistent part.

3. Query logic for scrap by city: join SCRAP to PART on part_no, join PART to SUPPLIER on supplier_id, group the joined rows by supplier_city, and sum scrap_qty within each group:

   SELECT   s.supplier_city, SUM(k.scrap_qty) AS total_scrap
   FROM     SCRAP k
   JOIN     PART p    ON k.part_no     = p.part_no
   JOIN     SUPPLIER s ON p.supplier_id = s.supplier_id
   GROUP BY s.supplier_city
   HAVING   SUM(k.scrap_qty) > 10
   

   Result: Toledo = $12 + 5 = 17$ (passes the HAVING filter); Akron = $8$ (filtered out). Note that the threshold on the aggregate belongs in HAVING, not WHERE.

---

Worked Example 8.4.2: Search Algorithm Selection

Problem: An MES queries a part master file containing $n = 262{,}144$ records.

1. Compute the worst-case comparison count for a linear search and for a binary search on a sorted file.
2. The file grows to $4{,}194{,}304$ records (16x). By how much does each method's worst case grow?

Solution:

1. Linear search examines records one at a time: worst case $= n = 262{,}144$ comparisons (average $\approx 131{,}072$). Binary search halves the candidate range each comparison. Since $262{,}144 = 2^{18}$: $C_{\max} = \lfloor \log_2 n \rfloor + 1 = 18 + 1 = 19\text{ comparisons}$ (Eighteen halvings shrink the range from $2^{18}$ to 1, and the nineteenth probe tests that last candidate.)
2. At $n = 4{,}194{,}304 = 2^{22}$:
   • Linear worst case $= 4{,}194{,}304$ comparisons — a 16x increase, matching $O(n)$.
   • Binary worst case $= \lfloor \log_2 2^{22} \rfloor + 1 = 23$ comparisons — an increase of only 4 comparisons, matching $O(\log n)$: each doubling of the file adds exactly one comparison, and $16 = 2^4$.
3. Engineering conclusion: the sort-plus-index overhead is repaid immediately. Note the precondition — binary search is invalid on an unsorted file, so the maintenance cost of keeping the index sorted is the real design trade-off.

---

Worked Example 8.4.3: Confusion Matrix for an Automated Inspection Model

Problem: A machine-vision classifier screens 10,000 stamped panels. Truth data from teardown audit shows 200 panels were genuinely defective. The model flagged 260 panels as defective, and 170 of those flags were correct.

1. Complete the confusion matrix.
2. Compute accuracy, precision, recall, and $F_1$.
3. Interpret the result for a quality engineer.

Solution:

1. Build the matrix:

   • $TP = 170$ (flagged and truly defective)
   • $FP = 260 - 170 = 90$ (flagged but actually good)
   • $FN = 200 - 170 = 30$ (defective escapes the model missed)
   • $TN = 10{,}000 - 170 - 90 - 30 = 9{,}710$

2. Compute metrics: $\text{Accuracy} = \frac{170 + 9{,}710}{10{,}000} = \frac{9{,}880}{10{,}000} = 0.9880 \quad (98.80\%)$ $\text{Precision} = \frac{TP}{TP + FP} = \frac{170}{260} = 0.6538 \quad (65.38\%)$ $\text{Recall} = \frac{TP}{TP + FN} = \frac{170}{200} = 0.8500 \quad (85.00\%)$ $F_1 = \frac{2(0.6538)(0.8500)}{0.6538 + 0.8500} = \frac{1.11146}{1.50380} = 0.7391$

3. Interpretation: The headline 98.8% accuracy is nearly meaningless — predicting "good" for every panel would score 98.0% by itself. The operationally relevant numbers are that 30 defective panels per 10,000 still escape (15% of all defects) and that 90 of every 260 alarms are false, consuming teardown labor. Raising the classifier's decision threshold reduces false alarms (precision up) but lets more defects escape (recall down); the correct threshold follows from the relative cost of an escape versus a false alarm, exactly the producer's-risk versus consumer's-risk trade-off from acceptance sampling.

---

7. NCEES Reference Handbook Tips and Realistic Exam Traps

• Primary vs. Foreign Key: The primary key uniquely identifies a row within its own table and can never be null. A foreign key points at another table's primary key and may legitimately repeat many times. Reversing these two definitions is the most common database question error.
• WHERE vs. HAVING: WHERE filters individual rows before grouping; HAVING filters groups after aggregation. A condition on SUM(...) or COUNT(...) can only appear in HAVING.
• Big-O Constants Are Dropped: An algorithm counted at $3n^2 + 5000n$ is $O(n^2)$, not $O(n^2 + n)$ and not $O(3n^2)$. Asymptotic notation reports only the dominant growth term.
• Binary Search Requires Sorted Data: If a question states the records are in arrival order or unsorted, binary search is not an option; the answer is the $O(n)$ linear scan (or sort first, at $O(n \log n)$).
• Do Not Average Ordinal Codes: A "3.7 average" on a 1-to-5 satisfaction scale assumes equal spacing the ordinal scale does not guarantee. Use the median or a top-two-box percentage.
• Accuracy Is the Wrong Metric for Rare Defects: With a 1% defect rate, judge classifiers on precision and recall, not accuracy.