S E S S I O N

3

Designing a Database

Session Checklist

Understanding database design concepts

Learning to normalize database tables

Learning the basics of Structured Query Language

The key to developing an active Web site is data. Data is basically unstructured information such as a name, address, or user preference (a favorite color, for example). When you think about it, as Information Technologists, all we do is move data from one place

to another and present it in different formats. There are many different types of data, including numbers, strings, and dates. For example, the number 30 might represent age, the string “Jason Butler” might present a name, and 1/1/2000 might represent a date.

A dynamicWeb site starts with a database. This is where information — that is, a collection of related data elements — is stored, modified, and transmitted. There are many databases

on the market, including Microsoft Access, SQL Server, and Oracle. The type of database you choose as the back end (or database that supports an application) for your site ultimately depends on the size and security requirements of your company, as well as your budget.

There are two types of data: (1) relational and (2) non-relational. The difference between the two is how the data is organized. This session focuses on relational data. Relational data is stored in a Relational Database Management System (RDBMS). The information in relational databases is often presented in tables. Tables are created by grouping related data in columns and rows. When necessary, tables are related back to each other by the RDBMS.

Designing a Database

We believe that the best way to learn is by doing — so, let’s start building a database. We will be using SQL Server 7.0, but you can use anything you like. The important thing to take away from this session is not only how to build a SQL Server database, but rather how to

design and build a database. Use the RDBMS with which you are most comfortable. If you are a novice, try using Microsoft Access. Access is generally not the RDBMS of choice as the back end for a high traffic Web site, but it’s a good program to start with.

In the remainder of this session, we will show you how to build a music catalog database — band names, band members, albums, and so on. For this purpose, you need to know what information, or data elements, your database will store. For example, you may want to gather the following information about a band:

1.Band Title

2.Music Type Title (the type of music a band plays, for example, Rock & Roll)

3.Record Company Title

4.Albums

5.Band Members

Next, you need to determine the data type for each element. The data type specifies the kind of information (numeric, character, and so on) and how much space that information takes up in a particular column. Though each RDBMS handles data types differently, you are likely to encounter at least three — characters, integers, and dates — as described in Table 3-1.

Next, you have to decide which data type to assign to each band item. At the same time, you need to determine which elements are optional and which are required (see Table 3-2) and how much space each element will occupy in the database. For example, you can reasonably assume that a band’s name won’t exceed fifty characters in length. So you specify in the database design that the column should not accept data elements that are longer than 50 characters in length. Data types represent the kind of data that can be stored in a particular column. For example, if you want to store a band’s name, you will store character data. If you want to store the number of members in the band, you would store the data

as a number or integer. Each RDMS supports different data types. SQL Server, for instance, provides VARCHAR and CHAR among other data types to store character data, and INT and FLOAT among others to store numeric data.

Refer to your RDMS’s documentation for supported data types.

Note

Armed with this information, you can now create your table. Follow a standard naming convention when creating table and column names. For example, you may choose to always name your tables using the following convention:

t_[plural object descriptor]

Since the table you are creating will contain a record, or row, for each band, choose t_bands as the table name. Use the following convention to name columns:

[singular object descriptor]_[column descriptor]

Figure 3-1 shows the design of your t_bands table.

Figure 3-1 t_bands table

You’ll notice that we have created a field for each of the data elements we defined earlier. The first field, band_id is our unique identifier. Without going into SQL Server details, we created the unique identifier by selecting the column’s identity field.

Your RDBMS should be structured to create a unique identifier for each record. For example, the first band could be assigned a unique identifier of 1, the second, 2, and so on. In Oracle, this is called a sequence, in SQL Server it is referred to as an identity. You should generally make this unique identifier the table’s primary key. A primary key is a field or group of fields that uniquely identifies a record.

OK, now let’s talk constraints. A constraint is a mechanism for enforcing the integrity of the data in your table. There are several types of constraints. Among these are primary key constraints, foreign key constraints, unique constraints, and check constraints. Check constraints ensure that the data entered in a column follows a set of rules. A unique constraint ensures that the data inserted into a column, or group of columns, is not duplicated in the table. A foreign key references the primary key of another table and ensures the data in the foreign key column is present in the referenced table.

The implementation of constraints differs drastically by RDBMS, so instead of actually going through the process of creating a constraint in SQL Server, you need to consider where constraints might be useful in your t_bands table. Because band titles are generally unique, it’s a good idea to place a unique constraint on the band_title column.

Normalization of Data

Now let’s take a moment to review and validate the design of the t_bands table. Generally, to validate the design of our table, it’s a good idea to ensure that it is normalized. Normalization is the process of organizing data into related tables. By normalizing your data, you are effectively attempting to eliminate redundant data in your database. Several rules have been established for the normalization of data. These rules are referred to as normalization forms. The first three normalization forms are:

First Normal Form (FNF): This rule states that a column cannot contain multiple values. If you further inspect t_bands for FNF compliance, you should come to the conclusion that the albums and members fields, band_albums and band_members, should be broken down into smaller, discrete elements. The band_members and band_albums columns are currently defined such that if a band has multiple members or have released multiple albums, then band_members and band_albums columns will contain multiple values.

Second Normal Form (SNF): This rule states that every non-key column must depend on the entire key, not just the primary key. Because you are using band_id as your primary key, you are in good shape with respect to SNF.

Third Normal Form (TNF): This rule is very similar to the SNF rule and states that all nonkey columns must not depend on any other nonkey columns. A table must also comply with SNF to be in TNF. OK, you pass this test too!

There are three other normalization rules that aren’t covered here. Generally, if your tables are in Third Normal Form, they probably conform to the other rules.

To fully optimize your tables, you should take some additional measures. It’s a good idea to break your t_bands table into several tables and link them to t_bands via foreign keys. Also, you should create a t_music_types table that holds all the possible music types. The t_bands table should have a foreign key to the primary key of the t_music_types table.

This is generally good practice for two reasons: (1) it ensures that your band’s music type falls into the music type domain and (2) it is easier to maintain. For example, if you change your mind and want to refer to “R&B” as “Rhythm & Blues,” you won’t have to change every instance of “R&B” in the band_music_type_title column — you only need to change

the music type title in the t_music_types table. You could also do the same thing for the band_record_company_title and contact_business_state fields.

At this point, your database contains three tables: (1) t_bands, (2) t_music_types, and

(3) t_record_companies. Figure 3-2 shows a diagram of our new database design:

Figure 3-2 Database design showing relationship of three tables

In the diagram in Figure 3-2, t_bands is linked to t_music_types via a foreign key to music_type_id and linked to t_record_companies via a foreign key to record_company_id. This new relationship between the tables is called one-to-many. In a one-to-many relationship, each entry in the contact type table may be referenced by one or many contacts.

You now have three tables and have met your current requirements. However, what about bands and albums? Currently, you are storing all of the band’s albums and members in a single column, band_albums and band_members, respectively. Currently, if you wanted to retrieve a list of a band’s members or albums, you would need to retrieve the data in the band_members or band_albums column and parse it. This is not the optimal approach. The best approach for this situation is to further normalize your database by creating two new tables. The first is a table to store all albums (for example, t_albums) and a second that stores all band members (for example, t_band_members). The tables t_albums and t_band_members will have foreign keys to the t_bands table. Figure 3-3 shows the new database diagram.

Figure 3-3 Diagram of expanded table design