Dream Home Real Estate Case Study
This case study has been adopted from the DreamHome case study in “Database Systems A practical approach to design, implementation and management” by Thomas Connolly and Carolyn Begg.
DreamHome is a popular real estate chain which has branches in many cities and towns across the country. DreamHome specialises in managing rental properties.
KEY DETAILS
Branches
Each branch office is allocated members of staff including a Manager to manage the operations of the office. The data held on a branch office includes a unique branch number, address (street, city, and postcode), telephone numbers (up to a maximum of three), and the name of the member of staff who currently manages the office. Additional data is held on each Manager, which includes the date that the Manager assumed his or her position at the current branch office, and a monthly bonus payment based upon his or her performance in the ¡®property-for-rent¡¯ market.
Staff
Members of staff with the role of Supervisor are responsible for the day-to-day activities of an allocated group of staff called Assistants (up to a maximum of 10, at any one time).
Not all members of staff are assigned to a Supervisor. The data stored on each member of staff includes staff number, name, address, position, salary, name of Supervisor (where applicable), and the details of the branch office at which a member of staff is currently working. The staff number is unique across all branches of DreamHome.
Properties for rent
Each branch office offers a range of properties for rent. The data stored on each property includes property number, address (street, city, postcode), type, number of rooms, monthly rent, and the details of the property owner. The property number is unique across all branch offices. The management of a property is assigned to a member of staff whenever it is rented out or requires to be rented out. A member of staff may manage a maximum of 100 properties for rent at any one time.
Property owners
The details of property owners are also stored. There are two main types of property owner: private owners and business owners. The data stored on private owners includes owner number, name, address, and telephone number. The data stored on business owners includes name of business, type of business, address, telephone number, and contact name.
Clients
DreamHome refers to members of the public interested in renting property as clients. To become a client, a person must first register at a branch office of DreamHome. The data stored on clients includes client number, name, telephone number, preferred type of accommodation, and the maximum rent the client is prepared to pay. Also stored is the name of the member of staff who processed the registration, the date the client joined, and some details on the branch office at which the client registered. The client number is unique across all DreamHome branches.
Leases
When a property is rented out, a lease is drawn up between the client and the property. The data detailed on the lease includes lease number, client number, name and address, property number and address, monthly rent, method of payment, an indication of whether the deposit has been paid (deposit is calculated as twice the monthly rent), duration of lease, and the date the lease period is to start and finish.
Advertisements
When required, the details of properties for rent are advertised in local and national newspapers. The data stored includes the property number, address, type, number of rooms, rent, the date advertised, the name of the newspaper, and the cost.
Examples of queries required
1) List the details of branches in a given city.
2) Identify the total number of branches in each city.
3) List the name, position, and salary of staff at a given branch, ordered by staff name.
4) Identify the total number of staff and the sum of their salaries.
5) Identify the total number of staff in each position at branches in Glasgow.
6) List the name of each Manager at each branch, ordered by branch address.
7) List the names of staff supervised by a named Supervisor.
8) List the property number, address, type, and rent of all properties in Glasgow, ordered by
rent.
9) List the details of properties for rent managed by a named member of staff.
10) Identify the total number of properties assigned to each member of staff at a given branch.
11) List the details of properties provided by business owners at a given branch.
12) Identify the total number of properties of each type at all branches.
13) Identify the details of private property owners that provide more than one property for rent.
14) Identify flats with at least three rooms and with a monthly rent no higher than ¡ê350 in
Aberdeen.
15) List the number, name, and telephone number of clients and their property preferences at a given branch.
16) Identify the properties that have been advertised more than the average number of times.
17) List the details of leases due to expire next month at a given branch.
18) List the total number of leases with rental periods that are less than one year at branches in
London.
19) List the total possible daily rental for property at each branch, ordered by branch number.
20) List details of staff supervised by a named Supervisor at the branch.
21) List details of all Assistants, alphabetically by name at the branch.
22) List the details of property (including the rental deposit) available for rent at the branch, along with the owner¡¯s details.
23) List the details of properties managed by a named member of staff at the branch.
24) List the clients registering at the branch and the names of the members of staff who
registered the clients.
25) Identify properties located in Glasgow with rents no higher than ¡ê450.
26) Identify the name and telephone number of an owner of a given property.
27) List the details of comments made by clients viewing a given property.
28) Display the names and phone numbers of clients who have viewed a given property but not
supplied comments.
29) Display the details of a lease between a named client and a given property.
30) Identify the leases due to expire next month at the branch.
31) List the details of properties that have not been rented out for more than three months.
32) Produce a list of clients whose preferences match a particular property.