This question's explanation and your answers so far.
関係データベースで管理している“学生”表，“科目”表，“成績”表がある。1人の学生は複数の科目を履修するものとし，“学生”表に登録されていない学生や，“科目”表に登録されていない科目は“成績”表に登録できないものとするとき，外部キーとして設定するのが適切なものはどれか。ここで，表中の下線は主キーを表す。 学生
| 学生番号 | 学生名 |
|---|---|
| G001 | 鈴木 次郎 |
| G002 | 山田 春子 |
科目
| 科目コード | 科目名 |
|---|---|
| K001 | データベース |
| K002 | コンピュータ |
| K003 | ネットワーク |
成績
| 学生番号 | 科目コード | 成績 |
|---|---|---|
| G001 | K001 | 優 |
| G001 | K002 | 可 |
| G002 | K002 | 良 |
| G002 | K003 | 優 |
The Student No. in the “Student” table is the primary key on the referenced side, not a foreign key. A foreign key is set on a column in the referencing child table (here, the “Grade” table). Moreover, this option only links students and lacks the reference to subjects, so it cannot satisfy the condition in the question.
The student name and subject name are ordinary attributes that are not primary keys, so they cannot uniquely reference another table. A foreign key must be a column that references the primary key of the other table, so a name column cannot be made a foreign key.
正解 The correct answer is “ウ”. A foreign key is a column that maintains consistency between tables (referential integrity) by referencing the primary key (or a unique column) of another table, and it is set on the referencing side (the child table). The condition in the question, “a student not in the “Student” table or a subject not in the “Subject” table cannot be registered in the “Grade” table”, precisely means that the “Grade” table references the “Student” table and the “Subject” table. The “Student No.” in the “Grade” table references the primary key (Student No.) of the “Student” table, and the “Subject Code” references the primary key (Subject Code) of the “Subject” table. Therefore, the columns that should be set as foreign keys are the “Student No.” and “Subject Code” in the “Grade” table, so ウ is appropriate.
The grade in the “Grade” table (Excellent, Good, Pass, etc.) is merely a data item representing an evaluation and does not reference the primary key of any other table. It is not a foreign key that ensures referential integrity.