Adobe 38043740 Developing Applications - Page 406

DEVELOPING COLDFUSION 9 APPLICATIONS 401 Accessing and Using Data EmpID LastName FirstName Title DeptID Email Phone 4 Smith John Engineer 3 jsmith x5833 Example employees table The record for employee 4 contains the following field values: • LastName field is "Smith" • FirstName field is "John" • Title field is "Engineer" 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: The SQL keywords and syntax are represented here as uppercase letters. Table and column names use mixed uppercase and lowercase letters. Using multiple database tables In many database designs, information is distributed to multiple tables. The following image shows two tables, one for employee information and one for employee addresses: EmpID LastName FirstName Title DeptID Email Phone 1 Jones 2 Davis 3 4 5 Employees table Baker Smith Morris Joe Engineer 3 Ken Manager 4 Mary Engineer 3 John Engineer 3 Jane Manager 3 jjones kdavis mbaker jsmith jmorris x5844 x5854 x5876 x5833 x5833 Last updated 1/20/2012