Design a database. (30 Points)

John owns a portfolio of rental properties with his partners. 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 the single-family house and monthly HOA(Home Owner Association) fee for a 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, an explanation.

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

Need to track monthly payment information including payment amount, payment 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 the 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.

Identify relationships among entities. List relationship type in terms of cardinality and specify business rules (e.g. 1: M between Library and Branch: a library has many branches; a branch belongs to one and only one library).

For each entity, identify primary key and foreign key if applicable. If you use a surrogate key for PK, also specify a business unique key (natural key). For FK, also specify the parent entity and the matching attribute in the parent entity.

Create ERD using crows foot notation using ER Assistant or Visio. The ERD should incorporate all items mentioned in I, II, and III (NOTE: I, II, and III still need to be answered separately in narrative format).