Project(120Points)[Link] Due:May5,2011at5pm [Link] TheThreeSeasonsApartmentComplexhasmanyautomatedfeaturesdesignedfortheconvenience ofitsresidents(automatedlightingsystem,etc.).Theonlydrawbacktolivinginacomplexwithmany [Link] systemthatlogstenantrepairrequestsandtrackstheprogressofeachrequestovertime. [Link],typeofwood,floor number, and color scheme.
me. The color scheme is composed of a carpet color, wallpaper color, and kitchenappliancecolor.Thefloorofanapartmentcanbecomputedbydividingthenumberby100. Forexample,theapartmentnumbered721isonthe7thfloor. Eachapartmentmusthaveonespecificfloorplan,andseveralapartmentshavethesamefloorplan. Each floor plan has a letter, a number of bedrooms, a number of bathrooms, a rental price, and a [Link] afloorplan(1A,1B,2A,). [Link],however,maynotleasemultiple apartments(however,eachtenantmustleaseatleastoneapartment,obviously).Eachtenanthasa first name, a credit score, an income, and a collection of references. Since the database is only trackingtenantsfirstnames,atenantcanonlybeidentifiedbyacombinationofhisorherfirstname andapartmentnumber. Requests may be made to fix systems in and around an apartment. Each request has a unique requestnumber,status(openorcompleted),requestdate,[Link] requestmustbecategorizedaseitherinternalorexternaltoanapartment,butarequestwillnotbe categorized as both. If the request is internal, the database should track the system that needs repair. In addition, each internal request must be tied to one specific apartment. Note that an [Link],thedatabaseshouldtrack thenumberoftimestenantshavecomplainedaboutthesamerequest. [Link] multiple technicians, and each technician may handle multiple requests. Each technician has a uniqueemployeenumber,aname(consistingoffirstandlastnames),aphonenumber,asalary,and [Link]. Foreachassociationbetweenarepairtechnicianandarequest,thedatabaseshouldstoreanumber from1to5(with1representingpoorworkand5representingexcellentwork).
[Link] 1. Enterafloorplanintothedatabase.Useatleast3examples. 2. Enteranapartmentintothedatabase,andtieittoafloorplan.Useatleast5examples. 3. Enter a tenant into the database, and associate him or her with an apartment. Associate each tenantwithtwoormorereferences.Useatleast7examples. 4. Enteranexternalrequestintothedatabase.Useatleast3examples. 5. Enteraninternalrequestintothedatabase,andtieittoanapartment.Useatleast5examples. 6. Enteratechnicianintothedatabase,[Link] atleast5examples. 7. Associate each request with at least 2 technicians. Include an evaluation for each (request, technician)combination. 8. Listthedate,system,apartmentnumber,carpetcolor,apartmentfloorplan,andcostpersquare foot (rental price/square feet) of all open internal requests. Sort the results by apartment numberfollowedbysystem. 9. List the full name and salary of each technician that has an average evaluation score below 3. Sorttheresultsbylastnamefollowedfirstname. 10. Displaytheidofthefloorplanthathasthemostcompletedrequests. 11. Foreachfloorplan,listitsnumberofapartments,thenumberoftotaltenantsintheapartments, theaveragecreditscoreandaverageincome. 12. [Link] ofthequery. 13. List the apartment number, first name, credit score, number of references, and deficit of each tenant leasing an apartment whose floor plans rental price exceeds the tenants income. The deficitshouldbecomputedas(rentalpriceincome).Sorttheresultsbydeficit. 14. Oftheapartmentsthathaveneverhadarepairrequest,listtheapartmentnumberandcarpet coloroftheapartmentwiththemostbedrooms. 15. Listthelastname,phonenumber,salary,andaverageevaluationscoreofalltechniciansassigned [Link] [Link] technicianssalariesinthesamecolumnastheindividualsalaries. 16. For each of the following threshold values of 4.0, 3.0, 2.0, 1.0, and 0.0, display the number of technicianswhoseaverageevaluationscoreisabovethethresholdandthesumofthesalariesof those technicians. A technician should not be counted twice, meaning that the collection of techniciansforthevalue3.0shouldnotincludethetechnicianswhoseevaluationscoreabove4.0. 17. [Link] beaparameterofthequery. 18. Increaseby10%therentalpriceofthefloorplanwiththelargestnumberofopenrepairrequests. 19. Removeallinternalrequestscorrespondingtoapartmentsthatdonothaveanytenants. 20. [Link] thesamesysteminthesameapartmentasanearlierrequest. 21. (Optional Bonus 10 points) Write a query that will generate the following table. Each row corresponds to a technician. Each column corresponds to a different floor plan. Each grid [Link] last row should display the total number of requests for each columns floor plan. The last [Link] shouldcontainthegrandtotal(sumofallcolumnsand/orallrows).
[Link] Task1.(20Points)DesignanERdiagramtorepresentthesystemdescribedinpartI.
Task2.(10Points)[Link] attributesandassociatedconstraintsforeachtable.
Task 3. (10 points) Construct SQL statements to create the tables, and implement them in Oracle. ImplementSQLstatementsinOraclethatwillremovethetablesaswell(aswellasanyviewsneeded forTask4).Alldatabaseobjectsshouldbedeletedafterexecutionoftheremovalscript.
Task 4. (60 Points) Write example SQL statements for all of the queries defined in part II, and [Link].
Task5.(20Points)WriteaJavaprogramthatinitiallypromptsforausernameandpasswordandthen [Link] statementsinTask3andthenprovideamenuallowingausertoselectivelyexecutethequeriesin [Link],whentheuserwantstoexit,theprogramshouldusetheremovalstatementsinTask 3 to clear all database objects it created. This program should be able to connect to the Oracle databaseprovidedbytheDepartmentofComputerScience.
[Link] 1. You must hand in a bound, paginated project document containing all of the tasks described in SectionIII. 2. Theprojectdocumentmustincludeacoverpagethatcontainsthefollowinginformation:course nameandnumber,semesterandyear,instructor'sname,author'sname,andprojecttitle. 3. [Link]. 4. You must also submit an electronic version of your project document. Your entire electronic submission [Link] [Link] ofit,youmusthaveanelectronicversionofitonaflashdrivewhereIcancopyitinclass. 5. In addition to the project document, you must electronically submit separate files that contain [Link] forquery#[Link],thefileforquery#[Link]. 6. You must electronically submit a single file that contains SQL statements for creating all of the [Link] [Link] [Link]. 7. All of the .sql files mentioned in #5 and #6 should contain only sql scripts. They should not contain any other text that prevents it from executing in the version of Oracle installed on the computer science network. If I cannot execute the script successfully with the start command, pointswillbededucted. 8. Theprojectisduebythegiventimeontheduedate.LateprojectswillbeaccepteduntilMay10, 2011atmidnight(witha10%penalty).[Link] boundprojectdocumentandtheelectronicfilesmustbesubmittedontimetoavoidthepenalty.
[Link]
ERDiagram . . . DataDictionary. . . SQLStatementsforCreatingTables SQLStatementsforQueries . Query1 . . Query2 . . Query3 . . Query4 . . Query5 . . Query6 . . Query7 . . Query8 . . Query9 . . Query10 . . Query11 . . Query12 . . Query13 . . Query14 . . Query15 . . Query16 . . Query17 . . Query18 . . Query19 . . Query20 . . BonusQuery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 6 10 13 14 15 17 19 20 21 23 25 26 27 28 29 31 32 33 35 36 37 39 40 41