Database Design

Estimated time to complete: 5-7 hours

Overview

In this phase of the project, you perform requirements analysis and record the results in an Entity-Relationship Diagram. This phase is divided into two parts:

1. Draft Design – Create an initial ERD
2. Final Design – Refine the diagram

Draft Design (10 points)

Analyze the information provided to you (see Project Docs), following the Requirements Analysis procedure discussed in class. Create an ERD that accounts for all of the information on the various forms, as well as the other information.

You may do the draft ERD neatly with pencil, or use a diagramming tool. No cover sheet is needed.

You should spend at least 1 hour of effort, but no more than 2. Write the amount of time spent on the diagram. You will receive full credit if you spend at least 1 hour and produce an ERD that shows effort.

This step will receive a check grade, and no late submissions will be accepted. It should represent your best initial effort. Bring a paper copy to class (if you do the work in pencil, you must turn in a photocopy so that you have the original to use in the next step).

Tips:

• Strive for a simple design (mine has fewer than 10 entities).

• When you are finished, you should be able to design a fairly straightforward query to generate the information for a service order document. If you cannot envision such a query, revise your design until you can.

Final Design (50 points)

Refine your design based on the results of in-class discussion. Your design must use the entities identified in class. Make sure that every piece of information on the sample month plan and service order documents has a spot in your model.

Document your design in a design document that contains the following elements:

1. A standard cover sheet.

2. An Entity-Relationship Diagram created with a diagramming tool. Use good relationship names.

3. A data dictionary that documents the ERD. Follow the format in this example. Be sure to:

   • define each entity and attribute clearly

   • give an example value for each attribute (other than sequential ID numbers)

   • give a complete list of values for attributes that have a fixed list of possible values (codes)

4. A short list of bulleted points that defends design decisions that you feel are potentially controversial, or explains potentially confusing aspects of the design (for example, if certain values are computed instead of stored as attributes, you would want to mention that). If you can’t think of anything controversial or confusing about your design decisions, feel free to omit this section.

5. A draft SQL query based on your design that would produce output that could be used to generate a service order document. It should join tables to produce a result with the structure shown in Figure One. Include an ORDER BY clause to generate the correct sequence of events (ex., the Opener must come first).

Upload your design document to Canvas in a file named report.pdf. Late submissions will receive a zero.

Figure One

Service Date	Service Time	Worship Theme	Songleader	Organist	Pianist	Event	Name	Title	Comments
10-3-2010	10:30am	Let the Peace…	Kennedy	Bixby	Martin	Opener	Hawkey	It is Well	flute
10-3-2010	10:30am	Let the Peace…	Kennedy	Bixby	Martin	Prelude	Bixby
10-3-2010	10:30am	Let the Peace…	Kennedy	Bixby	Martin	Welcome	Lee
10-3-2010	10:30am	Let the Peace…	Kennedy	Bixby	Martin	Prelude	Bixby
10-3-2010	10:30am	Let the Peace…	Kennedy	Bixby	Martin	Scripture Reading	Lee
…	…	…	…	…	…	…	…	…	…