Database Management · Module 1
Relational Keys, Joins and TransactionsA relational design separates facts that change independently, then connects them through keys. Consider a learning platform with Students, Courses and Enrolments. A student may enrol in several courses, and each course may have many students. 1. Model the relationship with keysUse a primary key to identify each student and course. Store one enrolment per student-course pair, with both columns together forming its primary key. Foreign keys connect those columns to their parent tables. This avoids repeating a course title for every enrolled student and makes an accidental duplicate enrolment detectable.
A name is generally a poor primary key: two people can share it and a person can change it. A foreign key controls valid relationships; it does not automatically imply every application-specific rule or every useful index. 2. Predict a join before running itSuppose Students contains (1, Asha), (2, Bilal), (3, Chen), and Enrolments contains (1, 10), (1, 20), (2, 10). An inner join on student_id produces three rows: Asha appears twice, Bilal once, and Chen not at all. The repeated name represents two matching enrolments; it is not automatically a duplicate-data error.
A left join keeps Chen as well, with NULL for the missing course_id. Be careful with a WHERE condition on a right-hand column: 3. Count missing matches correctly
The results are Asha 2, Bilal 1 and Chen 0. COUNT(column) excludes NULL, whereas COUNT(*) counts the preserved left-join row even when it has no enrolment. To test missing values, use IS NULL rather than = NULL. 4. Keep related updates togetherA transaction groups changes that must succeed together. If recording an enrolment also writes an audit entry, a failure between the two writes must not leave half the operation committed. Begin a transaction, execute both statements, then commit; on an error, roll back. Atomicity concerns the group of changes. Isolation concerns what concurrent transactions can observe. A transaction alone is not a complete solution to every race. For a limited number of seats, two requests may both see availability unless the update and concurrency control enforce the capacity rule. Use a conditional update or suitable locking and isolation, and check the affected-row result. Do not assume a previous SELECT reserves a seat. 5. Explain the design in an examState the entities and cardinality first, identify candidate keys, select primary keys, then draw the foreign-key relationships. Explain what happens for a student with no enrolments and for a course with several students. A concrete three-row example exposes mistakes that a definition-only answer can hide. Further reading: PostgreSQL’s join tutorial and transaction tutorial explain these operations in the database’s own documentation. The small enrolment dataset above is an original exercise. |