Database Fundamentals

The most common type of database in use with Web applications is a relational database management system (RDBMS), such as Microsoft Access or MySQL. An RDBMS stores data in a collection of tables that have a defined relationship with one another. The tables provide a structured way to store information. The columns (or fields) in a table contain data about a single category. The rows (or records) in a table contain one element of data in each category.

The easiest way to understand database tables is to look at some examples. Figure 4.1 shows a table we'll call the ClassRoster table, created in Microsoft Access 2003. This table has five fieldsStudentID, First Name, Last Name, CourseID, and InstructorIDand four records.

Figure 4.1. A database table organizes information in columns (fields) and rows (records).

If you use the ClassRoster table as a data source, you can access data from any or all records and fields in this table. For example, you could create a class roster for the instructor that lists all students by first and last names for the MA130 course. You could also add a Grade field to the Class-Roster table, and create a dynamic Web page that enables the instructor to enter grades online and add this information to the table in the database.

Figure 4.2 shows another table, the Instructors table, located in the same database as the ClassRoster table. This table has three fieldsInstructorID, First Name, and Last Nameand consists of four records. As you'll see in a moment, the data in these two tables is relationalthat is, the data in one table is connected to the data in the other.

Figure 4.2. A database may contain several tables of related information.

Databases use primary keys to identify a specific and unique record in a database table. In the Class-Roster table, the StudentID field is the primary key. Each StudentID is unique and identifies a specific record (row) in the table. In the Instruc-tors table, the InstructorID field is the primary key.

Databases use relationships among the data in the tables to define the interactions between pieces of information. There are three types of relationships:

• One-to-one relationships. When each instance of an item, which can be a field in a record, an entire record, or an entire table, has only one instance of a related item, that is a one-to-one relationship. For example, the first record in the ClassRoster table includes a value for StudentID (JA14784) and for Last Name (Jones), each of which is associated with the other in a one-to-one relationship in this table.

• One-to-many relationships. When one item has many instances of other related items, that is a one-to-many relationship. For instance, because each instructor may be associated with many students, the RB09736 value for InstructorID in the Instructors table is related to all the values for Student ID, First Name, Last Name, and CourseID in the ClassRoster table.

• Many-to-many relationships. When one item, which we'll call Item A, has many instances of Item B, which in turn has many instances of Item A, that is a many-to-many relationship. For example, because each student may be enrolled in more than one class, a JA14784 value for StudentID and Last Name may be associated with several CourseIDs, which in turn are associated with several InstructorIDs and other StudentIDs. Many-to-many relationships can be broken down further into multiple one-to-many relationships; otherwise, they are too nonspecific to be useful.

Primary keys and relationships are essential elements in database design; they are used to decide what tables you need to create and what information should be included in each table. For more information on database design, see the appendix "Beginner's Guide to Databases" in Dreamweaver Help (F1).

Once your database is built, you can request information from it using a database query. You use the results of these queries to add dynamic content to your pages.