Defined

A database is a collection of information that allows you to identify relationships between the information. Once the relationship is identified, you can draw conclusions about that relationship. A database organizes, stores, manages, and retrieves information through the use of tables.

For example: A research firm collects the following information from children in preschools located in Los Angeles:

Examine the table, can you see that there are some relationships formed by the number of identical responses. Can you identify the relationships?

There appears to be the following relationships:

• Blonde females and hotdogs
• Brown haired males and red M&M’s
• Females and potty trained
• Blonde and brown-haired children’s likes and dislikes are the same.

Through these relationships, conclusions can be drawn:

• Blonde haired preschool females prefer hotdogs for lunch.
• Preschool boys who have brown hair prefer to eat only red M&M’s.
• Boys do not get potty trained until after they are out of preschool.
• Kids with red hair are highly independent.

Now I said these conclusions can be reached, I didn’t say they were true.

Can you see how some researchers can come to faulty conclusions? Every day you eat oatmeal to lower your cholesterol, then you are told to trade in the oatmeal for red wine!

In ecommerce, databases hold product, customer, and financial information. The following would be an example of Walmart’s product database:

Relationship: Exists between the silk shirt, sizes, and price.

Conclusion: The shirt comes in three sizes. Each incremental size increases the price by $0.59.

Database Terms

These terms will be used over and over in this course. Make sure you understand them. Additional terms will appear in the relevant lecture.

• Table - a collection of data about a specific topic, organized into columns and rows.
• Field - a category of information. i.e. gender, hair color, product, attribute, cost, etc. (column)
• Record - a collection of information consisting of related fields about a specific entity (row).
• Data - the information contained in the table cells that conform to the field.

Q. Looking at the preschool database table above (the first table), can you identify the number of fields and records? Look for the answer at the bottom of the page.

Database Types

Databases are organized by their structure, content, or function. Databases can be simple or complex.

Flat Databases are nothing more than a simple collection of data in columns and rows in one table. The data can be sorted and filtered, but relationships are not defined between tables of data. This type of database looks like a spreadsheet.

Q. What is the difference between a database and a spreadsheet?

A. Databases are actually much more powerful than spreadsheets. Databases allow you to manipulate data in ways a spreadsheet cannot. In addition, spreadsheet’s main function is to do calculations. A database can do the following and a spreadsheet cannot:

• Retrieve all records that match certain criteria
• Update records in bulk
• Cross-reference records in different tables
• Perform complex aggregate calculations

Relational Databases use many tables to build relationships between the data in the tables. NOTE: although many tables may exist, there is only one database file.

The relationship occurs when the tables have a common field. These fields can be divided into primary and foreign keys.

The primary key is the key that is unique and corresponds to the same key in another table. A table should always contain a primary key to ensure the correct indexing of the data in the table. It is usually the first key in each table. In the image to the right, the primary keys are the first highlighted field in each table.

The foreign key is the matching field in the other table. In the image, the Students table contains a primary key called StudentID, which is matched to the foreign key by the same StudentID field in the Scores table.

More Database Terminology

• Sort - arranging the display of the records to a certain field.
• Filter - rules that are applied when you apply criteria to the data.
• Query - a method of viewing, changing, combining, or analyzing data.
• Data type — each field has a data type, such as text, number, memo, OLE object, etc.
• Keys - two types that are used to keep track of records.
• Primary key — unique value for each record in a table.
• Foreign key - used to create relationships between tables.
• Index - a table containing the key values in a table.