Assignment 4 - Databases Part I
Due Mon, Oct. 10, 2005
- To be able to categorize attributes into a multi-table database
- To understand and use keys and relationships
- To interpret and write different types of queries
- To add images to your web page
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.
- Customer first name
- Customer last name
- Album name
- Genre of album (Hip-hop, Pop, Country, etc.)
- Artist (or band) name
- Zip code of customer
- Date of purchase
- Enhanced media? (can album be placed into a computer's CD/DVD drive
to access extra content?)
- Store branch that album was purchased from
- Number of tracks of album
- 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.
- List all Gopher Buckets albums.
- Show all album-related information for those albums with more than 10 tracks.
- List albums that were purchased on sale from our Downtown branch.
- List all Country and Hip-hop albums along with any Enhanced media albums
(of any genre).
- Show all album-related information for any artist with "Jackson" in
his/her name (Janet Jackson, Jackson Brown, etc.)
- List customers who made purchases in the month of December last year.
- On what dates were pop albums bought by customers from the 12308 zip code?
- Given a user-specified artist, show all info regarding purchases of that
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.
I encourage you to talk to others about the
general nature of the homework and ideas about how to pursue it.
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