CS 564: Project Stage 1

Deadline: Monday Sep 24, 2:30pm, submit to AnHai at the start of the lecture

What to Do?

For this stage, you can work by yourself or within a team of 2-3 persons. You or your project team will do the following:

1) Identify an application that needs to use a database. For example, suppose you want to build an application to keep track of the videos in a video rental business, the customers of the business, and their rentals. This application will need to use a database to store the various pieces of the above information. Other examples include a book selling application (e.g., amazon.com), a real estate application that lists houses for sale (e.g., realestate.com), an application that allows students to enroll in courses, etc.

2) Design an ER diagram for this application. The ER diagram should capture all entities and relationships you want to eventually store in your relational database. This ER diagram should contain at least

Try to write down as many domain constraints as possible that you think will be applicable to your application. Clearly identify all keys of all entity sets, as well as the primary keys of the entity sets.

A question we often got is that how complex this ER diagram should be. There is no hard rule (it depends after all on your application). But roughly, you should aim for about 4-6 entity sets and 4-8 relationships. Fewer than this, and your application may be too trivial. More than this, and it may be hard for you to manage in the context of a course project.

3) Translate the above ER diagram to a relational schema, using the various translation rules discussed in the class. When translating an is-a hierarchy, recall that you can translate it in at least three different ways. Decide on a way to translate and explain why you select that way.

Note that when you do the translation, it is okay to write a table schema like this: Students(sid, name, age, gpa). You don't have to write the full SQL query, such as CREATE TABLE STUDENTS ... (You will do that in Project Stage 2.)

What to Submit

On the first piece of paper please describe (in plain English) your application (what is it supposed to do?). Describe the kinds of entities and relationships that your application will manipulate, and the kinds of queries that you think your applications will most often execute.

Write down the ER diagram on a second piece of paper. (You are not supposed to use any computer program that allows you to draw an ER diagram, then automatically translates that ER diagram to relational schema.) Write down the relational schema on a third piece of paper. Then submit all three.

How and When to Submit

Please submit the hard copy (that is, the above three pieces of paper) at the start of the lecture on Thursday, Sept 24. Please put them on the desk in the front of the class, used by AnHai.

On the first page of the submission, please clearly write the full names, student IDs, and login names of all members in the team (or of yourself if you work alone). Would be great if you have a name for the team, so that we can use it to identify your team.

Grading

We will evaluate your work based on conceptual cleanness, how well you adhere to good practice of ER design (as discussed in the class), how well you translate ER diagram into relational schema, and how well you present both the ER diagram and relational schema.