Academic help online

The Assignment-2 Specification and Marking CriteriaIn this assignment, you are to design and implement the database of Best Home real estate that youhave modelled in assignment-1 and a series of SQL queries to reflect the business logic of it. Note: If your assignment-1 was incomplete to cover the application scenario or was incorrect insome parts, you will need to remodel it before completing this assignment. Read the markingcomments of assignment-1 carefully and talk to your marker if necessary. Doing so would minimiselosing marks for this assignment. The specification of this assignment1. Database implementation • You are to design and implement the ER model of assignment-1, includingentities/associative entities, relationships and cardinalities, into a Microsoft Access Database.You need to make sure that your database is compatible with MS Access 2007 for markingpurpose.• You need to ensure that relations of your database meet 3NF.• Your database needs to include sample data to support the required queries.2. SQL queryAs a way to demonstrate success, you are to implement a series of queries on the database to reflectthe business logic of Best Home real estate. Query-1: List all the rental properties that the asked prices are the same as the rent prices toshow:• The property type• The property address• The asked price• The actual rent priceQuery-2: List all the sale properties that have been available for 6 months but not sold yet toshow:• The property type• The property address • The property sale price• The property available date1• The property manager’s name Query-3: List the properties that were sold in a particular suburb e.g. ‘The range’ to show: • The buyer name• The property manager who sold the property• The property type• The property address• The asked price• The deal price• The date when the property was soldNote: the suburb name can be hardcoded in your query.Query-4: List the contact details of the solicitor who involved in a sold property of a particularaddress, e.g. ’22 George Street’ to show:• The property type• The property address• Solicitor name• All contact details of the solicitorNote: the property address can be hardcoded in your query.Query-5: List all rent properties which have 2 or more applications to show:• The property ID• The property type• The property address• The weekly rent• The property manager’s nameQuery-6: An owner wants to complain to a higher level officer about the management ofhis/her rent property. Find the supervisor of the property manager, who is currentlylooking after the property to show:• The property type• The property address 2• The owner name• The property manager’s name• The supervisor’s name of the property managerQuery-7: List all property managers in descendent order of the numbers of properties that theyhave been looking after to show:• The property type• The property address • The property status• The property manager’s name3. Query ReportSuppose that a property manager will contact the owner of a sale property to talk about the currentoffers for the property. Create a report to sort the offers in descending order to show:• The property type• The property address • The property sale prices• The offered price• The name and his/her contact details of the customer who gave the offer • The name and his/her contact details of the owner of the propertyNote: You are to create the queries manually by using the SQL view of MS Access query builder todemonstrate your competency in SQL. Failing to do so may result in losing marks.4. Implementation documentYou are to write a short report to demonstrate that you have thought carefully about the issues thatarise when implementing a relational database. You may address:• The multiple implementation choices, such as the data type and size selection for aparticular attribute or the cardinality of a relationship etc and justify your selection• The major issues that you have encountered for the database implementation• The major issues that you have encountered for creating queries/reportsSubmission3To submit your assignment, you need to provide: 1. A Microsoft Access database, which contains the required:• Relations with sample data• Queries • The query report2. The Implementation document detailed in a Word documentYou must submit all the required files in a zip file into the course Moodle site.The marking criteria of this assignment are detailed in the following table.Marking Criteria Available MarksDatabase implementation 1. Coverage of the business data structures of Best Home 22. Enough sample data are populated for the requiredqueries23. Naming of relations is clear; use of data types is correct 2SQL query Query-1 2Query-2 2Query-3 3Query-4 3Query-5 3Query-6 3Query-7 3The query report1. The underlying query/queries for the report is correct 22. The report layout is appropriate 2The implementation document 1. Multiple implementation choices and selection 22. The major issues with the database implementation 243. The major issues with queries/reports creations 2Sub Total for Assignment-2 35Late Penalty -1.75 (5% each calendarday)Plagiarism PenaltyTotal for Assignment-2 5

All Rights Reserved,
Disclaimer: You will use the product (paper) for legal purposes only and you are not authorized to plagiarize. In addition, neither our website nor any of its affiliates and/or partners shall be liable for any unethical, inappropriate, illegal, or otherwise wrongful use of the Products and/or other written material received from the Website. This includes plagiarism, lawsuits, poor grading, expulsion, academic probation, loss of scholarships / awards / grants/ prizes / titles / positions, failure, suspension, or any other disciplinary or legal actions. Purchasers of Products from the Website are solely responsible for any and all disciplinary actions arising from the improper, unethical, and/or illegal use of such Products.