The diagram below shows a simplified... | Computer Science 0478 | Paper 2 Mock 01 | Algorithms, Programming and Logic

Question 1 Report

The diagram below shows a simplified entity-relationship (ER) model for a school database.

(a) Describe the relationship between TEACHER and CLASS shown in the diagram. [2]

(b) Describe the relationship between CLASS and STUDENT shown in the diagram. [2]

(c) State where the foreign key would be placed to link TEACHER and CLASS. Explain why it goes in that table. [2]

Answer

(a) The relationship between TEACHER and CLASS: [2]

This is a one-to-many relationship. One teacher can teach many classes, but each class is taught by exactly one teacher. [1] For example, Mr. Smith might teach Class 10A, 10B, and 11C, but each of those classes has only one teacher assigned. The "1" on the TEACHER side and "M" on the CLASS side in the diagram confirm this. [1]

(b) The relationship between CLASS and STUDENT: [2]

This is also a one-to-many relationship. One class can contain many students, but each student belongs to exactly one class. [1] For example, Class 10A might have 30 students, but each of those students is in only one class. The "1" on the CLASS side and "M" on the STUDENT side confirm this. [1]

(c) Where the foreign key goes to link TEACHER and CLASS: [2]

The foreign key (TeacherID) should be placed in the CLASS table. [1]

In a one-to-many relationship, the foreign key always goes in the table on the "many" side. Since one teacher has many classes, each class record stores the TeacherID of the teacher who teaches it. This way, multiple class records can point to the same teacher. If the foreign key were placed in the TEACHER table instead, each teacher could only reference one class, which contradicts the one-to-many requirement. [1]

Read lesson note on Relational Databases (OxfordAQA)

Read lesson note on Database (WAEC)

Read lesson note on Structured Query Language (sql) (OxfordAQA)

Read lesson note on Database Design (OxfordAQA)

Read lesson note on Databases (CIE)

Read lesson note on Database Package (JAMB)

Answer Details

(a) The relationship between TEACHER and CLASS: [2]

(b) The relationship between CLASS and STUDENT: [2]

(c) Where the foreign key goes to link TEACHER and CLASS: [2]

The foreign key (TeacherID) should be placed in the CLASS table. [1]

Read lesson note on Relational Databases (OxfordAQA)