Database Fundamentals & SQL Vocabulary

A concise vocabulary set covering the core terms, commands, concepts and data types introduced in the SQL & database lecture notes.

Database Management System (DBMS)

Software layer that stores, organizes and controls all read/write access to one or more databases.

Frontend

Client-side program (e.g., HeidiSQL, phpMyAdmin) used to send SQL statements to a DBMS and display results.

Database (DB)

Collection of structured data managed by a DBMS; often used synonymously with Database System (DBS).

Relational Database

Database whose logical structure is a set of related tables (relations) connected through keys.

Table (Relation)

Two-dimensional structure of rows and columns that stores data of the same entity type.

Column / Attribute

Named field in a table describing one property of an entity; has a fixed data type.

Row / Record / Tuple

Complete set of column values that represents one occurrence of an entity.

Primary Key

Column (or column set) whose values are unique and identify each row unambiguously.

Foreign Key

Column that stores values of a primary key from another table to create a relationship.

Composite Key

Primary key composed of two or more columns (e.g., artikelNr + liefNr in a junction table).

CREATE DATABASE

DDL statement that creates a new database schema; optional CHARACTER SET and COLLATE define encoding and sorting.

CREATE TABLE

DDL statement that creates a new table definition with column list, data types and optional constraints.

ALTER TABLE

DDL statement used to add, change, or drop columns, indexes or constraints in an existing table.

INSERT INTO

DML command that adds one or many new rows to a table.

UPDATE

DML command that modifies existing rows in one or more columns.

DELETE FROM

DML command that removes one or more rows from a table.

SELECT

DML command that queries data; basis for data retrieval in SQL.

Projection

Filtering of columns in a SELECT statement (SELECT col1, col2 …).

Selection

Filtering of rows in a SELECT statement using WHERE criteria.

ORDER BY

Clause that sorts query results ascending (ASC, default) or descending (DESC).

LIMIT

Clause that restricts the number of returned rows; optional OFFSET skips a given number first.

JOIN

Operation that combines rows from two or more tables based on related columns.

INNER JOIN (Equi-JOIN)

Returns rows where join condition matches in both tables; non-matching rows are discarded.

GROUP BY

Clause that groups rows sharing the same values so that aggregate functions can be applied.

HAVING

Clause that filters groups produced by GROUP BY according to aggregate conditions.

COUNT()

Aggregate function that returns the number of non-NULL rows in a group.

SUM()

Aggregate function that returns the total of numeric column values.

AVG()

Aggregate function that returns the average of numeric column values.

MAX()

Aggregate function that returns the largest value in a group.

MIN()

Aggregate function that returns the smallest value in a group.

ENUM

String-based column type that restricts values to a predefined list (one choice).

SET

String-based column type that allows any combination of predefined values (multiple choice).

AUTO_INCREMENT

Column attribute that automatically increases numeric primary key when a new row is inserted.

InnoDB

Default MySQL storage engine that supports transactions, row-level locking and foreign keys.

UTF8 Character Set

Unicode encoding often chosen for databases to store international characters consistently.

Collation

Rule set that determines how strings are compared and sorted; e.g., utf8generalci (case-insensitive).

Trigger

Stored program that executes automatically BEFORE or AFTER INSERT, UPDATE or DELETE on a table.

Stored Procedure

Named block of SQL statements stored in the DBMS; can accept parameters; executed via CALL.

Stored Function

Stored routine that returns a value and can be used in SQL expressions like regular functions.

First Normal Form (1NF)

Eliminates repeating groups by ensuring each column holds atomic, single values.

Second Normal Form (2NF)

Removes partial dependencies; every non-key column depends on the whole composite key.

Third Normal Form (3NF)

Eliminates transitive dependencies; non-key columns depend only on the primary key.

Referential Integrity

Rule that a foreign-key value must have a matching primary-key value in the referenced table.

ON UPDATE CASCADE

Foreign-key option that automatically propagates primary-key changes to child rows.

ON DELETE CASCADE

Foreign-key option that automatically deletes child rows when the parent row is removed.

1:1 Relationship

Each row in table A relates to exactly one row in table B and vice versa via unique foreign key.

1:n Relationship

One row in table A can relate to many rows in table B; implemented with non-unique foreign key.

n:m Relationship

Many-to-many link between two tables; implemented using a separate junction table with two foreign keys.

Junction (Connection) Table

Table that breaks an n:m relationship into two 1:n relationships; typically has a composite primary key.

HeidiSQL

Windows GUI frontend for MySQL/MariaDB used in the course for database creation and queries.

phpMyAdmin

Web-based MySQL frontend bundled with XAMPP; alternative to HeidiSQL.

XAMPP

Local web-server package containing Apache, MySQL/MariaDB, PHP and Perl for testing purposes.

INT

Numeric data type for whole numbers (-2,147,483,648 to 2,147,483,647).

DECIMAL(M,D)

Fixed-point numeric data type; M total digits, D digits after decimal point; precise for money.

CHAR(n)

Fixed-length character data type that always stores exactly n characters (padded with spaces).

VARCHAR(n)

Variable-length character data type that stores up to n characters without padding.

DATE

Data type that stores calendar dates in 'YYYY-MM-DD' format.

DATETIME

Data type that stores date and time in 'YYYY-MM-DD hh:mm:ss' format.

TINYINT

1-byte integer often used to mimic Boolean (0/1) values in MySQL.

DDL (Data Definition Language)

SQL subset that creates or alters structures: CREATE, ALTER, DROP, RENAME.

DML (Data Manipulation Language)

SQL subset that works with data: SELECT, INSERT, UPDATE, DELETE.

DCL (Data Control Language)

SQL subset for rights management: GRANT, REVOKE.

COMMIT

Transaction command that makes all changes since last COMMIT/ROLLBACK permanent.

ROLLBACK

Transaction command that undoes all changes since last COMMIT.

GRANT

DCL command that supplies privileges to a user or role.

REVOKE

DCL command that removes previously granted privileges from a user or role.

User Account (MySQL)

Identified by 'user'@'host'; authentication credentials plus specific privilege set.