ER/EER Mapping

     Sewana Property Renters Case Study

Consider the following scenario given with respect to a Property Renter.

The first branch office of Sewana Property Renters was opened in 1979 in Nugegoda. Since then,

the company has grown steadily and now has several offices in most of the main cities of Sri

Lanka. However, the company is now so large that more and more administrative staff are being

employed to cope with the ever-increasing amount of paperwork. The Director of the company

feels that too many mistakes are being made and that the success of the company will be

short-lived if he does not do something to remedy the situation. He knows that a database could

help in part to solve the problem and has requested that a database system be developed to

support the running of Sewana.The following description delivered by the director of the Sewana

Property Renters will help you to build the database system for the company.

Sewana specializes in property management, taking an intermediate role between owners

who wish to rent out their furnished property and clients of Sewana who require to rent furnished

property for a fixed period. Sewana currently has about 200 staff working in 11 branches. One

district has only one branch in their main city and those branches are responsible to cover the

whole district. Property owners and Clients can contact Sewana branch officers via phone call,

emailing or mailing.

When a member of staff joins the company, the Sewana staff registration form is used.

Each branch has an appropriate number and type of staff including a Manager, Supervisors, and

Assistants. Each branch is managed by the Manager of the branch. A manager can only manage

one branch. The Manager is responsible for the day-to-day running of a branch and each

Supervisor is responsible for supervising a group of staff called Assistants. Assistants can be

promoted as Supervisors and Supervisors can be promoted as Managers.Therefore Manager has

Manager number and appointed date. Supervisor has Supervisor number and appointed date for

ease of the employee management. With respect to each employee data such as EmployeeID,

Name, Salary, Gender, DOB,NIC, Contact number and Start date is maintained.

Each branch office offers a range of properties for rent. To offer a property through

Sewana, the property owner has to first register with any Sewana branch. Property owner can be

a person or a company. Person has NIC, name, email, contact number and address. A company

has company ID, Company name, email, contact number and address.The property should then

be registered at the district branch relevant to the property. An owner may have several

properties situated in any district. Owner can contact the branch manager and register a property

at another branch also. The owner provides the details of the property and agrees an appropriate

rent for the property with the branch Manager. Property has Property number, Type, Number of

rooms, proposed rental and Address of the property. Categories of Property types are Flat,

Annex, Bungalow and Commercial. Owner may have many properties which are in several

districts. Once a property is registered, Sewana provides services to ensure that the property is

rented out for maximum return for both the property owner and, of course, Sewana. These

services include interviewing prospective renters (called clients), organizing viewings of the

property by clients, advertising the property in local or national newspapers (when necessary),

and negotiating the lease. Once rented, Sewana assumes responsibility of the property including

the collection of rent. Members of the public interested in renting out property must first contact

their nearest Sewana branch office to register as clients of Sewana. However, before registration

is accepted, a prospective client is normally interviewed to record personal details and

preferences of the client in terms of property requirements. The registration form for a client will

capture the details of client number, full name, NIC, email and property requirements such as

requirement number, type of property, area, date willing to rent and maximum rent. A client may

have one or more property requirements. One client can register at one branch and registered

date and the name of the staff member should be specified in the client registration form.

Once registration is complete, clients are provided with weekly reports that list properties

currently available for rent.This report provides the details of the properties which are available

in branches which maps with the client requirement. If the client wants to get several weekly

reports from several districts he can contact the corresponding Sewana office branch. If the client

is interested in viewing or renting a property in the list, he can contact branch relevant to the

property to get a visit arranged with the owner. Sewana Property renters requires to keep the

records of the clients who have viewed the property with the date. Clients may request to view

one or more properties from the list. The system keeps track of such visits along with the date

visited. After a visit clients are requested to provide a comment on the suitability of the

property. Properties that are difficult to rent out are normally advertised in daily and weekly

newspapers. Staff needs to keep track of the date and advertisements. There are several news

newspapers that usually publish advertisements for Sewana property renters. Therefore they want

to manage newspaper details such as newspaper name, contact number, address and email etc.

When they publishing in daily newspapers they email the advertisement and for the weekly

newspapers they used to mail by post the advertisement. One advertisement may have several

properties. They publish the basic information about the properties in the newspapers with the

details about the branch office which the property has been registered. People who are willing to

view or buy the property can contact the branch office of Sewana. Once a client has identified a

suitable property, a member of staff draws up a lease. Lease form will capture the details of

client, property, monthly rent, payment method, rent starts and finished dates and the duration.

Payment can be done by card, cheque or cash. At the end of a rental period a client may request

the rental to be continued; however, this requires that a new lease be drawn up. Owner can

decide to increase or decrease monthly rent before drawing up new lease after discussing with

the client. If client wants to continue the rental, both owner and client should agree with the new

monthly rental. However owner cannot increase or decrease the monthly rent after renting starts

and monthly rent is fixed for a one lease. A client may also request to view alternative properties

for the purposes of renting. Then the client decides to rent a new property or to continue the

existing rental.

* Draw the ER/EER diagram and map the diagram.

Answer:

Diagram:

Relational Mapping:

1.employee (emp id, name, salary, c.num, start date, nic, gender, dob)

2.manager (manager number, emp id(fk), appointed date, branch num(fk))

3.supervisor (emp id(fk), supervisor number, appointed date)

4.assistant (emp id, assistant number, supervisor nimber(fk))

5.person (nic, name, email, contact num, address, owner id(fk))

6.company (company id, company name, address, email, contact num, owner id(fk)

7. property owner (owner id, branch num(fk))

8. branch (branch num, branch type, district)

9. property (property number, type, num of rooms, address, proposed rental, branch num(fk))

10. advertise on(property number, newspaper code)

11. newspaper details (newspaper code, newspaper name, contact number, address, email)

12. client (client number, property type, area, date willing to rent, register date, name of the staff member)

13. property requirement (property requirement num, client num, property req. name, branch num(fk), register date)

14. visit (property num(fk&pk), client num(fk&pk), visit date)

15. lease (branch num(fk&pk) client num(fk&pk), monthly rent, payment method, end date)

16. p.o register (branch num(fk&pk), owner id(fk&pk))

Assumptions:

1.Newspaper code has taken as newspaper’s primary key.

2.Assistant number has taken as assistant’s primary key.