CPSC 304, E/R diagrams and schemas

• 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. If you think that a certain table is already in 3NF or BCNF, briefly explain why. 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: