Design and query related tables

Store each fact in the right place

A primary key uniquely identifies a row. A foreign key refers to a key in a related table. Store a student's details once in Student and relate enrollments through StudentID. Repeating the student's address in every enrollment creates an update problem when the address changes.

A many-to-many relationship usually needs a linking table, such as Enrollment(StudentID, CourseID). Its key can combine both identifiers. Constraints express rules such as uniqueness and valid references; they do not replace understanding the data's meaning.

Read a join as a relationship

The query matches student identifiers in the two tables, keeps enrollments in course CS, and returns student names. A WHERE condition filters rows after the relationship has been expressed. Check whether a question needs unique names, a count, or all matching records.

A transaction groups related operations into one logical change. If an enrollment operation updates multiple tables, a partial update could violate the rules of the system. Explain which changes must succeed or fail together.

SELECT Student.Name
FROM Student
INNER JOIN Enrollment
ON Student.StudentID = Enrollment.StudentID
WHERE Enrollment.CourseID = 'CS';

Check yourself