Database Fundamentals - macromedia COLDFUSION MX 7 Web Application Construction Kit [Electronic resources] نسخه متنی

Database Fundamentals

You have just been assigned a project: you must create and maintain a list of all the movies produced by your employer, Orange Whip Studios.

What do you use to maintain this list? Your first thought might be to use a word processor. You could create the list, one movie per line, and manually enter each movie's name so the list is alphabetical and usable. Your word processor provides you with sophisticated document-editing capabilities, so adding, removing, or updating movies is no more complicated than editing any other document.

Initially, you might think you have found the perfect solutionthat is, until someone asks you to sort the list by release date and then alphabetically for each date. Now you must re-create the entire list, again sorting the movies manually and inserting them in the correct sequence. You end up with two lists to maintain. You must add new movies to both lists and possibly remove movies from both lists as well. You also discover that correcting mistakes or even just making changes to your list has become more complicated because you must make every change twice. Still, the list is manageable. You have only the two word-processed documents to be concerned with, and you can even open them both at the same time and make edits simultaneously.

The word processor isn't the perfect solution, but it's still a manageable solutionthat is, until someone else asks for the list sorted by director. As you fire up your word processor yet again, you review the entire list-management process in your mind. New movies must now be added to all three lists. Likewise, any deletions must be made to the three lists. If a movie tag line changes, you must change all three lists.

And then, just as you think you have the entire process worked out, your face pales and you freeze. What if someone else wants the list sorted by rating? And then, what if yet another department needs the list sorted in some other way? You panic, break out in a sweat, and tell yourself, "There must be a better way!"

This example is a bit extreme, but the truth is that a better way really does exist. You need to use a database.

Databases: A Definition

Let's start with a definition. A database is simply a structured collection of similar data. The important words here are structured and similar, and the movie list is a perfect example of both.

Imagine the movie list as a two-dimensional grid or table, similar to that shown in Figure 5.1. Each horizontal row in the table contains information about a single movie. The rows are broken up by vertical columns. Each column contains a single part of the movie record. The MovieTitle column contains movie titles, and so on.

The movie list contains similar data for all movies. Every movie record, or row, contains the same type of information. Each has a title, tag line, budget amount, and so on. The data is also structured in that the data can be broken into logical columns, or fields, that contain a single part of the movie record.

Here's the rule of thumb: any list of information that can be broken into similar records of structured fields should probably be maintained in a database. Product prices, phone directories, invoices, invoice line items, vacation schedules, and lists of actors and directors are all database candidates.

Where Are Databases Used?

You probably use databases all the time, often without knowing it. If you use a software-based accounting program, you are using a database. All accounts payable, accounts receivable, vendor, and customer information is stored in databases. Scheduling programs use databases to store appointments and to-do lists. Even email programs use databases for directory lists and folders.

These databases are designed to be hidden from you, the end user. You never add accounts receivable invoice records into a database yourself. Rather, you enter information into your accounting program, and it adds records to the database.

Clarification of Database-Related Terms

Now that you understand what a database is, I must clarify some important database terms for you. In the SQL world (you will learn about SQL in depth in Chapter 6, "Introduction to SQL"), this collection of data is called a table. The individual records in a table are called rows, and the fields that make up the rows are called columns. A collection of tables is called a database.

Picture a filing cabinet. The cabinet houses drawers, each of which contains groups of data. The cabinet is a way to keep related but dissimilar information in one place. Each cabinet drawer contains a set of records. One drawer might contain employee records, and another drawer might contain sales records. The individual records within each drawer are different, but they all contain the same type of data, in fields.

The filing cabinet shown in Figure 5.2 is the databasea collection of drawers or tables containing related but dissimilar information. Each drawer contains one or more records, or rows, made up of different fields, or columns.

Data Types

Each row in a database table is made up of one or more columns. Each column contains a single piece of data, part of the complete record stored in the row. When a table is created, each of its columns needs to be defined. Defining columns involves specifying the column's name, size, and data type. The data type specifies what data can be stored in a column.

Data types specify the characteristics of a column and instruct the database as to what kind of data can be entered into it. Some data types allow the entry of free-form alphanumeric data. Others restrict data entry to specific data, such as numbers, dates, or true or false flags. A list of common data types is shown in Table 5.1.

Most database applications provide a graphic interface to database creation, enabling you to select data types from a list. Microsoft Access uses a drop-down list box, as shown in Figure 5.3, and provides a description of each data type.

There are several reasons for using data types, instead of just entering all data into simple text fields. One of the main reasons is to control or restrict the data a user can enter into that field. A field that has to contain a person's age, for example, could be specified as a numeric field. This way, the user can't enter letters into itonly the digits 09. This restriction helps keep invalid data out of your database.

Various data types are also used to control how data is sorted. Data entered in a text field is sorted one character at a time, as if it were left justified. The digit 0 comes before 1, which comes before 9, which comes before a, and so on. Because each character is evaluated individually, a text field containing the number 10 is listed after 1 but before 2 because 10 is greater than 1 but less than 2, just as a 0 is greater than a but less than b. If the value being stored in this column is a person's age, correctly sorting the table by that column would be impossible. Data entered into a numeric field, however, is evaluated by looking at the complete value rather than a character at a time; 10 is considered greater than 2. Figure 5.4 shows how data is sorted if numbers are entered into a text field.

The same is true for date fields. Dates in these fields are evaluated one character at a time, from left to right. The date 02/05/05 is considered less than the date 10/12/99 because the first character of the date 02/05/05the digit 0is less than the first character of the date 10/12/99the digit 1. If the same data is entered in a date field, the database evaluates the date as a complete entity and therefore sorts the dates correctly.

The final reason for using various data types is the storage space that plain-text fields take up. A text field big enough to accommodate up to 10 characters takes up 10 bytes of storage. Even if only 2 characters are entered into the field, 10 bytes are still stored. The extra space is reserved for possible future updates to that field. Some types of data can be stored more efficiently when not treated as text. For example, a 4-byte numeric field can store numeric values from 0 to over 4,000,000,000! Storing 4,000,000,000 in a text field requires 10 bytes of storage. Similarly, a 4-byte date/time field can store the date and time with accuracy to the minute. Storing that same information in a text field would take a minimum of 14 bytes or as many as 20 bytes, depending on how the data is formatted.