Wild Wood Apartments is in need of a database, or RDBMS to manage each of their apartment buildings along with a breakdown of finances in various ways. The database will allow Wild Wood Apartments to search for information rather than manually calculate and write the data each month.
The major topics of the database begin with the list of tables such as Buildings, Units, Leases, Receivables, Vendors, and Repairs. The tables are determined by duplication of data, for example, if Buildings and Units were two columns in the same table then the Building name would be continuously written by hand when entering each Unit. However, as separate tables, Buildings will have 20 listings for each Building, while Units will simply mention the Building_ID as a foreign key column. The tables continuously collect data while, in turn, reports select data to manipulate in order to make more informed decisions. Wild Wood wants to track payments by Unit (Receivables table), formulate the total dollars collected, subtract the total expenses (Repairs table), look for vacant Units (Leases table) and send the reports to management. Therefore, we'll have to start building a list of reports in plain English such as, "SELECT all Payments and associated Unit_ID BETWEEN May 1 2016 and May 31 2016." Then an automated email can be set up to send to management.
The system will not allow the tenants to login or see the database information. If they need to know something the database can send them an email with certain selected data when necessary. The database will not track when the buildings or units need repairs but it will track the list of repairs for each month.
The project will be created in MS Project in order to get a good feel for the length of time it will take to launch the database. We started by collecting the initial information from Wild Wood. Now we need to show them a visual (Crow's Feet Notation) of what the database will look like and how they will interact with forms that enter the information into each table. During that time will collect more needs-and-wants from Wild Wood which we'll also account for in the MS Project timeline. Once we get the approval to move forward we'll create the 6 tables, the list of reports and the GUI. This is still the rough draft attempt that we'll discuss with Wild Wood before we move on.
Testing, error handling, and security are the last steps of the project that typically take the longest. Data can be missing (NULL), incorrect (text fields), not encrypted so hackers can easily steal it, and some testing issues come up due to syntax errors and other types of functionality issues. All of these things need to be corrected (debugged) before the database is launched. Finally, after a few more meetings with Wild Wood, we can launch the database. According to Harrington, of Relational Database Design and Implementation, "Incorrect data are probably the worst types of problems to detect and prevent. Often the [incorrect] data aren't detected until someone external to the corporation makes a complaint (Harrington & ebrary, 2009)."
Perfection is impossible in the world of databases, so we also need to set up a continuous improvement plan as things break of change in the future. We have to monitor the data before certain users fill the database with bad data that can't be selected. We anticipate the full project to take about 10-12 weeks when we incorporate the meetings and additional needs-and-wants along the way. MS Project will help keep us in line with a solid plan and allow us to stay on track in order to stay on budget.
EXCEL LAYOUT: To help with the Crow's Feet Notation
I will conduct an hour long interview with the stakeholders to determine the best course of action for a database management system. First, we need to make sure we have all of the financial information necessary for the finance team to receive full reports. They shouldn't have to make additional changes to the reports upon receipt. Next, we need to make sure the IT administrator includes all of the necessary fields in the database to collect the necessary data that will output specific information.
Once we get a few clarifications about late rent, we will have the business rules and exceptions to those rules set as a standard.
Buildings, Leases, Managers, Apartments, Tenants, Repairs, Rent, Vendors, Units, Tenant Name, Address, Receivables.
Making a list of nouns will be helpful when creating the different classes and attributes of a database. If you're making a database about animals then Dogs and Cats are both good nouns to list. Each noun might require further information such as a Husky, Gray, Age 4, Blue eyes, etc. Each of the dogs, when listed from the database, will also have the corresponding descriptions with it. The list of NOUNS and corresponding attributes are below:
Database normalization was created to improve data integrity and minimize data redundancy, so I believe that in order to deploy a denormalized database, one must be well versed in database structure along with data integrity, and have the experience to understand when data will be entered redundantly or inefficiently. Those that argue that a database can be used without first normalizing it to the fullest extent, must have the knowledge to know where the data is and how to retrieve it. However, that particular person might need to think about the longevity of the database and where he or she will be in 5 years because someone new may have to be the administrator which could leave that person with quite a mess.
A database for Wal-Mart tracks customers, orders and employees (among many other things), in which the customer will be mentioned throughout the database regularly as an attribute. It would be redundant for a table to ask for my Name within the Customers table and Orders table because it's not easily updated when I change my name. It's also possible that my name will be entered wrong by accident in different areas which will ruin reports that are generated later.
Another form of normalization happens when a table is dependent on another, such as an Item must be entered into an Inventory table before a Customer can purchase it in the Orders table. Without the item already in the Inventory table it's impossible to know the description, model number, cost, quantity on hand, etc. By forcing users to enter records with dependencies in place, the user will in fact enter less redundant data automatically. Without forcing dependencies by created a denormalized database, users can each enter data in different ways, which again, ruins reports generated later.
Certain attributes require normalization for integrity as well, such as State, Zip Code and Phone number fields. Requesting the State within a form, using a basic text field, allows the user to enter the state in many ways. However, data integrity and normalization say that the developer needs to force integrity on users by requiring that a zip code is not only 5 characters, but also that each character is a numerical digit (US only example). We take these things for granted when filling out forms because the team of programmers that work at Wal-Mart have normalized their database already. Programming code could populate the City and State after the zip code is entered, or offer a drop down menu of the choices available in order to promote normalization. "Third Normal form applies that every non-prime attribute of table must be dependent on primary key, or we can say that, there should not be the case that a non-prime attribute is determined by another non-prime attribute. (StudyTonight.com, n.d.)"
The database-programmer Blog states that "A denormalized database is a normalized database that has had redundancies deliberately re-introduced for some practical gain." That's different from a database that was created without trying to normalize any of it. Normalization states that calculated values aren't allowed to be stored as an attribute which creates more work later when retrieving data (sort of). Joining tables through the SQL JOIN statement basically assign variables primary keys so that the exact same Name, for example, is used throughout the entire database. Changing the name in the Users table will change it through the variable on all pages that have the Name mentioned through the primary key. Joining tables can also be cumbersome for some beginners, but it promotes data integrity and should be used.
Through my research I do not believe there is sufficient evidence of using a denormalizted database. I believe normalization should always be used to save headaches down the road. Even though many small companies use Excel or PDFs in desktop folders to manage data, a relational database with normalization in place will create reports that far exceed the time and effort of the Excel or PDF reports. These types of databases are normal in the business world, however it's due to constraints of the company such as funding or experience. Creating a denormalized database by purposely created redundant data seems asinine and problematic.
Authorization and Authentication
The security plan allows the database administrator (ONLY) to change access levels, or change anything in the database functionality such as tables, relationships and keys. Access must be granted into the database with a user name and password, otherwise a user will be denied access. Roles can be set for Apartment managers that grant the exact same permissions to all users with a title of Apartment Manager. Likewise, roles will be set up for accounting staff, management within the company, and database administrators (usually only one).
There will be views that are common to most users in terms of rental income, expenses, new leases, average rental payment, etc. The "view" will always use the most up-to-date data to provide information.
Setting authorization codes to access the database stop intruders from entering and also stop unauthorized personnel from making dangerous changes to the database tables, relationships or keys. Sometimes these things can happen by accident when exploring through the database, so we’ll stop that from happening by denying access to certain controls.
Preliminary Threat Analysis
Disaster Management Plan
The disaster management plan focuses on the worst of the worst case scenarios. Fires, database corruptions, viruses, evil employees and hackers are all possible threats of the system. Keeping them out will require everyone to create strong passwords that they don't share, and to back up the system to a second location. Backing up a system daily allows a company to obtain the latest version in the worst case scenarios such as a fire or corrupt employee. These backups can be set to run at particular times of the day whereas not to interrupt employees that are working hard.
Harrington, J. L., & ebrary, I. (2009). Relational database design and implementation: Clearly explained (3rd ed.). Amsterdam; Boston;: Morgan Kaufmann/Elsevier.
Downs, Ken (October, 2008). The Database Programmer. The Argument for Denormalization. Retrieved from:
StudyTonight.com (n.d.). Normalization of a Database. Retrieved from: