Reference no: EM132786890
Modeling Basics~ College Park Apartment Rentals
General Instructions
Read the Case Description below. Once you understand the case start formulating your entities and attributes. You can draw these out on paper as a rough draft and then transfer them to LucidChart. There is a template in LucidChart that you can use for this assignment if you want. You can also create a brand new file and select the ERD template.
Decide on a primary key for each entity. Determine how your entities relate to one another and draw the relationship lines. If there is something about the case that needs further explanation, please post your questions in the weekly discussion forum.
Case Description
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 can't 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 tenant's place of employment, social security number and driver's 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 apartment 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 4th of each month and then again on the 6th. 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.
After your meeting, Ron then takes you for a short tour of one of the buildings to show you a model unit. You are quite impressed with the condition of the building the grounds and the apartment.
After your tour, you collect all of the documents Ron has given you during the meeting and you drive home to start putting together a draft ERD.
Finishing Up
1) Now that you have read the case, start composing your entities and the attributes.
2) Assign a primary key to each entity.
3) Determine which entities relate to one another and place your foreign keys appropriately.
4) Determine the type of relationship between your entities and draw those in.
5) Read the rubric to make sure you have included everything. Go back and double check your model. Make sure all many to many relationships are properly broken down to one to many. Make you're your attributes are attributes and not data.
6) This diagram needs to be readable. What do I mean by ‘readable'? The normal lay person should be able to understand what you are trying to do. Are you lines convoluted? Is it hard to trace how one entity relates to the other? Have you adequately described each entity? Does the diagram fit on one page? Does your diagram have your name on it?
7) Make sure your diagram can fit on one page. Export your LucidChart file to a PDF and upload it to Canvas.
8) Points will be deducted for models that are sloppy, do not have a name on them or do not fit on one page.
Attachment:- Guidesheet.rar