Beach Rentals (BR) maintains an inventory of rental houses near universities and leases those houses to student renters. This case describes their rental business process. BR agentsformer marketing majors renowned for their fast-talking and flamboyant lifestyleswork with potential renters and sign the rental contracts for BR.

BR tracks its houses by city, neighborhood, and distance from campus. BR assigns one specific BR agent to each neighborhood to manage rentals for all houses in that neighborhood, but each BR agent may be assigned to multiple neighborhoods. BR cashiers collect the rent and are bonded for security purposes. Because cashiers never become agents (or vice versa), BR tracks BR cashiers separately from BR agents, although both are identified by employee numbers.

BR sets rental rates to its student customers by considering such matters as number of bedrooms and age of the house. Additionally, BR applies a monthly rental surcharge to each house that depends solely upon its neighborhood designation; for example, upscale neighborhoods have higher surcharges and less desirable neighborhoods have lower surcharges. The same surcharge applies for the life of the lease. Every house has a rental surcharge, and all houses in a particular neighborhood have the same surcharge.

Prospective renters, usually students, contact BR to inquire about renting a house. When a potential renter contacts BR, a BR agent is assigned to assist him or her. That BR agent remains the persons point of contact for as long as he or she continues to deal with Beach Rentals. BR records information on each potential renter as soon as he or she contacts BR to inquire about a house.

BR agents negotiate the rental contracts with the students. Each rental contract must last at least 6 months, and 12-month contracts get a 5 percent discount. BR also charges a damage fee that is due along with the first months rent when the rental contract is signed. The BR agent earns a 10 percent commission on each rental contract, and BR tracks the year-to-date (YTD) commission earned for each of the BR agents. Of course, the BR agents compete with one another to see who earns the highest commissions, and BR fosters the competition by giving an annual award to its best agent.

When multiple students want to rent one house, BR requires that they designate the primary renterthe one who will be responsible for paying the rent. BR also gathers information about all the other occupants of the house and designates them as secondary renters. All the student renters, however, sign the rental contract, and BR assigns a unique renter number to each occupant. The students may not change primary renter for the term of a contract. BR cashiers collect the rental payments monthly from the primary renters. BR records information concerning employees, house owners, bank accounts, and neighborhoods in the database before the renters are involved in any events.

• Using the preceding information, the following attributes (i.e., columns/fields) list, and MS Access, prepare the relational tables necessary to support this sales and collection process. You should define the relational tables (Name of tables and columns, PKs) and establish the relationships among tables necessary to implement this sales and collection process in Access.

Attributes:

• Agent employee#
• Agent name
• Agent real estate license status
• Bank account#
• Bank account balance
• Bank name and address
• Cash receipt $ amount
• Cash receipt#
• Cashier employee#
• Cashier name
• City name
• Damage fee
• House street address
• House zip code
• Monthly rent
• Neighborhood name
• Number of bedrooms
• Number of houses in this city
• Rent discount for 12-month contract
• Rental contract#
• Rental contract begin date
• Rental contract duration in months
• Rental surcharge amount
• Renter bank and routing numbers
• Renter name
• Renter number#
• YTD rental commissions