Managing & Using MySQL, 2nd Edition

Relational Databases

In the simplest terms, a database is a collection of data. An example of a nonelectronic database is the public library. The library stores books, periodicals, and other documents. When you need to locate some data at the library, you search through the card catalog or the periodicals index, or maybe you even ask the librarian. Those unsorted piles of papers on your desk also form a database. This database can potentially work, because the size of the database is incredibly small. A stack of papers certainly would not work with a larger set of data, such as the collections in the library. The library would still be a database without the card catalog, periodicals index, and librarian; it would just be an unusable database. A database generally requires some sort of organization to be of value. The paper pile, for example, is of greater value when organized into filing cabinets. So, restating our definition, a database is an organized collection of data.

The library and the stack of papers have many similarities. They are both databases of documents. It makes no sense, however, to combine them because your papers are interesting only to you, and the library contains documents of general interest. Both databases have specific purposes and are organized according to those purposes. We will therefore amend our definition a bit further: a database is a collection of data that is organized and stored according to some purpose.

Traditional paper-based databases have many disadvantages. They require a tremendous amount of physical space. Libraries occupy entire buildings, and searching a library is relatively slow. Anyone who has spent time in a library knows that it can consume a nontrivial amount of time to find the information you seek. Libraries are also tedious to maintain, and an inordinate amount of time is spent keeping the catalogs and shelves consistent. Electronic storage of a database helps to address these issues.

MySQL is not a database, per se. It is computer software that enables you to create, maintain, and manage electronic databases. This category of software is known as a database management system (DBMS). A DBMS acts as a broker between the physical database and the users of that database.

When you began managing electronic information, you almost certainly used a flat file, such as a spreadsheet. The filesystem file is the electronic version of the pile of papers on your desk. You likely came to the conclusion that this sort of ad hoc electronic database did not meet your needs anymore. A DBMS is the logical next step for your database needs, and MySQL is the first stepping stone into the world of relational DBMSs.

A relational database is a special kind of database that organizes data into tables and represents relationships among those tables. These relationships enable you to combine data from multiple tables to provide different “views” of that data. Table 1-1 describes a table that might appear in a library’s database.

Table 1-1. A table of books

ISBN	Title	Author
0-446-67424-9	L.A. Confidential	James Ellroy
0-201-54239-X	An Introduction to Database Systems	C.J. Date
0-87685-086-7	Post Office	Charles Bukowski
0-941423-38-7	The Man with the Golden Arm	Nelson Algren

Tables Table 1-2 and Table 1-3 are two tables that might appear in an NBA database.

Table 1-2. A table of NBA teams

Team #	Name	Coach
1	Sacramento Kings	Rick Adelman
2	Minnesota Timberwolves	Flip Saunders
3	L.A. Lakers	Phil Jackson
4	Portland Trailblazers	Mike Dunleavy

Table 1-3. A table of NBA players

Name	Position	Team #
Vlade Divac	Center	1
Kevin Garnett	Forward	2
Kobe Bryant	Guard	3
Rasheed Wallace	Forward	4
Damon Stoudamire	Guard	4
Shaquille O’Neal	Center	3

We will get into the specifics about tables later on, but you should note a few things about these examples. Each table has a name, several columns, and rows containing data for each of the columns. A relational database represents all of your data in tables just like this and provides you with retrieval operations that generate new tables from existing ones. Consequently, the user sees the entire database in the form of tables.

Also note that the “Team #” column appears in both tables. It encodes a relationship between a player and a team. By linking the “Team #” columns you can determine that Vlade Divac plays for the Sacramento Kings. You could also figure out all the players on the Portland Trailblazers. This linking of tables is called a relational join , or join for short.

A DBMS for a relational system is often called a relational database management system (RDBMS). MySQL is an RDBMS.