●Primary key
An item set to differentiate rows from other columns in a table.
Multiple items can also be combined and made a primary key.
●Foreign key
An item in a table that is the primary key of another table.
●Referential constraint
A constraint set to maintain consistency between tables by ensuring that
values that exist in the foreign key also exist in the referenced primary key.
3
Chapter 9
●Index
Created to speed up data searches. An index is created with respect to
items within a table specified by the search conditions.
Creating indexes speeds up database searches.
However, indexes are updated when data is updated. If indexes are created
indiscriminately, the processing speed will slow down.
Data normalization
• Eliminate redundancy of data stored in tables.
• Set tables in a format that enables data to be manipulated using SQL
statements.
Normalized tables are said to be in “normalized form,” and those that are
not are said to be in “non-normalized form.”
Examples of non-normalized form (grades table)
Student ID
2001010
2001021
Name
Iuchi
Department code Department name Course name Classroom
R
Department of Mathematics 1
A103
Science
English
B211
Nakahara
K
Department of Mathematics 1
A103
Economics
German 2
C402
“Course name”, “classroom”, and “grade” in
the grades table contain several items of
data.
These are called “repeating items.” This
grades table is yet to be normalized and is
in “non-normalized form.”
Grade
A
Ｃ
Ｂ
A
Reference
Normalization procedure
For “Non-normalized forms” that include
repeating items, normalization is performed in three steps. These are the
first normal form, second normal form,
and third normal form. Data redundancy
is eliminated by completing all three
steps.
Technology element
In order to use a database, it is necessary to determine a table format. At
such time, the process of “data normalization” is performed. Data normalization is the process of dividing tables appropriately so that data is not
duplicated.
Normalizing data eliminates redundancy, making it easier to manage data,
and enables the use of data for various purposes.
Data normalization is a basic technique for building databases.
The main purposes of normalizing data are summarized below.
First normal form
Eliminate repeating items
Second normal form
Move items dependent on part of the
primary key to other tables
Third normal form
Move items not dependent on the
primary key to other tables
Repeating items = “Non-normalized”
252