Data Management and Databases

Introduction

Modern mechatronic systems generate data continuously. Sensors record temperature, vibration, position, pressure, current, and speed; controllers issue commands; maintenance systems store inspection results; and enterprise systems manage materials, suppliers, operators, and production schedules. The usefulness of these systems depends not merely on collecting data, but on organizing, protecting, interpreting, and governing it.

This chapter introduces the foundations of data management and databases. It distinguishes data, information, and knowledge; explains how entities and relationships are modeled; develops the relational database model; introduces SQL; and examines data quality, integrity, metadata, backup, security, and data lifecycle management.

Learning Objectives

By the end of this chapter, you should be able to:

1. Distinguish data, information, and knowledge in technical systems.
2. Identify entities, attributes, keys, and relationships.
3. Construct a basic entity–relationship model and convert it into relational tables.
4. Explain primary keys, foreign keys, normalization, and integrity constraints.
5. Write and interpret basic SQL statements for creating, retrieving, modifying, and aggregating data.
6. Evaluate data quality and select appropriate backup and lifecycle practices.
7. Discuss ethical, security, governance, and professional implications of database use.

1. Data, Information, and Knowledge

Data are recorded representations of observations, measurements, events, or facts. A sensor reading such as 78.4 °C, a motor identifier such as MTR-204, and a timestamp are data. Data may be numerical, textual, categorical, graphical, audio, or video-based. Raw data do not necessarily explain what a value means or what action should follow.

Information is data placed into context so that it has meaning. The statement “Motor MTR-204 reached 78.4 °C at 14:05 and exceeded its 75 °C operating limit” is information because it associates a measurement with an asset, time, and reference condition.

Knowledge is the justified understanding used to explain, predict, or guide action. For example, an engineer may know from historical evidence that a sustained rise in motor temperature, combined with increased vibration, often indicates bearing degradation. Knowledge incorporates context, experience, models, and judgment.

A useful progression is:

Data → context and processing → information → interpretation and experience → knowledge → decision or action

The progression is not automatic. Poorly measured data can produce misleading information, and accurate information can still be interpreted incorrectly. Database design therefore contributes directly to engineering quality and decision-making.

Data in a Mechatronic System

A production cell may contain several categories of data:

The distinction matters because each category has different ownership, update frequency, retention requirements, and quality risks.

2. Database Concepts and Architecture

A database is an organized collection of related data managed so that it can be stored, accessed, updated, protected, and shared. A database management system (DBMS) is software that provides these capabilities. Examples include PostgreSQL, MySQL, Microsoft SQL Server, Oracle Database, and SQLite.

A DBMS commonly provides:

• data definition and modification;
• controlled multi-user access;
• query processing;
• transaction management;
• integrity enforcement;
• backup and recovery;
• security and auditing; and
• metadata management.

An application normally communicates with a DBMS rather than manipulating storage files directly. This separation provides data independence: applications can often continue to function when storage structures or indexes change.

Database Models

The relational model represents data as relations, commonly implemented as tables. It is particularly suitable for structured records and transactions. Other models include:

• Document databases, which store flexible document structures, often in JSON-like form.
• Key–value databases, optimized for retrieving a value by a unique key.
• Graph databases, which emphasize nodes and relationships.
• Time-series databases, optimized for timestamped measurements.
• Data warehouses, designed for analytical queries across historical data.

No model is universally superior. Selection depends on structure, consistency requirements, query patterns, scale, latency, and operational constraints. A relational database is often appropriate for production orders, assets, personnel, and maintenance transactions, while a time-series platform may be appropriate for high-frequency sensor streams.

3. Entity–Relationship Modeling

Before creating tables, designers should model the problem domain. An entity is a distinguishable object or event about which data are stored. In a manufacturing context, entities may include Machine, Sensor, Product, WorkOrder, MaintenanceEvent, and Technician.

An attribute describes an entity. Machine may have MachineID, Model, Location, and CommissionDate. A candidate key is an attribute or combination of attributes capable of uniquely identifying a record. A primary key is the candidate key selected as the official identifier.

A relationship describes an association between entities. A Machine may contain many Sensors; a WorkOrder may concern one Machine; and a Technician may perform many MaintenanceEvents.

Cardinality and Optionality

Cardinality specifies how many instances can participate in a relationship:

• One-to-one (1:1): one entity instance relates to at most one instance of another entity.
• One-to-many (1:M): one machine can have many sensors, while each sensor belongs to one machine.
• Many-to-many (M:N): many work orders may use many parts. This must be represented by an associative entity such as WorkOrderPart.

Optionality indicates whether participation is required. A machine may exist before any sensor is installed, so its relationship to Sensor may be optional. Conversely, every sensor record may be required to identify a machine.

A conceptual model should be validated with domain experts before implementation. Ambiguous names, missing identifiers, and incorrect cardinalities become expensive to repair after software is deployed.

4. From an ER Model to Relational Tables

A practical transformation follows several rules:

1. Create one table for each strong entity.
2. Choose a primary key for each table.
3. Represent a one-to-many relationship by placing the primary key of the “one” table as a foreign key in the “many” table.
4. Represent a many-to-many relationship with a junction table containing foreign keys to both participating tables.
5. Store each atomic attribute in an appropriate column.
6. Represent relationships and constraints explicitly rather than relying on informal conventions.

For example:

Machine(MachineID, Model, Location)

Sensor(SensorID, MachineID, SensorType, Unit, InstallationDate)

Here, Sensor.MachineID is a foreign key referencing Machine.MachineID. A many-to-many relationship between WorkOrder and Part can be represented as:

WorkOrderPart(WorkOrderID, PartID, Quantity)

The combination of WorkOrderID and PartID may serve as a composite primary key if a part occurs only once per work order.

Normalization

Normalization is a design technique that reduces redundancy and update anomalies. In an unnormalized table, a work order might repeat machine, technician, and part information across many rows. Such repetition can cause:

• Update anomaly: a machine location is changed in one row but not another.
• Insertion anomaly: a new machine cannot be recorded until a work order exists.
• Deletion anomaly: deleting the last work order accidentally removes the only record of a machine.

At an introductory level:

• First normal form (1NF): each field contains an atomic value, and repeating groups are removed.
• Second normal form (2NF): in 1NF, and every non-key attribute depends on the whole primary key, relevant mainly to composite keys.
• Third normal form (3NF): in 2NF, and non-key attributes depend on the key, the whole key, and nothing but the key; transitive dependencies are removed.

Normalization improves consistency, although excessive decomposition can make queries complex. Analytical systems may deliberately use denormalization to improve read performance. The appropriate design depends on operational requirements.

5. Relational Integrity

Integrity rules prevent invalid or contradictory records.

• Entity integrity: a primary key must be unique and must not be null.
• Referential integrity: a foreign-key value must match an existing referenced key, unless null is explicitly permitted.
• Domain integrity: values must satisfy the permitted type, range, format, or set of values.
• Business integrity: organization-specific rules must hold, such as “a maintenance completion time cannot precede its start time.”

Constraints should be enforced in the DBMS whenever possible. Application checks alone are risky because data may also be imported, edited by another application, or changed by administrative scripts.

A null value requires careful interpretation. It may mean unknown, not yet recorded, not applicable, or deliberately withheld. Null is not the same as zero, an empty string, or “false.” Poor handling of nulls can distort reports and calculations.

6. SQL Fundamentals

Structured Query Language (SQL) is the standard language used with many relational DBMSs. Although syntax varies slightly among products, the core concepts are widely transferable.

Defining Tables

CREATE TABLE Machine (
    MachineID INTEGER PRIMARY KEY,
    Model VARCHAR(40) NOT NULL,
    Location VARCHAR(60) NOT NULL
);

CREATE TABLE Sensor (
    SensorID INTEGER PRIMARY KEY,
    MachineID INTEGER NOT NULL,
    SensorType VARCHAR(30) NOT NULL,
    Unit VARCHAR(10) NOT NULL,
    InstallationDate DATE,
    FOREIGN KEY (MachineID) REFERENCES Machine(MachineID)
);

The NOT NULL constraint requires a value. The primary and foreign key constraints enforce identity and association.

Inserting and Retrieving Data

INSERT INTO Machine (MachineID, Model, Location)
VALUES (204, 'ServoCell-2', 'Line A');

SELECT MachineID, Model
FROM Machine
WHERE Location = 'Line A';

A query should normally select only the required columns. SELECT * is convenient during exploration but can create fragile applications and transfer unnecessary data.

Filtering, Sorting, and Aggregation

SELECT SensorType, Unit
FROM Sensor
WHERE MachineID = 204
ORDER BY SensorType;

Aggregate functions summarize records:

SELECT MachineID, COUNT(*) AS SensorCount
FROM Sensor
GROUP BY MachineID
HAVING COUNT(*) >= 3;

WHERE filters rows before grouping; HAVING filters groups after aggregation.

Joining Tables

SELECT m.MachineID, m.Model, s.SensorID, s.SensorType
FROM Machine AS m
JOIN Sensor AS s
  ON m.MachineID = s.MachineID
WHERE m.Location = 'Line A';

An inner JOIN returns matching rows. A LEFT JOIN returns all rows from the left table, including those without a match, which is useful for finding machines with no sensors:

SELECT m.MachineID, m.Model
FROM Machine AS m
LEFT JOIN Sensor AS s
  ON m.MachineID = s.MachineID
WHERE s.SensorID IS NULL;

Updating and Deleting

UPDATE Machine
SET Location = 'Line B'
WHERE MachineID = 204;

DELETE FROM Sensor
WHERE SensorID = 901;

An UPDATE or DELETE without a sufficiently restrictive WHERE clause may affect every row. Production systems should use transactions, authorization, testing, and audit logs for changes.

Transactions and ACID Properties

A transaction is a logical unit of work. The ACID properties are:

• Atomicity: all operations occur, or none do.
• Consistency: constraints remain satisfied before and after the transaction.
• Isolation: concurrent transactions do not produce unacceptable interference.
• Durability: committed changes survive an ordinary system failure.

For example, transferring a part allocation from one work order to another should not leave the database in an intermediate state if a failure occurs.

7. Data Quality and Metadata

Data quality is multidimensional. Important dimensions include:

Quality assurance begins at capture. Sensors require calibration, units must be explicit, clocks should be synchronized, and operator interfaces should use controlled choices where appropriate. Validation at entry should be supplemented by profiling, reconciliation, exception reports, and periodic audits.

Metadata is data about data. It may describe a field’s meaning, unit, type, owner, source, precision, allowed values, update frequency, retention period, and sensitivity classification. A data dictionary is a basic metadata artifact. For a temperature field, useful metadata include whether the value is measured or calculated, whether it is in °C or K, the sensor model, sampling interval, calibration date, and alarm threshold.

Without metadata, a database may preserve values while losing their meaning. This is especially dangerous when systems are integrated or personnel change.

8. Security, Privacy, Backup, and Lifecycle Management

Database security requires defense in depth:

• authenticate users and services;
• authorize access according to least privilege;
• encrypt sensitive data in transit and, where appropriate, at rest;
• separate development, test, and production environments;
• monitor access and retain audit logs;
• patch the DBMS and operating system;
• protect credentials and secrets; and
• test incident-response procedures.

Access should be based on roles. An operator may enter inspection results, an engineer may analyze them, and a database administrator may manage structures without automatically having unrestricted authority to use business data.

Privacy requirements apply when databases contain employee, customer, biometric, location, or other personal data. Organizations should define a lawful purpose, minimize collection, control access, document retention, and dispose of data securely. Applicable laws vary by jurisdiction; professional practice requires consultation with institutional policies and legal specialists.

Backup and Recovery

A backup is a separate copy from which data can be restored. A sound plan defines:

• Recovery point objective (RPO): the maximum acceptable amount of data loss measured in time.
• Recovery time objective (RTO): the maximum acceptable time to restore service.

If an application can tolerate losing at most 15 minutes of transactions, its RPO must be no greater than 15 minutes. If service must return within two hours, the RTO is two hours or less. These are requirements, not formulas for predicting performance.

Common backup types include full, incremental, and differential backups. Backups should be encrypted, access-controlled, geographically or logically separated from production, monitored for completion, and tested through restoration. A backup that has never been restored in a test is only an assumption of recoverability.

The Data Lifecycle

A data lifecycle typically includes:

1. planning and specification;
2. creation or capture;
3. validation and storage;
4. use and sharing;
5. maintenance and quality review;
6. archival or retention; and
7. secure destruction.

Retention should be based on operational, legal, safety, research, and historical requirements. Keeping everything forever increases cost, attack surface, and confusion. Destruction must be authorized, documented, and technically effective, including copies and replicas where applicable.

9. Illustrative Case: Predictive Maintenance for a Robotic Assembly Cell

Background

A factory operates six robotic assembly cells. Each cell contains motors, gearboxes, vibration sensors, temperature sensors, and programmable controllers. Maintenance records are kept in spreadsheets, while sensor data are stored in separate controller files.

Problem

Engineers cannot reliably relate a vibration increase to a particular maintenance event. Asset identifiers are inconsistent, timestamps use different time zones, and several spreadsheets contain duplicate machine names. As a result, maintenance is mostly reactive and historical analysis is slow.

Analysis

The team identifies core entities: Machine, Sensor, Measurement, WorkOrder, MaintenanceEvent, Technician, and Part. MachineID becomes the stable identifier. Each sensor is assigned to one machine, each measurement references one sensor and timestamp, and each maintenance event references a machine and work order. Units, sampling intervals, calibration status, and source system are recorded as metadata.

A relational database stores master and transactional data. High-frequency measurements are retained in a time-series structure linked to the relational Machine and Sensor tables. Foreign keys prevent measurements from referring to nonexistent sensors. A controlled vocabulary distinguishes “planned,” “in progress,” “completed,” and “cancelled” work orders.

Approach and Solution

The implementation team first cleans and maps legacy identifiers, then loads validated master data. They establish rules for timestamp standardization, sensor calibration, required fields, and duplicate detection. An automated process imports measurements and records rejected rows for review. Engineers receive a dashboard showing temperature and vibration trends alongside maintenance history.

Backups are scheduled according to the production system’s RPO and RTO. Role-based access limits editing of maintenance records. A data dictionary documents every field and its owner.

Outcome

The organization can trace a measurement to a sensor, machine, and maintenance history. Engineers identify recurring vibration patterns earlier and schedule inspections based on evidence rather than isolated spreadsheet observations. The database does not itself prove that a failure will occur; it provides a reliable foundation for analysis and possible predictive models.

Learning Points

The case illustrates that database projects are not merely software installation projects. Success requires domain modeling, identifier governance, metadata, quality controls, security, integration, and user adoption. Advanced analytics or AI cannot compensate for ambiguous, incomplete, or poorly governed source data.

10. Contemporary Developments and Professional Practice

Cloud database services can provide scalable storage, managed backups, replication, and monitoring, but they do not eliminate the need for architecture, security, cost control, or governance. Edge computing places data processing near machines to reduce latency and network use; only selected summaries may then be transmitted to central systems.

AI and machine learning increase the value of well-managed data but also introduce risks. Training data may contain bias, leakage, personal information, or hidden changes in measurement conditions. Engineers should document data provenance, validation procedures, model versions, and limitations. A prediction should not be treated as a fact merely because it was generated algorithmically.

Interoperability is another major concern. Industrial systems may use different naming conventions, protocols, units, and time bases. Standards and carefully designed application programming interfaces can reduce integration costs, but semantic agreement—what a field actually means—is as important as technical connectivity.

Best practice is therefore to treat data as an organizational and engineering asset. Define ownership, use stable identifiers, document semantics, validate at capture, enforce constraints, minimize privileges, test recovery, monitor quality, and review retention regularly.

Chapter Summary

Data are recorded observations; information is contextualized data; and knowledge supports explanation and action. Databases organize data for reliable storage, access, sharing, and governance. Entity–relationship modeling identifies entities, attributes, keys, relationships, cardinalities, and optionality before implementation. In the relational model, tables represent entities, primary keys identify rows, and foreign keys represent relationships.

Normalization reduces redundancy and update anomalies, while integrity constraints protect identity, references, domains, and business rules. SQL supports table definition, querying, joining, aggregation, insertion, updating, and deletion. Transactions provide atomicity, consistency, isolation, and durability. Data quality includes accuracy, completeness, consistency, timeliness, validity, uniqueness, and fitness for purpose; metadata preserves meaning and context.

Professional database management also requires security, privacy, backup, recovery, lifecycle planning, and governance. Contemporary systems may combine relational, time-series, cloud, edge, and AI technologies, but sound modeling and trustworthy data remain fundamental.

Key Terms

Data: Recorded facts, observations, measurements, or events.

Information: Data organized and interpreted in context.

Knowledge: Justified understanding used to explain, predict, or guide action.

Database: Organized collection of related data.

DBMS: Software that manages databases.

Entity: Distinguishable object or event represented in a data model.

Attribute: Property describing an entity.

Primary key: Selected identifier that uniquely identifies a table row.

Foreign key: Attribute referencing a key in another table.

Cardinality: Number of entity instances participating in a relationship.

Normalization: Design process that reduces redundancy and anomalies.

SQL: Structured Query Language for relational databases.

Transaction: Logical unit of database work.

ACID: Atomicity, consistency, isolation, and durability properties.

Metadata: Data describing the meaning, origin, structure, or governance of data.

Data quality: Degree to which data are accurate, complete, consistent, timely, valid, unique, and fit for purpose.

RPO: Maximum acceptable period of data loss after a disruption.

RTO: Maximum acceptable time to restore a service.

Data lifecycle: Stages from data planning and creation through use, retention, and destruction.

Review Questions

Short Answer Questions

1. Distinguish data, information, and knowledge using a temperature-monitoring example.
2. What is the difference between a primary key and a foreign key?
3. Why is a many-to-many relationship normally represented by a junction table?
4. Define metadata and give three examples relevant to sensor data.
5. What do RPO and RTO measure?

Long Answer Questions

1. Explain how an entity–relationship model can be transformed into relational tables. Include cardinality, keys, and integrity constraints.
2. Discuss first, second, and third normal forms and explain the anomalies that normalization seeks to prevent.
3. Explain the principal SQL operations used to create, retrieve, modify, and summarize relational data.
4. Analyze the role of data quality management throughout the data lifecycle of a mechatronic system.
5. Discuss database security, privacy, backup, and recovery requirements for an industrial production environment.

Analytical / Application Questions

1. Design a conceptual data model for a laboratory containing machines, sensors, measurements, experiments, and researchers. Identify entities, keys, relationships, cardinalities, and at least five important integrity rules.
2. A maintenance database contains repeated machine details in every work-order row, inconsistent temperature units, and missing sensor identifiers. Diagnose the design and quality problems and propose a staged improvement plan.
3. An industrial monitoring service can tolerate no more than 10 minutes of lost data and must be restored within 90 minutes after a major failure. Specify the maximum RPO and RTO, then recommend backup, replication, testing, and access-control practices that support those requirements.

Multiple Choice Questions with Answers

Question 1

Which statement best distinguishes information from raw data?

A. Information is always numerical, whereas data are always textual.

B. Information is data placed into a meaningful context.

C. Information is permanently stored, whereas data are temporary.

D. Information cannot be used to support decisions.

Correct Answer: B. Information is data placed into a meaningful context.

Question 2

In a relational database, what is the main purpose of a primary key?

A. To store multiple values in one field

B. To encrypt every record

C. To uniquely identify each row

D. To sort rows alphabetically

Correct Answer: C. To uniquely identify each row

Question 3

A Machine may have many Sensors, but each Sensor belongs to one Machine. What relationship is this?

A. One-to-one

B. One-to-many

C. Many-to-many

D. Many-to-zero only

Correct Answer: B. One-to-many

Question 4

Which constraint primarily enforces referential integrity?

A. Foreign key

B. CHECK constraint only

C. ORDER BY clause

D. Alias

Correct Answer: A. Foreign key

Question 5

Which SQL clause filters groups after aggregation?

A. WHERE

B. FROM

C. HAVING

D. VALUES

Correct Answer: C. HAVING

Question 6

Which ACID property means that a transaction is completed entirely or not performed at all?

A. Durability

B. Isolation

C. Consistency

D. Atomicity

Correct Answer: D. Atomicity

Question 7

Which item is an example of metadata for a temperature measurement?

A. The measured value alone

B. The sensor calibration date and unit

C. An unrelated work-order number

D. A deleted database row

Correct Answer: B. The sensor calibration date and unit

Question 8

A system may lose at most 15 minutes of transactions during a disaster. Which requirement follows?

A. RPO must be no greater than 15 minutes.

B. RTO must be no greater than 15 minutes.

C. The database must contain exactly 15 tables.

D. The backup must run only once per month.

Correct Answer: A. RPO must be no greater than 15 minutes.

Question 9

What is a principal reason for normalization?

A. To increase duplicate storage of every attribute

B. To eliminate the need for primary keys

C. To reduce redundancy and update anomalies

D. To prevent all database queries

Correct Answer: C. To reduce redundancy and update anomalies

Question 10

Which practice provides the strongest evidence that backups are usable?

A. Naming files with the word ‘backup’

B. Performing and documenting restoration tests

C. Storing every backup on the production server

D. Deleting old backups immediately

Correct Answer: B. Performing and documenting restoration tests