| Developing ColdFusion MX Applications with CFML |
|
|
Introduction to Databases and SQL |
|
| Developing ColdFusion MX Applications with CFML |
|
|
Introduction to Databases and SQL |
|
A database defines a structure for storing information. Databases are typically organized into tables, which are collections of related items. You can think of a table as a grid of columns and rows. ColdFusion works primarily with relational databases, such as Oracle, DB2, and SQL Server.
The following figure shows the basic layout of a database table:
A column defines one piece of data stored in all rows of the table. A row contains one item from each column in the table.
For example, a table might contain the ID, name, title, and other information for individuals employed by a company. Each row, called a data record, corresponds to one employee. The value of a column within a record is referred to as a record field.
The following figure shows an example table, named employees, containing information about company employees:
The record for employee 4 contains the following field values:
This example uses the EmpID field as the table's primary key field. The primary key contains a unique identifier to maintain each record's unique identity. Primary keys field can include an employee ID, part number, or customer number. Typically, you specify which column contains the primary key when you create a database table.
To access the table to read or modify table data, you use the SQL programming language. For example, the following SQL statement returns all rows from the table where the department ID is 3:
SELECT * FROM employees WHERE DEPTID=3
Note: In this chapter, SQL keywords and syntax are always represented by uppercase letters. Table and column names used mixed uppercase and lowercase letters.
In many database designs, information is distributed to multiple tables. The following figure shows two tables, one for employee information and one for employee addresses:
In this example, each table contains a column named EmpID. This column associates a row of the employees table with a row in the addresses table.
For example, to obtain all information about an employee, you request a row from the employees table and the row from the addresses table with the same value for EmpID.
One advantage of using multiple tables is that you can add tables containing new information without modifying the structure of your existing tables. For example, to add payroll information, you add a new table to the database where the first column contains the employee's ID and the columns contain current salary, previous salary, bonus payment, and 401(k) percent.
Also, an access to a small table is more efficient than an access to a large table. Therefore, if you update the street address of an employee, you update only the addresses table, without having to access any other table in the database.
In many database environments, a database administrator defines the access privileges for users accessing the database, usually through username and password. When a person attempts to connect to a database, the database ensures that the username and password are valid and then imposes access requirements on the user.
Privileges can restrict user access so that a user can do the following:
In ColdFusion, you use the ColdFusion administrator to define database connections, called data sources. As part of defining these connections, you specify the username and password used by ColdFusion to connect to the database. The database can then control access based on this username and password.
For more information on creating a data source, see Administering ColdFusion Server.
Before you access data stored in a database, it is important to understand several database concepts, including:
A database commit occurs when you make a permanent change to a database. For example, when you write a new row to a database, the write does not occur until the database commits the change.
Rollback is the process of undoing a change to a database. For example, if you write a new row to a table, you can rollback the write up to the point where you commit the write. After the commit, you can no longer rollback the write.
Most databases support transactions where a transaction consists of one or more SQL statements. Within a transaction, your SQL statements can read, modify, and write a database. You end a transaction by either committing all your changes within the transaction or rolling back all of them.
Transactions can be useful when you have multiple writes to a database and want to make sure all writes occurred without error before committing them. In this case, you wrap all writes within a single transaction and check for errors after each write. If any write causes an error, you rollback all of them. If all writes occur successfully, you commit the transaction.
A bank might use a transaction to encapsulate a transfer from one account to another. For example, if you transfer money from your savings account to your checking account, you do not want the bank to debit the balance of your savings account unless it also credits your checking account. If the update to the checking account fails, the bank can rollback the debit of the savings account as part of the transaction.
ColdFusion includes the cftransaction tag that allows you to implement database transactions for controlling rollback and commit. For more information, see CFML Reference.
From this basic description, the following database design rules emerge:
The best way to familiarize yourself with the capabilities of your database product or database management system (DBMS) is to review the product documentation.
