CSIT58 Module 13 Lecture: Database Concepts

The Dozer's textbook doesn't give you quite enough background about databases. It's an important subject so I'll add a little more here.

You have contact with databases every day. When you get your cell phone and credit card bills in the mail, those are generated from databases. When you use your Albertson's preferred card at the supermarket, your purchase information is going into a database. When you use a debit or credit card to pay, that generates database entries (transactions). When you enroll in a class or receive a grade for a class you are taking, that makes an entry in a database.

Large businesses use large network database systems like Oracle or Microsoft SQL Server. Smaller businesses often use Microsoft Access. Access is a complex program to learn and it only comes with the Professional version of Microsoft Office. Most of you probably don't have Access installed on your computer. Excel can be used as a simple database program. It is only useful if you have less than a thousand entries. It cannot handle saving information about related objects like the examples I'll show here. Excel is not good at generating complex reports.

Field (or Attribute)
The smallest unit of information in a database is called a field. A field is a single characteristic or attribute of a person, place, object, or event. A person can be an employee, a customer, a wedding guest and so on. A place can be a city, a college, a theater. An event can be a baseball game, a play, a concert. A field just divides up the information.

For a company that sells coffee to restaurants, some fields are CustomerNum (short for customer number), CustomerName, Street, City, State, and Phone. The field names are shown as column headings over the data. The entries in the cells below the field name are the field values.

Why not combine Street, City and Zip Code all into one single field called Address? It wouldn't be wrong to do that, but it would make the information harder to use. When you create a separate field, you can easily search and sort on those values. If you wanted to do a big mailing to all customers, you could get a discount from the post office if the letters are sorted by zip code. The only way to be able to do that is to have the zip code in a separate field.

Records (Entities) and Tables
The field values in one row are called a record. Some books also use the word entity. In the example above, each record is the set of field values for a particular customer. The entire structure of fields and records is called a table. The table has a name that indicates what's stored in it. The table above is named the Customer Table.

There are usually several different related tables in a single database. The relationship is through a field that appears in both tables. When the field values match, the information from both tables can be used as one record. The Order table links to the Customer table through the CustomerNum field.

Primary Key
Customer number 635 has three different records in the Order table (highlighted) to match to the Customer table. This only works if the field values in CustomerNum in the Customer Table are unique (don't repeat). If a customer number was assigned twice, which one would the orders match to? A field that has unique values for each record is called a primary key. CustomerNum is the primary key in the Customer table. Order Number is the primary key in the Order Table. No two orders can have the same number.

Another Example: The Student Database

People always have a difficult time at first understanding the difference between the field names (the structure) and the field values. Here's an example you are familiar with to reinforce the idea.

The L.A. Community College District (of which Harbor College is a part) maintains a database on students, the courses they've taken, the grades they got, the class schedule, placement tests taken, degrees awarded, etc. This information is divided into several tables for efficient processing.

One table contains the information you filled in on your Application for Admission form. Each question you answered has a corresponding field in the table. It asked for your social security number, last name, first name, middle initial, Gender (Male or Female), Street Address, City, State, Zip, Phone Number and more. A simplified table structure (without any values yet) might look something like this:

The field names appear at the top of each column. The blank row represents an empty record, waiting for field values to be filled in.

A related table contains the grades for each class you have taken. The records are matched through your student number. In the student table, your student number is unique, so it is a primary key. The social security number is not used because of the potential for identity theft.

Here's how the tables might look with data in them:

A list of all of your background information and grades can be prepared by creating a query to search for all records in the tables matching your social security number. The matchups for Matthew Jones are highlighted. As you can imagine, there are a lot more records than this, hundreds of thousands of them for all students at all nine colleges that make up the Los Angeles District.