In this assignment, you are to design and implement The Best Home business database that you have modelled in the assignment-1 and a series of SQL queries to reflect the business logic of The Best Home. – Best Writers

In this assignment, you are to design and implement The Best Home business database that you have modelled in the assignment-1 and a series of SQL queries to reflect the business logic of The Best Home.Note: If your assignment-1 was incomplete to cover the application scenario of The Best Home or incorrect in some parts, you will need to remodel it before completing this assignment. Read the marking comments of assignment-1 carefully and talk to your marker if necessary.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 2010 for marking purpose.• You need to ensure that relations of your database meet 3NF.• Your database needs to include enough sample data to support the required SQL queries.2. SQL queryAs a way to demonstrate success, you are to implement a series of queries on the database to reflect the business logic of The Best Home. Note: You are to create the queries manually by using the SQL view of MS Access query builder to demonstrate your competency in SQL. Failing to do so may result in marks to be deducted.Query-1: List all the contact details of a particular customer to show:• The customer’s name• The contact type e.g. email• The contact details e.g. w.li@cqu.edu.auNote: the customer’s name is a parameter to enter when executing this query in Microsoft Access. A customer could have more than one contact e.g. email, mobile and home phone.Query-2: List all the rental properties that the asked prices (the price on the advertising) are the same as the rent prices (the price on the rent contract) to show:• The property type• The property address• The asked price• The actual rent priceQuery-3: List all the sale properties that have been available for 6 months but not sold yet to show:• The property type• The property address• The property sale price• The property available date• The property manager’s nameQuery-4: 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-5: List the contact details of the solicitor who involved in a sold property of a particular address, 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-6: 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-7: An owner wants to complain to a higher level officer about the management of his/her rental property. Find the supervisor of the property manager, who is currently looking after the property to show:• The property type• The property address• The owner name• The property manager’s name• The supervisor’s name of the property managerNote: the property address can be hardcoded in your query.Query-8: List all property managers in descendent order of the number of properties that they have been looking after to show:• The property type• The property address• The property status• The property manager’s name3. Query ReportAssume that a property manager will contact the owner of a sale property to talk about the current offers for the property. Create a report to sort the offers in descending order (from the highest to the lowest offered price) 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 offerNote: the property address can be hardcoded in your query.4. Implementation documentYou are to write a short report to demonstrate that you have thought carefully about the issues that arise when implementing the database and the SQL queries. You need to address:• How you populate data for multi-valued attributes to make sure that they are enough to support the required SQL queries. Concrete examples from the database are required to justify your statement.• Whether it is necessary to implement the property address as a composite attribute for the required SQL queries. Concrete examples from the database are required to justify your statement.SubmissionTo submit your assignment, you need to provide:1. A Microsoft Access database, which contains the required:• Relations with sample data• SQL queries• The query report2. The Implementation document detailed in a Word documentYou must submit all the required files in a zip file via the online submission system. Any email or hardcopy submission will not be accepted. Any late submissions of assignment will not be accepted after the marked assignment have been released.The marking criteria of this assignment are detailed in the following table.Marking Criteria Available MarksDatabase implementation 61. Coverage of the business data structures of The Best Home 22. Enough sample data are populated for the required queries 23. Naming of relations is clear; use of data types is correct 2SQL query 21Query-1 2Query-2 2Query-3 2Query-4 3Query-5 3Query-6 3Query-7 3Query-8 3The query report 41. The underlying SQL query for the report is correct 22. The report layout is appropriate 2The implementation document 41. How to populate data for multi-valued attributes 22. Whether it is necessary to implement the property address as a composite attribute 2Sub Total for Assignment-2 35Late Penalty -1.75 (5%) each calendar day(either full or part)Plagiarism PenaltyTotal for Assignment-2

YOU MAY ALSO READ ...  Topic: What William Morris means by useful work and useless toil, and why he thinks the latter is “mere toiling to live, that we may live to toil” – Original Paper

Assignment status: Already Solved By Our Experts

(USA, AUS, UK & CA Ph. D. Writers)

CLICK HERE TO GET A PROFESSIONAL WRITER TO WORK ON THIS PAPER AND OTHER SIMILAR PAPERS, GET A NON PLAGIARIZED PAPER FROM OUR EXPERTS

Order from Australian Expert Writers
Best Australian Academic Writers

QUALITY: 100% ORIGINAL PAPER – NO PLAGIARISM – CUSTOM PAPER