The Patient Assistance Network (PAN) is a non\u2010profit organization that providessupport and care for<\/p>\n
patients. PANneeds a database systemto keep track ofthe personnelthatsupportthe organization.<\/p>\n
There are many categories of people that need to be tracked in the PAN database. Each person may<\/p>\n
fall into more than one of the following categories: clients, volunteers, employees, and donors.<\/p>\n
There will be some people who do not fall in any of those categories. PAN tracksthe name (first and<\/p>\n
last), date of birth, ethnicity, gender, and profession of each person. In addition, PAN assigns a<\/p>\n
unique ID number to each person. PAN also stores the contact information for each person<\/p>\n
consisting of a mailing address (street, city, state, zip), email address, and contact numbers. Each<\/p>\n
contact numbershould be stored with itstype (such as home, work, cell, etc.). Finally, PANmaintains<\/p>\n
a mailing list for its monthly newsletter, so the system should track whether each person in the<\/p>\n
database is on thatlist.<\/p>\n
PAN tracksitslist of clientsin the database. For each client, PAN tracksthe names(first and last) and<\/p>\n
phone numbers of his or her doctor and attorney. PAN also tracksthe date the client first joined the<\/p>\n
organization. Each client has a list of needs such as housekeeping, transportation, yard work, etc.<\/p>\n
Each ofthese needsis also associated with a value indicating itsimportance to the client(1\u201010).<\/p>\n
PAN provides care and support for each client using teamsthat contain many volunteers. Each team<\/p>\n
caresforseveral clients, and more than one team may support a client. Each team isidentified by its<\/p>\n
name, and each team has a type and date it wasformed. A volunteer may serve on multiple teams.<\/p>\n
For each volunteer, the database should store the date he orshe first joined PAN. In addition, PAN<\/p>\n
should record the number of hours a volunteer worked each month for a particularteam. Note that<\/p>\n
the volunteers do not work the same number of hours eachmonth. One ofthe volunteers on a team<\/p>\n
serves asthe teamleader, and thisinformation should be tracked in the database as well.<\/p>\n
Every team must report to a PAN employee, and more than one team may report to the same<\/p>\n
employee. Each team meets periodically with its employee to discuss its current status. The<\/p>\n
database should record the date of each meeting as well as a brief description of its content. For<\/p>\n
each employee, the database should store the employee\u2019s monthly salary, maritalstatus, date hired,<\/p>\n
and whether employee isfull\u2010 or half\u2010time. An employee may charge several expenses each month.<\/p>\n
The database should track the date ofthe expense, along with the amount and its description.<\/p>\n
PAN depends on support from its donors. The database should track these people as well asrecord<\/p>\n
each oftheir donations. Thisinformation should include the date, amount, and paymenttype of each<\/p>\n
donation. In addition,the database should track whether each donor wishesto remain anonymous.<\/p>\n
Each person in the databasemay be affiliated with one or more external organizations. The database<\/p>\n
should track thisinformation as well. The database should note which person in the database isthe<\/p>\n
official contact person for the organization. Each organization should have a unique name, type,<\/p>\n
mailing address, phone number, and Web site. In addition, each organization may sponsor one or<\/p>\n
more PAN teams, and a team may have more than one sponsor. Each organization may also make<\/p>\n
several donationsto PAN, and the database should track the same donation information asit doesfor<\/p>\n
individual donors. Thisincludesthe ability forthe organization to donate anonymously.<\/p>\n
<\/p>\n
II.QUERIES<\/p>\n
1. Enter a new employee into the database and associate him or herto one or more expenses. Use<\/p>\n
7 examples.<\/p>\n
2. Enter a new teaminto the database and associate itto an employee. Use 5 examples.<\/p>\n
3. Enter a new client into the database and associate him or her with one or more teams. Include<\/p>\n
one ormore needsfor each client. Use 7 examples.<\/p>\n
4. Enter a new volunteer into the database and associate him or her with one or more teams.<\/p>\n
Include the number of hoursthe volunteer worked this month on each team and if the volunteer<\/p>\n
isthe leaderforthatteam. Use 7 examples.<\/p>\n
5. Enter a new organization and associate itto one ormore PANteams. Use 5 examples.<\/p>\n
6. Enter a new donor and associate himor her with one ormore donations. Use 7 examples.<\/p>\n
7. Enter a new donation and associate it with an organization. Use 7 examples.<\/p>\n
8. List the name and phone number ofthe doctor of each client (along with the client\u2019s name). Sort<\/p>\n
the list by client\u2019slast name,then by first name.<\/p>\n
9. List the names of anonymous donorsin the database who are not on the mailing list. Include the<\/p>\n
total amount donated from each donor. Sort the list by total donation amount in descending<\/p>\n
order.<\/p>\n
10. Retrieve the list of volunteersthat are members ofteamsthatsupport a given client. The client\u2019s<\/p>\n
IDnumbershould be prompted by the database. Sortthe list by the volunteersjoining date.<\/p>\n
11. Retrieve the names and addresses of clients that are supported by the teams that report to the<\/p>\n
employee with the highestsalary. Sortthe list by the client\u2019slast name.<\/p>\n
12. Retrieve the name, address, and total amount donated by donors that are also clients. The list<\/p>\n
should be sorted by the total amount of the donations, and indicate if each donor wishes to<\/p>\n
remain anonymous.<\/p>\n
13. List the occurrences when a volunteer supports a client that is a different gender. For each<\/p>\n
occurrence, listthe volunteer\u2019s name, joining date,teamname, and client\u2019s name. Sortthe list by<\/p>\n
client\u2019s name (last,first),then by teamname,then by volunteer\u2019s name (last,first).<\/p>\n
14. Retrieve the list of needs that are considered to be important by multiple clients. An important<\/p>\n
need has a rank of 7 or higher.<\/p>\n
15. Retrieve the effective amount donated by each organization. The effective amount isthe sum of<\/p>\n
the organization\u2019s donations and the total amount of donations made by all of the individuals<\/p>\n
affiliated with the organization.<\/p>\n
16. Retrieve the names, total expenses, and salaries of the employees with annual salaries that<\/p>\n
exceed the total amount of his or her expenses. Sortthe list by salary in descending order.<\/p>\n
17. Increase the salary by 10% of all half\u2010time employees who havemultiple teamsreportto them.<\/p>\n
18. Reduce by 1 the importance ofthemost commonly requested need for each client.<\/p>\n
19. Delete the employee that hasmet with the fewesttotal number oftimes with his or herteams.<\/p>\n
20. Delete the volunteer that has worked the least total number of hours. The volunteer should be<\/p>\n
removed fromallteams as well.<\/p>\n
21. (Optional for Bonus Points) Generate a report that lists all of the donations made by donors and<\/p>\n
organizations. Each row should list the donor name, amount, and date of donation along with a<\/p>\n
field indicating if the donation was anonymous. The list should be sorted by donation date in<\/p>\n
decreasing order. There should be a row after eachmonth giving a total ofthe donationsmade in<\/p>\n
that month (in the same column as the donation date). The donor name in this row should be<\/p>\n
\u201cMonthly Total\u201d. Similarsubtotalrowsshould exist after each year with the name \u201cYearly Total\u201d.<\/p>\n
The lastrow in the reportshould be a grand total with the name \u201cGrand Total\u201d.<\/p>\n
<\/p>\n
III. TASKS TOBE PERFORMED<\/p>\n
Task 1. (15 points)Design an E\u2010R diagramto representthe systemdescribed in PartI.<\/p>\n
Task 2. (15 points) Reduce your E\u2010R diagram to a relational database. Provide a Data Dictionary that<\/p>\n
liststhe names,types, definitions, and constraints of all attributesin each table.<\/p>\n
Task 3. (10 points) Construct SQL statements to create the tables and any necessary views, and<\/p>\n
implement them in Oracle. Implement SQL statements in Oracle that will remove the tables and<\/p>\n
views as well.<\/p>\n
Task 4. (80 points) Write example SQL statements for all of the queries defined in part II, and<\/p>\n
implementtheminOracle. Note thatsome ofthe queriesmay requiremultiple SQL statements.<\/p>\n
<\/p>\n
<\/p>\n<\/div>\n \n
![\"\"](\"https:\/\/nursingwritershelp.com\/wp-content\/uploads\/2019\/07\/order_now.png\")
<\/a>\n\n","protected":false},"excerpt":{"rendered":"