SQL - Diagram with primary and foreign keys

You have been in search of an internship for months now. Finally something has become available. A local apartment rental company College Park Apartment Rentals, needs a database. You are incredibly excited about the opportunity so you schedule your first interview.

On a snowy day in January you meet with the owner Ron, who seems rather flustered. He is trying to get rental data together for his accountant and cant seem to locate all of the necessary paperwork. He sighs and tells you, how glad he is to see you because he needs this database yesterday.

During your meeting Ron explains to you what they are trying to keep track of. He mentions that they have 5 apartment rental buildings that they maintain and rent out. These buildings vary in what amenities they offer, size and how many units they hold. The amenities can include but not limited to a pool and clubhouse, workout room, general meeting room, etc. Each building has an address.

Additionally you question Ron about the apartments themselves. The apartments vary in size and floor plan. Keeping track of the square footage or size of the apartment is important to Ron. Apartments can feature between 0 (Studio) and 3 bedrooms and 1 to 2 bathrooms. All units come with a refrigerator and stove but not all units feature a dishwasher. Some units come furnished and some do not. Some units have balconies or patios and some do not. After further discussion Ron basically describes these as amenities and adds that other amenities can include hardwood floors, covered parking, storage units, washer & dryer, some utilities are paid, etc. He shows you on his laptop his website were you begin to scroll through the different buildings/properties and the different units.

Additionally Ron mentions that the new database needs to keep track of the renters. All renters have contact information (names, addresses, phones, etc.). At this point Ron hands you a Lease Application form. After glancing at it briefly you see that the database will also need to keep track of each tenants place of employment, social security number and drivers license number. Ron further explains that before someone can rent one of his units he needs to run a credit report. If the credit report comes back with a score of 200 or below, he denies the application. Additionally the applicant must have worked a full time job for at least a year. If the individual renting has not, then they need to get a co-signor. Lastly Ron needs to know the names of all who will be living in the apartment. These are individuals that will not be on the lease but living in the apartment. Finally there can be more than one person on the lease. Once the tenant(s) is/are approved, a Lease contract is signed that details the building address, apartment number, amount of rent per month, names of the individuals leasing the appartment and lease effective dates. Ron gives you a sample copy of a lease.

Finally Ron needs to the database to keep track of rent. All rent is due on the first of each month. Ron allows a 5 day grace period where the rent can be turned in and not be charged a late fee. After the 5 days, the renter is charged a late fee. Ron then shows you an Excel spreadsheet that lists the names of the individuals on the lease, the apartment building, apartment number, the amount of the rent, the date paid, the check number and the late charge (if any).

Before you leave your meeting, you ask Ron what reports he needs to see. Ron mentions that he needs to see a report that shows who has not paid rent. He would like to run this on the 4^th of each month and then again on the 6^th. He also needs a report to show how many units per building are being rented and the total rent collected for each building. He will need to run this report periodically throughout the year. Finally he would like a report to show him what units are available per each building. He wants to run this as an ad-hoc report anytime someone is interested in renting from him