please follow the assignment requirements...all the tasks require proper documentation. university will not accept assignment Without documentation. Plagiarism free, Table of content and References should be provided.
School of Electrical Engineering & Computing
Degree Programme
Level: 5
Module: COM519 Database Administration and Optimisation
Assignment: 1
Issue Date: 25th June 2013
Review Date: Tutorial Sessions
Submission Dates
Task 1 & Task 2: 25th November 2011
Task3: 14th August 2013
Estimated Completion time: 48 Hours
Lecturer: Parvez Jugon
Verified by: John Davies
To be completed by student:
I certify that, other than where collaboration has been explicitly permitted, this work is the result of my individual effort and that all sources for materials have been acknowledged. I also confirm that I have read and understood the codes of practice on plagiarism contained within the Glyndwr Academic Regulations and that, by signing this printed form or typing my name on an electronically submitted version, I am agreeing to be dealt with accordingly in any case of suspected unfair practice. I also certify that my attendance for the module has been at least 70% Name: ..........................................................
Student Number: .........................................
Date Submitted: .........................................
Student Signature: ..........................................
If the assignment has been submitted electronically then please indicate the file pathname:
………………………………………………………………………………………………………………….………
To be completed by lecturer
Comments:
Grade / Mark
(Indicative: may change when moderated)
If Mitigating Circumstances are being claimed then please indicate your Code Number: ..........
REA Properties
This assignment aims to improve your design and programming skills in database systems. In particular, it requires you to exercise programming skills in SQL and PL/SQL under Oracle PL/SQL Developer or SQL*Plus tool.
You have been given the task of optimising the design and building a database using ORACLE RDBMS for a real estate agency in Australia. You are identified as the best database consultant they could hire by REA Properties to build a database to manage their property and customer details. Due to some unforeseen events the consultants who were working on this case has to halt the design and development half way through. You have been given a copy of their work including the design, schema and some scripts.
Assignment Description
The assignment is based on an imaginary database to manage information for a real estate company. The following gives an informal description of the requirements for the system:
You are dealing with an imaginary real-estate agency "REA Properties" that has branches in a number of suburbs in Australia, and deals with a range of properties, including domestic houses and units (both buying/selling and rental) and commercial properties (similarly, buying/selling and leasing). Assume that all branches are able to access the database, but don't worry about the details of how this is accomplished (treat it as if all users logged onto a single server which contains a single copy of the database).
• The database must keep track of information about REA's staff members, including their personal contact details and payroll information. Each staff member is associated with one particular branch, where they do all of their work.
• The database must maintain information about branches, including where they are located and which properties they handle (each property is entirely managed through one particular branch, but, of course, the database holds information about all properties from all branches). We also need to know who manages each branch.
• The database maintains information about properties, including location, owner and what kind of property it is (see below under constraints). REA also wants to record when a property is first listed by the company, whether it is being listed for sale or rent and which staff members (one or more) are the contacts for all matters related to this property (such staff members are called the "property managers" for that property).
• REA deals with a number of different kinds of clients: owners, buyers and renters. There are two types of property owners: private owners and business owners; in both cases we record the same kind of information, primarily their contact details. We also need this kind of information for buyers and renters, but for buyers we also wish to maintain details of the kind of property that they are looking for, and for renters we also need to maintain information about their rental agreement and payment record.
• Properties may be advertised in a newspaper. The database should record the main details for each advertisement (for example, which newspaper it appeared in, what the advertisement said, and when it appeared).
• Properties are occasionally made "Open for Inspection" (on a particular day for a particular time period), and the database must keep a record of such events, including ones scheduled for the future.
• Properties are offered for sale either by auction or privately. For auction sales, the database should record when and where the auction occurs and its result (when known). For private sales, the system should note the vendor's reserve price. For rental properties, the database should record information about the rental rate and current availability.
Constraints:
• People's names are stored in the format: first name followed by last name.
• The type of sale takes a value from {AUSD, PISD, AUHB}, where AUSD means "auction sold", PISD means "private treaty sold", and AUHB means "highest bid" (this is used for properties that are not sold at auction).
• Property types take a value from {House, Unit, TownHouse, Commercial}.
• Property addresses should be decomposed into street address, suburb, and post code.
Tasks
Task 1: Fine tune the existing Design
Produce an ER-Diagram and Relational Schema to satisfy the data requirements or REA Properties in the best possible way. You must use the standard notations used and discussed in the class.
Task 2: Build the database
Produce the script for creating the tables, make sure you have identified and defined all Primary Keys and Foreign Keys. Implement the database in the Oracle server here at Glyndwr University.
Task 3: Test your database
Make sure you can satisfy the data and application requirements of REA properties.
To test the database you have to produce the scripts and results for the following tasks.
Sample Data
In order to test the database you must have some sample data. You will be provided some sample data (as INSERT Queries devised by the consultants who were working on this project). These data/queries might not be suitable for your design, make sure you are using the given data as a starting point and it is your responsibility to make the necessary amendments to suit your design.
Task 3.1: Oracle SQL
You should use correct SQL statements in Oracle to implement the following queries and data modifications.
In all cases, names should be displayed in the format FirstName LastName (e.g. a person with LastName='Smith' and FirstName='John' would be displayed as John Smith) in a column labelled NAME. Similarly, addresses should all be displayed in the format Street Suburb PostCode in a column labelled ADDRESS.
Each query and update should be implemented by ORACLE SQL.
Select Queries
1. List the properties which are not advertised nor has open inspections.
Some students raise the problem that this question could have two interpretations. We would like to clarify that this question is asking for properties that are not listed in either of advertisement and in open inspections.
2. List the owners who own a property in Kingsford or Coogee.
3. List the properties that are still on the market for lease.
A property is still on the market for lease if it is for lease and there is no rental contract on this specific property.
4. List all properties which are at a suburb starts with "C", which may be recorded in the database in either capital or lower case.
5. List the staffs who manage at least one commercial property in Coogee.
A staff is managing a property if he/she is the contact person for this property.
6. For each suburb, list the difference between average price of properties sold via auction and the ones sold by private sale in that suburb in the last 365 days.
7. List the features for each house whose asking- or reserve-price is less than $200,000.
8. List the properties (which are for private sale or auction) whose asking- or reserve-price is lower than at least one of the offers received for this property.
9. List the staffs who have sold the greatest number of properties in the last 365 days.
A property is sold by a staff if these staffs are the contact person of the property which is recorded in the Contact table.
10. For each property that was sold in last 365 days and have not been either advertised or open for inspection in last 365 days, list its features.
We only consider those properties which are not listed in either of the advertisement and open inspections in the last 365 days.
Update Queries
1. Remove the advertisements in newspaper "Sydney Morning Herald".
2. Give renters in Maroubra a 10% reduction in rent but terminate their agreements on June XXXXXXXXXXNote: "renters in Maroubra" means the properties are in Maroubra.
3. Increase the asking price by 10% for the properties for private sale that are still on the market.
Guidance
Students will get assistance to complete the tasks through the tutorial sessions. Drafts will be reviewed and formative feedback will be given in the tutorial sessions. So you are much less likely to obtain a good grade if you don’t attend the tutorial sessions.
All task of the assignment must be submitted via Moodle by 14th August 2013.
Failure to complete any of the above tasks in time will result in a loss of marks
Submission
The assignment should be word-processed. Diagrams must be neatly drawn using the standard notation discussed in the class. This specification document should be filed at the front of the assignment, with the front sheet (with your Name, Student Number, Date and Signature) visible at the front.
Work must be submitted via Moodle by according by the following dates
1. Task 1 and Task 2 : by 25th July 2013
2. Task 3 : by 14th of August 2013
The Glyndwr policy on assignment submission will be rigidly adhered to (see your Student Handbook).
You are required to submit the following:
For Task 1 & Task 2
• Word processed file including
o Your Entity Relationship Diagram, entity listing, constraints and assumptions
o Your schema definitions, indicating key fields (both primary and foreign).
o Your SQL code required to create the tables (including code for integrity rules).
For Task 3
You must implement the database in accordance with your design (task 1 and Task2) in the ORACLE server here at Glyndwr University. You should document the CREATE Table queries in a Text file or Word document. You must populate enough sample data (use INSERT Queries). The INSERT queries must be documented in a Text file or word processed file. All the SELECT queries must be documented with the results. All Procedures must be documented and must be tested. The results need to be documented in the text or word processed file.
• Text or Word processed file including the
o Your SQL code required to create the tables (Including code for integrity rules).
o Your SQL code required to insert the data
o Select Queries with Answers (showing the SQL code and the result.)
o Update Queries showing the result after the update (you may use a select query to list the updated rows)
Learning Outcomes
1. Analyse the requirements, design, identify violations of normalisation rules in the design and implement an appropriate database solution using SQL, PL/SQLfor a problem of defined scope.
2. Plan and organise the database administration tasks for the database including security issues.
3. Appraise the tools and tasks required to optimize the running of large databases and the methods used to deal with these problems
Transferable/Key Skills and other attributes:
1. Solve problems using appropriate tools/methods;
2. Evaluate alternatives.
3. Use computing software effectively
Assessment Criteria
In order to achieve an A grade, the work must be excellent in almost all respects, only very minor limitations.
In order to achieve a B+ grade, the work should show strength in most respects, but perhaps has limitations in one or two areas. A good piece of work nevertheless.
In order to achieve a B grade, the work should be of a satisfactory standard, showing strength in some areas, but perhaps let down by poor presentation, poor practical work, or poor written explanations where required.
In order to achieve a C grade, the work should be of a satisfactory standard but may have significant shortcomings in some areas. Nevertheless shows at least a basic understanding of the concepts and a basic practical ability.
A Refer grade will be given to work that is just unsatisfactory.
The ER model from the initial analysis
Attributes derived from the above ER design:
The following tables were identified after the initial discussions:
1. Table Advertisement records advertisement details of properties.
2. Table Property records property details.
3. Table Inspection records property inspection details.
4. Table Features records features associated with a property or requested by a client, such as "three bedrooms", "LUG", etc.
5. Table Featured describes the associations between features and properties.
6. Table Person gives person details.
7. Tables Owner, Renter, Buyer, Staff are sub-classes of Person.
8. Table Branch records the details of each company branch.
9. Table Auction records the auction details.
10. Table Rental_Agreement records the rent contract details.
11. Tables Property_for_Private_Sale, Property_for_Auction, Property_for_rent are sub-classes of Property.
12. Table Contract describes associations among a property, a rental agreement, and a renter.
13. Table Required describes the features required by potential buyers.
14. Table Deal records the details of already sold property.
15. Table Offer records the purchasing prices offered by buyers under certain conditions.
16. Table Contact records the contact persons for each property.
The following Schema has been designed after the initial discussions:
Note: key fields are written in italic font in the description below. Any attributes whose type is not obvious from their name should be considered to be simply text information.
Type Fields
Address (street, suburb, postcode)
Name (firstname, lastname)
Entity Attributes
Advertisement ANo, Contents
Property PNo, Address, First_listed_date, Type
Inspection INo, Date, Time, Duration
Property for Private Sale Asking_price
Property for Auction Reserve_price
Property for Rent Rate, Start_date, Ending_date
Person PeID, Address, Name, Telephone
Staff DOB, Sex, Payroll_information
Buyer Price_range
Renter Payment_record
Owner Type
Branch BNo, Address, Phone
Auction AID, Date, Time, Venue
Features FID, Description
Rental_Agreement RID, Contents
Relationship Attributes
Has Date, Newspaper
Deal Date, Price
Offer Date, Price, Condition