SQL

3. ADVANCED DATA RELATIONSHIPS: JOINING TABLES

In a real-world student information system, data is normalized across multiple tables to reduce redundancy. This requires linking the Students table to a Courses table using a Foreign Key.

Schema Expansion (Create)

The system now requires a table for courses and a "junction" table to track which students are enrolled in which courses.

-- Create the Courses table

CREATE TABLE Courses (

    CourseID VARCHAR(10) PRIMARY KEY,

    CourseName VARCHAR(100) NOT NULL,

    Credits INT

);

-- Create the Enrollment table (Links Students and Courses)

CREATE TABLE Enrollment (

    EnrollmentID INT PRIMARY KEY,

    StudentID INT,

    CourseID VARCHAR(10),

    Semester VARCHAR(20),

    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),

    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)

);

Population of Related Data (Create)

-- Insert courses into the catalog

INSERT INTO Courses (CourseID, CourseName, Credits)

VALUES

('CS101', 'Introduction to Databases', 3),

('MATH202', 'Linear Algebra', 4);

 

-- Enroll Marcus Chen (ID 202602) into CS101

INSERT INTO Enrollment (EnrollmentID, StudentID, CourseID, Semester)

VALUES (1, 202602, 'CS101', 'Spring 2026');

Data Integration (Read)

The JOIN command combines rows from two or more tables based on a related column. This is essential for generating transcripts or class lists.

-- Retrieve a list of students and the specific courses they are taking

SELECT Students.FirstName, Students.LastName, Courses.CourseName

FROM Students

JOIN Enrollment ON Students.StudentID = Enrollment.StudentID

JOIN Courses ON Enrollment.CourseID = Courses.CourseID;

CRUD Maintenance in Linked Tables

Update (U): If a course ID changes, the Enrollment table must reflect this to maintain Referential Integrity.

UPDATE Enrollment

SET CourseID = 'CS102'

WHERE EnrollmentID = 1;

Delete (D): When a student withdraws from a course, the record is removed from the Enrollment table, but the student remains in the Students table.

DELETE FROM Enrollment

WHERE StudentID = 202602 AND CourseID = 'CS101';

Summary of Relational Concepts

Primary Key: Uniquely identifies a record (e.g., StudentID).

Foreign Key: Points to a primary key in another table (e.g., Enrollment.StudentID).

Normalization: Dividing data into multiple tables to eliminate duplicate information.