Project Requirements

Use this scenario, material in your textbook, and the Tegrity videos MySongs Parts 1 and 2 to guide you in creating a data model that incorporates E/R diagramming techniques. The textbook and the videos have a different scenario, but the principles are the same. You will need to adapt those examples to the data model for your scenario and complete the process for all of the entities, attributes, and relationships your scenario requires.

You have three options listed below for recording your data model in a digital format to submit at a link the Assignments area of Blackboard. My advice is to create your first model in sloppy form with paper and pencil. When you have it worked out, then transfer your data model into one of the formats below.

1. Use paper and pencil. Your diagram must be neatly drawn, well labeled, and easy to read. Take a very clear photo of the data model or two photos if your model is on two pages. If my assistant and I can't read something, you won't get credit for that part of the diagram. Save the photo as a graphic file (.jpg, .png, etc.) with a file name following the rules for this course (see example below).

2. Use Word (five bonus points). I recommend use of Shapes. You will find typical blocks and lines in the Shapes area of the menu under the Insert ribbon tab. Add the object attributes and their properties (data type, length, required or not) inside the block designated for each object (don't forget to label the primary key attribute).

Add connecting lines between objects that are directly related. Add the lines and circles for the E/R relationship attributes (mandatory/optional, cardinality) on top of the connection lines. It works best when you group the E/R symbols and place that group in the foreground and the connection line in the background.

3. Use Visio (10 bonus points). I will award up to 10 extra credit points for using Visio because you will probably be learning a software package that is new to you. (Students who took my Business Process Management course last spring will already know how to use it.)

Visio is available in all lab computers, remotely on the ATU Virtual Desktop (for Apple computers), and can be downloaded free from Microsoft Imagine (contact Susan Morris, the Dean's secretary, to get ID/password credentials and the URL of ATU's portal site; smorris@atu.edu).

Visio has stencils for E/R diagrams with all the blocks, connection lines, and E/R relationship attribute symbols needed. The object blocks have the ability to add all the attributes needed and the required properties needed for each attribute (name, data type, length, required or not). The connection lines have the crow's-foot and other designations for recording relationship properties in an E/R diagram. There are some extra resources for using Visio in the Data Model folder in the Assignments area of Blackboard and in the Software Application Resources folder.

No matter which of these options you choose, make sure to put the usual identifying information (your name, course and section number, date) in a box in the upper right-hand or upper left-hand corner of the data model. If you use paper and pencil, write this the information clearly. In Word or Visio, insert a text box to add this information.

Data Model Process

For reference, here is the process to use in creating a data model. See the Tegrity video in Blackboard for an example. The PowerPoint file developed in the video is also posted in this folder.

1. Identify all entities the manager wants to keep track of. Typical entities are persons, things, places, and events (like transactions).

2. Describe each entity. Create a list of attributes that describe each entity. These attributes will become fields or columns in your database tables. Just include attributes necessary to this scenario (just the data you will want to keep track of).

3. Determine the properties for each attribute:

• Data type (text, numeric, date, etc.)

• Length or size. This is the number of characters for text attributes, the type of number for numeric fields (like integer or long integer for values without decimals, single or double precision for values with decimals, currency, etc.)

• Required. This refers to whether a value in the attribute is required or optional when a record is created. (That is, if you say a value is required, you cannot create a new record for that entity without entering a value for this attribute. For example, once the data model is implemented in a database, if the ‘required' property is set to yes for the last name field, you would not be able to create a record for a new student without entering in a value for last name.)

4. Identify an attribute in each entity that can act as an identifier (eventually a primary key in a table in a database).

5. Identify any direct relationships that might exist between various pairs of entities.

6. Make sure you can actually link any related entities. This means they will have one attribute in common. Typically, we place the identifier attribute (primary key) of one entity into the other entity as an additional attribute (where it becomes a foreign key). In MySongsNow, the Sales Transaction entity has Customer ID as an attribute (who the song was sold to).

Customer ID is the primary key of the Customer entity but it is a foreign key in the Sales Transaction entity. Having Customer ID in both entities will eventually allow the database to connect those two tables.

7. Describe the properties of each relationship. This involves establishing minimum/maximum cardinality; i.e., mandatory/optional and one-to-one (1:1), one-to-many (1:M), or many-to-many (N:M).

8. Normalize the data model.

• Remove any many-to-many (N:M) relationships by adding an intersection entity consisting of at least the primary keys of both original entities. (These two attributes combine into what is called a composite key.) Consider whether this intersection represents something in the real world that you do want to keep track of. If it does, name the intersection entity appropriately and add any necessary attributes.

• Remove any derived attributes. Those are attributes whose value can be figured or otherwise established from the values of other attributes. (For example, in a sales transaction, total price for an item can always be calculated by multiplying the number bought by the price. You can calculate your GPA by using total earned hours and total quality points.)

• Make sure all attributes are in the right entity. Sometimes, in the heat of designing the data model, an attribute that really belongs to a particular entity is thought of when considering a different attribute and gets added there. If an attribute does not directly describe the entity in which it is located, it needs to be moved to the attribute it does describe. (For example, you might have listed the vendor of a product in a sales transaction. Really, the vendor should be listed in the product inventory table, as it describes the product, not a sales transaction.)

Attachment:- Low Budget Rentals Assignment.rar