Question 2: Design a database. (30 Points) John owns a portfolio of rental properties with his parterns. He wants to develop a database to store...

Question 2:

Design a database. (30 Points)

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.

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

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

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

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

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

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

G. 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 needs to store bank routing number.

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

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

                       II.        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).

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

                    IV.        Create ERD using crow's 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).