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!
Databases can be organized into types based on:
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.
Field = one item of data
Example: the last name or the city in an address book.
Field name = value, like LASTNAME = "Washington".
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
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.
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.
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.
Some forms do not show records but instead offer action buttons that perform tasks or open forms and reports.
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!
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,
Example: Sort prices from highest to lowest.
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.
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.
Sometimes 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!
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.
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
The 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 ) 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.
- 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
- More complex
- More difficult to set up initially
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.