To Midterm Guide

IT 240 -- Midterm Exam, Part 1 (30%)

Due: Monday, May 5, 3pm

The Comptopia computer store started up business in Florida in 2003. Currently, as orders come in, they just enter them into their store spreadsheet, Comptopia.xls. The spreadsheet contains two worksheets, one for customers and their orders (CustomerOrders) and one for their products (ProductDetail).  Both the store's customer base and product line are increasing rapidly. For this reason and because they can not quickly retrieve critical information about customers, orders, and products, Comptopia has decided to hire a database expert to design and get up and running an Access database for their business.  You have been selected to do the job.

After examining the two worksheets, you realize that if a "good" database is to be designed, more than two tables will need to be created.  By "good" we mean the tables are normalized up through 3NF. After you decide on the tables and their attributes (fields), you will need to set the various field data types.  As part of the learning process, you may want to set some masks, formats, and validation rules for fields.

An important step will be the establishment of primary keys for your tables. Using data type AutoNumber might be a good idea as a primary key for most tables.  Setting primary keys will enable you to create the necessary relationships between tables. As you create the relationships, make sure to enforce referential integrity. Also, make sure all your relationships are one-to-many, so if you incur any that are many-to-many, use the bridge table technique to resolve them.

Comptopia wants to be able to get quick and reliable answers to questions such as:

I mention these queries only as examples of why one would want to create a database, you do not need to construct actual queries to answer these questions. Although, I would highly reccommend that you try many different queries to make sure that your database is functional.

Use all the data from the Excel spreadsheet to populate your tables. And use all techniques/principles that have been discussed in lecture and in chapters 2 through 5 in our textbook to design the Comptopia database. See also, 1NF to 2NF and 2NF to 3NF in this regard.

In the database that you submit, make sure that in the Tables object in Access you only include the tables you actually use in the Comptopia database. For example, don't include temporary tables that you may have used to help construct your actual tables. Also, please use good, consistent naming conventions for both your tables and your attributes.