Database Design and SQL

Introduction

Database Design and SQL is about turning real-world information needs into a reliable relational database and then using SQL to define, change, retrieve, and protect the data. This course is designed for learners in Grades 11–13. You will move from requirements and entity-relationship modeling to normalized table structures, SQL queries, transactions, indexing, security, testing, and a small database project.

A well-designed database does more than store facts. It represents meaning: which objects exist, which facts belong to them, how they relate, what values are valid, and which rules must always hold. SQL then gives you a declarative way to express what data you want or what change you want the database system to perform.

The relational model organizes information into relations, commonly presented as tables. A table has columns that describe attributes and rows that represent individual records. Relationships between tables are represented through keys rather than by repeatedly copying the same facts.

The video above provides a broad course on relational database design. Use it selectively alongside the sections below: pause at examples, predict the next design step, and compare the presented design choices with your own.

From Requirements to a Database

Requirements Analysis

Database design begins before you create tables. First identify the purpose of the system and the questions it must answer. For a school database, stakeholders might need to know which learners are enrolled in which courses, who teaches each course, when a learner enrolled, and which results are recorded.

Useful requirements are specific. Instead of saying “store student information,” determine which information is needed, who may view or change it, whether a value can be missing, whether it must be unique, and how long it should be retained. You should also identify business rules such as “a learner may enroll in many courses” or “a course code must be unique.”

A good requirements interview distinguishes between entities, attributes, relationships, and constraints. It also tests edge cases. Ask what happens when a learner changes an email address, when a course is cancelled, or when two learners have the same name.

Conceptual, Logical, and Physical Design

A practical design process has three connected levels. A conceptual model describes important entities and relationships without committing to a particular database product. A logical model maps these ideas to relational tables, keys, and constraints. A physical design decides implementation details such as data types, indexes, storage choices, and DBMS-specific features.

These levels help you separate questions. “What facts exist?” is different from “Which table stores them?” and different again from “Which index will make this query faster?” Keeping those questions distinct makes a design easier to review and change.

Entity-Relationship Modeling

An entity-relationship model represents the structure of a domain before you write SQL. An entity is a distinguishable thing such as a Student, Course, Teacher, or Room. An attribute describes an entity, such as StudentName or CourseTitle. A relationship connects entities, such as Student enrolls in Course.

Cardinality and Optionality

Cardinality describes how many instances can participate in a relationship. Common patterns are one-to-one, one-to-many, and many-to-many. Optionality asks whether participation is required. For example, a course might have zero or many enrollments, while each enrollment must refer to exactly one course.

A many-to-many relationship should normally become an associative or junction table in a relational design. If students can take many courses and courses can contain many students, an Enrollment table can store one row per student-course combination. It can also hold relationship attributes such as EnrolledOn or Grade.

Relational Tables and Keys

Rows, Columns, and Domains

In a relational table, each column has a meaning and a domain of permitted values. A DBMS represents domains through data types and constraints. A date should normally be stored as a date type rather than as arbitrary text. A quantity should use a suitable numeric type. Choosing types carefully improves validation, storage, comparison, and calculation.

A table should represent one coherent kind of fact. Column names should be understandable and consistent. Rows should be identifiable without depending on their physical order.

Primary, Candidate, Composite, and Foreign Keys

A candidate key is a minimal set of attributes that can uniquely identify a row. One candidate key is selected as the primary key. A key made from more than one column is a composite key. A foreign key is a column or set of columns whose values refer to a candidate key, commonly a primary key, in another table.

Fehler beim Erstellen des Vorschaubildes:

Foreign keys support referential integrity. For example, an Enrollment row should not refer to a StudentID that does not exist. Actions such as deleting a referenced row require an explicit policy: the DBMS may reject the deletion, cascade it to related rows, or set the foreign-key value to null where that is allowed and meaningful.

Natural keys have meaning in the domain, such as an official product code. Surrogate keys are identifiers created mainly for database use. A good key should be unique, stable, and as simple as the domain permits.

Normalization

Normalization is a systematic way to reduce harmful redundancy and dependency problems in relational schemas. The goal is not “more tables at any cost.” The goal is to store each fact in a structure that avoids common insert, update, and delete anomalies while preserving the required information.

Fehler beim Erstellen des Vorschaubildes:

First, Second, and Third Normal Form

A table is in first normal form when each field contains an atomic value for the chosen model and there are no repeating groups of columns. For example, storing Phone1, Phone2, and Phone3 as repeated columns is usually a sign that phone numbers should be represented differently.

Second normal form matters when a candidate key is composite. A non-key attribute should depend on the whole candidate key, not only part of it. In Enrollment(StudentID, CourseID, CourseTitle), CourseTitle depends only on CourseID, so it belongs in Course rather than Enrollment.

Third normal form removes inappropriate transitive dependencies. If Student(StudentID, PostalCode, City) assumes that PostalCode determines City, then City is not directly dependent on the student identifier. Separating location data may prevent inconsistent city names for the same postal code, depending on the actual rules of the domain.

Boyce-Codd normal form is a stronger condition than third normal form: every non-trivial functional dependency should have a determinant that is a candidate key. You do not need to force every school project to BCNF, but you should understand that normalization decisions depend on the actual functional dependencies in the data.

When Denormalization Can Be Reasonable

After you have a correct normalized design, selective denormalization can sometimes improve performance for a measured workload. This creates trade-offs: duplicated data may make reads faster but writes and consistency harder. Denormalize only with evidence, documentation, and a plan for maintaining consistency.

A Running School Database Example

Consider three tables:

Table	Main columns	Purpose
Student	StudentID, StudentName, Email	Stores one row per learner
Course	CourseID, Title, Credits	Stores one row per course
Enrollment	StudentID, CourseID, EnrolledOn, Grade	Connects learners and courses

The Enrollment table resolves the many-to-many relationship between Student and Course. Its composite primary key can be StudentID plus CourseID if the rule is that a learner may enroll in a given course only once. If repeated attempts must be stored, the key design must change, for example by adding an AttemptID or term identifier.

Datei:SQL Simple Table Example with Foreign Key.png

SQL: Defining the Schema

SQL is a declarative language for working with relational database systems. Different DBMS products have dialect differences, so always check the documentation for the system you use. The examples here use widely recognizable SQL ideas but may need small syntax changes.

SQL statements are often grouped informally into categories. Data Definition Language includes schema-changing commands such as CREATE, ALTER, and DROP. Data Manipulation Language includes commands such as INSERT, UPDATE, and DELETE. SELECT is frequently described as a query language operation.

A possible schema for the running example is:

CREATE TABLE Student (
    StudentID INTEGER PRIMARY KEY,
    StudentName VARCHAR(100) NOT NULL,
    Email VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE Course (
    CourseID INTEGER PRIMARY KEY,
    Title VARCHAR(150) NOT NULL,
    Credits INTEGER NOT NULL CHECK (Credits > 0)
);

CREATE TABLE Enrollment (
    StudentID INTEGER NOT NULL,
    CourseID INTEGER NOT NULL,
    EnrolledOn DATE NOT NULL,
    Grade VARCHAR(10),
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

Constraints are part of the design, not merely error messages. PRIMARY KEY enforces row identity, FOREIGN KEY protects references, UNIQUE prevents duplicate values where uniqueness is required, NOT NULL requires a value, and CHECK can enforce domain rules that the DBMS supports.

SQL: Creating and Changing Data

To insert a row, specify the target columns explicitly:

INSERT INTO Student (StudentID, StudentName, Email)
VALUES (101, 'Amina Patel', 'amina@example.org');

To change existing rows, use UPDATE with a carefully tested condition:

UPDATE Course
SET Credits = 4
WHERE CourseID = 20;

To remove rows, DELETE also needs a precise condition:

DELETE FROM Enrollment
WHERE StudentID = 101
  AND CourseID = 20;

Before a large UPDATE or DELETE, first run a SELECT with the same WHERE condition and inspect the rows. In real systems, transactions, backups, permissions, and review procedures add further protection.

SQL: Querying Data

SELECT, WHERE, ORDER BY, and Expressions

A basic query chooses columns, a source table, optional filtering conditions, and an order:

SELECT Title, Credits
FROM Course
WHERE Credits >= 3
ORDER BY Title;

SQL is declarative: you state the required result rather than manually describing every storage step. The database optimizer chooses an execution plan based on available statistics, indexes, and implementation details.

Aggregation with GROUP BY and HAVING

Aggregate functions summarize groups of rows. COUNT, SUM, AVG, MIN, and MAX are common examples.

SELECT CourseID, COUNT(*) AS NumberOfStudents
FROM Enrollment
GROUP BY CourseID
HAVING COUNT(*) >= 10;

WHERE filters rows before grouping, while HAVING filters groups after aggregation. Keeping this distinction clear prevents many query errors.

Joining Tables

A JOIN combines related rows from multiple tables. An INNER JOIN returns matching rows. A LEFT JOIN keeps every row from the left table and adds matching data from the right when it exists.

Example:

SELECT s.StudentName, c.Title, e.Grade
FROM Enrollment AS e
JOIN Student AS s
  ON s.StudentID = e.StudentID
JOIN Course AS c
  ON c.CourseID = e.CourseID
ORDER BY s.StudentName, c.Title;

The join conditions express how foreign keys connect rows. Omitting a required condition can produce an unintended Cartesian product and a much larger result.

Subqueries and Common Table Expressions

A subquery is a query nested inside another statement. A common table expression, introduced with WITH in many SQL systems, can make a complex query easier to read. Use the simplest form that clearly expresses the task, and inspect the execution plan when performance matters.

Transactions and Data Integrity

A transaction groups operations into one logical unit of work. The classic ACID properties are atomicity, consistency, isolation, and durability. Together they describe important guarantees for dependable transactional systems.

A transfer between two accounts illustrates why transactions matter: decreasing one balance and increasing another should succeed together or fail together. Commands such as BEGIN, COMMIT, ROLLBACK, and savepoints exist in many systems, although exact syntax and behavior differ by DBMS.

Isolation controls how concurrent transactions interact. Stronger isolation can prevent more anomalies but may reduce concurrency. The correct choice depends on the application and the guarantees required.

Indexes and Performance

An index is an auxiliary data structure that can help the DBMS locate rows without scanning an entire table. B-tree-family structures are widely used for ordered lookup in relational systems.

Datei:B-tree example.svg

Indexes are not free. They use storage, must be maintained when data changes, and can slow INSERT, UPDATE, and DELETE operations. Add indexes for real query patterns, measure performance, and inspect query plans rather than indexing every column.

Good performance also depends on appropriate data types, selective predicates, efficient joins, current statistics, suitable constraints, and a schema that matches the workload. Performance tuning should preserve correctness.

Security, Privacy, and Responsible Data Use

A database may contain personal or confidential information. Apply the principle of least privilege: users and applications should receive only the permissions they need. Separate administrative accounts from normal application access, protect credentials, maintain backups, and monitor important changes.

Applications should send user-supplied values to SQL through parameterized queries or prepared statements rather than building commands by concatenating raw input. This is a central defense against SQL injection because data values remain separate from SQL syntax.

Privacy-aware design starts with data minimization. Store only data that serves a legitimate purpose, define retention rules, control access, and consider whether reports can use aggregated or pseudonymized data. Legal requirements vary by jurisdiction, so a real system must follow the applicable rules and organizational policies.

Testing and Reviewing a Database

A database design should be tested against both normal cases and edge cases. Try duplicate emails, missing required values, nonexistent foreign keys, repeated enrollments, empty result sets, and concurrent updates. Confirm that constraints reject invalid states and that valid workflows remain possible.

Review queries for correctness before speed. A fast query that returns the wrong rows is still wrong. For important reports, compare query results with small hand-worked examples where you know the expected answer.

A useful design review asks: Does every table represent a coherent concept? Are keys stable? Are many-to-many relationships resolved? Are dependencies normalized appropriately? Are constraints enforced in the database where possible? Are permissions, backups, and indexes justified by requirements?

Interactive Tasks

Quiz: Test Your Knowledge

What is the main purpose of a primary key? (To uniquely identify each row in a table) (!To sort every query automatically) (!To encrypt sensitive columns) (!To store only numeric values)

Which table structure normally resolves a many-to-many relationship? (A junction table) (!A backup table) (!A temporary view) (!A single text column)

What does a foreign key help enforce? (Referential integrity) (!Screen resolution) (!File compression) (!Password strength)

Which clause filters rows before grouping? (WHERE) (!HAVING) (!ORDER BY) (!COMMIT)

Which statement defines a new table? (CREATE TABLE) (!SELECT TABLE) (!GROUP TABLE) (!COMMIT TABLE)

What problem does normalization primarily reduce? (Harmful redundancy and dependency anomalies) (!Network latency between browsers) (!The number of SQL keywords) (!The need for any backups)

What does an INNER JOIN return? (Rows that satisfy the join condition) (!Every row from the left table regardless of matches) (!Only rows with null values) (!Only the first row of each table)

Which transaction command normally makes completed changes permanent? (COMMIT) (!ROLLBACK) (!SELECT) (!HAVING)

Why can too many indexes be harmful? (They add storage and write maintenance costs) (!They remove all constraints) (!They force every column to be text) (!They prevent tables from being joined)

Which practice helps prevent SQL injection in applications? (Use parameterized queries) (!Concatenate raw user input into SQL) (!Give every user administrator rights) (!Disable database constraints)

Memory Game

Primary key	Chosen identifier that uniquely distinguishes each row
Foreign key	Column or columns that reference a key in another table
Normalization	Process of organizing relations to reduce harmful redundancy and anomalies
Junction table	Relation used to represent a many-to-many association
Transaction	Logical unit of database work that can be committed or rolled back
Index	Auxiliary structure that can speed selected data access patterns

Drag and Drop

Match the correct terms.	Topic
CREATE TABLE	Define a new relation
INSERT	Add new rows
SELECT	Retrieve data
UPDATE	Change existing rows
DELETE	Remove selected rows

Match each SQL command to its core purpose before writing a small example of your own.

Crossword Puzzle

Schema	What word means the formal structure or blueprint of a database?
Entity	What word names a distinguishable thing represented in a data model?
Attribute	What word describes a property of an entity or relation?
ForeignKey	What one-word answer represents a key that references another table?
Transaction	What word describes a logical unit of database work?
Index	What structure can speed selected lookups at the cost of extra maintenance?

LearningApps

Cloze Text

A relational database organizes data into

with rows and columns. A chosen unique row identifier is a

. A reference to a key in another table is a

. A many-to-many relationship is commonly implemented with a

. The process of reducing harmful redundancy is called

. SQL uses

to retrieve rows from tables. Related rows can be combined with a

. A reliable unit of work can be handled as a

. A data structure that can accelerate selected searches is an

. Applications should use

when sending user-supplied values to SQL.

Open-Ended Tasks

Easy

Data requirements: Choose a familiar setting such as a club, library, or school event and write ten precise facts the database must store plus five questions users should be able to answer.
ER sketch: Draw a simple ER diagram with at least three entities, useful attributes, relationship names, cardinalities, and optionality.
Query postcard: Create a one-page visual guide that explains SELECT, FROM, WHERE, and ORDER BY using one consistent example.
Database interview: Interview a teacher, librarian, coach, or school administrator about one real data-recording process and summarize the data fields, rules, and pain points you discover.

Standard

Relational schema: Convert your ER diagram into tables and mark every primary key, foreign key, candidate key, and junction table; justify each choice in a short design note.
Normalization lab: Start with one deliberately redundant table, identify update, insert, and delete anomalies, then decompose it to third normal form while explaining each dependency.
SQL query set: Build a small sample database and write at least eight queries that include filtering, sorting, aggregation, grouping, and at least two joins; record the expected result of each query.
Index experiment: Create a larger test table using synthetic data, compare the execution plan or timing of a selected query before and after adding an appropriate index, and explain the trade-off you observe.

Advanced

Database application project: Design and implement a complete relational database for a realistic school or community scenario, including requirements, ER model, normalized schema, constraints, sample data, and a documented query collection.
Transaction investigation: Design a two-step business operation, demonstrate what could go wrong if only one step succeeds, then show how a transaction with commit and rollback protects consistency.
Security audit video: Produce a three-to-five-minute instructional video that reviews least privilege, parameterized queries, backups, and privacy controls for your database project without exposing real credentials or personal data.
Performance review: Analyze a non-trivial query with joins and aggregation, inspect its execution plan, propose at least two performance improvements, test them, and argue which change should or should not be kept.

Learning Assessment

Database design case study: Given a narrative about a school club system, identify entities, relationships, cardinalities, keys, and constraints, then defend your design against at least one plausible alternative.
Normalization analysis: Diagnose a denormalized table by stating its functional dependencies, identifying concrete anomalies, decomposing it to an appropriate normal form, and showing how the decomposition preserves required facts.
SQL reasoning: Write and explain a query that joins at least three tables, filters rows, groups the result, and applies a group condition; predict the result for a supplied small dataset before executing it.
Transaction design: Explain how atomicity and isolation matter in a realistic multi-user scenario, then propose a transaction boundary and discuss one concurrency risk.
Database index evaluation: Decide whether to add an index for a given workload by comparing expected read benefits with storage and write costs, and state what measurement would confirm your decision.
Data governance: Review a database scenario containing personal information and propose changes that improve data minimization, access control, retention, and auditability while preserving the system's purpose.

Evidence of Learning

Strong evidence of learning combines knowledge, practical skill, a usable product, and transfer to a new situation.

Evidence type	What successful work shows
Knowledge	You can explain entities, relationships, keys, normalization, constraints, joins, transactions, indexes, and core security principles accurately.
Design skill	You can turn requirements into an ER model and then into a coherent relational schema with justified keys and constraints.
SQL skill	You can create tables, insert and change data safely, write multi-table queries, aggregate results, and interpret errors and execution plans.
Product	You can deliver a documented database with sample data, test cases, queries, and a design rationale.
Reasoning	You can compare alternatives and explain trade-offs involving normalization, indexing, constraints, concurrency, and privacy.
Transfer	You can apply the same design process to an unfamiliar domain and adapt SQL to the dialect and requirements of a different DBMS.

OERs on the Topic

The English Wikipedia article on Database design offers a useful open reference for conceptual, logical, and physical design. Compare its terminology with the models and examples in this course.

Linked Learning Areas

Database design connects computing theory, programming, information systems, mathematics, privacy, and real organizational processes. The core pathway is to understand a domain, model its entities and relationships, map the model to a relational schema, normalize dependencies, enforce integrity with constraints, query with SQL, and then test performance and security under realistic use.

Database Design and SQL