Jan's Illustrated Computer Literacy 101 Logo:Jegsworks Jan's Illustrated Computer Literacy 101


Home > Jan's CompLit 101 > Working with Databases > Intro
Icon: Arrow - Previous pagePrevious    NextIcon: Arrow - Next page

Jan's Working with Databases

   Project 1: Intro to Databases

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?

Examples of simple databases: grocery list, address book, phone book, table of test scores, ledger, rolodex

Do you recognize these simple databases?
Icon: Mouse click Click image to see labels.

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 teacher's grade book, 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 sharp pencil.

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:

  • Structure (like text, flat file, and relational)
  • Content (bibliography data, full-text documents, images, numbers, info about other databases)
  • 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. Just remember that 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 = "Washington". Last Name field

Record = a set of fields that belong together

Example- Student info: ID#, name, preferred name, which section, which line in the grade book, birth date, bus number

Example: Record is made of several fields

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

Icon:  Datasheet 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, as in the example below.

Datasheet showing records of assignments from Language Art Classes database

Example: Form - student plus grades on assignments and tests (Access 2010)Icon: Form 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.

The illustration shows a form for entering and viewing student data. The calculated grade averages are a subreport, so those cannot be edited. The scores at the bottom are a subform and can be edited as needed.

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


ReportIcon: Report 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 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). The print-out groups these results by class section and puts the students in alphabetical order. Lots of organizing going on here!

Icon: Sort 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 into alphabetical order by last name, first name, middle name.
Example: Sort prices from highest to lowest.

Icon: Filter Filter = a rule that shows only the records that match the criteria in the rule.

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

Example 2: Require that the value of the field Amount be greater than 10,000 and less than 100,000.

Filtering does not delete records! It just shows the records that match.

Icon: Query 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 usually 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

List view in Microsoft WorksSometimes all you really need is a set of rows and columns from a spreadsheet or a flat file database, like Microsoft Works or AppleWorks, both of which are no longer produced.

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. This looks VERY much like a spreadsheet.

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!

Form in Microsoft WorksEven 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. Each record is made up of a set of fields. So the structure of each table is much like that of 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 in your database, the more useful a relational database will be to you. That makes it worth the extra effort to set up a relational database.

Example: Language Arts class grades
Relationships between tables in a relational databaseThe illustration shows some of the tables (with the fields listed) in a relational database that I created to manage grades: Classes/Subjects, Students, Scores, and Assignments.

Each table has a primary key field (marked with a key shape Icon: Primary Key) 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!!!

Table of scores from Language Arts databaseBy 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 and MSI in yet 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.

Examples:

Example: Outlook Contacts - 2013 Example: Quicken - checkbook software

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.