Assignment 2: Step 1 of Your PDA (Personal Database Application)

Note: This assignment is a slight modification of material developed by the Stanford Database Group.

As the course progresses you will be building a nontrivial database application for a real-world scenario of your choosing. You will design schemas for the database, then using MySQL, create your database, create synthetic data, load the data, query the system, and write complete interactive programs that use the database.

Your first step is to identify what you want to use for your databases and to construct an entity-relationship design for the data. We suggest that you pick an application that you will enjoy working with, because you'll be stuck with it for the whole quarter! For example, pick something you are interested in—a hobby, material from another course, a research project, or the like. Get the most out of this part of COMP 3421.

Try to pick an application that is not trivial but also not too large. For example, when expressed in the entity-relationship model, you might want your design to have in the range of four or more entity sets and a similar number of relationships. Note that this is a ballpark figure only! You should certainly include different kinds of relationships (e.g., many-one, many-many) and different kinds of data (strings, integers, etc.), but your application need not necessarily require advanced features such as weak entity sets or roles in ER.

1. (10 points)

Write a short (approximately one paragraph) description of the database application you propose to work with throughout the course. Your description should be brief and relatively informal. If there are any unique or particularly difficult aspects of your proposed application, please point them out. Your description will be graded only on suitability and conciseness.

1. (20 points)

Specify an entity-relationship diagram for your proposed database. Don't forget to underline key attributes and specify the arity of relationships using numeric (1:1, N:1, 1:N, N:M) designations.

1. (10 points)

Describe two high-level interactions (involving two or more relations/relationships) your database should support. For each describe which relations are touched and how. For example, in the University database example in class I may want to have a WithdrawStudent function. The WithdrawStudent function will delete all currently enrolled classes for that student from the Enrolled relationship. Another example might be an AddClass function that given a student (ID, name, and age) and a course number would add a tuple to both the Student relation and the Enrolled relationship.

Don't forget to save a copy of your PDA for reference as you do Step 2 of the PDA.

What to "hand in"? Please create a document including using Word/Pages and a drawing program for the ER diagram. I recommend the following free drawing tool that works well for ER diagram creation, but you can use any tool you like:

https://www.draw.io

Please upload a single .pdf file.

If you are having trouble thinking of an application, or if you are unsure whether your proposed application is appropriate, please feel free to consult with the TA or the professor.