Working with Databases
Project 1: Intro

Project Objectives

• Know what a database is used for
• Know the difference between a flat file database and a relational database
• Know the advantages and disadvantages of each
• Know what a record and a field are
• Know what a database application is
• Understand how a text database is set up
• Know what a delimiter is
• Work with a database in a spreadsheet program
• Work with a flat file database (in Microsoft Works)
• Understand how a relational database is set up

What is a database?

A database is basically just a list. Some lists are a lot more complicated than others!

You use databases every day. Your grocery list, address book, and telephone book are all simple databases (VERY simple!). A table of test scores, an accounting ledger sheet, and the address cards in a rolodex are also simple databases. You don't need a computer and special software to use these, just good eyes and perhaps a pencil.

Do you recognize these simple databases?
Click image to see labels.

Computer database software is useful or even required for more complex databases, like a store's inventory, a bank's records of your deposits and withdrawals, the state's database of crimes, an airline's database of upcoming flights and reservations. These are more than just simple lists!

Types of Databases

Databases can be organized into types based on their structure (like text, flat file, and relational) or on their content (bibliography data, full-text documents, images, numbers, info about other databases) or on their function (static information or allowing changes). A database of databases is called a metadatabase. Some just list the available databases. Others can reach into those databases to select documents or data.

We will be looking at three of the types based on their structure: text, flat file, and relational. There are other types of database structures besides these!

After this first project, you will work with relational databases.

Database Terms

Field = one item of data
     Example:  the last name or the city in an address book.
     Field name = value, like LASTNAME = "Carter".

Record = a set of fields that belong together
     Example: a complete name and address.

Data type = Each field has a data type, such as text, number, memo, OLE object... Most data types also have attributes, like the size of the field or a default value.

Datasheet view= a display of the data in a table of rows and columns, like a spreadsheet, where each record is on one row. There are often too many fields to see all of them at once.

Form view = a display of the fields in a record that makes it easy to enter new data or to edit existing data or to choose actions.

A well-designed form shows all the fields with little or no scrolling, yet keeps everything at a readable size.

Some forms do not show records but instead offer action buttons that perform tasks or open forms and reports.

Report view = a print-out of data and calculations, usually formatted in a more readable manner than just printing the datasheet.

A report usually organizes the data in some manner and may show only some of the records or fields. For example, a business might want a report of just the customers who are late paying their bills.

In the illustration, the records of students and their grades on assignments are grouped by section and then sorted alphabetically by name. The report calculates the averages for the six weeks grading period for each type of assignment (Daily, Quiz, Test)  and then for the subject (Reading, Spelling, English). Lots of organizing going on here!

Click the report image to see a larger version. If Internet Explorer automatically resizes the image, move your mouse over it until the image resize button appears, then click it.

Sort = to rearrange the display of the records to set the order for a certain field or fields(s), but still keeping all of the fields in a record together.

Example: Sort names into alphabetical order by last name, first name, middle name.
Example: Sort prices from highest to lowest.

Filter = a rule that hides the records that do not match the criteria in the rule.

Example: Require that the value of the field City be "Nashville" and that the value of the field State be "Tennessee".

Filtering does not delete records! It just temporarily hides them.

Query = a method of viewing, changing, combining, or analyzing data.

Example: Display just the fields needed for mailing labels.
Example: Update all of a student's records when the student's name changes.

All filtering and sorting is actually done by simple queries. Forms and reports often get their data from queries rather than from the original tables.

Index = a table of the key values in your table.

Sorting and filtering are often much faster if the database looks at an index instead of the original table with all its fields. Indexes do take up space in your database and must be rebuilt whenever the values that they contain change. If the data changes often in a large database, rebuilding the indexes can slow the entire database to a crawl.

Flat File Database Programs

Sometimes all you really need is a set of rows and columns from a spreadsheet or a flat file database, like Microsoft Works.

The illustration at the right show the list view in Microsoft Works. Each row is a single record. The field names are at the top of the columns.

There are actually 15 fields. The horizontal scroll bar in the illustration is the only clue that a lot of each record is out of view to the right.  Not a comfortable view to work in!

Even basic flat database programs offer tools for sorting and filtering your data. Also, you can use a form to enter and view the data in a single record more conveniently.

Such programs do not usually offer the advanced management or reporting options that a relational database program has.

Relational Database

Most recent databases are relational databases. These are made up of a set of related tables. Each table is made up of records which are made up of a set of fields, just like the single table in a flat database.

Printing any one table does not show all of the data. It can be very difficult to print a relational database in a way that shows all of the data at once because there is so much more data included than in a flat file database. The more data in a record and the more records you will have in your database, the more useful a relational database will be to you.

Example: Language Arts class grades
The illustration below shows some of the tables (with the fields listed) in a relational database: Classes/Subjects, Students, Scores, and Assignments.

Each table has a primary key field (in bold letters) that will be unique for each record in that table. The tables are connected with join lines that connect the primary key from one table with a matching field in the other table. This tells the database which student and which assignment match up with a particular score.

It would be VERY awkward to try to put all of the same information into a single table. Each record about a score would have ALL of the fields about the student and ALL the fields about the assignment plus the other fields from the Scores table. That's 27 fields for one record, all of which have to be typed in for each of the 3500 records for each grading period. No one would do that!!!

By separating the information, linking the tables together, and creating some forms, the teacher can enter a student's information only once in the year and can enter the information about an assignment only once also. In the Scores table the teacher selects a student and an assignment and then enters the score and any other information that is required. Much better!

In fact the teacher can use an update query to add records for each student for a given assignment. All she would have to do then is to type in the actual scores.

Advantages

• Avoids duplication of data
     Example: Student info and Assignments are in separate tables that are linked to a table of Scores
      Example: Supplier info and Customer info are in separate tables that are linked to the Orders.
• Reduces file size by avoiding duplication
• Can keep data consistent
     Example: Choosing from a list of suppliers avoids winding up with "My Supplier, Inc." in one record and just "My Supplier" in another. Totals and groupings won't work if the names are not exactly the same.
• Faster queries

Disadvantages

• More complex
• More difficult to set up initially

Commercial Database Applications

Many programs that you can buy for home or business use are actually databases, including clip art collections, contact managers, address books, and checkbook software. This kind of database application does not allow the user to see the inner workings of the tables, forms, and reports. The user sees only the choices that the database programmer allowed for reports and forms. This works well for situations when the programmer can predict very accurately what tasks will need to be done.

A commercial database has been compiled (turned into machine code of 1's and 0's). Users cannot access the underlying code, so they cannot break the database by uneducated tinkering. That is usually a good thing!  However, a user cannot usually add a new field to the underlying table or write a new query in such a program. You are at the mercy of the database programmer's ability to guess what you will need to do. For some situations, they guess very well.

~~  1 Cor. 10:31 ...whatever you do, do it all for the glory of God.  ~~

Last updated: 30 Apr 2012