The relational model

The relational model

Related data is contained in a database. This related data may encompass a single application, such as the inventory management system for a retail store, or for a whole company, such as the accounting, sales, production, and human resources data, or anywhere in between.

A database is made up of tables, each of which looks like a spreadsheet-a two-dimensional grid of data elements. Each table stores zero or more instances of an entity-in other words, one or more rows of the spreadsheet. An entity is the generic description of what's being stored in the table, and an instance is a single specific version of the entity.

For example, an inventory management system for a music store might have one table that contains each stock item-CD, tape, or album-that the store carries or can order, and another table that contains each distributor from which the store buys music. The distributor table's entity is "distributor" and each individual distributor is an instance. If there were 200 distributors, there would be 200 instances in the table.

Each column in a table is called a field (or an attribute) and contains a single data element of a single type. This data element must be atomic-that is, it cannot be broken down into smaller pieces, and all of the data elements in a column must be of the same type and contain the same sort of information. For example, columns in the Distributor table might include Company Name, Contact Name, Address, City, State, Zip, Voice Phone, Fax, and Email.

The definition of "atomic" varies according to how the database will be used. The State is an excellent example of an atomic data element-it can't be broken down any further. If you need to store the County within the State, you would use a separate field, but you could still include the State. The Voice Phone might be broken out into three fields-Area Code, Exchange, and Number-but this might not be appropriate if the Distributor table will contain Voice Phones for different countries, because different countries have different structures.

How you define "same sort of information" also can vary. For example, the Email field might contain any kind of e-mail address-Internet, CompuServe, MCIMail, and so on. If it was important, you might need to define different types of e-mail addresses and store them in different fields. However, you wouldn't store Zip Codes, Email Addresses, and Hat Sizes in the same field. (I'll discuss how to handle issues such as these later in this chapter.)

The set of possible values of a column is called the domain of that column. (Imagine my surprise when I found out that the famous "Master of His Domain" episode on the TV sitcom Seinfeld wasn't about database programming!) The domain for the State column would be the list of all 50 states in the United States, but could also include the 13 provinces and territories in Canada and the many states in Mexico if the Distributor table was going to include companies throughout North America.

The collection of all the fields in a table make up a record (or a tuple). Each row in a table contains one record and represents a unique instance of that entity. This last point begs repeating. In a properly designed table, each instance in the table-each record-must be unique, by definition. If there are multiple records that are completely identical, then the table was designed incorrectly. There must be a value in at least one of the columns-or a value from a combination of columns-that distinguishes each record from any other record. (If this were not the case, it would be impossible to retrieve that specific record.)

The other important point about an instance is that it can't span more than one record in the table. Often, people would use more than one row in a spreadsheet to store all of the data for a single instance. This is not permitted in the relational model. Use more fields so that the entire instance can be stored in a single record.

Tables are related to each other through the use of attributes (just fields, right?) called keys. Each table must contain at least one column or group of columns that uniquely identifies each instance; if more than one column is used, this groups of columns is called a superkey. (There can be more than one key that uniquely identifies each instance.)

For example, the combination of Name, Address, City, State and Zip Code would make up one superkey; the combination of Phone Number and Zip Code would make up another. There can be more than one superkey in a table, but only one can be defined as the primary key. The choices for a primary key are called candidate keys, and a candidate must contain the very minimum number of columns necessary to make it unique. (Not all candidate keys are superkeys, because a candidate key might be a single column.) For example, if Phone Number and Zip Code make up a candidate key, Phone Number, Zip Code and State do not make up another candidate key, because State is superfluous. If a single column is used to create a key, it is called a single key; if two or more columns are used, the key is called a composite key. The Phone Number/Zip Code column combination would be an example of a composite key, while a Company ID Number would be an example of a primary key made up of a single column.

One of the basic rules of the relational model is the transference of duplicate data out of a table into a second table where it has to exist in only a single record. For example, consider a garden supply store that buys lawnmowers of a certain manufacturer from several different distributors. The Stock table contains a record for each physical lawnmower, and needs to know which distributor a specific lawnmower came from. But you wouldn't want to repeat the company and address information in every Stock record. Instead, all of the Stock items are placed in one table and the Distributors are in another table, and the Distributor's primary key (such as the Distributor ID number) is placed in the Stock table as an attribute. When a primary key for a table appears in another table, it's referred to as a foreign key. Thus, the Distributor ID is called a primary key when it's in the Distributor table and a foreign key when it's in the Stock table.

Obviously, a table can have only one primary key, but it can contain several foreign keys. For example, to expand our garden supply store example, we might have a table that defines a particular brand of lawnmower-model number, price, horsepower, riding or pushing, and whether or not it comes with a refrigerator and a stereo. The Stock table would contain a record for every physical lawnmower; if the store has six identical lawnmowers, the Item table would contain one record whose attributes contained "JX-100", "$4,300", "1.2 HP", "Riding", "2 cu. Ft refrigerator", and "Bose dual speaker stereo." It would also have a primary key, which might be a randomly generated unique number, like "65094009." The Stock table, on the other hand, would have six records, each of which contained the Item primary key (65094009) as a foreign key, and the Distributor primary key that identifies which Distributor that specific physical lawnmower came from. (It's possible that the garden supply store gets the same model of lawnmower from more than one Distributor, and they want to track where each is coming from. Suppose a particular model had a high defect rate-you'd want to be able to track which Distributor needed to be contacted.)

The Fundamentals of Visual FoxPro 6.0

It's important to remember that the primary key must contain a unique value for each record in the table. This means that it may not be empty or null, and that the method for creating the key for new records must guarantee unique values forever. It's also important to note that while we view records in tables as having "record numbers" much like the row numbers in a spreadsheet, the primary key is the only way a record should be accessed. It is possible to change to "record number" (the relative position) of a record through any number of operations (such as deleting records, physically rearranging the table, and so on), but those operations don't change the value of the keys. In other words, the record number is only a physical convenience and has no meaning in the relational world.

Remember again, the physical representation of these elements-databases, tables, indexes, and so on-don't have to have a one-to-one relationship with their logical cousins. For example, a "database" might be stored in more than one file, or a group of (logical) tables might all be stored in the same physical structure. However, the logical and physical representations often do map to each other. Let's look at how Visual FoxPro physically stores relational data.