Database Basics

  Previous topic Next topic JavaScript is required for the print function Mail us feedback on this topic! Mail us feedback on this topic!  

 

As simple to use as Report Builder is, there are some database fundamentals that you should be comfortable with in order to get the most out of this powerful tool. It is one thing to blindly type information into a computer, but empowering yourself as to how a database program really works will greatly enhance the reports that you create.

 

A database is nothing more than a collection of information that is somehow related. A common phone book is a wonderful example of a database. A basic definition of a phone book would be a collection of names/addresses/phone number(s) for people and businesses in one physical area (usually a city, or a common area code number). So, a database would be all the names, addresses and phone numbers contained in a phone book. The ComQuest Respondents file is very much like a phone book database, storing names, addresses and phone numbers of various respondents (as well as radio station listening, demo, gender and ethnic information).

 

A database file is comprised of two new terms: database records and database fields. Going back to the phone book example once again, each person's name listed in the phone book would be a database record - it contains all the information for one person or business (their name, address, phone number(s), etc). The next name shown in the phone book would be a separate database record, and so on.

 

A database record is made up of database fields. Using the phone book again as an example, we would have separate database fields for the following information:

 

Individuals/business name

 

Street address

 

Phone number

 

A database might have only one field, or it can have hundreds of fields; whatever it takes to store all the information required for one database record entry.

 

A phone book is usually sorted on the name of the individual/business, beginning with the number 0, and ending with the letter Z. This type of sort is known as ASCENDING sequence. If you started at the back of a phone book and worked your way to the front (going from the letter Z to the number 0) this sort is called DESCENDING sequence. Sorting a database requires the creation of a second type of file known as a database index, which can also be called a database key.

 

A database index is a file used by a database application to change the appearance of information stored by the database. Going back to the phone book example - every day the phone company creates phone numbers as people and businesses move into the city. Their name(s) are obviously not going to be in alphabetical order when the clerk creates their new phone number - a person named Jones may be first, then Joe-Bob's Barbecue is next, then Alpha-Beta Drugs may be third, and so on. Without a way of sorting this information, a printed phone directory would be a total mess - names would jump around the alphabet! The index file's job is to regain some structure and order to the information being input into the database.

 

A database index file can be created using one database field, or several database fields.  These file(s) are normally opened when the database is opened, and as each database record is added/deleted an identical action is performed on the index file. For the phone book example, the desired sort would be the individual/business name to be sorted in ascending alphabetical sequence. The field name used for the index would be the individual/business name. If your phone company were really customer-oriented they could perhaps create section(s) in their phone book that were sorted on the street address - a new index created on that database field would be created/used to print this new section of the phone book.

 

Index file(s) are most commonly used in browse screens (a screen where all the database records are displayed in columns, sorted alphabetically for easy location of information) and reports.

 

Index file(s) are also used to relate two or more database files together, which store different pieces of information that belong together. This type of database application is known as a relational database - two or more database file(s) relate to each other by means of a common database field, and an index file that links the two separate files together.

 

An example of a relational database could be your bank account. When you first joined your bank you filled out a form with your name, address, work history, etc. The clerk typed this information into the computer, handed you a bundle of blank checks and thanked you for joining their bank. All this "initial" information is stored in a database. As you start writing checks to pay for things a transaction is created for the date it was written, the dollar amount, the date the transaction was posted to your account, the check number, etc. These transactions are stored in a separate database file.

 

At this point we have one database that stores customer information (name, address, etc) and another database that stores transactions for each check cashed. In both database files is a field that contains the account number, and an index file is created for each database on this field. The two database files are related to each other by these index files created on the account number.

 

Not all database applications require a relational setup - if the information being tracked is simple then a single database file will contain all the data being monitored. However, all business type database systems rely on the relational database structure in order to keep track of a large quantity of information. Relational databases greatly reduce the amount of information being kept in database files, which keeps the size of the database file(s) small and increases the speed of the program in retrieving data.

 

ComQuest is very much a relational database; the data in the basic Respondent file is related to other files for each respondent, such as to the Scores, History and Schedule files.  Then, the Scores file is also related to the Songs file, which contains the title and artist information for each of the song entries in the Scores file.

 

Here is an example of related files in just the portion of ComQuest that involves Perceptual results:

 

ComQuest File Relationships for Perceptual Surveys