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.
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.
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.)
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.
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.
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.
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/.