Database
EDTC645 Data Management Assignment
Due Dates / Points
Due: October 3
Points: 25 total: paper- 5; Database file- 15; Merge/Integration- 5
Description
You will have a choice of three applications to complete this assignment with: AppleWorks, FileMaker Pro, or Microsoft Access. Each of these applications uses a different file format, so when you download the sample database file make sure you select the correct format for the program you are using. FileMaker Pro is the preferred data base for this assignment as students in the past have had much more success learning this as their first data base. FileMaker Pro is a powerful relational Database and is available for both Mac & PC as a 30 day free trial package from FileMaker.
FileMaker also has a function that converts Excel SS files into a database file; if you copy your assignment 1 Excel gradebook file and reformat it so the top row contains the titles of your data entries (name, Quiz 1, Assignment 1, etc.), save it as a 2003 format file, and then drop that on the FileMaker icon it will convert it to a database file: it will use the top row as the field names and all of your student data will already be in place. You will need to enter new formulas for the calculated fields as they will be transformed only as the final values. Use this as your main file and then develop the required extra fields, layouts, etc.
You may use MS Access (PC only) if you already have it and wish to learn to use it (Access comes as part of advanced versions of MS Office, the 2007 version is reported to be much more manageable than the 2003 version).
Database files
Download the grade management database file for the utility that you intend
to use:
AppleWorks, FileMaker Macintosh,
FileMaker PC, or MS
Access. These files have been compressed and need to be expanded. The
recommended utility for this is Aladdin Expander; links to a free download
are located on the Application Help page.
As an alternative, you can use these links to obtain a zipped version of these PC files: FileMaker PC, or MS Access. Use a zip utility to expand these files.
Database/Spreadsheet Comparison Paper
Open the file you have selected and explore its functions and contents; write a one or two page paper comparing the general format and functions of database utilities with those of spreadsheets (remember, there are many database and spread sheet utilities in use), you can also focus on the database and spreadsheet files that you are working on. Include the methods of component creation and the functionality (display, calculations, data manipulation, etc.) of each type of file. Be sure to format the file much like the spreadsheet paper, use graphics & examples where appropriate.
Design a Database
Design a database file for similar data management which includes student/trainee performance in your interest area. The database must contain the following features:
Database Requirements- Common to All Database Applications
- standard personal fields (name, address, etc.)
- at least 5 data entry fields
- at least 2 calculated fields
- any necessary fields to support the individualization in the merge document (pronouns, relationship, etc. see below)
In addition, you must create additional control and access features depending on the database application you are using. Only create the features described for the application you are using; do not try to implement all three lists. (Note that these numbers are minimums; if you would like to add more features beyond the minimum, please feel free to do so.)
Database-Specific Requirements
AppleWorks Control/Access Feature Requirements
- two layouts
- two saved sorts
- two saved finds
- one saved report
FileMaker Pro Control/Access Feature Requirements
- two properly named layouts
- two buttons with saved sorts
- two buttons with saved finds
Microsoft Access Control/Access Feature Requirements
- one table
- at least one query
- two forms for individual data reporting, one with a sort and one with a find
- one report for printing group data
Using the components you created, create printouts for each of the following:
Insert text and dynamic times and dates in the headers of all files so that printouts contain information clearly describing the content and when it was printed.
- Print the complete data set (all data fields). This will be all of your data for each record (person) in the database. Arrange this information (using layouts/forms) to print multiple records on each page, where possible. Avoid layouts that spread an individual's data over more than one page.
- Complete data set, one criterion subset. This will be all of the data for specific records (persons) chosen using the find/filter functions, for example, only those with less than a 70% average. Indicate the search criterion in the header of the printout.
- Complete data set, two criteria subset. This will again be the data for a specific group of records (persons) chosen using a find/filter. However, this time the search should include two criteria, such as those with no extra point scores and attendance problems. Indicate the criteria in the header of the printout.
Merging
Develop a word processing document (a merge layout in FileMaker pro) which would involve the integration of the data from your database in some meaningful way to produce a series of documents; e.g. individualized strength/weakness reports, study guides. Not just a simple letter to the parents (or to the student in a training setting), this should use formatting features such as tables, columns, etc. Describe the intent of your merge on the cover sheet for that part of the report. If you're using AppleWorks, use the AppleWorks word processor module to prepare the document to merge with your database. If you're using Access, you'll need to prepare a Microsoft Word document to merge your data into. However, FileMaker Pro 7 has a special format for creating merge documents directly in the database which is a very efficient alternative to using the word processor file and it should be used.
Merge Enhancements:
In order to fully personalize the documents created you will need to create fields that contain extra information about the individual; e.g. gender, special group placement, employment category, etc. These are then used to alter the content of the document created for each; special fields in the database can be used for this as illustrated below. Also, in MS Word, you can create special fields that respond to the content of the database fields and alter the content accordingly. See examples below:
The data merge function is intended for the production of large numbers of customized printouts by merging the data from one or more databases into a word processing document. The intent is to produce a customized report of all the relevant data for each of the students (one printout per student). However, the assignment doesn't need to produce that many printouts to illustrate the process and your intent. So, do a Find for Filter in the database that limits the visible records to two so that only two printouts are produced during the merge. Also, print out the word processing document in the standard print mode to produce a copy which shows the fields to be merged.
Turn-in / Final Materials
Distance students should mail their printouts via regular mail. On-campus students can bring them to my office in an envelope indicating your name and the assignment name. Indicate in an e-mail to me when the assignment was mailed; your e-mail subject should be DB assignment.
Print
a cover sheet and the comparison
paper described above.
Database
file: the following items for the
database you designed:
- total data set
- subset selected (not sorted) by one criterion
- subset selected by two criteria
- Merge Documents: print the following
items from your data merge:
- word processor file showing the fields which will be merged, and
- two of the resulting merged printouts (don't print all the records; use selection criteria to limit the records that are printed)
- Upload a copy of your database file named in the form of DBLastName using the Assignment Upload Page.
- Upload a copy of your word processor merge file named in the form of DBMergeLastName using the Assignment Upload Page.
Participate
in the EDTC645 online forum adding questions and/or
responses to the section on databases.
Related Resources
Interactive Illustrations
AppleWorks
Access
FileMaker Pro
Objectives
The following objectives should apply to any modern database utility. This course will utilize MS Access, FileMaker Pro or AppleWorks database. Many operations will be similar between these utilities, however, there are significant differences in the developmental procedures and operation of these two resources. In addition, the location of the menu items, icon location and function names differ significantly. Skills learned on one application should transfer to utilizing the other if you concentrate on learning the functional components of database utilization rather than memorizing keystrokes and operational steps.
After completing this section, you should be able to (in AppleWorks, FileMaker Pro or Access):
- Understand the organization of the database menu bar and the functions of the items in each category.
- Identify and utilize the database's graphical tools and icons.
- Understand the applications of databases for increasing personal productivity.
- Enter and format data and text in a database file to produce any standard arrangement or page layout function.
- Use a database to accomplish daily productivity functions.
Basic Database Operations
You will know how to accomplish the following functions:
File Level (AppleWorks, FileMaker Pro, and Access)
- Create a New file; standard and from a stationery(template) file
- Open an existing file; from the desk top and from within the database Utility.
- Save changes in an active file; update current file, create a back up file on same or different storage device.
- Save an existing file as a new file; from the desk top. From within the database (Only AppleWorks).
- Translation of files to and from universal formats (ASCII/TEXT, DIF, DBF).
- Translation of files to and from other database formats (This may be restricted more to moving the contents to another program rather than actually translating the file).
- Integrate data file with word processing document for functional data merge.
Data/Layout Level (AppleWorks, FileMaker Pro)
- Create appropriate data fields for text, numeric, date, time, and calculated data.
- Create preset data fields: Pop up, Radio Button, Check Box
- Alternate data display controls:
- Create multiple layouts for alternate data display options.
- Selected fields, spatial arrangement, font, size, color, graphics (create and clip-art).
- Use Search/Find and Sort to interpret the data.
- Create alternate Reports for presentation of the data.
- Enter/delete/move text or numerical data.
- Add a footer/header
- Add text to a header or footer
- Add a labeled dynamic page number to a header or footer
- Add a dynamic date to a header or footer
- Add a fixed date to a header or footer
- Set the preferences of your personal database utility.
Data/Layout Level (Access)
- Create appropriate data table and fields for text, numeric, date, and time, calculated data.
- Create appropriate data queries for calculating data and searching data.
- Create multiple Forms and Reports for presentation of the data
- Selected fields, spatial arrangement, font, size, color, graphics (create and clip-art).
- Incorporate Search/Filter and Sort to interpret the data.
- Create preset data fields: List Box, Option Button, Check Box
- Enter/delete/move text or numerical data.
- Add a footer/header
- Add text to a header or footer
- Add a labeled dynamic page number to a header or footer
- Add a dynamic date to a header or footer
- Add a fixed date to a header or footer
- Set the preferences of your personal database utility.
Basic Document Formatting (AppleWorks, FileMaker Pro, Access)
- Number Formatting and Precision.
- Lock/Unlock fields
- Display and edit the tool palette
Functions for Formulas
- Count, Count2 [CountA, Countif]
- Average
- Choose
- IF
- IsBlank
- Match
- Max, Min
- Round
- Stdev
- Sum