Apartments CaseStudy Introduction: Round Beach Apartments is anapartment rental

Question

Apartments CaseStudy

Introduction:

Round Beach Apartments is anapartment rental business owned by Samantha Hill. Samanthahas a day job, and owns the three, small apartment buildings as aninvestment. The apartments are located in the Beach Citiesarea of SouthernCalifornia.

Samantha realizes she can nolonger afford the time to track everything on paper. She hasdecided to computerize her operations to a great extent.

Requirements:

Samantha has provided thefollowing information about her business. There are threeapartment buildings. She names the buildings by the digits intheir street address: 209, 1923, and 2101. The 209apartment building has 2 units, the 1923 building has 4 units, andthe 2101 building has 3 units. In all 3 buildings, apartmentunits are designated by letter: A, B, … , until allunits are named.

Each apartment unit may have oneor more tenants sharing occupancy of the unit.

Samantha's cash flow is alreadycomputerized. She uses Quicken to track her bank accountactivity; therefore, rental income and all her expenses are storedin Quicken. She uses the information from Quicken to fill outher 1040 schedule E federal income tax form.

However, there are some questionsshe needs the answers to from time to time, which Quicken cannotanswer or cannot easily answer. She needs this informationfor answering these questions to be stored in adatabase.

·        Who are the current tenants, whichapartment unit are they in, and when did they movein?

·        What is the security deposit for eachapartment unit? (This might change over time, even for the sametenants.) Who paid the security deposit?

·        What is each tenant's social securitynumber, previous address, emergency contact (name, relationship,phone number), date and time they met with Samantha as aprospective tenant to see the apartment and fill out an applicationto rent, and (after they move out) their next address (for sendingthe appropriate security deposit refund), plus how much securitydeposit was returned and when. Each tenant's email, phonenumber, and mobile phone number should also be tracked.

·        What is the rental rate history foreach apartment? In other words, when was the rent changed,and what is the new amount? (This information is used todetermine the annual rent increase for each apartment.)

·        What is the current gross rentalincome per building?

·        When was the last time a particularapartment had a major maintenance item performed? Examples ofmajor maintenance items include replacing all or part of a carpet,painting the inside of an apartment, painting the outside of anapartment building, replacing a fence, trimming a tree, andreplacing an appliance (refrigerator, stove, hot water heater, ordishwasher).

·        What vendor performed each item ofmajor maintenance? What is the vendor contactinformation?

·        How much did each major maintenanceitem cost?

Your Unit 4 projectshould have the following parts:

Explanation / Answer

  Apartments Case Study (TableStructure – Database Design)

Buildng(BldgID (PK), BldgName, NoOfUnits)

Unit(UnitID (PK), BldgID (FK), UnitName)

AddressDetail (AddressID(PK), AddressLine1, AddressLine2, City, State, Country, ZipCode,OtherDetails, ActiveRow)

ContactDetail (ContactID(PK), HomePhone, CellPhone, Email, SecondaryEmail, ActiveRow)

ProspectiveTenant(ProspectiveTenantID (PK), PTFName, PTLName, MeetinDate, AddressID(FK), ContactID(FK))

Tenant(TenantID (PK), UnitId (FK), TenantFName, TenantLName, SSN,EmerContactName, EmerPhoneNum, EmerRelationship, MovinDate,ProspectiveTenantID(FK), ContactID(FK), AddressID(FK),CurrentActiveStatus)

MovOutTenant (MovOutID(PK), TenantID (FK), AddressID (FK), ContactID (FK),SDRefundAmt, SDRefundDate)

SecurityDeposit (SDID(PK) ,UnitId (FK), TenantID (FK), SDPaidDate, SDMID (FK))

SecurityDepositMaster (SDMID (PK), UnitId (FK), EffectiveDate, SDMAmount,ActiveRow)

RentalCollection (RCID(PK), UnitId (FK), TenantID, RMID (FK), RentPaidMonth,RentPaidYear, RentPaidDate)

RentMaster(RMID (PK), ), UnitId (FK), EffectiveDate, RentCollectionAmount,ActiveRow)

VendorMaster (VMID (PK),VendorName, AddressID, ContactID)

MaintenanceActivityMaster(MAID (PK), MaintActivityName)

UnitMaintenance(M-M) (UMID (PK), UnitId (FK), MAID (FK), VMID (FK), Cost,ActPerformedDate)

-- DataType to be used

Buildng

(

BldgID int (PK),

BldgName varchar(32),

NoOfUnits int

)

Unit

(

UnitID Int (PK),

UnitName varchar(32)

BldgID int (FK - Building)

)

AddressDetail

(

AddressID int (PK),

AddressLine1 varchar(100),

AddressLine2 varchar(100),

City varchar(32),

State varchar(32),

Country varchar(32),

ZipCode varchar(10),

OtherDetails varchar(100),

)

ContactDetail

(

ContactID int (PK) ,

HomePhone,

CellPhone,

Email,

SecondaryEmail,

)

ProspectiveTenant

(

ProspectiveTenantID int (PK),

PTFName varchar(32),

PTLName varchar(32),

MeetinDate datatime,

AddressID int(FK - AddressDetail),

ContactID int(FK - ContactDetail)

)

Tenant

(

TenantID int(PK),

UnitId (FK - Unit),

TenantFName varchar(32),

TenantLName varchar(32),

SSN varchar(11),

EmerContactName varchar(32),

EmerPhoneNum varchar(10),

EmerRelationship varchar(32),

MovinDate datetime,

ProspectiveTenantID int (FK - ProspectiveTenant),

ContactID int ( FK - ContactDetail),

AddressID int (FK - AddressDetail),

CurrentActiveStatus bit (1 = active ,0 = inactive)

)

MovOutTenant

(

MovOutID int (PK),

TenantID int (FK - Tanant),

AddressID int (FK - AddressDetail),

ContactID int (FK - ContactDetail),

SDRefundAmt decimal,

SDRefundDate datetime

)

SecurityDeposit

(

SDID int (PK) ,

UnitId int(FK - Unit),

TenantID int(FK - Tenant),

SDPaidDate datetime,

SDMID (FK –SecurityDepositeMaster)

)

SecurityDepositMaster

(

SDMID int (PK),

UnitId int (FK - Unit),

EffectiveDate datatime,

SDMAmount decimal,

ActiveRow bit (1 = active ,0 = inactive))

RentalCollection

(

RCID int (PK),

UnitId int(FK - Unit),

TenantID int (FK – Tanant),

RMID (FK - RentMaster),

RentPaidMonth varchar(10),

RentPaidYear smallint,

RentPaidDate datetime

)

RentMaster

(

RMID int(PK),

UnitId int(FK - Unit),

EffectiveDate datetime,

RentCollectionAmount decimal,

ActiveRow bit (1 = active, 0= inactive

)

VendorMaster

(

VMID int (PK),

VendorName varchar(32),

AddressID int (FK - AddressDetail),

ContactID int (FK – ContactDetail

)

MaintenanceActivityMaster

(

MAID int(PK),

MaintActivityName varchar(32)

)

UnitMaintenance(M-N)

(

UMID int(PK),

UnitId int(FK - Unit),

MAID (FK - MaintenanceActivityMaster),

VMID (FK - VendorMaster),

Cost decimal,

ActPerformedDate datetime

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.