IT 310
Database Planning and Design

Client Database Design and Implementation
using Access


Instructor: Dale R. "Zai" Fox, Ph.D.
Office Hours: 3:30 - 5:50 PM Monday, Wednesday
other hours by appointment
Text: We will take a sort of dual approach to this course. 

One branch will relate to the standard database approaches used in IT and IS courses across the country.  In this vein will rely on the text.

Hoffer, Prescott & McFadden, Modern Database Management, 8th Edition, Prentice Hall.

For the other branch of our approach we will focus on Microsoft Access to get more involved in practical design and implementation.  You should find a book that suits your purposes.  When teaching MBAs I usually recommend

Andersen, Access 2003: The Complete Reference, Osborne.

Though, you should be able to make use of an earlier version of this book if you already have it or don't have Access 2003.

As usual, you may find it unnecessary to buy any books.  Though, in this course, particularly if you expect to be doing database work in the future you may decide you want to have some appropriate books for references.

Methodology: I will take a largely case based approach at the core of this course.  I have five reasonably realistic examples I will work from throughout the semester.  These cases can be referred to as

These cases all involve fairly standard looking sorts of databases.  We will also make sure we don't have overly elaborate table structures and relationships so that a few of our examples will be toned down at least a bit from what they would be in the real world.


- 40% of your grade will be based on a final test. The test will be a mixture of multiple choice and short answer with some program debugging.

- 60% of your grade will be based on two homework assignments. All of the problems need be turned in individually.  On the other hand, you are expected to work together to develop your solutions.   Each homework assignment will be graded on a 100 point scale.

They will be due at midnight on the day they are due.

  • If you turn a homework assignment in late within a day of the due date you will lose 5 points.
  • If you turn it in late within 3 days of the due date you will lose 10 points.
  • Anything turned in later than 3 days but still within a week will result in an automatic loss of 15 points.
  • Anything more than a week late will result in an automatic loss of 25 points.
  • You are always best off getting in portions of your homework that are well done as soon as you can.  For example, problems that are turned in on time will not be subject to late penalties.

You will want to e-mail your solutions to me by the date and time they are due. Any written discussion should be placed in a Word file and attached/inserted. You will also attach/insert things such as forms and projects to your e-mail.  You also need to make sure you get an acknowledgement that I have received your email and solutions.

Any lateness due to things such as illnesses, illnesses in the family or other things need to be independently verified.  I do not necessarily need to know the details.  For example, I am quite comfortable trusting a physician's statement without knowing the specifics of an illness.  But I need some sort of reliable verification so that other students in the class do not feel that anyone is getting special treatment.

Course Objectives:
  • Understand the main uses of databases
  • Understand the advantages and disadvantages of using client databases
  • Understand what relational databases are and why they are so much more prevalent than other types
  • Understand the advantages and disadvantages of relational databases
  • Be able to work with databases that require multiple tables that have relationships between them
  • Understand the importance and uses of keys and foreign keys
  • Understand the value and importance of working normalized tables
  • Be able to develop tables, fields, forms, queries and reports in Access as they relate to specific situations



Weekly Schedule

Week 1 Some Discussion of the Assigned Readings from HPM - Hoffer, Prescott and McFadden Book - Chapter 1
PP 3 - 10
PP 13 - 18

An Example
Background on
The Desired Data and Tables
Developing the Forms
Advantages/Disadvantages of a Web versus a Client Database

Some Advantages/Disadvantages of Excel versus DBMS for Databases

Week 2 Some Discussion of the Reading from HPM
Chapter 3
PP 85 - 90 - - Business Rules
PP 90 - 93 - - Data Names and Definitions
PP 93 - 106 - - The Entity - Relationship Model

Another Example
Background on QUESBMI
A Fragmented Approach for QUESBMI Needs
A More Organized Approach for QUESBMI Needs
Some Flaws with Using a Client Based Design for QUESBMI

Homework 1

Week 3 Some Discussion of the Reading from HPM
Chapter 3
PP 106 - 123 - - Keys and Relationships
PP 123 - 129 - - Pine Valley Furniture

Revisiting an Earlier Example
Madam Curry Revisited

Another Example
Background on the Missoula Food Bank
A Flat File Approach to the Database for MFB Clients

Week 4 Some Discussion of the Reading from HPM

Chapter 5
PP 187 - 210 - - Relationships
PP 211 - 226 - - Normalization

A Bit of a Supplement on Developing Forms in Access
Dealing with a Pre-Specified Set of Options

More on the Missoula Food Bank
A Normalized Set of Tables for the MFB Client Database
Developing the Forms for the MFB Client Database

Some Flaws with Using a Client Based Design for MFB

Week 5 Queries
Queries in Access

Revisiting Madam Curry
Some Basic Queries for Madam Curry
A One-to-Many Join Query for a Form for Purchaser Orders

Revisiting Missoula Food Bank
A One-to-Many Join Query for the Form for Dependents for MFB
A One-to-Many Join Query for the Form for Past Visits to MFB

Another Example
Some Background on Emilio Francisco
Tables and Relationships for Emilio Francisco

Homework 2

Week 6 Reports
Reports in Access

Returning to Madam Curry for Reports
A Report on the Purchasers and How Much They've Spent
A Report on How Much has been Spent on Each Title/Book

Returning to Missoula Food Bank for Reports
A Summary Report on Outside Income Sources
Comparing Outside Income Based on Gender
Working with Design View on Reports

Week 7 Review for the Final
Structure for the Final

Working on Homework

Week 8 Test
Week 9  
Week 10