2. The Three-Schema Architecture

The Relational Model — Tables, Rows, and Columns

A relational database organises all of its data into tables. A table is a grid of rows and columns — similar in appearance to a spreadsheet, but governed by strict rules that spreadsheets do not enforce.

In formal database terminology, these words have precise names. A table is called a relation. A single row of data is called a tuple. A column is called an attribute. And every attribute has a domain — the complete set of values it is legally allowed to hold.

For example, a column called DateOfBirth has a domain of valid calendar dates. Attempting to store the word hello in that column would violate its domain, and the database engine would reject it immediately.

This vocabulary matters because every constraint, every join, and every normalization rule is defined in terms of these formal concepts.

The Three-Schema Architecture

One of the most important ideas in database theory is that a database is separated into three completely independent levels. This architecture, standardised by ANSI/SPARC, solves a fundamental real-world problem: how do you change the internal storage of a database without breaking every application that uses it?

The answer is three layers, each hiding its complexity from the others.

The Internal Level — also called the physical level — describes how data is actually stored on the hard drive. It covers file formats, page sizes, byte ordering, and index structures. You almost never work at this level directly. The database engine manages it completely.

The Conceptual Level — also called the logical level — describes what data the database contains and how it is organised. Table names, column names, data types, primary keys, and foreign keys all live here. When you design a schema and write CREATE TABLE statements, you are working at the conceptual level.

The External Level — also called the view level — describes what individual users or applications can see. Different users can be shown different subsets of the same underlying data. A student might see only their own records. A lecturer might see their own class list. A payroll system might see only salary columns. None of them need to see the full database.

The critical benefit of this separation is data independence. If you move your database to faster storage hardware, only the internal level changes — your conceptual schema and all application code remain untouched. If you restructure a table, you can update external views so existing applications continue to work without modification.

Keys — How Rows Are Uniquely Identified

Every table needs a reliable way to identify each individual row. This is done through keys.

A primary key is one or more columns whose values uniquely identify every row in the table. No two rows can share the same primary key value, and it can never be empty. For a student table, the student ID is the natural primary key — every student has a unique ID assigned to them by the university.

A foreign key is a column in one table that references the primary key of another table. This is how relationships between tables are established. If your student table has a column called DeptID, and that column references the primary key of a Department table, then DeptID is a foreign key. The engine will enforce that you cannot assign a student to a department that does not exist, and cannot delete a department that still has students.

A composite key is a primary key made from two or more columns combined. For an enrollment table that records which student is in which course, neither the student ID alone nor the course ID alone is unique — but the combination of both is. That combined pair becomes the composite primary key.

Constraints — Rules Built Into the Table

Constraints are rules embedded directly into the table structure. The database engine enforces them automatically on every INSERT and UPDATE. No application code is needed to check them — the database rejects any data that violates a constraint, regardless of where the request came from.

The most important constraints are listed below.

NOT NULL means the column must always contain a value. A student's name should never be blank, so the Name column should carry a NOT NULL constraint.

UNIQUE means no two rows can hold the same value in this column. An email address is a good candidate — each person should have exactly one unique email in the system.

PRIMARY KEY combines both NOT NULL and UNIQUE into a single constraint. It is the official row identifier.

FOREIGN KEY ensures that a value in one table matches an existing primary key in another table. It is how the database enforces relationships.

CHECK lets you write a custom condition. For example, a student's GPA must be between 0.0 and 4.0, or an age must be greater than zero.

DEFAULT provides an automatic value when none is supplied. An enrollment date could default to today's date if the user does not provide one.

Exploring Your Server in SSMS

Open SSMS and connect to your server. In the Object Explorer panel on the left, find the Databases folder and expand it by clicking the arrow. You will see a set of system databases: master, model, msdb, and tempdb. These are internal databases that SQL Server uses to manage itself. Do not modify them.

Later in this track, you will create your own database and see it appear in this list. When you expand your own database, you will see sub-folders for Tables, Views, Stored Procedures, and Programmability — each corresponding to a concept you will learn in the modules ahead.

For now, click New Query and run this command to see your system databases listed programmatically:

SELECT name, database_id, create_date
FROM sys.databases;

Press F5. A results grid will appear showing the name and creation date of each database on your server. This is your first look at querying system metadata — the database describing its own structure to you.

Schema vs Instance

Two more terms you will encounter throughout this track are schema and instance.

The schema is the structure or blueprint of the database — the table names, column names, data types, and constraints. The schema describes what the database looks like. It rarely changes once the system is built.

The instance — sometimes called the database state — is the actual data living inside the database at a specific point in time. It changes constantly as rows are inserted, updated, and deleted.

A useful analogy: the schema is the architectural blueprint of a building. The instance is the people and furniture currently inside it. The blueprint stays the same. The occupants change every day.