Database Concepts
| Simply put, a database is a collection of information, typically organized as a collection of records. Each record is composed of fields, and each field contains information related to that specific record. For example, suppose you have an address database. In this database, you have one record for each person. Each record contains six fields: the person's name , street address, city, state, ZIP code, and phone number. A single record in your database might look like this: NAME: Jane Customer STREET: 16 Maple Dr. CITY: Indianapolis STATE: IN ZIP: 46290 PHONE: 800-555-1212 Your database will contain many records like this one, with each record containing information about a different person. To find a person's address or phone number, you search for the name. When you find the name, you also find all the information that's included in the record with the name. This example uses a flat database. For home use or for small businesses, the simple flat database model can be a powerful tool. However, for large databases that must track dozens, or even hundreds, of fields of data, a flat database can lead to repetition and wasted space. Suppose you run a large department store and want to track some information about your employees , including their name, department, manager's name, and so on. If you have 10 people in Sporting Goods, the name of the Sporting Goods manager is repeated in each of those 10 records. When Sporting Goods hires a new manager, all 10 records have to be updated. It would be much simpler if each employee record could be related to another database of departments and manager names . A relational database is like several flat databases linked together. Using a relational database, you can not only search for individual records, as you can with a flat database, but also relate one set of records to another. This enables you to store data much more efficiently . Each set of records in a relational database is called a table . The links are accomplished through keys , which are values that define a record. (For example, the employee ID might be the key to an employee table.) The sample relational database that you use in this chapter was created using Microsoft Access. The database is a simple system for tracking employees, managers, and the departments for which they work. The Employees table (see sample data in Table 11.1) contains information about each store employee; the DeptManagers table (see sample data in Table 11.2) contains information about each store department's manager; and the Departments table (see sample data in Table 11.3) contains information about the departments themselves . (This database is very simple and probably not usable in the real world.) The database is included with the sample application for this chapter; copy the file somewhere onto your own computer. Table 11.1. Employees Table
Table 11.2. DeptManagers Table
Table 11.3. Departments Table
Typically a database is used to answer business questions such as
Some of these questions can be answered by looking in a single table: The Employees table contains all the employees and their pay rates. Others require combining tables. Finding where Richard Anderson works involves finding his department ID, MENSCLOTHING, and then finding that department's location, 1st Floor. To answer the pay rate question, you would loop through each employee, noting their department ID, using the DeptManagers table to find the department manager's employee ID, from there going back to the Employees table to determine the manager's pay rate, and comparing it to the pay rate of the employee. The questions answered by this chapter's samples are simple, but you can easily adapt these same techniques to more complicated manipulations of more complicated databases.  |
EAN: 2147483647
Pages: 141