IPA IP 2023 Question 59

Topic: Databases

A question IPA Japan published in 令和5年度 ITパスポート試験 公開問題 問59, translated into English. ITPEC is based on the same exams.

A “Member_management” table that is managed in a relational database was normalized and divided into a “Store” table, a “Member_type” table, and a “Member” table. Which of the following is appropriate as the “Member” table? Here, an underline in a table indicates a primary key, and when one member registers with multiple stores, a member number is assigned for each store.

Member_management

Store_​codeStore_​nameMember_​numberMember_​nameMember_​type_​codeMember_​type_​name
001Sapporo1Shiken Hanako02Gold
001Sapporo2Joho Taro02Gold
002Tokyo1Kodo Jiro03Regular
002Tokyo2Gozen Sakurako01Platinum
003Osaka1Gozen Sakurako03Regular

Store

Store_codeStore_name

Member_type

Member_type_codeMember_type_name

Explanation

Member(Member_number, Store_code, Member_name, Member_type_code) — the member's key needs the store, and the member type belongs to the member at that store.

  • •Key: member numbers are assigned per store (number 1 exists at stores 001, 002 and 003), so a member is identified only by Store_code and Member_number together.
  • •Member_name depends on that whole key, so it stays in the Member table.
  • •Member_type_code also depends on the whole key: Gozen Sakurako is 01 (Platinum) at Tokyo but 03 (Regular) at Osaka, so the type is a property of each registration and must stay in the Member table.
  • •Store_name depends only on Store_code, and Member_type_name only on Member_type_code: these go to the Store and Member_type tables, which the Member table refers to by the two codes.

Answer (d)

Why not others:

  • •(a) and (b) — Member_number alone is not unique (three members have number 1), and without Store_code the member cannot be linked to the Store table
  • •(c) — drops Member_type_code, so the member type of each registration is lost and the Member_type table cannot be joined

Key rule: In normalization, move each column that depends on only part of the key (or on a non-key column) to its own table, and keep the code that refers to it as a foreign key.

Databases · AI-written, may contain errors

Practice this exam

This question comes from the past questions IPA Japan publishes, translated into English by ITPEC Practice. ITPEC Practice is an independent study tool and is not affiliated with IPA or ITPEC. See the original paper (PDF) or Report an issue.