Objective

In this lab, the class will collectively interview me about a
database that I would like designed (to replace one I currently use
that has serious shortcomings). Each student will then individually
develop an E-R model, create the relevant tables, and produce some
sample SQL queries showing how the database tables would be used in the
application program that I will later have someone else develop. The
end result will be a report to pass on to that application developer,
explaining the database tier of the application.

Any student who would prefer an alternative project is also welcome
to build a web interface for their movies database from the previous
lab. This would leave the database systems area per se and get into
application development, but still seems like a valuable experience.
It will require more independent learning of a new tool. For more
information, see the section at the end of this assignment.

Background

A number of years ago, when I wasn't as savvy about database
systems, I rather hurriedly threw together a system for tracking
homework problems that can be repeatedly submitted until mastered.
Despite my intention that it just be a quick-and-dirty solution to my
immediate needs, I'm still using it all these years later, including
for this course. Worse yet, a number of my colleagues in the
department have gotten me to set it up for their use as well. This is
a problem because one of the many limitations in the original design
is that it has no provision for multiple instructors, each with their
own courses. As such, I've wound up making separate copies of the
database, one per instructor. This existence of multiple copies
stands as one of the impediments to my fixing other shortcomings, of
which there are plenty. Mostly, though, I just haven't taken the time
to do the thorough redesign that is really called for. So, I've
decided to ask you to do it for me. After you do the redesign of the
core database part of the system, I will get someone else to do a
similar redesign of the web interface (and email generator) that sits
on top. You will start the same way as with any other client: by
interviewing me about my needs.

E-R Model

Based on your notes from the interview session, your first and most
important task is to develop an appropriate E-R diagram. Because the
decisions made at this stage are so crucial, you would be wise to get
feedback from me on your draft diagram before progressing any
further. (For example, it was already at this stage that I made the
error of not providing for other instructors to use my system.)

Tables

Once you are satisfied that your E-R diagram correctly embodies the
design of a database system that will meet my needs, you should
translate that logical design into specific SQL statements such as
CREATE TABLE. (You may also use other statement types such as CREATE
TYPE or CREATE INDEX.) Be sure to include appropriate constraints
such as primary and foreign keys.

Sample Queries

To show the application developer how to build on top of your
database design, you should give some sample SQL queries that would be
used in the situations (use cases) that were mentioned in the initial
interview.

Lab Report

Write a report that is suitable for passing on to the application
developer. The application developer will be a technical reader, not
a layperson, and will have some understanding of SQL, though perhaps
not a whole semester's worth. Your goal is to present the design
clearly, through a textual summary of what you learned from the
interview as well as through the E-R diagram and SQL code. That way,
your design can be effectively used for the application development.
Additionally, the application developer may wind up having to do a
little tweaking of your design, as a result of coming to a more full
understanding of the application's needs. By communicating your
design's foundations, you facilitate this evolution.

Alternative: Building a Web Interface

If you would rather play the application developer role, you can
build a web interface for your movie database from the prior lab. I
would suggest you do this using Oracle's Application Express system,
which is a rapid-development environment for web-based interfaces to
databases. Unfortunately, this system has evolved rapidly enough that
the copy we have installed on our server is already a couple versions
old. As such, I would suggest you take advantage of the fact that
Oracle will let you try out Application Express on one of their own
servers. To learn more about Application Express as well as to sign
up for an account on Oracle's server, see http://apex.oracle.com/.