Question

John owns a portfolio of rental properties with his parterns. He wants to develop a database to store property information as well rental data. Below are some of the data that need to be kept track of.

For each property, the database needs to store property address, size, number of rooms and bathrooms, and purchase date.

There are two types of properties – single family house and condo. Need to store yard size for single family house and monthly HOA(Home Owner Association) fee for condo.

For current and potential customers, need to store customer name, current address, family size, and contact phone number.

A customer needs to submit an application to rent a property. Need to track application submission date, potential start date, application result and explanation.

Once approved, a lease will be signed and need to track start date, end date, sign date, montly rent, and deposit amount.

Need to track montly payment information including payment amount, pay date, and payment method.

A customer can set up different payment methods. Each method includes financial institute name, address, and account number. There are two types of methods – credit card and check. For credit card, need to store expiration date and security code. For check, need to store bank routing number.

Please complete the following tasks to design the database to meet requirements specified. State any relevant assumptions you make, but do not add any additional data requirement unless it is really necessary.

Identify entities and attributes. List entity name and attribute names for each entity.

Answer 1

Entities and their attributes :

(a) Entity Name : Property

Property's Attributes : property address, size, number of rooms and bathrooms, purchase date , types of property.

(b) Entity Name : Customers

Customer's Attributes : customer name, current address, family size, contact phone number.

(c) Entity Name : Application

Application's Attributes : submission date, potential start date, application result , explanation , isapporoved , start date, end date, sign date, montly rent, deposit amount.

(d) Entity Name : Payment

Payment's Attributes : payment amount, pay date, payment method - ( credit card - ( expiration date , security code , financial institute name, address, account number ) , check - ( bank routing number , financial institute name, address, account number ) )