Having submitted your project domain proposal (and received
approval), your first step is to construct an entity-relationship
diagram for the data. As suggested in Project 1 assignment,
your entity-relationship diagram should reflect a moderate number of
entity sets and relationships -- in the 8-14 range. You should
have already been thinking about the types of queries you want to
run. In other words, what is the intended purpose of the
database? The answer to this question should guide your modeling
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 is not
required to use advanced features, such as subclassing, multiway
weak entity sets, if they are not appropriate for your application.
Project 2 Deliverable
1. Create an entity-relationship diagram for your proposed
database. As always, don't forget to underline key attributes and
include arrowheads and rounded arrows
indicating the multiplicity of relationships.
If there are weak entity sets, indicate them by double lines, as
described in class. I suggest
you use a drawing tool for this part of the assignment.
2. Use the method
for translating an E/R diagram to relations
class and the text to produce a set of relations from your E/R design.
Specify your relational schema using the notation of Section 3.1.2,
and please be sure to underline key attributes.
3. Are there any flaws in the relational database
schema you get from step 2?
Are there opportunities to combine relations without introducing
If so, indicate which, and if not, indicate there are none.
Are there examples of non-BCNF relation schemas?
If so, do you want to decompose them?
For each opportunity to combine or decompose
relations, decide whether or not to do
so, and explain your reasoning briefly (e.g., explain what queries you
expect will be typical for your database, and tell how the design you
pick facilitates them).
Is there anything
you still don't like about the schema (e.g., attribute names, relation
structure, duplicated information, etc.)? If so, modify the relational
schema to something you prefer. You will be working with this schema
a bit, so it's worth spending some time to make sure you're happy with
Also, don't forget to save a copy of your E/R diagram for
later reference as you work on subsequent parts of the PDA.
of the material for the project descriptions and assignments has been
taken from Professor Ullman's website.