You have been hired to design the data model for an accounts receivable system for a homeowner’s association. Through an interview, you have determined the following:

Homeowners Information System
You have been hired to design the data model for an accounts receivable system for a
homeowner’s association. Through an interview, you have determined the following:
• The neighborhood consists of 212 properties with lots numbered from 1 to 212.
• Lots range in size from 1 to 5 acres.
• The association charges the homeowners dues every year on each lot. Dues tend to
increase each year. In 2022 they are expected to be $450 per lot.
• Bills are mailed to the homeowners once per year.
• Some people own more than one lot
• Most lots have houses on them but some are still vacant
• The owner of a lot or lots may not live in the neighborhood
• Dues can be paid monthly, quarterly, or annually
The completed system should track:
• Who owns which lots
• Which lots are built on and which are not
• Phone numbers and addresses of owners
• The size of the lots in the neighborhood
• A history of amounts billed and payments made for each owner in the system.
• The association only accepts checks as payment.
• The billing and payment history should include owners that may no longer own a lot
but did at one time
Your design should be reasonably flexible. For example, don’t assume that there will
always be exactly 212 lots. More lots could be added, or some removed.
Required:
• Either draw by hand or use some sort of diagramming software (such as Microsoft
Visio) to draw a Data Model Diagram for the above data. Your diagram must
include the following:
o A name for each table
o A list of column names for each table.
o A primary key designated for each table (at the top)
o Appropriate foreign keys for each relationship
o Minimum and maximum cardinalities for each relationship.
o A short descriptive word or phrase for each direction of each constraining
relationship between entities.
o Your design should be normalized (no violations of 1st, 2nd, 3rd, BCNF, or
4th normal form.)
• For each table, include 3 or more rows of example sample data. Your sample data
could be hand-drawn or (more preferably) you can put your sample data in an
excel spreadsheet.
o Your sample data should reflect the cardinalities of the relationships. For
example, if you have a 1 to many relationship from table A to table B,
then you should have at least one PK of A that appears in B multiple
times.
• Upload your DMD diagram and sample data to D2L by the posted deadline
Test Queries:
The following queries will help determine if you have a workable data model. You don’t
have to actually write these queries as a deliverable, but thinking about how your schema
design can support your queries should help detect errors.
Retrieve:
1. The name and address of the current owner of lot 34.
2. The name and address of all current and past owners of lot 34.
3. The date and amount of every payment ever made by a particular owner.
4. All payments made