Jan's Illustrated Computer Literacy 101 Logo:Jegsworks Jan's Illustrated Computer Literacy 101
Home > Jan's CompLit 101 > Computer Basics > Applications > Database
Icon: Arrow - Previous pagePrevious    NextIcon: Arrow - Next page

Jan's Computer Basics:

Applications: Database

DatabaseA database is a collection of data that you want to manage, rearrange, and add to later. It is a good program to use to manage lists that are not entirely numbers, such as addresses and phone numbers, inventories, and membership rosters. With a database you could sort the data by name or city or postal code or by any individual item of information recorded. You can create forms to enter or update or just display the data. You can create reports that show just the data you are interested in, like members who owe dues.

Both spreadsheets and databases can be used to handle much the same information, but each is optimized to handle a different type most efficiently. The larger the number of records, the more important the differences are.

Examples of databases: MS Access, dBase, FoxPro, Paradox, Approach, Oracle, Open Office Base.

Purpose:
  • Managing data
     
Major Advantage:
  • Can change way data is sorted and displayed
  • Can easily sort and filter records

Features/Terms:

A flat database contains files which contain records which contain fields
A database is made up of files A file is made up of records A record is made up of fields.

A relational database contains tables which are linked together.
Each table contains records which contain fields.
A query can filter your records to show just the ones that meet certain criteria or to arrange them in a particular order.

Relational database tables linked. (Access 2010)

Tables in a relational database (Access)

Types of databases

Flat database:

The earliest and simplest databases are flat databases.  A flat database may still be all you need for your purpose.

Advantages: 
  • Easy to set up
  • Easy to understand
     
Disadvantages:   
  • May require entering the same information in many records.
  • A text database is hard to read.
  • A single record in a spreadsheet database may not fit across the screen.

A text database is a plain text file where the fields are separated by a particular character like a vertical bar | or a comma , or a semi-colon ; . The example below puts a vertical bar between each field. The first record shows the names of the fields.

A text database is hard to read in this raw form. A database program can show each record separately in a more readable display. It is hard to create a report that contains just the parts you want to see.

Text database

A spreadsheet can work as a flat database. Each field is in a separate column and each row is a single record.  The example below shows how quickly a record gets wider than the screen. You cannot use two rows for one record.

Spreadsheet as a database (Excel 2010)

Relational database:

Microsoft Access and Oracle and other relational databases are more advanced and more efficient. This kind of database uses a set of tables which are linked together. Using a well-designed relational database can greatly reduce the amount of data you must enter each time you add a record. For large numbers of records, a relational database can search through the records faster.

Advantages:
  • Reduces the duplication in data entry.
  • Faster searches.
  • Can create forms and reports that display only the data you want to see.
  • Can create queries to answer questions that are hard or impossible to answer in flat databases.
     
Disadvantages:
  • Can be complex to set up, using many tables.
  • It is harder to understand how all the parts relate to each other.

Below is an illustration of the tables in a small database that records information about insurance agents and policy holders. This particular insurance office works with several different insurance companies. There are 7 tables in the database. The lines show which fields in each table are the same. These link the tables together.

Relational database tables linked. (Access 2010)

Each agent can be licensed with several different companies and in several different states. In a flat database you would have to have a record for each agent for every company and state he is licensed with, repeating the agent info for each record. If the agent moved to a new home, you would have many records to update with his new address.  Argh! Too much typing!! With a relational database, there will be only one place to update.

You can design a form so that you can do this all in one spot! This is much easier than repeating so much information for each record as you would do in a flat database. The images below show the two screens for the Agent Info Form. This form can be used for entering new agents as well as for displaying the current ones.

Data entry form for agents (Access 2010) Data entry form for agents - subform for company and licensing info (Access 2010)

Queries

You often want to look at just part of the data in a database. You can reorder or filter your data using Structural Query Language (SQL). You might want a list of people who have a particular postal code, for example. Happily there are visual methods you can use to create your query, like the example below from MS Access. You can drag the fields from the list at the top and drop them in the columns at the bottom. You can add sorting orders or criteria, like picking a particular postal code. The query below brings together the fields from the Agent Info table that are needed for mailing labels.

Query: Design view (Access2010)

The actual SQL code for this query looks a lot different from the Design view above! No wonder a drag-and-drop method was created!

Query: SQL view (Access 2010)

For a full set of lessons on databases using MS Access, go to: Working with Databases Icon: Change Web