Teaching Kai: Creating a College Database with Tables, Indexes, and Foreign Keys in SQL Server

Recently, I’ve been helping Kai move from Microsoft Access to Microsoft SQL Server to better understand real-world SQL and database design. Access is a great tool for small projects, but it can get tricky (“finiqi”) especially when inserting multiple rows and enforcing data integrity.

SQL Server is a powerful relational database management system (RDBMS) used by professionals worldwide, and learning it early gives Kai a solid foundation.

Step 1: Create the Database

First, let's create a database called CollegeDB where all our tables and data will live.

CREATE DATABASE CollegeDB;
GO

USE CollegeDB;
GO

Step 2: Create Two Tables with a Relationship

We will create two tables:

Students — to store student information
Enrollments — to store course enrollments for each student

The key idea is to connect these tables using foreign keys, so you cannot insert an enrollment for a student who doesn’t exist — keeping the data consistent.

Students Table

CREATE TABLE Students (
    StudentID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    DateOfBirth DATE NULL
);

Enrollments Table

CREATE TABLE Enrollments (
    EnrollmentID INT IDENTITY(1,1) PRIMARY KEY,
    StudentID INT NOT NULL,
    CourseName NVARCHAR(100) NOT NULL,
    EnrollmentDate DATE NOT NULL,
    CONSTRAINT FK_Enrollments_Students FOREIGN KEY (StudentID)
        REFERENCES Students(StudentID)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

What’s Happening Here?

Primary Key (PK): Both tables have a primary key (StudentID and EnrollmentID) which uniquely identifies each row.
Foreign Key (FK): The Enrollments.StudentID links to Students.StudentID. This enforces referential integrity — you can’t have an enrollment without a matching student.
Constraints: NOT NULL means a column must have a value.
ON DELETE CASCADE: If a student is deleted, their enrollments are deleted automatically.
ON UPDATE CASCADE: If a student’s ID changes (rare, but possible), the related enrollment records update automatically.

Step 3: Why Are Indexes Important?

Primary keys automatically create an index, which helps SQL Server find data quickly. Indexes are like the index in a book — they help you locate the information fast without reading every page.

Foreign keys also help maintain fast joins between tables.

Step 4: Insert Sample Data

Let's add some sample students and enrollments.

-- Insert students
INSERT INTO Students (FirstName, LastName, DateOfBirth)
VALUES ('Kai', 'Smith', '2000-06-15'),
       ('Emma', 'Johnson', '1999-11-22');

-- Insert enrollments
INSERT INTO Enrollments (StudentID, CourseName, EnrollmentDate)
VALUES (1, 'Math 101', '2025-09-01'),
       (1, 'History 201', '2025-09-02'),
       (2, 'Biology 101', '2025-09-01');

Step 5: Joining the Tables

To get a list of students along with their courses, we use a JOIN:

SELECT s.StudentID, s.FirstName, s.LastName, e.CourseName, e.EnrollmentDate
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID;

This query pulls student info with their enrolled courses in one result.

Why SQL Server Over Access?

Multi-row Inserts: SQL Server handles bulk inserts and complex queries much more efficiently than Access.
Strong Data Integrity: Foreign keys and constraints prevent orphaned data and keep your database consistent.
Performance: Indexes speed up data retrieval, essential for larger datasets.
Scalability: SQL Server works great for everything from small projects to enterprise applications.

Conclusion

Teaching Kai to create databases and tables with relationships in SQL Server sets him up with real-world skills — beyond the limits of Access. Understanding primary keys, foreign keys, constraints, and indexes ensures that data stays clean and queries run fast.

Next up: we’ll explore more complex queries, stored procedures, and backups. Stay tuned!

Search This Blog