IT360 Lab 5: Data Modeling

DUE: February 15, 2013 BEFORE start of lab (only paper submission required)

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 (the identifiers for the two entities are independent of each other).

- 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 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.

- In ERWin, 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.

Optional : The Modeling Tool - ERWin

You can use pen and paper to draw the ER diagrams, or you can use any of the software tools available for this. ERWin from Computer Associates is installed on the lab computers and you can use it as the database modeling tool. To start creating the ER model, follow these steps:

1. Start - > All Programs - > CA (ComputerAssociates) - > AllFusion - > ERWinDataModeler r8 - > ERWin Data Modeler
2. From File menu - > New - > Create Model – Select Template - > check Logical - > OK
3. From Model menu - > Model Properties - > General tab - > Notation -> select Information Engineering option from drop-down menu - > Close
4. From Diagram menu - > Diagrams… - > Entity tab - > un-check Foreign Key Designator
5. From Diagram menu - > Diagrams… - > Entity tab - > un-check Show Migrated Attributes
6. From Diagram menu - > Diagrams… - > Relationship tab - > check Display Logical Relationship Name

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.

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:

• The squadron has 9 boats but is expanding the number of crafts soon. Boats are tracked by hull number, which is unique to each boat.
• The squadron has boats of several models, and each model is identified by a model name. For each model, the length, beam, draft, displacement, cruising range, max speed, and maximum occupancy are recorded.
• The squadron has many members (all midshipmen), and keeps track of name, alpha, email, phone number, and class year, for each member.
• Some of the members of the squadron have command positions. For each such member, the position title is recorded.
• For each boat, the squadron has one (and only one) crew or “pennant” that uses that boat, and only that boat. Each crew is identified by a number, and has a name, and a motto.
• Each squadron member is part of one and only one crew. Each crew has at least one member, and hopefully, more than one.
• A squadron member might have been part of several different crews, over the time. Past associations are recorded.
• Cruises done by the YP Squadron crews are tracked. Cruise data includes start date and time, return date and time, destination, boat, and crew executing the cruise.

Exercise 2: By the end of this course, you will create a website with a database backend for an Online Store (think Amazon, but you choose 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 for several labs, it is very important that you create a good design.

Below is a brief description of information that the database must be able to manage. You need to flush out the details about appropriate information that should be stored in the database.

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.:

• The store keeps track of employee information, including their position in the organization. Your system should eventually allow employees to login (each employee will have a password) in order to make changes to the database, or record sales information.
• The store keeps track of all the products it sells.
• The store keeps track of sales information.

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

2.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.

2.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.

Exercise 3: 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 diagram that models this data. 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 4: SQL

Given the following tables:

ITEM(ItemID, Description, PurchaseDate, Store, City, Quantity, LocalCurrencyAmt, ExchangeRate)

SHIPMENT(ShipmentID, ShipperName, ShipperInvoiceNumber, DepartureDate, ArrivalDate, InsuredValue)

SHIPMENT_ITEM(ShipmentID, ShipmentItemNb, ItemID, Value)

Write the SQL query to achieve the following: For each ShipperName, display the ShipperName and total Value (from the SHIPMENT_ITEM table) of the items shipped by that shipper.

Exercise 5 (Extra credit)

Create an ER diagram to model the following requirements for a company database

a) Every company has many members, each with a rank. Members of the company are divided into midshipmen, who have an alpha, and active duty (i.e. company officer, senior enlisted) who have a previous assignment. Each midshipman has a position in the company. There are two types of positions: command and non-command. Each midshipman has either a command position or a non-command position. Each command position is held by exactly one midshipman. Non-command positions can be held by multiple midshipmen. Command positions command a least one other position.

b) Add the following requirements:

Midshipmen also belong to groups. These groups include, but are not limited to, sports, ECAs, and year groups. Each midshipman in a company should have a biography that includes personal (but not sensitive) information and an official photo.

c) Add the following requirements:

Companies maintain a calendar of events, with event name, location, and time. These events include, but are not limited to, company meetings, ECA meetings, sporting events, and social events. These events are either optional or mandatory. Mandatory events can be mandatory for all midshipmen or for just a group of the midshipmen in a company.

Turn in:

Hard-copies (due before start of lab on February 15, 2013):

1. The completed assignment coversheet. Your comments will help us improve the course.
2. Hard copies of the data models you created. If you used ERWin to create the models, make sure you click “Fit Model” in the print menu in ERWin, so your diagrams will fit in one page.
3. For exercise 2.a) and 4) include a hard copy of your answers.