Database design for Wild Wood Apartments leasing and maintenance reporting

Wild Wood Apartments needs a database that is more efficient in its reporting of leasing, maintenance, and repairs from individual apartment managers to the central headquarters for review and approval. This database needs to be easy to use and accessible for apartment managers and have two basic sections, based on leasing and maintenance needs. Apartment managers can then input all requests, reports, and financial data into the system as it occurs, rather than filing monthly reports.

Issues with Current System

The current system has a number of issues. First and foremost, it requires individual apartment managers to manually fill out forms quarterly that includes any new or changes to rentals in his or her building, as well as reports regarding repairs and maintenance. This means that money spent on maintenance is not repaid until the end of the quarter, which can be frustrating for managers. Moreover, the forms are bulky and difficult to fill out for each and every incident at the end of the quarter. The database will increase the efficiency by storing certain apartment and leasing information, making it easier for managers to pull up what is already stored and then adding information as it occurs, rather than waiting till the end of the quarter to be bogged down by paperwork. The database will also help streamline the process so that individual managers are not overwhelmed with making decisions about certain events, as they are immediately uploaded to the headquarters for review.

Business Rules

Essentially, business rules "are rules about how the data are captured and used and what limits or constraints are placed on the data" (Cogner 51). Here, the primary business rules are that all interview data of the managers be handled by the IT department so that the can best design a database that will make it as easy as possible for managers to use. Interviews should not be passed around the home office, as it may instill bias against certain managers. Moreover, managers must be able to access the records of tenants and reports for their buildings, so that they can make adjustments and pull up stored information when entering new reports about existing leases and units. Managers, however, should not have access to all the reports for all locations, only theirs. The home office is the only entity that should be allowed full access to the reports and data for all units across all locations, as this information is needed to approve repair requests and keep track of leasing performance on a company-wide basis, which is critical for financial and marketing strategies.

Security Rules

One of the biggest security threats to the database is the potential attempts of third parties breaking into the system to adjust data in their favor. There are two security scenarios here. The first is the potential security breach hacked by tenants in order to change their leasing information and possibly lower their rent so that they benefit. Moreover, there is also the potential threat of outside contractors hacking into the system in order to increase the amount of estimates provided for repairs or maintenance needs.

This means that the database must be protected from potential outside parties hacking into it. This can be accomplished with the use of a strong firewall and by using a secure server to hold the database. Using a cloud system could place the data in more danger, and thus use of a secure server is advised, even if it is more expensive overall.

Another major security scenario is managers entering in wrong information, either intentionally or unintentionally. Managers may mistype the amounts of estimates and repair bills accidentally, or they may intentionally increase the amounts of estimates or repair bills so that they can skim money off the top for themselves. Thus, there must be unique security codes assigned to each manager, with a tracking system for the number of log ins and when those log ins occurred. Any changes to records will be recorded and notified to the home office. Moreover, Managers will be required to submit scanned copies of any bills, leasing agreements, or maintenance estimates at the end of each month so they can be fact checked with the information that was inputted by the managers themselves. Moreover, the security system will have to prevent individual managers from accidentally accessing other participant records. This can occur with the mistyping of log in information, or the intentional fishing for entrance into other records. In order to prevent this scenario from occurring, a verification system could be put into place. Such systems can be used to verify individual data, and if for example a log in occurs from a different IP address, may require additional information for the system to grant access to individual users.

Preliminary Entities and Attributes

Interview

Nouns: Name, System, Problems, Role, Contractors, Location

Questionnaire

Nouns: Name, Microsoft Excel, Google Sheets, Internet, Spreadsheet, Reports, Notifications,

Job Shadow Report

Nouns: Names, Payments, Leases, Repairs, Notification, Grace Period, Rules,

Entities

Attributes

Manager and Renter Names

Reports

Including use of Internet and spreadsheets

Notifications

About lease and repair information via online notifications

Payments

On time, late, grace period

Contractors

Repair, Maintenance, Payments

System

Problems, Internet, Spreadsheet

Location

Apartment Number, Names

Lease

Payment, Amount

Candidate Keys

Natural Keys

Since these are the keys that eliminate the process of duplication, some possible keys include: Name, Lease, Location, System

Surrogate Keys

These keys are unique for every entry and thus include: Reports, Notifications, Payments, Contractors

Scholarly Activity

Protection Operation

Types of Protection

Enable user to see table

GRANT SELECT

Prevent user from seeing a table

REVOKE SELECT

Prevent user from adding rows to table

REVOKE INSERT

Prevent user from deleting table rows

REVOKE DELETE

Enable user to reference a table

GRANT REFERENCE