Download Wiley Beginning MySQL

Chapter 4
the ACM, Volume 13, Number 6 (June 1970). In this paper, Codd introduced a relational data structure that
was based on the mathematical principles of set theory and predicate logic. The data structure allowed
data to be manipulated in a manner that was predictable and resistant to error. To this end, the relational
model would enforce data accuracy and consistency, support easy data manipulation and retrieval, and
provide a structure independent of the applications accessing the data.
At the heart of the relational model — and of any relational database — is the table, which is made up of a
set of related data organized in a column/row structure, similar to what you might see in a spreadsheet
program such as Microsoft Excel. The data represents the physical implementation of some type of object
that is made up of a related set of data, such entities as people, places, things, or even processes. For
example, the table in Figure 4-1 contains data about authors. Notice that all the values in the table are in
some way related to the central theme of authors.
Column name
Authors table
AuthID AuthFN
1001
Edith
1002
Kate
1003
T.
1004
Joyce
1005
Samuel
Primary key
Figure 4-1
AuthMN
NULL
NULL
S.
Carol
L.
AuthLN
Wharton
Chopin
Eliot
Oates
Clemens
Born
1862
1850
1888
1938
1835
Died
1937
1904
1965
NULL
1910
Rows
Columns
Each table in a relational database is made up of one or more columns. A column is a structure in a table
that stores the same type of data. For example, the table in Figure 4-1 includes a column named AuthMN.
The column is intended to hold a value for each author that is equal to the author’s middle name, if one
is known. The column can hold only data that conforms to the restrictions placed on that column.
Along with the columns, each table contains zero or more rows. A row is a structure in a table made up
of a set of related data. A row can be thought of as a record that represents one instance of the object
defined by the table. For example, in Figure 4-1 the last row contains data about the author Samuel L.
Clemens, who was born in 1835 and died in 1910. The author is identified by an AuthID value of 1005.
Together, this set of data makes up one row.
One other item that is important to note in a table is the primary key. A primary key is one or more columns
in a table that uniquely identify each row so that no two rows are identical. In Figure 4-1, a primary key is
defined on the AuthID column. Because this column is the primary key, no values can be repeated in the
column. As a result, even if two authors share the same name, same year of birth, and same year of death,
the values in the primary key column would provide a unique identifier for each row.
Although the table serves as the foundation for every relational database, the relational model includes
other important characteristics, including the normalization of data and the relationships that exist
between tables. The following section discusses how data is normalized in a relational database and,
after that, reviews the types of relationships that can exist between tables.
110