Beginner's guide to databases

Note: This article was written for Dreamweaver CS3. However, the content is still valid.

This article is intended for Adobe Dreamweaver CS3 users who have little or no experience working with databases or database connections. It explains general concepts, not specific procedures. To see how these concepts apply in practice, see Dreamweaver Help.

The article describes how to design a database but not how to create one in an application such as MySQL or SQL Server. That process is described in the printed or online documentation that came with your database system.

The building block of a database is the record. A record is a collection of related data treated as a single entity. For example, a hockey trading card could be called a record: it brings together the name, photograph, team, and statistics of one player. Using database terms, each of these related pieces of information is called a field: each hockey card record has a name field, a photograph field, a team field, and various player statistics fields.

A collection of records that share the same fields is called a table because this kind of information can easily be presented in table format: each column represents a field and each row represents a record. In fact, the word column is synonymous with the word field, and the word row is synonymous with the word record.

A database can contain more than one table, each with a unique name. These tables can be related or independent from one another.

A subset of data extracted from one or more tables is called a recordset (or a DataSet in ASP.NET). A recordset is also a table because it’s a collection of records that share the same columns. For example, a hockey team roster listing the names and positions of the players could be called a recordset: it consists of a subset of all the possible information about the players, including goals, assists, penalty minutes, and so on.

To create a recordset, you run a database query. A query consists of search criteria. For example, the query can specify that only certain columns be included in the recordset, or that only certain records be included.

Database design is the first step in building any database-driven website. This section presents a case study to explain basic database design principles. The case study involves a web application commissioned by a fictional company called Arrow Aircraft Services, a firm that manages a small fleet of privately owned business jets.

Database design consists of a series of common steps. First, study the business rules and policies your client wants the web application to reflect. Second, consider the questions users will ask the database. Third, define the structure of the database. Finally, create the database.

This section contains the following topics:

• Studying Arrow Aircraft’s business rules and policies
• Studying Arrow Aircraft’s feature request
• What questions will users ask the database?
• Choosing the tables that belong in the database
• Choosing the columns in each table
• Defining relationships between the tables
• Creating the database

Studying Arrow Aircraft’s business rules and policies

You've been hired on a contract basis to build a web application for Arrow Aircraft Services. Before you start designing the database, you want to understand all the organization’s business rules and policies that will affect the application. This section describes the (simplified) business rules and policies of Arrow Aircraft Services.

Arrow Aircraft manages a fleet of five business jets of varying sizes and models for their owners. Each jet has up to eight fractional owners—corporations or individuals who have purchased a share of the aircraft. This type of arrangement is popular with those who don’t need a business jet on a year-round basis.

The fractional owners, or shareholders, pay Arrow Aircraft the following fees:

• A monthly management fee proportional to the owner’s share of the aircraft to cover pilot, insurance, and hangaring costs
• An "occupied" hourly fee covering all direct costs such as maintenance, engine reserves, and catering

In exchange, a shareholder tells Arrow Aircraft when and where they want to fly and Arrow Aircraft takes care of the rest, including obtaining flight and ground crews and catering the flight. Arrow Aircraft requires a minimum notice of 8 hours to prepare an aircraft for a flight.

Arrow Aircraft guarantees 800 “occupied hours” per year for each aircraft. Occupied hours are hours where the jet is both in flight and occupied by at least one passenger. Occupied hours begin six minutes (0.1 of an hour) before the aircraft takes off with passengers and end six minutes after the aircraft lands.

Each aircraft can have up to eight owners. For example, an individual or corporation can purchase a 1/5 interest (or a 1/5th share) in a jet. Since Arrow Aircraft guarantees 800 occupied flight hours per year, the owner of the 1/5th share is entitled to 160 occupied hours (800 hours / 5 = 160).

Studying Arrow Aircraft’s feature request

Arrow Aircraft hired you to build a web application with the following features:

• Allow any aircraft shareholder to request an aircraft for a flight
• Provide the company’s Flight Operations Department (Flight Ops) with all the information needed to begin preparing the aircraft, including details of the shareholder’s request (itinerary, departure date and time, catering needs, etc.), the shareholder’s remaining occupied hours, and the availability of the aircraft for the proposed trip
• Allow Flight Ops to book the aircraft to prevent scheduling conflicts

What questions will users ask the database?

After you become familiar with the proposed features of the web application, you sit down with the database users and pose the following question: "What questions will you ask the database?"

You learn from some of the aircraft shareholders that they want to ask the database the following questions:

• How many occupied hours do I have left?
• Is my aircraft available on a certain date or dates?

After a shareholder requests an aircraft, the employees in Flight Ops will ask the database the following questions:

• Where does the shareholder want to go?
• What is the itinerary—one way, return, multi-leg? Flight Ops needs this information to start planning the flight (check weather forecasts, file flight plans, and so on) and to estimate the total occupied hours.
• Does the shareholder have sufficient occupied hours remaining for the proposed itinerary?
• When does the shareholder want to leave?
• What is the shareholder’s plane?
• Is the plane available for the proposed itinerary?
• How many passengers will accompany the shareholders?
• How much luggage will they bring—light (carry-on), normal (one suitcase per passenger), or heavy (more than one suitcase)?
• What are the shareholder’s catering needs?
• What is the occupied hourly fee for the plane?
• Where can I contact the shareholder to confirm the flight and the fee estimate?

Choosing the tables that belong in the database

After learning the questions users will ask the database, you think about how your database should be structured to best answer their questions. The first step is to choose the tables in the database.

In a relational database, all data is represented in the rows and columns of tables. Each table describes a collection of related entities such as persons, objects, or events. Each row describes one occurrence of the entity and each column describes one property of the entity—for example, a person’s last name, an object’s color, or an event’s date.

You choose the following five tables for the Arrow Aircraft database:

• An aircraft table describing all of the business jets managed by Arrow Aircraft
• An aircraft bookings table listing all the dates the jets are booked or otherwise unavailable to fly
• A shareholders table describing all the corporations and individuals who own shares in the jets
• An itineraries table describing all the itineraries requested by the shareholders
• A flights table describing all the legs ("flights") of the itineraries.

Choosing the columns in each table

The next step is to choose the columns in each table. The columns describe the properties of each entity in the table.

A good rule of thumb when choosing columns is to avoid duplicating data. For example, in the Arrow Aircraft application, you know shareholder names will have to be used in association with the itinerary data to answer the following Flight Ops question: "What are the shareholder’s catering needs for a particular itinerary?"

One approach is to have shareholder first and last name columns in both the shareholders table and the itineraries table. However, this would require not only duplicating the name data in two tables, but also several times in the itineraries table (you would have to enter the shareholder’s name in the itineraries table every time the shareholder requested a new itinerary). This approach increases data-entry effort, introduces opportunities for errors, and threatens the data’s integrity (a change in one part of the database must be done in other parts).

A much better solution is to enter the shareholder’s first and last name a single time in a single table, the shareholders table. When the time comes to answer the Flight Ops question above, you can join the shareholders table with the itineraries table using a SQL join expression.

Make sure each of your tables has a primary key column. A primary key column contains values that are unique for each row. This allows you to zero in on the exact row you want when searching the database. Most primary key columns consist of ID numbers, but you can use natural primary keys such as government form numbers or aircraft serial numbers.

After some thought, you choose the following properties and primary keys for the tables in the Arrow Aircraft database:

Defining relationships between the tables

After defining the basic columns and primary keys in your tables, you can start defining relationships between the tables. Once the relationships are defined, you can write SQL statements to combine data from two tables.

For example, each plane managed by Arrow Aircraft has several shareholders. You would like to establish a similar "one-to-many" relationship between each plane in your aircraft table and the shareholders in the shareholders table. This would save you from entering and tracking redundant aircraft data in the shareholders table.

In a one-to-many database relationship, a single row in one table is related to several rows in the another table. You can define this kind of relationship by including a foreign key in the table providing the many rows—in the above example, the shareholders table. A foreign key is a column containing values matching those in the primary key column of another table. Your aircraft table’s primary key is called ac_serial. Therefore, including a foreign key called ac_serial in the shareholders table would define a "one-aircraft-to-many-shareholders" relationship.

With your knowledge of the client’s feature request and of the company’s business rules and policies, you decide to define the following one-to-many relationships in your database:

• Each aircraft can have many shareholders
• Each aircraft has many bookings
• Each aircraft has many itineraries
• Each shareholder has many itineraries
• Each itinerary can have many flights (or legs)

Here are the revised table diagrams after you add the foreign keys defining these relationships:

The diagrams are known as E-R diagrams, or entity-relationship diagrams.

Creating the database

The final design step is creating a database based on your E-R diagrams. You can use a database system like MySQL, Microsoft Access, SQL Server, or Oracle9i. Consult your database system’s documentation for more information.

If you plan to use a database with your web application, you need to create at least one database connection. Without one, the application won’t know where to find the database or how to connect to it. You create a database connection in Dreamweaver by providing the information—or the "parameters"—the application needs to establish contact with the database.

You don’t need to create database connections for ColdFusion pages in Dreamweaver. You connect using ColdFusion data sources.

This section discusses database connections in general terms. For specific instructions on creating connections in Dreamweaver, see "Preparing to Build Dynamic Sites" in Dreamweaver help.

This section contains the following topics:

• Communicating with the database
• Using database drivers to communicate with your database
• Viewing the ODBC drivers installed on a Windows system
• Invoking database drivers
• Using a DSN

Communicating with the database

Data stored in a database is normally in a proprietary format in the same way text in a word processor file is in a proprietary format. For example, here’s what data looks like in Microsoft Access:

Here’s what the same database looks like in Notepad:

Your web application faces the same problem as Notepad or any other application trying to access data in an unknown format: the application can’t decipher the data. A software interface is needed between your web application and the database allowing the application and the database to talk to each other.

Three common interfaces let applications communicate with databases. The first is called Open Database Connectivity, or ODBC; the second is called OLE DB (object linking and embedding database); and the third is called Java Database Connectivity, or JDBC.

The job of these interfaces is to act like interpreters. For example, when a speech is given in English at the United Nations, one interpreter translates for French-speaking delegates while another interpreter translates for German-speaking delegates. Similarly, you use one interface for OLE DB-speaking applications, another interface for ODBC-speaking web applications, and still another interface for JDBC-speaking applications. ColdFusion and JSP applications are JDBC speakers, ASP speak ODBC (through a built-in OLE DB/ODBC interpreter), and ASP.NET applications speak OLE DB.

Using database drivers to communicate with your database

The ODBC, OLE DB, and JDBC interfaces are implemented by database drivers (or "data providers" in OLE DB), which are simply pieces of software. When your web application communicates with your database, it does so through the intermediary of a driver.

Database drivers are database-specific. For example, you can use a driver designed for Microsoft Access with Microsoft SQL Server. Your choice of driver depends on your database.

Drivers are written by database vendors such as Microsoft and Oracle, and by a variety of third-party software vendors. Microsoft offers a number of ODBC drivers and OLE DB providers for the most popular database packages, including Microsoft Access, Microsoft SQL Server, and Oracle. The ODBC drivers, which only run on the Windows platform, are automatically installed with Microsoft Office and with Windows XP Professional. They are also installed with the Microsoft Data Access Components (MDAC) 2.8 package, which you can download for free from the Microsoft website at http://msdn.microsoft.com/data/ref/mdac/downloads/.

To find out which ODBC drivers are installed on your Windows system, see Viewing the ODBC drivers installed on a Windows system.

Here are the database interfaces for each type of web application and some common database drivers for each:

Viewing the ODBC drivers installed on a Windows XP system

If you need a specific ODBC driver and your web server runs on a Windows system, you can easily find out whether or not the ODBC driver you need is installed on your system.

To view the ODBC drivers installed on a Windows XP system:

1. Open the ODBC Data Source Administrator by selecting Start > Control Panel > Administrative Tools > Data Sources (ODBC).
2. Click the Drivers tab.

A list of ODBC drivers installed on the system appears.

Invoking database drivers

An application must invoke a database driver to establish two-way communications with a database.

A web application invokes a driver by using a connection string. A connection string consists of all the information (or parameters) required to establish a connection to a database. In its simplest form, a connection string specifies a driver and a database for the web application, as in this example:

ASP connection strings can contain a Provider parameter specifying an OLE DB provider. If you omit this parameter, by default ASP uses the OLE DB provider for ODBC drivers. In the above example, the OLE DB provider for ODBC drivers would communicate with the ODBC driver, Microsoft Access Driver, which in turn would communicate with the Access database, scaalcoffee.mdb.

The parameters in a connection string may vary depending on the driver. Here’s a connection string for a SQL Server database called Cases located on a server named Hoover:

Note: UID stands for user ID; PWD for password.

If you're developing a ColdFusion application, use the ColdFusion Administrator to define connection parameters. ColdFusion Administrator provides a simple user interface for defining new connections, which are also known as data sources in ColdFusion. Dreamweaver can automatically retrieve these data sources from the ColdFusion server.

Dreamweaver also simplifies the task for other types of applications by providing you with a dialog box in which to enter the different connection parameters. For example, here’s how the dialog box to define a MySQL connection looks when you’re developing a PHP application:

After you complete the dialog box and click OK, Dreamweaver adds the MySQL connection to the Databases panel (Window > Databases). Your page can now communicate with the database. For example, the page can retrieve data by using a database query.

Using a DSN

You can specify data source names (DSNs) for ASP connections. A DSN is a type of shortcut you create in Windows to represent a connection string.

Note: Windows data source names are not to be confused with ColdFusion data sources.

For example, suppose you have a Microsoft SQL Server database called Precinct located on a server called Kojak. To gain access to the database, you must enter the user name columbo and the password savalas7. After using these parameters to define a DSN called ourcops, you can create the connection by entering the single word ourcops in Dreamweaver instead of all the other parameters.

If your application server is running on a Windows system and you defined a DSN on that system, then you can use the DSN to define the ASP connection.

If you do not have physical access to a server—and so are unable to define a DSN on it—then you must use a connection string to connect to the database.

To set up a DSN in Windows XP, see Microsoft Knowledge Base Article 305599 at http://support.microsoft.com/default.aspx?scid=kb;en-us;305599.

Where to go from here

This article briefly touched on database basics. Dreamweaver has many tools to help you work with databases. To find out more, see the following topics in Dreamweaver Help.

• Set up a web application in Dreamweaver. For more information, see "Setting up a web application."
• Define recordsets for your pages. For more information, see "Defining sources of dynamic content."
• Display recordset data in your pages. For more information, see "Create a dynamic table."
• Quickly develop PHP, ColdFusion, or ASP web applications. For more information, see "Making Pages Dynamic" and "Building Applications Visually."