ER Exercise

Problem Specification

This exercise is based on a homework assignment on Fall 2015.

(1) Download and install "EA Assistant v2.1" from http://highered.mheducation.com/sites/0072942207/student_view0/e_r_assistant.html. You will need it to draw Entity-Relationship Diagram (ERD).

(2) Consider the Beta University Project, a guided project from Ricardo: http://computerscience.jbpub.com/ricardo/guidedProjects.aspx. Download and the file "Project 1-Beta University-Introduction.docx". You do not need to do the four steps mentioned in the document. Instead, you should:

1. Construct a reasonable ERD to capture the data requirements of the project. You will need to make reasonable assumptions. Send
2. Write down the assumptions you have made to create the ERD.

(3) Real world applications are much more complicated. The initial document is highly simplified. Ask three reasonable questions to clarify how credit cards will be used. (This may in term affect how you model the problem, including your ERD.)

Suggested Solution

(1) The ER Diagram:

(2) Some assumptions made in constructing the ERD:

• Companies are identified by id. Thus, two companies can have the same name. Furthermore, companies can change names.
• A company has a unique address not shared by other companies or individuals.
• Individual persons may share an address.
• A class representative (coordinator) can only be the coordinator of one class.
• Credit card information is stored as part of a pledge, not part of a payment. (It is ok if an alternate design stores it in the payment, or not storing it at all.)
• Only the credit card number is stored. In reality, processing a credit card transaction requires more information.
• It is not necessary to differentiate between a donor and a potential donor.
• It is not necessary to store detailed class information.
• It is not necessary to store alumni information besides names, addresses, phones and graduation years.
• Circles are defined by the sum of actual donation payments, not be individual pledges.
• It is not necessary to store other personal information about a person besides name, phone and address, not even email, for examples.
• It is not necessary to store the callers resulting in a pledge.
• If a payment does not come from a matching company, it is assumed that it comes from the donor of the pledge.

(3) Examples of reasonable questions on credit cards:

• Do we need to store credit card information in the database?
• If an external company will handle credit card processing for us, do we need to store any processing and crediting information?
• If we store credit card information, what do we need to store beyond credit card type, credit card number, expiration date, the name of the card (which may be different than the name in our database) and address?
• If we store credit card information, what kinds of security measure are needed?
• If we store credit card information, what are the purposes besides charging the card once (for the lump sum)?
• Can the donor use a credit card to pay for multiple payments instead of just a lump sum?
• Can a donor use different credit cards for different payments?