Database Fundamentals

Introduction

A database is an organized collection of data that is stored so that it can be found, changed, protected, and reused efficiently. In workplaces, databases support tasks such as managing customers, stock, work orders, appointments, invoices, machine maintenance, training records, and quality checks. A database management system (DBMS) is the software that defines, stores, queries, controls, and protects the data.

This aiMOOC is designed for apprentices, trainees, and vocational students who need practical database skills for technical, commercial, administrative, logistics, and IT-related work. You will learn the concepts behind relational databases, practise core SQL operations, design small data models, and connect database quality with everyday workplace requirements.

Why Databases Matter at Work

Imagine a repair workshop. Staff need to know which customer owns a machine, which technician is assigned to a work order, which spare parts were used, whether a part is in stock, and whether the job has been invoiced. Keeping all of this in one long spreadsheet quickly creates duplicated data and inconsistent updates. A well-designed database separates different kinds of facts and links them through defined relationships.

A database does more than store facts. A DBMS can enforce rules, coordinate simultaneous users, control permissions, support transactions, create backups, and answer questions with queries. This is why database skills are useful in professions ranging from industrial mechanics and retail logistics to office administration, software development, healthcare administration, and technical support.

Learning Objectives

After working through this aiMOOC, you should be able to explain the difference between data, a database, and a DBMS; identify tables, rows, columns, keys, relationships, and constraints; create a simple relational data model; use basic SQL to create, read, update, and delete data; combine related tables with joins; explain the purpose of normalization; describe transaction safety and database security; and test a small database against a realistic workplace scenario.

Core Database Concepts

Data, Information, Databases, and DBMS

Data are recorded facts such as a part number, quantity, date, customer name, or measured temperature. Information is data interpreted in context, such as “Part P-104 is below its reorder level.” A database stores related data in a structured form. A DBMS is the software layer used to create, manage, query, secure, and maintain databases.

A database application normally has several layers. A user may work with a web page, desktop application, scanner, or mobile device. The application sends requests to the DBMS. The DBMS reads or changes stored data and returns results. Separating these responsibilities makes it easier to control access, validate data, and maintain the system.

Database Models

Different database models suit different types of work. A relational database organizes data into relations that are commonly represented as tables. A document database stores document-like structures, a key-value database retrieves values by keys, and a graph database focuses on nodes and relationships. This course concentrates on relational databases because they are widely used in business and vocational applications and because SQL is a common language for working with them.

Choosing a model depends on requirements. You should ask what must be stored, how strongly the data are related, which consistency rules matter, what queries are required, how much data is expected, and which tools the organization can maintain.

The Relational Model

Tables, Rows, Columns, and Schemas

A relational table describes one kind of entity or relationship. A table named Customer might contain columns such as customer_id, name, email, and phone. Each row represents one stored customer. Columns describe attributes and have defined data types.

A schema is the database blueprint: it describes structures such as tables, columns, keys, relationships, and constraints. The data stored at a particular moment are the current database state. Keeping the schema separate from individual records helps teams discuss design before entering real data.

A realistic production schema can become much more complex than a training example. The MediaWiki schema below shows how a mature application can contain many connected tables.

Data Types and NULL

A data type limits what can be stored in a column and often affects storage, validation, sorting, and calculations. Common categories include integers, decimal numbers, character strings, dates and times, and Boolean values. Exact type names differ between DBMS products.

Choose types that match the business meaning. Store a quantity as a numeric type if you need arithmetic. Store a date as a date type if you need date comparisons. Do not use a text field merely because it seems flexible.

NULL represents a missing or unknown value. It is not the same as zero, an empty string, or the word “unknown.” Database queries often need special logic such as IS NULL to test for it.

Keys and Relationships

Primary Keys

A primary key is the selected column, or set of columns, used to identify each row uniquely in a table. A good primary key must be unique and cannot be NULL. For example, customer_id can identify a customer even if two customers have the same name.

A key based on meaningful existing data is called a natural key. A generated identifier is often called a surrogate key. The choice should reflect stability, uniqueness, privacy, and how the database will be used.

Foreign Keys and Referential Integrity

A foreign key is a column or set of columns whose values refer to a candidate key, commonly the primary key, in another table. For example, work_order.customer_id can refer to customer.customer_id. This link allows a work order to be associated with the correct customer.

Referential integrity means references remain valid. A DBMS can reject a work order that refers to a customer that does not exist. When referenced data is updated or deleted, the database design should define what is allowed, restricted, cascaded, or set to NULL.

Cardinality

Relationships describe how many records can be associated. Common patterns are one-to-one, one-to-many, and many-to-many. A customer can have many work orders, while each work order belongs to one customer: this is one-to-many. A work order can use many parts, and one part can be used on many work orders: this is many-to-many.

Many-to-many relationships are normally implemented with an additional junction table. For example, WorkOrderPart can contain work_order_id, part_id, and quantity_used.

Entity-Relationship Modeling

From Workplace Requirements to an ER Diagram

An entity-relationship model helps you translate workplace requirements into entities, attributes, and relationships before writing SQL. Start with nouns in the process: Customer, Machine, Technician, WorkOrder, Part. Then ask what facts belong to each entity and how the entities are connected.

A useful ER model should show identifiers and cardinalities. It should also be reviewed with the people who understand the process. A technically elegant model can still fail if it does not match real work.

Example: Training Workshop Database

Suppose a training workshop needs to record service jobs. A simple model could contain these entities:

Customer: customer_id, name, email, phone
Machine: machine_id, customer_id, serial_number, model
Technician: technician_id, name, specialization
Work order: work_order_id, machine_id, technician_id, opened_on, status
Part: part_id, description, unit_price, stock_quantity

The many-to-many relationship between work orders and parts can be resolved with a WorkOrderPart table. This table can also record quantity_used, because quantity is a fact about the relationship between a particular job and a particular part.

Integrity and Constraints

Why Integrity Rules Matter

Database quality depends on rules that prevent impossible or contradictory states. Constraints move some validation into the database so that the rule applies regardless of which application is writing the data.

A NOT NULL constraint requires a value. A UNIQUE constraint prevents duplicate values in a defined column or column set. A CHECK constraint can enforce a condition such as quantity >= 0 where supported. A PRIMARY KEY combines row identity with uniqueness and non-nullability. A FOREIGN KEY enforces a permitted reference between tables.

Constraints should represent real business rules. If a workshop allows negative stock temporarily during stocktaking, a rule that forbids every negative quantity may conflict with the real process. Good database design requires both technical knowledge and careful requirements analysis.

SQL Fundamentals

What SQL Does

Structured Query Language is used with relational database systems to define structures, query data, insert records, change records, delete records, control access, and manage transactions. SQL has standardized foundations, but products such as PostgreSQL, MySQL, MariaDB, SQLite, Oracle Database, and Microsoft SQL Server also have product-specific features.

Creating Tables

The following simplified example uses common SQL syntax. Exact details can vary by DBMS.

CREATE TABLE customer (
    customer_id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE
);

CREATE TABLE work_order (
    work_order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status VARCHAR(30) NOT NULL,
    opened_on DATE NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);

The schema makes the relationship explicit: every stored work order must refer to an existing customer unless the design is changed to permit a missing customer reference.

CRUD Operations

CRUD is a useful workplace shorthand for Create, Read, Update, Delete. SQL commonly implements these actions with INSERT, SELECT, UPDATE, and DELETE.

INSERT INTO customer (customer_id, name, email)
VALUES (101, 'Alex Rivera', 'alex@example.org');

SELECT customer_id, name, email
FROM customer
ORDER BY name;

UPDATE work_order
SET status = 'completed'
WHERE work_order_id = 5001;

DELETE FROM customer
WHERE customer_id = 101;

Before changing or deleting production data, verify the selection condition and follow workplace procedures for authorization, transactions, backups, and testing. A missing or incorrect WHERE condition can affect many rows.

Filtering, Sorting, and Aggregation

A WHERE clause filters rows. ORDER BY sorts results. Aggregate functions such as COUNT, SUM, AVG, MIN, and MAX summarize groups of rows. GROUP BY defines those groups.

SELECT status, COUNT(*) AS number_of_orders
FROM work_order
GROUP BY status
ORDER BY status;

This type of query can support a workshop dashboard by showing how many work orders are open, waiting for parts, or completed.

Joins

A join combines related rows from multiple tables. An inner join returns matching rows according to the join condition.

SELECT w.work_order_id,
       w.status,
       c.name
FROM work_order AS w
JOIN customer AS c
  ON w.customer_id = c.customer_id
ORDER BY w.work_order_id;

The join works because the foreign key in work_order corresponds to the customer key. A left outer join is useful when you want all rows from the left table even if no matching row exists on the right.

Normalization

Why Normalize Data

Normalization is a systematic way to organize relational data so that each fact is stored in an appropriate place and unwanted redundancy is reduced. Poorly organized data can cause update, insertion, and deletion anomalies.

Suppose one work-order sheet stores customer name, customer phone, machine serial number, technician name, and several part columns in every job row. If a customer changes a phone number, many rows may need to be edited. If one copy is missed, the database becomes inconsistent.

First, Second, and Third Normal Form

First normal form (1NF) requires a relational structure without repeating groups and with values appropriate to individual attributes. Instead of columns Part1, Part2, and Part3, use rows in a related WorkOrderPart table.

Second normal form (2NF) builds on 1NF and removes partial dependency on part of a composite candidate key. This matters when a key contains more than one column. If a table has only a single-column candidate key, partial dependency on part of that key cannot occur.

Third normal form (3NF) builds on 2NF and removes certain dependencies of non-key attributes on other non-key attributes. In practical terms, store each independent business fact in the table that represents the thing it describes.

Normalization is not a goal by itself. The purpose is a design that preserves meaning, supports integrity, and is maintainable. Performance needs may later justify carefully controlled denormalization, but that decision should be measured and documented.

Transactions and Reliability

Transactions

A transaction is a logical unit of work that should be completed in a controlled way. Imagine issuing a spare part to a work order. The system may need to reduce stock and also create a stock-movement record. If one change succeeds and the other fails, the data can become unreliable. A transaction lets the DBMS treat related changes as one unit.

Many transactional systems aim to provide the ACID properties:

Atomicity: all operations in the transaction succeed as a unit or the transaction is rolled back.
Consistency: a successful transaction takes the database from one valid state to another according to defined rules.
Isolation: concurrent transactions are controlled so their interactions do not produce unacceptable results.
Durability: once a transaction is committed, its result survives later failures according to the guarantees of the system.

Transaction behavior depends on the DBMS, storage engine, configuration, and isolation level. In practical work, you should know where transactions begin and end and how your application handles errors.

Backups and Recovery

A backup is useful only if it can support recovery. Organizations should define what must be backed up, how often, where copies are stored, how they are protected, and how restoration is tested. Recovery plans should consider both data loss and downtime.

A sound routine includes documented backup schedules, protected backup storage, retention rules, restore tests, and responsible persons. For important systems, disaster recovery should be tested rather than assumed.

Security, Privacy, and Safe Use

Least Privilege and Access Control

Users and applications should receive only the permissions they need. A reporting account may need read access without permission to delete tables. Administrative accounts should not be used for routine application work.

Authentication verifies who a user is; authorization determines what that user may do. Database roles can group permissions by job function. Access decisions should follow organizational policy and applicable data-protection requirements.

Protecting Data and Queries

Sensitive data should be protected in storage, in backups, and during transmission where appropriate. Passwords should not be stored as readable plain text. Secrets such as database credentials should be handled with approved secret-management methods rather than placed directly in source code.

Applications should use parameterized queries or prepared statements for user-provided values. Building SQL commands by directly concatenating untrusted input can create SQL injection vulnerabilities. Input validation is still useful, but it does not replace correct query parameterization.

Logging and monitoring can help identify errors, misuse, and unusual access. Logs themselves may contain sensitive information and therefore also require protection and retention rules.

Performance and Maintainability

Indexes

An index is a data structure that can help a DBMS locate rows efficiently for certain queries. Indexes are often useful on columns used for lookups, joins, sorting, or filtering. However, each index consumes storage and can add work to inserts, updates, and deletes.

Do not add indexes blindly. Measure important queries, inspect execution plans where your DBMS supports them, and test with realistic data volumes.

Documentation and Naming

Database work is collaborative. Use consistent names, document the purpose of tables and columns, record important business rules, and keep schema changes under controlled change management. A data dictionary can describe each field, type, meaning, valid values, source, and sensitivity.

Clear documentation helps new trainees understand the system and reduces the risk of unsafe changes.

A Vocational Database Workflow

A practical database task can be organized into a repeatable workflow.

Requirements analysis: Talk to users, observe the process, identify decisions and reports, and list the data that must be stored.
Data modeling: Identify entities, attributes, keys, relationships, and cardinalities.
Database normalization: Check whether facts are duplicated or dependent on the wrong key.
Database schema: Translate the model into tables, data types, keys, and constraints.
SQL: Implement queries and data-change operations needed by the process.
Software testing: Test normal cases, invalid data, missing data, concurrent activity, and recovery procedures.
Database security: Define roles, permissions, secure connections, and protection for sensitive data.
Database maintenance: Monitor performance, back up data, test restores, document changes, and review the design as requirements evolve.

In vocational practice, quality is shown not only by whether a query runs, but by whether the database represents the real process accurately, protects data, rejects invalid states, and remains understandable to the next person who must maintain it.

Troubleshooting Checklist

When a database task does not work, diagnose the problem systematically. Check whether you are connected to the correct database; verify table and column names; inspect data types; confirm primary and foreign key values; test the SELECT condition before an UPDATE or DELETE; read the DBMS error message carefully; reduce a complex query to smaller parts; check permissions; and compare the current schema with the documentation.

For performance problems, first identify the slow operation and reproduce it safely. Then inspect row counts, filters, joins, indexes, execution plans, locking, and workload. Measure before and after changes.

Interactive Tasks

Quiz: Test Your Knowledge

What is the main role of a DBMS? (To manage and control database data) (!To replace every business application) (!To store only unstructured pictures) (!To create network cables)

Which database object usually stores records in rows and columns? (Table) (!Router) (!Folder) (!Printer)

What must be true of a primary key value? (It uniquely identifies a row) (!It is always a customer name) (!It must contain a date) (!It may freely duplicate another key)

What is the purpose of a foreign key? (To connect related rows between tables) (!To encrypt a backup) (!To sort every query automatically) (!To replace all indexes)

Which SQL command is mainly used to retrieve rows? (SELECT) (!DELETE) (!DROP) (!UPDATE)

Which SQL clause filters rows according to a condition? (WHERE) (!VALUES) (!CREATE) (!COMMIT)

Why is a junction table commonly used? (To represent a many-to-many relationship) (!To store every password in plain text) (!To remove all foreign keys) (!To avoid defining a schema)

What is a central purpose of normalization? (To reduce harmful redundancy and anomalies) (!To make every table contain one column) (!To remove all business rules) (!To replace SQL with a spreadsheet)

Which ACID property means a transaction is completed as a unit or rolled back? (Atomicity) (!Durability) (!Indexing) (!Authorization)

Which practice helps prevent SQL injection in application queries? (Parameterized queries) (!String concatenation of untrusted input) (!Shared administrator passwords) (!Removing all input checks)

Memory Game

Schema	Blueprint of database structures and rules
Tuple	One row in a relation
Primary key	Chosen identifier that uniquely distinguishes a row
Foreign key	Attribute that references a key in another table
Transaction	Controlled logical unit of database work
Index	Structure that can speed up selected data access

Drag and Drop

Match the correct terms.	Topic
Retrieve selected rows	SELECT
Add a new row	INSERT
Change existing rows	UPDATE
Remove selected rows	DELETE
Finish a successful transaction	COMMIT

Match each workplace action to the SQL command that normally performs it. Then explain why an UPDATE or DELETE should be tested carefully before it is run on production data.

Crossword Puzzle

Schema	What word means the blueprint of database structures?
Tuple	What relational term can describe one row?
Constraint	What rule can the DBMS enforce on stored data?
Index	What structure can improve certain lookup operations?
Transaction	What unit groups related database operations?
Normalization	What design process reduces harmful redundancy?

LearningApps

Cloze Text

A database management system is commonly abbreviated as

. In a relational database, data are organized into

. A selected identifier for each row is a

. A reference to a key in another table is a

. SQL retrieves rows mainly with the

command. A many-to-many relationship is commonly resolved with a

. The design process that reduces harmful redundancy is called

. A controlled unit of database work is a

. The ACID property that protects committed results is

. Application code should use

for untrusted values.

Open-Ended Tasks

Easy

Workplace Data Hunt: Identify five examples of data used in your training company, school workshop, or a realistic workplace and explain who creates each item and who needs it.
Table Sketch: Draw a simple table for tools, products, customers, or machines with at least six columns, suitable data types, and one proposed primary key.
SQL Reading Practice: Find three SELECT examples in this course, rewrite each in your own words, and describe the result you expect before running it.
Database Interview: Interview a trainer, supervisor, or classmate about one information problem at work and summarize which data would need to be stored to solve it.

Standard

ER Diagram Project: Design an ER diagram for a small repair workshop, store, warehouse, salon, training center, or similar workplace with at least five entities and clearly marked relationships.
CRUD Practice Database: Create a small practice database in a DBMS approved for your course and demonstrate one safe INSERT, SELECT, UPDATE, and DELETE operation using non-sensitive sample data.
Data Quality Investigation: Build a deliberately flawed table with duplicated facts, then identify update, insertion, and deletion anomalies and redesign it to reduce the problems.
Database Tutorial Video: Produce a three-to-five-minute tutorial video or screen recording that explains primary keys, foreign keys, and one example join using your own practice database.

Advanced

Normalization Case Study: Take a realistic unnormalized workplace dataset and transform it step by step toward third normal form, documenting functional dependencies and each design decision.
Transaction Experiment: In a safe training database, design a two-step operation such as stock issue plus movement logging, test commit and rollback behavior, and explain what could go wrong without a transaction.
Database Security Review: Analyze a fictional database application for excessive permissions, weak credential handling, unsafe query construction, missing backups, and sensitive logging, then propose prioritized improvements.
Database Prototype Project: Plan, implement, test, and document a small database solution for a vocational process, including requirements, ER model, schema, constraints, sample data, useful queries, role concept, backup plan, and a short user demonstration.

Learning Assessment

Requirements-to-Schema Assessment: Given a new workplace process, identify entities, attributes, candidate keys, relationships, and constraints, then justify how your schema represents the process.
Integrity Assessment: Analyze a database with duplicated identifiers, orphaned references, and invalid quantities, explain which integrity rules are missing, and propose specific constraints or process controls.
Query Transfer Assessment: Write SQL that answers a new business question requiring filtering, a join, and aggregation, then explain how you verified that the result is complete and correct.
Normalization Assessment: Compare two alternative designs for the same dataset, identify likely anomalies, and defend which design is easier to maintain and why.
Transaction Reasoning Assessment: Explain how a multi-step stock or payment process should behave if one step fails and connect your design to atomicity, consistency, isolation, and durability.
Security and Recovery Assessment: Create a risk-based plan for database permissions, parameterized queries, backup frequency, restore testing, and logging for a small vocational organization.

Evidence of Learning

Knowledge: You can explain databases, DBMS functions, relational structures, data types, keys, relationships, normalization, SQL operations, transactions, indexes, security, and recovery in clear workplace language.

Skills: You can turn requirements into a data model, define tables and constraints, write and test core SQL, join related data, diagnose basic errors, normalize a small design, and apply safe working practices.

Products: Strong evidence may include an ER diagram, documented schema, SQL script, sample dataset, query results, test protocol, data dictionary, security plan, backup-and-restore checklist, and short demonstration.

Transfer achievements: You can apply database principles to a new vocational context, explain design trade-offs to non-specialists, identify risks in an unfamiliar schema, and justify improvements using both business requirements and technical evidence.

OERs on the Topic

Open educational resources can help you practise beyond this course:

Khan Academy: Intro to SQL offers interactive practice in querying and managing relational data.
Wikiversity: Database Fundamentals provides openly accessible learning material and activities.
Wikipedia: Relational model provides background on relations, keys, and relational operations.
Wikipedia: Database normalization provides further detail on normal forms.
Wikipedia: ACID explains atomicity, consistency, isolation, and durability.