Wild Wood Apartments
Table of Contents
Background 3
The Problem 3
How are Departments Effected 5
Executives 5
Chief Executive Officer 5
Chief Operating Officer 5
Chief Financial Officer 6
Regional Managers 6
Complex Managers 6
Background Summary 7
Business Rules 7
Conceptual Model 8
Conceptual Diagram 9
Logical Model 11
Logical Diagram 12
Physical Model 13
Physical Diagram 14
ments 15
Research 15
Analysis by Database Management System 16
Microsoft SQL Server 16
Oracle Database 16
Microsoft Access 17
MySQL 18
Recommendation 19
Hardware 20
Wild Wood Apartments Database Model for Complex Managers 21
Enterprise Data Model 21
Operating Rules 22
Rule Reflection 24
Standards 25
Legal Compliance 26
Privacy 26
Communication Security 26
Access to Stored Information 27
Ethical Practices 27
Solution Security Needs 28
Technical Threats 28
Human Threats 28
Natural Threats 29
Global vs. Local Systems 29
Database Security Plan 29
Authentication 29
Authorization 30
Roles 30
Disaster Plan 31
Documentation 31
References 33
Background
Wild Wood Apartments is midsized company that rents apartments in communities located in Washington, Oregon, California and Idaho. In total, the company has 20 properties. Each community has between 10 and 60 apartments with lease terms that are either 6 months or one year in length. Rather than hire a staff to manage each community, it is the company’s practice to retain a tenant as the manager. The manager is responsible for accepting new tenants, collecting rent and closing leases. The manager is also responsible to conduct any needed repairs. As compensation the manager does not pay any rent, and receives a stipend for services rendered. As part of their duties, managers send quarterly reports, through the mail, to the headquarters in San Francisco. The report is a simple summary of the occupancy rate, revenues and expenses, with other information as well.
The Problem
Currently, Wild Wood Apartments are facing a few challenges with the existing system. One of the biggest issues the current system represents is lack of real time information. Since the Wild Wood Apartments headquarters only requires the reports quarterly, they are only able to know the status of their most recent quarter. This means that if there is an issue, potentially, the corporate office may not find out until three months down the road. This prevents the company from being agile enough to deal with unexpected issues. In addition, the quarterly reports are mailed in. This can be an issue if the forms are lost in transit. Sending corporate information through the mail is also a very insecure solution. Unless the apartment manager is filling the reports in on spreadsheets that are save to his computer, there are no backups to paper forms. If one is lost in the mail it would take some time to recreate the data and then re-mail to the corporate office.
Currently, the reports are time consumptive too complete. The managers are spending excess time filling out the reports that could be better spent in other areas, such as, maintaining the buildings and grounds. Proper maintenance should increase the occupancy rate and therefore revenues to the company. This is accomplished by performing the repairs and maintenance in a timely fashion increasing the reputation for taking care of issues. This cannot be done with the current system as the managers are spending a lot of time with the reporting function.
Another problem with the current system are the paper reports. As the reports are in a for that cannot be data-mined with ease. To find information on all of the apartment complex, someone at the corporate office would need to manually calculate or manually enter the information into a spreadsheet to do the calculations for them. In either case this is a slow and tedious process. With the system being highly inefficient, payroll costs increase due to the amount of time involved for the corporate office. Even though the system is inefficient it does not increase the hard costs for the company, only the managers time, which could be better applied.
Another area of concern with the current system is the inability to hold managers accountable. If a manager is breaking the corporate rules the corporate office may not find out until months down the road, if at all. This means that the managers are given a hi degree of trust. One area they are trusted in is collecting rents. Since the corporate office does not have monthly data they may not know how much revenue to expect. This means that if a manager were to steal any rents given the Wild Wood Apartments could lose a substantial amount of money and not know until it is too late to do something about it.
How are Departments Effected
Each department within the organization is effected differently and has different goals. For this reason, we need to understand how the departments are effected.
Executives
C-level executives view the systems in a different way than lower level managers. Generally, Chief Executive Officers (CEO), Chief Operating Officers (COO), and Chief Financial Officers (CFO) want more of a summary based report. This helps executives to maximize their time and focus on corporate growth. The current system will can provide the information that they are looking for, however, as the forms have to be manipulated by several lower level managers before it reaches them, the report could be inaccurate do to mistakes. In accurate information can affect future growth plans, cash-flow and profitability.
Chief Executive Officer
The CEO is responsible for the overall profitability and growth of the company. In addition, the CEO must be able to hold all other employees accountable for their actions. With the slow and possibly inaccurate information the CEO cannot effectively do their job.
Chief Operating Officer
The COO is responsible for the day-to-day operations of the company. This means that the COO is going to want to make sure that the operations are smooth and free of as many issues as there can be. The current system prevents this as there is too much room for errors and it is costly to operate in the form of labor costs.
Chief Financial Officer
The CFO is in charge of finances. This includes all accounting functions. With the current system the CFO and any accountants the company employs cannot accurately tell how much money in revenues are due each month. The reason for this is that they do not have up to date occupancy rates. As different apartment units command differing amounts of rent the accounting team is left to guess on a monthly basis how much cash is in the bank only reconciling on a quarterly basis. This method also goes against generally accepted accounting processes.
Regional Managers
Regional Managers accept information from complex managers and over see their day-to-day operations. They are the frontline of accountability. With the lack of real time information this makes their position very difficult. As the regional managers answer to the COO they are going to be asked questions that will be difficult to answer. A lot of their time will also be to reformulate the reports. They will take the paper reports and put them into useable data for the executives to base decisions on. Since they are performing these entries manually there could errors that in the long run could get them fired.
Complex Managers
The apartment complex managers need to be efficient. They are charged with many tasks. If their tasks are not done in a timely fashion they everyone in the upstream of data will not know what is going on in the company. Currently, the managers need to input data manually. In the case of the managers of larger communities, the manager could be spending the majority of their time filling out the reports and tracing down rent payments. This leaves little time to for repairs and maintenance.
Background Summary
As you can see the main problem with the current system is the lack of real time data preventing real time decisions and the general lack of accountability. Every level inside of the company needs to be able to see information in real time. By implementing a new database system, all of the concerns can be addressed. The new system will provide real time information and automatically generated reports based on the criteria that each department needs. This will allow the company to reach maximum potential with little relative cost.
Business Rules
Business rules are an important part of designing a database. A business rule is a statement that imposes some form of constraint on a specific aspect of the database, such as the elements within a field specification for a particular field or the characteristics of a given relationship (Hernandez, 393). In other words, a business rule is a translation from how the business operates into how the database will operate. In the proverbial perfect world, each rule will match exactly to how the business operates. It is impossible to say that this can always be done, given unknown situations, however, this should be the mindset when creating the business rules. The business rules can also be used by an end user to understand how the database will function and how they will use it. Given the prior definition, our business rules are as follows:
1) Apartment Communities have Apartments.
2) Apartment Community names must be unique.
3) Apartments have tenants.
4) Tenants have Leases and make Rent Payments.
5) Tenants live in Apartments.
6) Apartments have Maintenance Requests.
7) An Apartment Community must exist before anything else.
8) Apartments must exist before Tenants can be added.
9) Apartments have apartment numbers and cannot be duplicated within the same community.
10) Apartments can have more than one tenant.
11) More than one person can have a lease for the same Apartment, but Apartments can only have one Lease.
12) Apartments can have an infinite number of Maintenance Requests and survive the lease term of the tenant.
13) The database will store information in a secure state.
14) The database will be accessible from multiple locations.
15) Complex Manager will enter information in a timely fashion into the database.
16) Regional Manager will generate reports.
Conceptual Model
Conceptual ERD models information gathered from business requirements. Entities and relationships modeled in such ERD are defined around the business's need. The need of satisfying the database design is not considered yet. Conceptual ERD is the simplest model among all (Conceptual, n.d.). In conceptual modeling we are abstracting the model in a very loose form. We are getting the framework started for what will eventually become the database. In this form we are looking at the entities without their attributes. The attributes will be defined according to the business rules in the logical model. The conceptual model only contains what tables will be built. At this point we also define the relationships between the tables. This will help for future modeling and database construction. In our conceptual model we can tell that there is a one to many relationship between Apartment Communities and Apartments, a one to many relationship between Apartments and Maintenance Requests, a many to one relationship between Tenants and Leases, a one to many relationship between Leases and Rent Payments and a one to many relationship between Apartments and Tenants.
Conceptual Diagram
Logical Model
A logical data model describes the data in as much detail as possible, without regard to how they will be physical implemented in the database (Conceptual, n.d.). In other words, the logical model further defines what the database will become. At this point we add the entities to the diagram and model. It includes the relationships and entities as well as all attributes. Within the logical model we also define primary and foreign keys. Normalization also begins at this level.
In the Wild Wood Apartments logical model, we have 6 entities and many attributes. Each primary key is defined to aid in the data integrity of our model. There is a logical flow to each database and the structure of our logical model helps to reinforce this.
According to www.1keydata.com, Comparing the logical data model shown with theconceptual data modeldiagram, we see the main differences between the two:
· In a logical data model, primary keys are present, whereas in a conceptual data model, no primary key is present.
· In a logical data model, all attributes are specified within an entity. No attributes are specified in a conceptual data model.
· Relationships between entities are specified using primary keys and foreign keys in a logical data model. In a conceptual data model, the relationships are simply stated, not specified, so we simply know that two entities are related, but we do not specify what attributes are used for this relationship.
Logical Diagram
Physical Model
In the physical model, we have completely defined our structure. The key difference between the logical model and the physical model is the inclusion of data types. In modelling, the attributes are our columns and each column must have a data type that is specified according to the type of data it will contain. For our database we will be using several different data types. Our data types are: int, bit, money, date, and varchar. These were chosen with specifically to not only meet the requirements of the data the column will hold but to also meet the business rules requirements. Once the physical model has been completed, it will be translated into a Database Management System (DBMS). It is important to realize that if changes to the structure need to be made, they should be made at this point. Once the entities and attributes are in the form of a database and have data added it is much more difficult to make changes. It is also riskier if you need to change the data type. Therefore, extra review steps should be taken to ensure that the physical model is shaped exactly as it should be. It is important to note that the physical model includes data types that are specific to the type of DBMS you are using. This means that data types can be different between types of databases such as Microsoft Sql and an Oracle database.