Textbook :Use Modern Database Management
8th,9th or 10th Edition by Jeffrey A. Hoffer, V. Ramesh and Hiekki Topi
Please draw an E-R diagram for the Home Sweet Home case (see pages 2-7).
Use the Supertype and Subtype concepts (see Chapter 4 [8th or 9th edition] or Chapter 3 [10th edition]) if needed. State any assumption you have when developing the diagram.
Submit the printout of the following 2 items in class on the due date:
1. Assignment #1 (database
E-R Modeling of Home Sweet Home
2. The E-R diagram and assumptions, if any, for Home Sweet Home case. Use a computer-based graphic tool to draw your diagrams such as VISIO or Gliffy (visit http://www.gliffy.com).
3. You also need to show the attributes for each entity on your E-R diagram. If you run out of space to show all the attributes in your diagram, you can indicate only the identifier of each entity on your E-R diagram. In this case, however, you also need to turn in a list of all attributes for each entity in an Excel worksheet.
This case study describes a company called Home Sweet Home, which specializes in the management
of properties for rent on behalf of the owners. The company offers a complete service to owners who wish to rent out their furnished property. The service provided by Home Sweet Home includes advertising the property in the local or national press (when necessary), interviewing prospective renters, organizing visits to the property by prospective renters, and negotiating the lease agreement. Once rented, Home Sweet Home assumes responsibility for the property, which involves regular property inspections by Home Sweet Home staff. Listed below is a description of the data recorded, maintained, and accessed at each branch office to support the day-to-day operation and management
of Home Sweet Home.
Home Sweet Home has several branch offices located throughout the United States. Each branch office is identified by a unique branch number and has an address (street, area, city, zip code), telephone number and fax number. Each branch office has members of staff.
Each Home Sweet Home branch office has a manager responsible for overseeing the operations of the office. Home Sweet Home closely follows the performance of its managers, and notes the date that they assumed their position at their current branch office. Each manager is allocated an annual car allowance and a monthly bonus payment based upon the branch’s performance.
Each Home Sweet Home branch office has members of staff with the job title of Supervisor (sometimes called Senior Administrator). Supervisors are responsible for the day-to-day activities of a dedicated group of staff (minimum of five and a maximum of ten members of staff) responsible for the management
of property for rent. The administrative work of each group of staff is supported by a secretary.
Each member of staff is given a staff number, unique across all branch offices. Information held on each member of staff includes the name (first and last name), address (street, city, zip code), telephone number, gender, date of birth, social security number (SSN), job title (position), salary, and the date the member of staff joined Home Sweet Home. Additional information held on staff with the job title of Secretary is the typing speed.
It is company policy to record the details of the next-of-kin of members of staff, including the next-of-kin’s full name, relationship to the member of staff, address (street city, zip code), and telephone number. Only the details of a single next-of-kin are held for each member of staff.
An example of the Home Sweet Home form used to record the details of a member of staff called John White based at the Chicago branch office is shown in Figure 1.
Home Sweet Home
Staff Number 12
First Name John Last Name White
Address 750 Halsted, Chicago IL 60607 Gender Male
Tel No Date of Birth 05/05/1975
Full Name Mary White Address 750 Halsted, Chicago IL 60607
Relationship Wife Tel No
General Work Details Managerial Staff
Position Manager Date Start 07/19/2009
Allocated to Branch Office Chicago Salary 55,000
Branch Number 3 Car Allowance 2000
Joined Company 10/24/2008 Bonus Payment 1000
Secretarial Staff Typing Speed
Fig 1. Home Sweet Home Staff Details form.
Property for rent
Each Home Sweet Home branch office has properties for rent that are identified by a property number, which is unique across all branch offices. The details of property for rent include the full address (street, area, city, Zip code), type of property, number of rooms, and monthly rent. The monthly rent for a property is reviewed annually. Most of the properties rented out by Home Sweet Home are condos. Each property for rent is assigned to a specific member of staff who is responsible for the management
of that property. A member of staff may only manage a maximum of 10 properties for rent, at any one time.
When a property is withdrawn from Home Sweet Home and is no longer available for rent, it is a company policy to retain the information associated with this property a minimum of three years. An example of a Home Sweet Home report listing the details of properties for rent available at the Chicago branch office is shown in Figure 2.
1 Home Sweet Home
Property for Rent Date 05/12/2008
Branch Number 3 Telephone Number
Branch Office Address 500 W. Racine Ave.
Chicago, IL 60607 Fax Number
Property Number Street Area City Zip code Type No of Rooms Monthly Rent
PG4 10 E. Eighth St. Downtown Loop Chicago 60605 Condo 3 2200
PG36 20 E. Ontario St. Downtown Loop Chicago 60611 House 5 3500
PG21 800 W. Racine Ave UIC Chicago 60607 Condo 3 1200
PG16 4500 Marine Dr. Irving Park Chicago 60657 House 3 2200
Fig. 2 Home Sweet Home report listing properties for rent.
Home Sweet Home manages property for private or business owners. Each private owner and business owner is uniquely identified by an owner number, which is unique across all branch offices. Additional information on private owners includes the owner’s name, address (street, city, and zip code), and telephone number. The details of business owners include the name of the business, the type of business, business address (street, city, and zip code), telephone number, and contact name. An example of the Home Sweet Home form used to record the details of a single property for rent and the owner was shown in Figure 3. One owner may list more than one property for rent; and each property is assumed to be owned by a single owner.
When a renter first contacts a Home Sweet Home branch office, his or her details are recorded. This includes the renter’s name (first and last name), address (street, city, and zip code), telephone number, preferred type of accommodation, and the maximum rent the renter is prepared to pay. As a prospective renter, each renter is given a unique number called the renter number, which is unique across all branch offices. It is Home Sweet Home’s company policy to interview all prospective renters wishing to rent property. The information recorded about each interview is the date of the interview, the member of staff who conducted the interview and any general comments about the prospective renter. An example of the Home Sweet Home form used to record the details of a prospective renter called Mike Ritchie was shown in Figure 4.
Home Sweet Home
Property for Rent Details
Property Number 21
Address 800 W. Racine Ave Allocated to Branch 500 W. Racine Ave.
Chicago, IL 60607
Area UIC Branch No 3
City Chicago Staff Responsible Ann Beech
Zip Code 60607 Rent 1200
Type Condo No of Room 3
Name Carol Farrel Business Name
Address 550 W. Cornelia Ave. Address
Tel No Tel No.
Owner No 87 Owner No.
Fig 3. Sales Department forms: Property for Rent Details form.
Home Sweet Home
Renter Number 74
First Name Mike Last Name Ritchie
Address 1000 Taylor St.
Chicago, IL 60607 Tel No
Property Requirement Details
Preferred Property Type Condo Maximum Monthly Rent 1300
General Comments Currently living at home with parents.
Getting married in August
Seen By Ann Beech Date 03/24/2008
Branch No 3 Branch City Chicago
Fig 4. Sales Department forms: Renter Details form.
In most cases, a prospective renter will request to view one or more properties before renting. The details of each viewing are recorded and include the date of the viewing and any comments by the prospective renter regarding the suitability or otherwise of the property.
In the case of properties that prove difficult to rent out, Home Sweet Home will advertise these properties in local and national newspapers. For each advertisement, the company holds the details of the property being advertised, the date and cost of the advertisement. Only appropriate newspapers are used to advertise Home Sweet Home’s properties and the details of each newspaper used by the company include the newspaper name, address (street, city, and zip code), telephone number, fax number, and contact name.
Home Sweet Home is responsible for drawing up the terms of the lease (rental) agreement between a client and a property. The lease agreement records the lease number, the details of the renter, the details of the property including the monthly rent, the method of payment, the rental deposit, whether the deposit is paid, the date the rent starts and finishes, the duration of the lease, and the member of staff who arranged the lease. The minimum and maximum duration for a single lease period are three months and one year, respectively. The lease number is unique across all branch offices. Home Sweet Home’s clients can rent out one or more properties, at any one time.
When a lease agreement expires between a client and a property, it is Home Sweet Home’s policy to retain this information for a minimum of three years. An example of the Home Sweet Home form used to record the details of a lease agreement between a renter called Mike Ritchie and a property located in Chicago was shown in Figure 5.
Home Sweet Home
Lease Number 10012
Renter No 74 Property No 21
Full Name Mike Ritchie Address 800 W. Racine Ave
Address (Previous) 1000 Taylor St.
Chicago, IL 60607
Monthly Rent 1200 Rent Start Date 07/01/2007
Payment Method Check Rent Finish Date 06/30/2008
Deposit 1800 Duration 1 Year
Paid (Y or N) Y
Fig 5. Lease Details Form.
As part of the service to property owners, Home Sweet Home is responsible for undertaking regular inspections of property to ensure that the property is being correctly maintained. Each property is inspected at least once over a six-month period. However, Home Sweet Home staff is only required to inspect property that is currently being rented or is available for rent. For each inspection, the company notes the details of the property, the date of the inspection, and any comments regarding the state of the property given by the member of staff undertaking the inspection. An example of a property inspection report is given in Figure 6.
Page 1 Home Sweet Home
Property Inspection Report Date 05/18/2008
Property Number 21 Allocated to Branch 500 W. Racine Ave.
Chicago, IL 60607
Address 800 W. Racine Ave Branch Number 3
Property Type Condo
No of Rooms 3
Staff Name Inspection Date Comments
Susan Brand 04/12/2006 No Problems
Susan Brand 09/30/2007 Cracked ceiling in living room.
Required urgent repair.
Ann Beech 03/01/2008 Kitchen floor needs to be replaced.
Fig. 6 Home Sweet Home Property Inspection report.
[ Order Custom Essay ]
[ View Full Essay ]