1105 shackleton road apex nc 27502

Computer Assignment!

Summer 2013

Project 3. Design and Implement a Database for The Trailhead using Access 2010

Worth: 81 points

Due: Tuesday, June 25 th

@ 11pm

Read all descriptions and instructions carefully and pay attention to detail.

Imagine that you are newly hired at The Trailhead store (http://www.trailheadmontana.net/) in downtown

Missoula to design and implement a new database system. First become familiar with this store, the gear and

products they sell, and the services they provide (like rentals). You will design and implement part of a database

for this single store only, and are not to consider any potential branch locations.

Follow each of the steps outlined below to complete this project:

1. Design your database (30 pts). Before you implement a database, it’s critical to think through the design thoroughly first. The first step of your assignment does not involve using Access.

On paper, either hand-drawn or using a software program like Word, do the following:

a. Identify and show at least 5 tables that would be relevant for your database. Note that I don’t want

you to use any tables that maintain Customer data, like name, address, etc.

b. For each of the tables you list in step 1a, show the data that each will contain. You should have

somewhere between 4-8 fields per table. Remember to store data in its smallest parts as described in

your text.

c. For each piece of data in all of your tables, choose a meaningful name to describe the data. List the

appropriate data type for each field of data, and be sure to incorporate a good sampling of different

data types throughout.

d. Establish and identify the primary key of each table. Establish and identify foreign keys in tables,

and show via links what tables they connect (e.g., see Figure 2.2 in your text).

e. Based on your design thus far, come up with 5 different queries that users of your database would

likely want to run. At least two of these queries should involve multiple tables (the other three can

use one table only). Give each of these queries a short but meaningful name, and write them out in

your design document. Note which tables are used in each query.

For example, a possible query that the Trailhead store manager might want to know is: “How much

profit did my store make from kayak rentals in the month of June?” A good name for this query

might be June profit. Note that this query is just an example and may not be supported by the tables

you chose to design. You have to determine what does make sense for your database.

f. Save your design document, put your name and a Heading on it, scan it in if necessary, and call the

file p3trailhead_design_LastnameFirstname

2. Implement your database (25 pts). Using your database design from Step 1 and Access 2010, implement your database by doing the following:

a. Create a new database in Access 2010 and name it p3trailhead_LastnameFirstname

b. Create tables based on your design document. Include all field names, data types, primary keys and

foreign keys.

c. Create relationships between tables based on your primary and foreign keys as shown in your design

document. Make sure to enforce referential integrity when you create these relationships, and choose

Cascade Update for related fields.

d. Enter sample data into each of your tables. There should be 5-10 rows of data per table. Do not use

any real names or other potentially sensitive data in your database.

3. Create queries (20 pts).

a. In your Access database, create each of the 5 queries you indentified in Step 1 of this project. Run

them and save them using meaningful/descriptive names. Remember, at least two of these queries

should be multi-table queries.

4. Finalize and Submit (6 pts). Compact and repair the database. Make a backup copy. Submit both the design document and your database to finish this project.

If you have questions, email me at reimer@cs.umt.edu