IT360 Lab 2: Data Modeling
DUE: January 28, 2010, before 2359
In this lab you will develop your data modeling skills by translating system requirements and descriptions into Entity-Relationship data models using Information Engineering (crow’s foot) notation.
ER Model Quick Review
Entities
• Something that can be identified and the users want to track
• Described by attributes
• Some attributes are identifiers: attributes that identify entity instances
Relationships
•
Non-identifying relationships
- Non-identifying relationships are 1:1 or 1:N relationships between strong entities or between one strong and one weak, non-id-dependent entity.
- A dashed relationship line is used for non-identifying relationships.
- An oval appears next to an optional entity (minimum cardinality 0).
•
Identifying relationships
- The identifier of the parent is always part of the child’s identifier. The child is id-dependent on the parent.
- A solid relationship is used for identifying relationships.
- An oval appears next to an optional entity (minimum cardinality 0).
•
Many-to-Many Relationships
- N:M relationships.
- A solid relationship line is used. There is no way to set minimum cardinalities in ERWin, so you need to write in a text box
•
IS-A Relationships
- Relationships between a Generic Entity (Supertype) and Category Entities (Subtypes).
- If subtypes are exclusive, one supertype relates to at most one subtype.
- If subtypes are inclusive, one supertype can relate to one or more subtypes.
- Discriminators are attributes of the generic entity that indicates the type of category an instance belongs to.
•
Relationship Names
- Relationship names normally consist of a verb or phrase expressed from the standpoint of the parent, followed by a slash, and followed by a verb phrase expressed from the standpoint of the child.
The Modeling Tool - ERWin
We will use ERWin as the database modeling tool. To start creating the ER model, follow these steps:
You might have to check the last three steps every time you start ERWin. If the settings are not correct, change them!
The “shapes” available for use (entities, relationships) are in the toolbar. You draw in the worksheet pane by first selecting the desired “shape” from the toolbar. You can then specify the properties for the “shape” by double-clicking the shape in the worksheet pane.
Experiment with the interface to get accustomed with creating entities, relationships, cardinalities, and verb phrases. I will do a brief ERWin overview in class.
Now you should be ready to do some modeling. Do not forget to save your models on your X drives.
Part 2: Exercises
Create the Entity-Relationship model for the following situations. Show the entities, their attributes and identifiers (called primary key in ERWin), the relationships and the appropriate cardinalities (minimum and maximum) for all relationships. Provide an appropriate verb phrase for each relationship. Write down any assumptions you make.
Exercise 1: The Commodore of the Yard Patrol Squadron “volunteered” you to create a database to help manage squadron information. The following is your view of YP Squadron operations:
Exercise 2: Consider the traffic citation
shown below. The rounded corners on this form provide graphical hints about the
boundaries of the entities represented.
Create the ER model. For each entity, use the data items on the form to specify identifiers and attributes for that entity. For each relationship, specify a verb phrase, and the cardinalities.
Exercise
3: By the end of this course, you will create a website with a
database backend for an Online Store (you chose the theme). For this particular
exercise you are both the end user (“the client”) of a database and the
developer. You have to specify the requirements and create the ER model for the
database behind your Online Store. Since you will use this database the entire
semester, it is very important that
you create a good design.
The current system uses multiple excel spreadsheets which results in duplicated data (error prone!) and no easy way to query information across the several different spreadsheets. As an “IT Guru” you see that a database system is the correct tool to track the information. Below is a brief description of information that the database must be able to manage (again, you flush out the details about appropriate information that should be stored in the database):
The requirements for your online store should be complex enough to require:
- At least three entities, each with at least three attributes
- At least one many-to-many relationship or two one-to-many identifying relationships
- At least one one-to-many non-identifying relationship
- At least one super-type/subtype relationship
3.a) Write down detailed requirements for the Online Store. These requirements should be detailed enough so anyone can infer entities, attributes, relationships and cardinalities, based solely on your requirements.
3.b) Create the ER diagram that models the requirements you specified. Your entities should include both the identifier(s) and other attributes that you deem important and appropriate. All relationships should include cardinality information and a verb phrase describing the relationship.
Turn in:
BOTH Electronic and paper copies are required.
Electronic (due
before 2359 on January 28, 2010):
Copy – paste the data-model you created for each exercise, and the requirements you wrote for exercise 3 in a Word document called yourlastname_lab2.doc. Upload the Word document containing all your answers to Lab 2 assignment on Blackboard.
Hard-copies(due
before start of lab on January 29, 2010):