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.
1. Enter a new employee into the database and associate him or herto one or more expenses. Use
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
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
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.