An E-R diagram (E-R図) shows the entities a business manages and the relationships between those entities.
Multiplicity
How records correspond
Example
One-to-one
Seen from either side, there is one record on the other
Store and store manager (each store has one manager, and each manager runs only one store)
One-to-many
One record on one side matches several on the other; the reverse direction matches one
Class and student (a class has several students, and each student belongs to one class)
Many-to-many
Seen from either side, there are several records on the other
Student and club (students may join several clubs, and a club has several students)
An entity is a group of things you want to record as data, and a relationship shows how entities are connected. Each relationship is labeled with its multiplicity (one-to-one, one-to-many, many-to-many and so on), which tells how many records on one side match a single record on the other.
AnalogyThink of the character map for a TV drama. Each character gets a box, and lines labeled "parent" or "coworker" show at a glance who is connected to whom and how.
How to rememberE stands for Entity and R for Relationship, so the name itself lists the diagram's two building blocks.
Exam points
ExamAn E-R diagram (E-R図) sorts out how the things a business manages relate to one another when a database (データベース) is designed, and in a relational database it also shows how tables (テーブル) connect.
ExamIf a class has several students and every student belongs to just one class, class and student have a one-to-many (1対多) relationship. Multiplicity (多重度) is fixed by counting records in both directions.
ExamAn E-R diagram is drawn from the results of business analysis, and table design then builds on that diagram.
A primary key (主キー) is the column, or set of columns, chosen to uniquely identify each row (record) in a table.
Show No.
Seat No.
Booked by
P01
A1
Aoki
P01
A2
Aoki
P02
A1
Nishimura
In the table above, the same show number appears in several rows, and so does the same seat number, but the two columns together pin down a single row. When columns are combined like this, the whole combination counts as one primary key.
AnalogyIt works like the ID number on a student card. Two students may share a name, but the number points to exactly one of them.
How to rememberPrimary means "first in importance": the primary key is the first thing you look at to tell rows apart.
Exam points
ExamEvery row has a different primary key (主キー) value, and a key built from several columns tells rows apart by the combined value, so the individual columns may repeat. None of the columns that make up a primary key may be empty (NULL).
ExamWhen no single column identifies a row, two or more columns can be combined into one primary key.
ExamA primary key is set so that any row in the table can be identified uniquely.
A foreign key (外部キー) is a column (or set of columns) that refers to primary key values in another table, linking the two tables through those values.
Table
Column
Role
Class table
Class code
Primary key
Student table
Student No.
Primary key
Student table
Class code
Foreign key (refers to the class code in the class table)
A foreign key column cannot hold a value that does not exist in the table it refers to, and this rule is called a referential constraint (referential integrity). In the tables above, it means no student row can point to a class missing from the class table, so the link between the tables stays intact.
AnalogyIt is like the member number written on a delivery slip. The number has to match someone who actually appears in the member list.
How to rememberForeign means it points outside its own table: a foreign key is a signpost to another table.
Exam points
ExamA one-to-many (1対多) relationship is represented by giving the table on the "many" side the primary key (主キー) of the table on the "one" side as a foreign key (外部キー).
ExamBecause a foreign key cannot hold a value missing from the table it refers to, data must be added to the referenced table first.
ExamA table that refers to several tables gets a separate foreign key for each one, so a single table may have more than one foreign key.
An index (インデックス) is a lookup structure that maps a column's values to row locations, built to speed up searches.
Without an index
With an index
Finding rows that match a condition
Check every row in turn
Go straight from the index to the row
Adding, updating, deleting rows
Rewrite the table only
Rewrite the index as well as the table
Storage space
The table alone
Extra space for the index
Without an index, finding rows that meet a condition means scanning the table from top to bottom. With one, searches get faster, but every change to a row also requires updating the index, which adds work to each write and takes extra storage for the index itself.
AnalogyIt is like the index at the back of a thick book. You can jump from a word to its page right away, but whenever the text is revised, the index has to be revised too.
How to rememberIn English, index also names the forefinger (the index finger): picture it pointing straight at the row you want.
Exam points
ExamAn index (インデックス) exists to speed up searches, and which columns get one depends on how searches are actually used.
ExamAn index can be built for each column used in searches, so one table may have several indexes.
Normalization (正規化) is the design work of splitting one table into several to remove duplicated data and prevent inconsistencies when data is updated.
Booking table also holds member and course details
Split off a member table (items set by member No.)
Split off a course table (items set by course code)
Booking table refers by member No. and course code
The booking table keeps the member number and course code as foreign keys that link it to the new tables.
4 / 4
When the same fact is stored in several places, a change may reach only some of them and leave the values out of step. Normalization splits tables according to which item each item depends on, then links the resulting tables through shared columns (primary and foreign keys). The further normalization goes, the finer the tables are divided.
AnalogyImagine a business partner's address that used to be copied onto every form, moved into a dedicated partner list so that forms carry only the list number. When the address changes, you fix one entry in the list.
How to rememberTo normalize is to put things into a standard form, and here tidying up has a counterintuitive effect: the tidier it gets, the more tables you have.
Exam points
ExamNormalization (正規化) aims to store each fact in just one place, so values never disagree after an update and maintenance gets easier.
ExamAn item whose value depends only on another column (such as the member number) moves to a table that uses that column as its primary key (主キー), and the original table keeps the column to stay linked.
ExamAn item that can be calculated from other items is redundant (冗長). For example, the amount follows from the unit price and quantity.
An E-R diagram describes a business's data structure with entities and relationships, and each relationship carries a multiplicity such as one-to-one or one-to-many.
A primary key is a column or set of columns that uniquely identifies a row; its value cannot repeat in another row, and none of its columns may be empty.
A foreign key is a column (or set of columns) that refers to another table's primary key, and it cannot hold a value missing from that table.
An index speeds up searches at the cost of extra write work and storage space.
Normalization splits tables by what each item depends on, removing duplication and mismatches after updates.