E/R diagrams and schemas
One member from each project team must either submit the following by
using the handin directory "project-schema" (i.e., running
the command "handin cs304 project-schema", where all of your files are
in a directory called ~/cs304/project-schema")
To do so, you'll need to get
an account on the CS ugrad machines.
- A cover page
- An E/R diagram for the database the application will use. (I
suggest using Microsoft Visio or the online Gliffy to draw your ER diagram, but hand drawn
diagrams are fine. You may hand those in at the beginning of class if
you don't want to scan them in. However, make sure you retain a copy
for yourself as we will not be returning them). Your
E/R diagram should include:
- At least 5 entity sets. For each entity set:
- There will likely be a numeric attribute. This suggestion is
designed to help with the discovery of the primary key. However, you
may find that some entities do not require it.
- Identify candidate keys, and the primary key. Each entity must
have a primary key
- At least 5 relationships. For each relationship, identify
the cardinality constraint and other constraints, such as
participation constraints.
- The schema derived by translating from your ER diagram (above). For each table:
- List the table definition e.g., Table1(attr1:
domain1, attr2: domain2...)
- specify the primary key, foreign keys, and other constraints that
the table has to maintain
-
List all functional dependencies that are applicable to the table (including the ones involving the primary key).
For each functional dependency, briefly describe its meaning in English.
- List all candidate keys
- Nomalize each of your tables, if necessary, to be in 3NF or
BCNF (you don't need to give a formal proof about 3NF or BCNF.) Give
the list of tables, their primary keys, and foreign keys after normalization.
- The SQL DDL to create all the tables in SQL. All primary keys and
foreign keys must be declared appropriately.
- Populate each table with at least 5 tuples. Show the instance of
each relation after inserting the tuples.
Note:
The requirements stated herein, are not expected to be complete. As
you start analyzing these requirements you may notice that certain
details are missing. In this case, you may make any reasonable
assumptions about them, but if there is any uncertainty about some
requirements you should post an inquiry on the bulletin board. The
TAs and the instructor will try to respond to these requests as soon
as possible.