logo
 
Assignment - ER Modeling
CS 5800 - Database Systems
Utah State University
Home
Calendar
Homework   
Lectures
Syllabus
Resources
People

Due Date: Tuesday, Oct. 10 (23:59), late turnins will have 20% deducted.
Groups List: The current list of who is in what group.
Grading sheet: The grading sheet that we will use is available.
Turnin: Turnin in the assignment using canvas.

Overview

In this assignment you will design a database. The design will consist of a conceptual model (e.g., an ER model) and a schema (or physical model). To do the design you may use any ER design tool. The has links to several tools. MySQL Workbench is perhaps the easiest to use free, open source tool.

Grading

The single most important part of a database design is documentation. Document everything in your model. This includes the following.
  • A description of your miniworld
  • A detailed description of each entity type, including justification of keys, and sufficiency of attributes
  • A detailed description of each relationship type, including a justification of participation and cardinality constraints

Provide complete, clear, concise, and relevant documentation.

Groups

The assignment permits you to work in groups of at most two. Please note that the assignment specifies separate tasks for each group member (ALPHA and BETA), as well as some common tasks for the entire group. The group members will be graded separately. A person may choose to do the assignment individually, in which case they must perform the tasks for both group members. In other words, forming a group will usually result in less work, but you may choose to do the assignment individually if you so desire, in which case you do the work for both ALPHA and BETA. To register your group, please e-mail to me your group members. Before you register it would be a good idea to decide which person will be ALPHA and which will be BETA. Once a group is formed, there will be no changes to the group for any reason. Ideally, each group will be happy and harmonious, but the assignment is structured such that the contribution of each group member can be assessed independently.

Database Modeling

The assignment is to build an Entity-Relationship model within one of the following areas.
  • Field biology - Design a database to provide support for the study of an animal species in the field. The database should do tracking, habitat representation, etc.
  • Sports - Choose a sports-related mini-world, such as a tournament, event, or le ague.
  • City planning - Sewage, public transportation, and law enforcement are just thr ee of the many data-intensive activities that city governments undertake.
  • Art - Choose a field of art, or a museum collection.
  • Space mission - Design a database for the space shuttle or Apollo programs.
The areas are incompletely specified, the group is to choose a direction, research the area, and supply the necessary details. You may extend the description to incorporate "reasonable" components that satisfy the various required deliverables, which are listed below. For example, ALPHA needs a many-one relationship type with total participation on the one side. The idea is that each group will tailor the model differently. Collusion between groups is not permitted.

The ER model that the group develops must have five or more entity types, and generally it should not have more than twenty. Make suitable assumptions where necessary and add suitable attributes wherever appropriate.

Deliverables and Submission Instructions

Create a directory called model that contains the following file (the file extensions may vary depending upon the product that you use.)
    er.doc or er.pdf or er.ps - A Word document, Postscript, or PDF file with the following sections.
  1. Title page: Title, ALPHA: Name, BETA: Name
  2. ER Diagram - Cut and paste the ER or UML diagram from whatever tool that you decide to use.
  3. Overview - An overview of the entire model (from one to four paragraphs) presenting the model, design decisions, and general assumptions. This is a joint contribution.
  4. List of entity types - Description, attributes, keys, and constraints should be discussed for each entity type. Discuss design decisions for keys.
  5. List of relationship types - Description, attributes, and constraints should be discussed for each relationship type. Discuss all design decisions for participation constraints.
  6. ALPHA - Group member ALPHA's contributions (see below for structure, each deliverable should be addressed). List and discuss how each deliverable is modeled.
  7. BETA - Group member BETA's contributions (see below for structure, each deliverable should be addressed). List and discuss how each deliverable is modeled.
  8. Physical Schema - The physical schema that results from mapping the ER diagram to relations, including any assumptions in the mapping. The Schema can be a list of create table statements. You may include a screen dump of your physical schema if your tool supports it.
To get a screen dump, e.g., of an ER diagram in Windows, use ALT-PRINT SCREEN which copies the screen to your clipboard (it can then be pasted in, for example, Microsoft Word). Alternatively some of the tools can produce a "report" of the diagram in one or more formats that can be included in a design document.

Zip or tar and gzip the directory to create the file model.zip or model.tar.gz. Turnin in the assignment using canvas. You may turnin your assignment as many times as you like.

ALPHA and BETA may develop each part of the model together, but will be graded (for the most part) independently. The group will combine their results into a single integrated whole.

The following should be accounted for in the documentation associated with your model.

  • A key should be clearly identified and discussed for each entity type.
  • Constraints should be identified.
  • Constraints relevant to maintaining data integrity should be included and de scribed in the documentation.
Please ensure that your ER diagram is clear and aesthetically pleasing.

ALPHA's Contribution

ALPHA's contribution is a section identifying the relationship type, entity type, constraint, and/or attribute involved in the modeling the following. Important: There will likely not be a specific feature in the design tool to model each kind of thing, so you will have to figure out how to do it in the model by reasoning about the table(s) that are produced. Clearly identify in the document how each kind of thing is modeled in your diagram.
  • A many-one relationship type with total participation on the one side
  • A many-many relationship type
  • A ternary relationship
  • A disjoint inheritance hierarchy
  • A composite attribute
  • A domain constraint
  • A multivalued attribute

BETA's Contribution

BETA's contribution is a section identifying the relationship type, entity type, constraint, and/or attribute involved in the modeling the following. Important: There will likely not be a specific feature in the design tool to model each kind of thing, so you will have to figure out how to do it in the model by reasoning about the table(s) that are produced. Clearly identify in the document how each kind of thing is modeled in your diagram.
  • A one-one relationship type with total participation on at least one side
  • A one-many relationship type with partial participation on both sides
  • A relationship with an attribute(s)
  • An overlapping inheritance hierarchy
  • A weak-entity type
  • A reflexive relationship type
  • A check (integrity constraint on an attribute).

                                                                                                                                                                                                                                                                                                                                             
  Copyright © 2017 by Curtis Dyreson. All rights reserved.