Project IS

Stage 1 Project Requirements –Data Requirements and ERD

35 Total Points. Submit a hard copy before class.

Step 1): (15 Points) Discuss information requirements for the database application, addressing the following points.

· (0.5 point) Discuss background of the company and the industry they are in.

· (0.5 point) Discuss problems with the current way of doing business or with the current system, problems with obtaining timely and accurate vital information. Your database may not be able to resolve all problems identified here. Specify the problems your database is trying to solve (i.e.; scope of your solution).

· (4 points) Provide detailed descriptions of business operations that will need or generate data for which you are designing the database. The write up here serves as the natural language description of data requirements. This is not the place to describe your tables or what the attributes are for the tables. This is where you describe business processes and business rules.

· (8 Points) Use the four sentence templates discussed in class to describe detailed data requirements that mostly match the data requirements descriptions in the last bullet. The sentences should start with “Database needs to store.” For cardinality of each relationship, start the two sentences with “each.” See the third sub-bullet below. The descriptions of data requirements should be such that another person (say, your instructor) can draw an ERD based on your descriptions. You may want to use bulleted statements to clearly state data requirements. Provide an example instance of each entity as well as for attributes of relationships. Here are some examples:

· (For entities, attributes, and primary keys). Database needs to store information about each Order. For each order, database needs to store OrderId, OrderDate, and Total dollar amount. OrderId is unique to each Order. Order example: Order1234 ordered on 3/1/2015, with a dollar amount of $540.

· (For relationships) Database needs to store information about what products are being ordered in each order.

· (For relationship cardinalities): Each order can contain multiple products. Each product can appear in multiple orders. For example, Order1234 ordered SKU78 and SKU72; Order34 ordered SKU72 and SKU83.

· (For relationship attributes): Database needs to store quantity and price of each product ordered in each order. (Or, for each product ordered in each order, database needs to store quantity and price.) For example, Order 1234 ordered Product SKU78 at $78/unit for 5 units. Order1234 also ordered SKU72 at $60/unit for 9 units.

Your write-up may benefit from an iterative process whereby you compare your ERD with your write-up and revise the write-up if you find that certain information is not contained in the write-up to allow another person to derive attributes, primary keys, or cardinalities, or if you find that your ERD does not match your write up. Give examples of data elements if those elements are specific to the application environment, because other people (e.g., your instructor) may not know what they are (see the next requirement).

· (2 points) Provide sample data for all data requirements as part of the submission. If the application environment uses any paper forms to collect data, fill out a set of forms with fictitious information as sample data and attach those forms as part of the submission. If the application environment does not use paper forms, provide screen captures or otherwise provide sample data (made up data are fine). If your project is made up, provide made up forms or data.

Step 2): (20 Points) Draw an E-R diagram to document your application according to your descriptions provided in Step 1), including the mapping ratios for relationships. The ERD you draw here should match your descriptions in Step 1, Bullet 4. Clearly identify attributes of entities and relationships. Each entity must have a primary key and necessary attributes.

· To save on diagramming effort, you may want to list all attributes of an entity inside the box for that entity.

· For each entity, underline or otherwise indicate the primary key.

· If writing out the attributes inside the box crowds the diagram, list the attributes for each entity separately, beneath the diagram if there is room, or on a separate page.

· If the meaning of an attribute is not self-explanatory, add explanations.

You may hand-draw your ERD, or you may use any drawing tools.

There are many free online drawing tools available, for example, http://www.draw.io (use a template under Software Design). ERDplus.com is another good tool. If you’re not using Visio, Take a screenshot of your ERD and insert the screenshot as an image in your Word Document. If you’re using Visio, insert the ERD as an editable image. The solution file for ERD exercises posted in Canvas were drawn with Visio. You can copy one and modify it for your own ERD. It would save you some time this way.

Important

Drawing ERD and converting it to a list of tables is an iterative process. After drawing the ERD, it’s beneficial to convert that ERD into a list of tables and then investigate whether the final tables make sense. This often will give you feedback on your ERD. However, ERD conversion is not required in Prj1. It’ll be on Prj2 (because your ERD may change).

2