The Patient Assistance Network (PAN) is a non‐profit organization that providessupport and care for

patients. PANneeds a database systemto keep track ofthe personnelthatsupportthe organization.

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.

There will be some people who do not fall in any of those categories. PAN tracksthe name (first and

last), date of birth, ethnicity, gender, and profession of each person. In addition, PAN assigns a

unique ID number to each person. PAN also stores the contact information for each person

consisting of a mailing address (street, city, state, zip), email address, and contact numbers. Each

contact numbershould be stored with itstype (such as home, work, cell, etc.). Finally, PANmaintains

a mailing list for its monthly newsletter, so the system should track whether each person in the

database is on thatlist.

PAN tracksitslist of clientsin the database. For each client, PAN tracksthe names(first and last) and

phone numbers of his or her doctor and attorney. PAN also tracksthe date the client first joined the

organization. Each client has a list of needs such as housekeeping, transportation, yard work, etc.

Each ofthese needsis also associated with a value indicating itsimportance to the client(1‐10).

PAN provides care and support for each client using teamsthat contain many volunteers. Each team

caresforseveral clients, and more than one team may support a client. Each team isidentified by its

name, and each team has a type and date it wasformed. A volunteer may serve on multiple teams.

For each volunteer, the database should store the date he orshe first joined PAN. In addition, PAN

should record the number of hours a volunteer worked each month for a particularteam. Note that

the volunteers do not work the same number of hours eachmonth. One ofthe volunteers on a team

serves asthe teamleader, and thisinformation should be tracked in the database as well.

Every team must report to a PAN employee, and more than one team may report to the same

employee. Each team meets periodically with its employee to discuss its current status. The

database should record the date of each meeting as well as a brief description of its content. For

each employee, the database should store the employee’s monthly salary, maritalstatus, date hired,

and whether employee isfull‐ or half‐time. An employee may charge several expenses each month.

The database should track the date ofthe expense, along with the amount and its description.

PAN depends on support from its donors. The database should track these people as well asrecord

each oftheir donations. Thisinformation should include the date, amount, and paymenttype of each

donation. In addition,the database should track whether each donor wishesto remain anonymous.

Each person in the databasemay be affiliated with one or more external organizations. The database

should track thisinformation as well. The database should note which person in the database isthe

official contact person for the organization. Each organization should have a unique name, type,

mailing address, phone number, and Web site. In addition, each organization may sponsor one or

more PAN teams, and a team may have more than one sponsor. Each organization may also make

several donationsto PAN, and the database should track the same donation information asit doesfor

individual donors. Thisincludesthe ability forthe organization to donate anonymously.

II.QUERIES

1. Enter a new employee into the database and associate him or herto one or more expenses. Use

7 examples.

2. Enter a new teaminto the database and associate itto an employee. Use 5 examples.

3. Enter a new client into the database and associate him or her with one or more teams. Include

one ormore needsfor each client. Use 7 examples.

4. Enter a new volunteer into the database and associate him or her with one or more teams.

Include the number of hoursthe volunteer worked this month on each team and if the volunteer

isthe leaderforthatteam. Use 7 examples.

5. Enter a new organization and associate itto one ormore PANteams. Use 5 examples.

6. Enter a new donor and associate himor her with one ormore donations. Use 7 examples.

7. Enter a new donation and associate it with an organization. Use 7 examples.

8. List the name and phone number ofthe doctor of each client (along with the client’s name). Sort

the list by client’slast name,then by first name.

9. List the names of anonymous donorsin the database who are not on the mailing list. Include the

total amount donated from each donor. Sort the list by total donation amount in descending

order.

10. Retrieve the list of volunteersthat are members ofteamsthatsupport a given client. The client’s

IDnumbershould be prompted by the database. Sortthe list by the volunteersjoining date.

11. Retrieve the names and addresses of clients that are supported by the teams that report to the

employee with the highestsalary. Sortthe list by the client’slast name.

12. Retrieve the name, address, and total amount donated by donors that are also clients. The list

should be sorted by the total amount of the donations, and indicate if each donor wishes to

remain anonymous.

13. List the occurrences when a volunteer supports a client that is a different gender. For each

occurrence, listthe volunteer’s name, joining date,teamname, and client’s name. Sortthe list by

client’s name (last,first),then by teamname,then by volunteer’s name (last,first).

14. Retrieve the list of needs that are considered to be important by multiple clients. An important

need has a rank of 7 or higher.

15. Retrieve the effective amount donated by each organization. The effective amount isthe sum of

the organization’s donations and the total amount of donations made by all of the individuals

affiliated with the organization.

16. Retrieve the names, total expenses, and salaries of the employees with annual salaries that

exceed the total amount of his or her expenses. Sortthe list by salary in descending order.

17. Increase the salary by 10% of all half‐time employees who havemultiple teamsreportto them.

18. Reduce by 1 the importance ofthemost commonly requested need for each client.

19. Delete the employee that hasmet with the fewesttotal number oftimes with his or herteams.

20. Delete the volunteer that has worked the least total number of hours. The volunteer should be

removed fromallteams as well.

21. (Optional for Bonus Points) Generate a report that lists all of the donations made by donors and

organizations. Each row should list the donor name, amount, and date of donation along with a

field indicating if the donation was anonymous. The list should be sorted by donation date in

decreasing order. There should be a row after eachmonth giving a total ofthe donationsmade in

that month (in the same column as the donation date). The donor name in this row should be

“Monthly Total”. Similarsubtotalrowsshould exist after each year with the name “Yearly Total”.

The lastrow in the reportshould be a grand total with the name “Grand Total”.

III. TASKS TOBE PERFORMED

Task 1. (15 points)Design an E‐R diagramto representthe systemdescribed in PartI.

Task 2. (15 points) Reduce your E‐R diagram to a relational database. Provide a Data Dictionary that

liststhe names,types, definitions, and constraints of all attributesin each table.

Task 3. (10 points) Construct SQL statements to create the tables and any necessary views, and

implement them in Oracle. Implement SQL statements in Oracle that will remove the tables and

views as well.

Task 4. (80 points) Write example SQL statements for all of the queries defined in part II, and

implementtheminOracle. Note thatsome ofthe queriesmay requiremultiple SQL statements.