Project 5.0

Feb 15, 2012


Design and implement the queries necessary in the "data access layer" of an application.


"I still can't believe that rhinocerous had a furry tail, I've never seen that before," says your roommate. His words drift by you almost unnoticed, since you're busy setting up your development environment.

"Just think of all the breakfast burritos I can buy with that $1000," you wonder.

"Look, I really want to win this competition," says your roomie, "so I created some simple screen specs for our first iteration. I tell you what, if you take care of the database queries, I'll take care of the beer -- er -- I mean, I'll take care of the rest."

"Great I'll take a look."

... a few hours later ...

"Hey, how's it going?" asks your roommate.

"Pretty good! I've reviewed the screens and I'm about to start creating the queries we need for our application," you say. "I think I..."

"No way, I don't believe it! What did he do?"

"What? Nothing, didn't you..." you say as you turn around to find your roomie flapping his hand at you in the 'get away' motion... while talking behind you into his cell phone.

"Did you hear about what happened to Fred? He's in jail, can you believe it?" he continues, walking away.

"If I win this contest, not only will I eat, but I can move out of this place," you think. "Better get busy!"


For this project, spend some time looking at the screens linked below. Your first responsibility is to notice what data is displayed on each screen. What queries would be necessary in order to easily display the data as presented?

Furthermore, there are two screens for creating a new wine and editing a wine. For these two screens, think of the database queries necessary for adding the captured data to your database and for updating the data in the database.

For this assignment, your only deliverable is to define all the relevant SQL queries. Use ? as placeholders for values. You should have some questions about some aspects of the screens -- be sure to ask your roomie early (he's on Piazza).

You may use multiple queries per screen if you wish (in some cases, it may be necessary).

Lastly, be sure to base your queries off of the schema in this database. Most importantly, ask yourself: How well does this schema serve the application requirements? Are some screens "hard" to generate a query for using this schema? Why?

Grading Criteria (400 pts)

Create a submission in blackboard and paste (do not attach) your queries into your submission.

This is due by midnight on Wednesday, February 22.