Question

System Requirements

There are many categories of people that need to be tracked in the PAN database. Each person may fall into more than one of the following categories: clients, volunteers, employees, and donors. PAN tracks the name, social security number, birth date, race, gender, and profession of each person. In addition, PAN stores the contact information for each person consisting of a mailing address, email address, and home, work, and cell phone number. PAN also sends a monthly newsletter to people on its mailing list, so the database should indicate whether or not each person in the database is on that list. Finally, the system should have the ability to store a list of emergency contacts for the people in the database. This information should record the name and contact information for each of the emergency contacts along with their respective relationship to the person in the database.

PAN tracks its list of clients in the database. For each client, PAN tracks the name and phone number of his or her doctor and attorney. PAN also tracks the date the client was first assigned to the organization. Each client has a list of needs. Examples of these needs include visiting, shopping, housekeeping, transportation, yard work, and food. Each of these needs is also associated with a value indicating its importance to the client (1-10). PAN also tracks the list of insurance policies that each client has. Each insurance policy has a unique policy id, a provider id, provider address, and a type such as life, health, home, or auto.

PAN provides care for each client using teams that contain many volunteers. Each team cares for several clients, and more than one team may care for a client. Each team is identified by its name, and each team also has a type and a date it was formed. A volunteer may serve on multiple teams. For each volunteer, the database should store the date he or she first joined PAN and the date and location of his or her most recent training course. In addition, PAN should record the number of hours a volunteer worked each month for a particular team. Note that the volunteers do not work the same number of hours each month. One of the volunteers on a team serves as the team leader. This information should be tracked in the database as well. In addition, volunteers and clients may switch teams; so, the database system should provide the ability to mark whether or not each volunteer and client is active or inactive on a specific team.

Every team reports periodically to one PAN employee to discuss its current status, and more than one team may report to the same employee. The database should record the date of each report as well as a description of its content. For each employee, the database should store the employee’s salary, marital status, and hire date. An employee may charge several expenses each month. The database should track the date of the expense, along with the amount and its description.

PAN depends on support from its donors. The database should track these people as well as record each of their donations. This information should include the date, amount, and type of donation along with the name of the fund raising campaign that generated the donation if it is applicable. If the donation was made by check, the database should record the check number. If the donation was made by credit card, the database should record the card number, card type, and expiration date. In addition, each donor may wish to remain anonymous, so the database should record that information as well.

Each person in the database may be affiliated with an external organization. The database should track this information as well. Each organization should have a unique name, mailing address, phone number, and contact person. In addition, each organization may sponsor one or more PAN teams, and a team may have more than one sponsor. If the organization is a business, the database should record the business type, size, and company web site. If the organization is a church, the database should record its religious affiliation. Each organization may also make several donations to PAN, and the database should track the same donation information as it does for individual donors. This includes the ability for the organization to make anonymous donations

Question 1: Construct and ER diagram for the above System Requirements

Answer 1

ERD for given database :

No of entities :

Person entity , Client entity , Volunteers entity , Employee entity , Donors entity , Emergency_Contacts entity , Insurance_policies entity , Team entity, Organisation entity , Business entity , Church entity , Donation entity , Check entity, CreditCard entity

Relationships:

1. There are Has relationship between Person entity and Emergency_Contacts entity.
2. There are Affiliated relationship between Person entity and Business entity.
3. There are has relationship between Client entity and Insurance_Policies entity.
4. There are Care_For relationship between Client entity and Team entity.
5. There are Serve relationship between Volunteers entity and Team entity.
6. There are report relationship between Employee entity and Team entity.
7. There are Charge relationship between Employee entity and Expenses entity.
8. There are record relationship between Donors entity and Donation entity.
9. There are Make relationship between Donation entity and Organization entity.
10. There are Sponsor relationship between Business entity and Organization entity

Multiplicity:

1. There are M:M multiplicity between Person entity and Emergency_Contacts entity.
2. There are M:1 multiplicity between Person entity and Business entity.
3. There are 1:M multiplicity between Client entity and Insurance_Policies entity.
4. There are M:M multiplicity between Client entity and Team entity.
5. There are 1:M multiplicity between Volunteers entity and Team entity.
6. There are 1:M multiplicity between Employee entity and Team entity.
7. There are 1:M multiplicity between Employee entity and Expenses entity.
8. There are 1:M multiplicity between Donors entity and Donation entity.
9. There are M:1 multiplicity between Donation entity and Organization entity.
10. There are M:M multiplicity between Business entity and Organization entity

Generalization exists in the diagram :

• 1. Person entity is super entity and Client entity, Volunteers entity, Employee entity, Donors entity are Sub entities.
• 2. Organization entity is super entity and Business entity, Church entity are Sub entities.
• 3. Donation entity is super entity and Check entity and CreditCard entity are sub entities

ERD :

Thank you................