問55
頻出度★★★
関係データベースを使い“社員”表と“部署”表を作成して社員情報を管理する。“社員”表と“部署”表に，必要に応じて設定する主キーと外部キーの適切な組合せはどれか。ここで，社員は必ず“部署”表に存在する部署に所属するものとし，社員データの追加や更新をするときには，参照制約を利用して整合性を確保するものとする。 社員
| 社員コード | 社員名 | 入社年 | 生年月日 | 部署コード |
|---|
部署
| 部署コード | 部署名 |
|---|
平成25年度 春期 ITパスポート試験 問55
テクノロジ系データベース › データベース設計
This question's explanation and your answers so far.
解説
問 55
Incorrectア
The primary keys are set correctly, but the foreign key is given as “none”. Without a foreign key, no referential constraint takes effect, so the DBMS cannot prevent a department code that does not exist in the “Department” table from being entered into the employee data. This fails to satisfy the condition required by the question, 『use referential constraints to ensure integrity』, so it is wrong.
- イ
正解 A primary key is “a column that uniquely identifies each row of a table”, so in the “Employee” table, where one employee occupies one row, it is the employee code, and in the “Department” table, where one department occupies one row, it is the department code; each of these becomes the primary key. Next, the department code column of the “Employee” table holds the value that shows which department the employee belongs to, and it points to a value of the primary key (department code) of the “Department” table. A column that refers to the primary key of another table in this way is a foreign key. Once a foreign key is set, a referential constraint (referential integrity constraint) takes effect, and if you try to add or update employee data with a department code that does not exist in the “Department” table, the DBMS rejects it, so the condition given in the question, 『every employee must belong to a department that exists in the Department table』, is kept automatically. Therefore the correct answer is イ: the primary keys are the employee code of the “Employee” table and the department code of the “Department” table, and the foreign key is the department code of the “Employee” table.
Incorrectウ
Because no primary key is set on the “Employee” table, rows with duplicate employee codes could be registered, and an employee could not be uniquely identified. In addition, the employee code is not a column that refers to the primary key of another table, so it does not qualify as a foreign key. The only column that can be a foreign key is the department code of the “Employee” table.
Incorrectエ
If the department code of the “Employee” table were made the primary key, duplicate values would no longer be allowed, so several employees could not belong to the same department. Also, the department code of the “Department” table is the primary key on the referenced side, not a foreign key, and the employee code does not refer to another table either, so it cannot become a foreign key. This combination swaps the roles of the primary key and the foreign key, and the direction of the reference is reversed as well.
ここがポイント
- A primary key is a column that uniquely identifies a row of a table, and duplicate values and null values (NULL) are not allowed. The employee code identifies one employee, and the department code identifies one department.
- A foreign key is a column that refers to the primary key of another table, and it is placed on the “many” side of a one-to-many relationship (here, the employee side). Setting it as a foreign key makes a referential constraint take effect, and the DBMS rejects the addition or update of a value that does not exist on the referenced side, thereby keeping integrity.
用語