CSc 050
Assignment 4 - Databases Part I
Due Mon, Oct. 10, 2005


In this assignment, you will create a small database recording customer purchases in your fictitious music store. Using Microsoft Access, you will create tables, input data, and then run queries on that data to obtain various types of information.

Part 1: Creating tables (10 pts.)
Create a database that contains the following fields. It is your job to determine how many tables you need and which fields belong together in which table. Remember to keep the "single-themed table" mentality. Assign each table a primary key. You may add Autonumber fields to serve as keys if you need them, but don't overuse them. (You'll lose points for unnecessary Autonumber fields.)

You should use lookup fields and input masks where appropriate. Feel free to shorten the names of the fields as long as you keep them meaningful.

Field list:

  1. Customer first name
  2. Customer last name
  3. Album name
  4. Genre of album (Hip-hop, Pop, Country, etc.)
  5. Artist (or band) name
  6. Zip code of customer
  7. Date of purchase
  8. Enhanced media? (can album be placed into a computer's CD/DVD drive to access extra content?)
  9. Store branch that album was purchased from
  10. Number of tracks of album
  11. Did customer purchase the album on sale?

After creating the tables, form Relationships between them by joining together appropriate fields.

Part 2: Data entry (2 pts.)
Once the tables are finished, enter made-up data into them. Each table should have at least ten (10) records. Resist the temptation to type in a unique value for every field. Make it realistic. For example, it's entirely possible for the same person to make multiple purchases. Your data should reflect this and other such real-world possibilities.

Part 3: Queries (8 pts., 1 each)
Write the following queries in Access. Each should retrieve the requested information using a single query. You may need to add more records to your tables in order to adequately test that your queries run correctly. Save each query you write.

  1. List all Gopher Buckets albums.
  2. Show all album-related information for those albums with more than 10 tracks.
  3. List albums that were purchased on sale from our Downtown branch.
  4. List all Country and Hip-hop albums along with any Enhanced media albums (of any genre).
  5. Show all album-related information for any artist with "Jackson" in his/her name (Janet Jackson, Jackson Brown, etc.)
  6. List customers who made purchases in the month of December last year.
  7. On what dates were pop albums bought by customers from the 12308 zip code?
  8. Given a user-specified artist, show all info regarding purchases of that artist's work.

HTML: images (2 pts.)

Add at least two images to your web page. The pictures should be in either .JPG or .GIF format, and they should be relevant to the content of the page.

What to turn in

Turn in electronic copies of your HTML file, the included picture files, and your database on Blackboard. As before, compress everything into a zip file before uploading, and name the zip file with YOUR NAME. Print all of your database tables and turn them in on paper.

Administrative statement

Homeworks are individual projects. I encourage you to talk to others about the general nature of the homework and ideas about how to pursue it. However, the technical work, the writing, and the inspiration behind these must be substantially your own. If any person besides you contributes in any way to the project, you must credit their work on your homework. Similarly, if you include information that you have gleaned from other published sources, you must cite them as references. Looking at, and/or copying, other people's homework or written work is inappropriate and will be considered cheating.
Back to Homework Index