Modeling Basics College Park Apartment Rentals Assignment
Modeling Basics College Park Apartment Rentals Assignment Question
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
Now that you have read the case, start composing your entities and the attributes.
Assign a primary key to each entity.
Determine which entities relate to one another and place your foreign keys appropriately.
Determine the type of relationship between your entities and draw those in.
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.
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?
Make sure your diagram can fit on one page. Export your LucidChart file to a PDF and upload it to Canvas.
Points will be deducted for models that are sloppy, do not have a name on them or do not fit on one page.
General Instructions
Download and review the Modeling Basics Guidesheet. Make sure you understand the case. Start the formation of your entities. Analyze the case to determine the relationships that exist between the entities. Make note of any business rules. Think about what attributes are associated with the entities.
Once you have a good idea of how this model needs to be drawn open LucidChart and find the Modeling Basics template in the Shared folder. Specific instructions on how to do this are listed below. Draw your entities and fill in as many attributes as you can. Draw in your relationship lines last.
Once your model is complete, make sure you look over the rubric to make sure you are not missing anything. Then export your diagram to a PDF and upload it to Canvas. Specific instructions on how to do this are listed below.
Creating your ERD in LucidChart
Open LucidChart and locate your My Documents folder.
Find the link that reads 'Shared with Me'.
Find the shared ITCS 1170 Templates folder and click on it to open it.
Click on the Modeling Basics Template.
This will open this file and prompt you to save it. Give your file a name.
Handing it all in
Review the Rubric prior to submission.
Make sure your diagram can fit on an 8 1/2 X 11 sheet of paper. Reference the Working with LucidChart document for help.
Click on the File menu and select Export.
Select .pdf
Upload your .pdf to Canvas.
Modeling Basics College Park Apartment Rentals Assignment Solution
