It is no exaggeration to say that the quality of an information system is determined by the quality of its database design. Poor design causes the same data to be duplicated in many places, creating inconsistencies whenever updates are made. In this lesson, you will learn ER diagrams, which are design diagrams, and normalization (the first through third normal forms), which is the process of organizing tables.
Starting Point of Design — ER Diagrams
When designing a database, first represent in a diagram what kinds of things (entities) exist in the target world and how they are related (relationships). This is an ER diagram (entity-relationship diagram). For example, in a library, the entities are “students” and “books,” and the relationship is “borrow.” One student can borrow many books, and one book can be borrowed by many students at different times, so “students” and “books” have a many-to-many relationship. Because a many-to-many relationship cannot be represented directly in a table, an “loan” table is placed between them to divide it into a one-to-many and many-to-one relationship.
Primary Keys and Foreign Keys (Review and Development)
A primary key is a column that uniquely identifies each row and must satisfy two conditions: it must not be duplicated and must not be NULL (empty). A foreign key is a column that references the primary key of another table and connects tables. By imposing the constraint of referential integrity—only values that exist in the referenced table may be entered as foreign keys—we can prevent inconsistent data such as “a loan record for a nonexistent student.”
Normalization — Organizing Tables in Stages
Normalization is the process of dividing and organizing tables in stages to prevent duplicated data and inconsistencies during updates (update anomalies). Let us proceed through the first to third normal forms using the following order-slip example.
Unnormalized form: Order table (Order ID, date and time, Member ID, member name, {Product ID, product name, unit price, quantity}×multiple rows)
There is a “repeating group” in which multiple products appear on one slip, so it cannot be treated as a table as it is.
① First normal form: Expand repeating sections so that each row contains only one fact.
→ Order details table (Order ID, Product ID, date and time, Member ID, member name, product name, unit price, quantity). The primary key is the composite key (Order ID, Product ID).
② Second normal form: Separate columns determined by only part of the primary key (eliminate partial functional dependencies).
“Product name・unit price” are determined only by Product ID, while “date and time・Member ID・member name” are determined only by Order ID.
→ Order table (Order ID, date and time, Member ID, member name)／Product table (Product ID, product name, unit price)／Order details table (Order ID, Product ID, quantity)
③ Third normal form: Separate columns determined by non-primary-key columns (eliminate transitive functional dependencies).
In the order table, “member name” is determined by Member ID, not by the primary key Order ID.
→ Order table (Order ID, date and time, Member ID)／Member table (Member ID, member name)／Product table (Product ID, product name, unit price)／Order details table (Order ID, Product ID, quantity)
This means that changing a member’s name requires changing only one place in the member table, and raising a product’s price requires changing only one place in the product table.
Effects and Cautions of Normalization
The purpose of normalization is to create a state in which one fact is recorded in only one place. This has the effects of ① preventing inconsistencies caused by missed updates, ② reducing wasted storage capacity, and ③ clarifying the meaning of data. On the other hand, because tables are divided, the amount of joining (JOIN) processing described later increases. In practice, it may be decided to intentionally reduce normalization (denormalize) to prioritize search speed, but the basic principle is to design in normal form first.
- Organize entities and relationships with an ER diagram before designing tables. Divide many-to-many relationships into one-to-many relationships using an intermediate table.
- Primary key＝no duplicates・no NULL. Foreign key＝reference to the primary key of another table (referential integrity).
- First normal form: Remove repeating groups and put one fact in each row.
- Second normal form: Separate columns determined by only part of a composite primary key (eliminate partial functional dependencies).
- Third normal form: Separate columns determined by non-primary-key columns (eliminate transitive functional dependencies).
- The purpose is to record “one fact in one place.” This prevents inconsistencies during updates.
Practice Problems
- Consider the relationship between “students” and “clubs.” If one student can join several clubs and one club can have many students, how should this relationship be designed in tables?
- The table “grade table (student ID, name, subject code, subject name, score)” has a composite primary key of student ID and subject code and is not in second normal form. Explain why.
- Explain the purpose of normalization using the words “one fact.”
Answers・Explanations
- Answer: Because the relationship is many-to-many, create an intermediate table such as a “membership table (student ID, club ID)” between the student table and club table, and divide the relationship into one-to-many and many-to-one relationships.
Explanation: The student ID and club ID in the membership table become foreign keys referencing the student table and club table, respectively. - Answer: “Name” is determined only by student ID, which is part of the primary key, and “subject name” is determined only by subject code. Because columns that depend only on part of the key (partial functional dependencies) remain, the table is not in second normal form.
Explanation: Separating the data into a student table (student ID, name), subject table (subject code, subject name), and score table (student ID, subject code, score) produces second normal form. - Answer: To eliminate duplication of the same information and create a state in which “one fact is recorded in only one place,” thereby preventing inconsistencies (update anomalies) caused by missed corrections during updates and deletions.
Explanation: When data is duplicated, values become inconsistent if one place is corrected but the others are forgotten.