Basic Concepts Underlying Relational Databases in SQL Server 2025

A relational database organizes data into tables — structured collections of rows and columns — where relationships between tables are defined through keys. SQL Server 2025 is the DBMS used throughout this course. Understanding the foundational concepts of relational databases is essential before creating, querying, or administering any SQL Server database. This lesson covers tables, keys, relationship types, referential integrity, and the logical model approach to database design — illustrated through the course project schema visible in the diagrams below.

What Is a Relational Database?

A relational database is a collection of one or more tables that share defined relationships. The term "relational" comes from the mathematical concept of a relation — a set of tuples sharing the same attributes — formalized by E.F. Codd in 1970. In practical terms, each table represents an entity: a customer, an employee, a task, or a product. Columns define the attributes of that entity. Rows represent individual instances. The relationships between tables are enforced by the database management system through primary keys, foreign keys, and referential integrity constraints.

SQL Server 2025 is a relational database management system (RDBMS) that implements the relational model using T-SQL (Transact-SQL) as its query language. As the database designer, you decide how tables relate to one another — not every table relates to every other table — and SQL Server enforces those relationships through constraints defined at table creation time.

Tables, Rows, and Columns

Every piece of data in a SQL Server database lives in a table. A table is a two-dimensional structure with columns that define the data type and meaning of each attribute, and rows that represent individual records. The following T-SQL creates a simple Employees table with three columns:

CREATE TABLE Employees (
    Employee_ID    INT           NOT NULL PRIMARY KEY,
    Full_Name      NVARCHAR(100) NOT NULL,
    Department_Code CHAR(4)      NOT NULL
);

Employee_ID is the primary key — a column or combination of columns whose values uniquely identify each row. No two employees can share the same Employee_ID, and the column cannot contain NULL. Primary keys are the anchor points through which relationships between tables are defined.

Logical Database Design — Starting with a Logical Model

When you design a relational database, the easiest way to begin is to construct a logical database design, also known as a logical model. In your logical model, you identify on paper — or in a diagramming tool — the major categories of information that the database must store, without yet committing to implementation details such as data types, indexes, or storage configuration. The logical model answers the question: what entities exist, and how do they relate to one another?

The two diagrams below show the logical model for the course project database — a timesheet tracking system that records employee work against client tasks and produces Actual vs. Estimated Reports.

The SQL Server 2025 diagram expands the original logical model by adding column-level detail to each entity. Employees stores Employee_ID (PK), Full_Name, and Department_Code. Clients stores Client_ID (PK), Company_Name, and Industry. Tasks stores Task_ID (PK), Task_Name, Priority, and Client_ID (FK) — the foreign key that links each task to its client. Timesheets stores Timesheet_ID (PK), Week_Commencing, and Hours_Logged. All four tables feed into the Actual vs. Estimated Reports output at the bottom of the diagram.

Once you and your client are satisfied with the logical model, you are ready to construct the physical database design — the actual CREATE TABLE statements, index definitions, and constraint declarations that implement the logical model in SQL Server 2025. Physical database design is covered later in this course.

Types of Relationships in Relational Databases

Three relationship types govern how tables connect in the relational model. Understanding each type is essential for translating a logical model into a correct physical schema.

One-to-One (1:1)

Each row in one table corresponds to exactly one row in another table. One-to-one relationships are less common than the other types. They are used to split a wide table for security or performance reasons — keeping sensitive columns in a separate table that requires additional permission to access. In the following example, Users stores login credentials while UserProfiles stores personal data, and the UNIQUE constraint on the foreign key enforces the one-to-one cardinality:

CREATE TABLE Users (
    user_id  INT          NOT NULL PRIMARY KEY,
    username VARCHAR(50)  NOT NULL
);

CREATE TABLE UserProfiles (
    profile_id INT  NOT NULL PRIMARY KEY,
    user_id    INT  NOT NULL UNIQUE REFERENCES Users(user_id),
    birth_date DATE
);

One-to-Many (1:N)

A row in one table can be associated with multiple rows in another table, but each row in the child table relates to only one row in the parent table. This is the most common relationship type in relational database design. In the course project, the relationship between Clients and Tasks is one-to-many — one client can have many tasks, but each task belongs to exactly one client. The Client_ID (FK) column in Tasks enforces this:

CREATE TABLE Clients (
    Client_ID    INT           NOT NULL PRIMARY KEY,
    Company_Name NVARCHAR(150) NOT NULL,
    Industry     NVARCHAR(100)
);

CREATE TABLE Tasks (
    Task_ID   INT           NOT NULL PRIMARY KEY,
    Task_Name NVARCHAR(200) NOT NULL,
    Priority  TINYINT       NOT NULL DEFAULT 3,
    Client_ID INT           NOT NULL REFERENCES Clients(Client_ID)
);

A second one-to-many relationship exists between Employees and Timesheets — one employee can submit many timesheet records over the course of a project.

Many-to-Many (N:M)

Rows in one table can relate to multiple rows in another table, and vice versa. SQL Server cannot directly implement a many-to-many relationship between two tables — it requires a junction table that holds foreign keys to both sides. A classic example is students and courses: one student enrolls in many courses, and one course enrolls many students. The Enrollments junction table resolves this:

CREATE TABLE Students (
    student_id   INT           NOT NULL PRIMARY KEY,
    student_name VARCHAR(100)  NOT NULL
);

CREATE TABLE Courses (
    course_id   INT           NOT NULL PRIMARY KEY,
    course_name VARCHAR(100)  NOT NULL
);

CREATE TABLE Enrollments (
    student_id INT NOT NULL REFERENCES Students(student_id),
    course_id  INT NOT NULL REFERENCES Courses(course_id),
    PRIMARY KEY (student_id, course_id)
);

The composite primary key on Enrollments ensures that a student cannot enroll in the same course twice. More detail on all three relationship types is available in the ERD module at Three Relationship Types.

Enforcing Relationships — Foreign Keys and Referential Integrity

Defining a relationship in the logical model is not enough — the DBMS must enforce it at runtime. SQL Server 2025 enforces relationships through foreign key constraints and referential integrity rules that prevent inconsistent data from entering the database.

A foreign key is a column or set of columns in a child table whose values must match an existing primary key value in the parent table. If you attempt to insert a Tasks row with a Client_ID value that does not exist in Clients, SQL Server rejects the operation with a foreign key violation error. This enforcement is called referential integrity — the guarantee that relationships between tables remain consistent at all times.

Cascade operations extend referential integrity by automating related changes when a parent row is modified or deleted:

• ON DELETE CASCADE — automatically deletes child rows when the parent row is deleted. If a client is removed from Clients, all related rows in Tasks are deleted automatically.
• ON UPDATE CASCADE — automatically updates the foreign key value in child rows when the referenced primary key changes in the parent table.
• ON DELETE SET NULL — sets the foreign key column to NULL in child rows when the parent row is deleted, preserving the child rows without a valid parent reference.
• ON DELETE NO ACTION — the default behavior. SQL Server raises an error and rolls back the delete if any child rows reference the parent row being deleted.

-- Tasks are automatically deleted when the parent Client is deleted
CREATE TABLE Tasks (
    Task_ID   INT           NOT NULL PRIMARY KEY,
    Task_Name NVARCHAR(200) NOT NULL,
    Priority  TINYINT       NOT NULL DEFAULT 3,
    Client_ID INT           NOT NULL,
    CONSTRAINT FK_Tasks_Clients
        FOREIGN KEY (Client_ID)
        REFERENCES Clients(Client_ID)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

From Logical Model to Physical Schema in SQL Server 2025

The logical model shown in the course project diagrams maps directly to the physical schema created in SQL Server 2025. Each entity in the logical model becomes a table. Each attribute becomes a column with a defined data type. Each relationship line becomes a foreign key constraint. The translation process follows these steps:

1. Identify all entities in the logical model and create a table for each one.
2. Define the primary key for each table — the column or combination of columns that uniquely identifies each row.
3. Identify the relationship type between each pair of tables — one-to-one, one-to-many, or many-to-many.
4. Add foreign key columns to the child table for each one-to-many relationship, referencing the primary key of the parent table.
5. Create junction tables for each many-to-many relationship with composite primary keys referencing both parent tables.
6. Define cascade rules for each foreign key based on the business rules governing data deletion and updates.

In SQL Server 2025, this physical schema is implemented through CREATE TABLE statements executed in SQL Server Management Studio (SSMS) or Azure Data Studio. The order of table creation matters — parent tables must be created before child tables that reference them through foreign keys.

The Course Project Schema

The four-table schema visible in the SQL Server 2025 diagram — Employees, Clients, Tasks, and Timesheets — is the database you will build throughout this course. It models a professional services timesheet system where employees log hours against client tasks, and management reports compare actual hours worked against estimated hours budgeted per task. The complete T-SQL for the course project schema is developed across the remaining lessons in this module, beginning with the Clients and Employees parent tables before adding the Tasks and Timesheets child tables.

Summary

A relational database stores data in tables connected through primary key and foreign key relationships. The three relationship types — one-to-one, one-to-many, and many-to-many — determine how tables are structured and how junction tables are used to resolve many-to-many cardinality. Referential integrity enforced by SQL Server 2025 guarantees that foreign key values always correspond to valid primary key values in the referenced table. Cascade rules automate related changes when parent rows are modified or deleted. Database design begins with a logical model that identifies entities and relationships before any physical implementation decisions are made — the course project schema of Employees, Clients, Tasks, and Timesheets illustrates this progression from logical model to physical SQL Server 2025 schema. The next lesson covers SQL Server 2025 services in detail.