Working with Databases
Did you want: Working with Databases: Access 2007, 2010, 2013, 2016
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.
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.
Field = one item of data
Record = a set of fields that belong
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.
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.
Filter = a rule that hides the records that do not match the criteria in the rule.
Query = a method of viewing, changing, combining, or analyzing data.
Index = a table of the key values in your table.
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.
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
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.
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