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.
Paper#72945 | Written in 18-Jul-2015Price : $32