About Me >>   Master's Degree >>   IT-650 - Principles of Database Design

SNHU - IT-650 - Principles of Database Design
Written by: Chris Bell - June, 2016

Wild Wood Apartments RDBMS Relational Database Management System | Database Design

HISTORY

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.

SCOPE

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.

CONSTRAINSTS

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.

OBJECTIVES

• Save Wild Wood's information in a more organized, secure and non-redundant format


• Save the managers time by using the database information to calculate reports


• Create searchable fields that hold history to allow management to review trends over periods of time


• Track all Buildings, Units, Leases, Receivables, Vendors and Repairs

TASKS & TIME LINE

• Create visuals for Wild Wood and make a presentation


• Deliverable: Create a Crow's Feet Notation diagram and include Tables, Attributes, primary keys and foreign keys. 1 Week


• Interview with Wild Wood to discuss progress and gain feedback


• Deliverable: Meeting and additional requests. 3 Days


• Building the database


• Deliverable: Create Tables and relationships in SQL with each attribute used as the Column name and IDs used as primary and foreign keys. 1 Week


• Creating reports, forms and GUI


• Deliverable: Use MySQL to build reports that select and manipulate the entered data. 4 Days


• CTest the prototype before trying to perfect it


• Deliverable: Meeting with client to test the functionality. Gain feedback to make alterations. Pay careful attention to data integrity, data entry accuracy, security problems and required information for each table. Also consider additional time for requests from Wild Wood after the testing phase. 2 Weeks


• Better design for GUI and more testing and debugging


• Deliverable: Programmers need to write code selects data, generates documents and sends emails using the selected data. Designers need to put finishing touches on the GUI and templates for reports and emails. 2 Weeks

DOCUMENTATION

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

List of Questions for the Database

1. Do you have to enter the person's name every month they pay rent?


2. How do you know how much each apartment is supposed to pay each month?


3. How do you keep track of late payments?


4. Once you have collected the rent checks, how do you put the reports together for management?


• Do you have to calculate everything by hand?


5. What is the exact criteria for the $100 late fee?


• Is everyone forced to pay it on Day 6 without exceptions?


• Does anyone beside the retired older lady have exceptions to this rule?


• Perhaps is the first time they are late due to something unfortunate.


6. How many times do you call to remind each tenant after you collect the initial checks?


• Are you forced to call them even once to remind them or is it just a courtesy you like to do?


7. How often do you accidentally write the wrong name, check amount or apartment number?


8. What happens when a check gets lost? Perhaps a check ended up in the trash by accident or a tenant says they gave you one but actually didn't.


9. What is the exact criteria for evicting someone? Can it be put into a list of rules that everyone needs to follow?

Stakeholders

1. Headquarters Manager


2. Financial Manager


3. IT Administrator


4. Apartment Manager

INTERVIEW

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.

Questions for Management

1. Is there any information you want or need that you're not currently getting?


2. Are there more reports, or more fields within the reports, that would be useful to you?


3. Can the IT team set up a remote access portal for the apartment manager to input the income and expenses directly into their database?


4. Is there a more organized way to collect the rent checks?


• Some people may want to pay electronically.


5. How long do you wait for checks before creating the report with "late fees" and sending the report to management?

Job Shadow Report

Some exceptions to rules include:


1. The older lady is allowed to pay late due to social security checks.


2. Tenants can hand the check to the manager directly instead of placing it in the locked box.


3. One tenant is behind on rent by 2 months, but the manager said it's annoying to evict a tenant so hasn't yet started the process.

New business rules:


1. There is a 5-Day grace period the needs to be defined as Strict or Loose.


2. If a tenant has paid on time for a certain amount of months, will they be granted more leeway for on-time rent?


3. Each tenant will receive one phone call for missing rent on the 5th day after it's due, after which the report will be sent with a $100 late fee attached.

Once we get a few clarifications about late rent, we will have the business rules and exceptions to those rules set as a standard.

Additional questions include:


1. How does Headquarters review previous rental income trends beyond the current month?


2. Do they manually enter the information into another database for reporting?

List of Issues with the Current System

1. The apartment manager manually enters data that cannot become valuable information to management. Long term rental history would be hard to obtain.


2. There aren't strict rules that define when rent is late, or if a late fee should be added to the payment.


3. There isn't a strict process of when to enforce eviction, or start the legal process of eviction.


4. If forms were created to enter data it would be easier to collect more data and report more information from the data, in reference to income, expenses and net profits.

Database Requirements for Stakeholders

1. Headquarters Manager


• Monthly Report and YTD report with basic information.


• Search for a list of late rent. Search for tenants that have been late more than twice.


• More specific information about tenants, repairs to apartments and past due rent.


2. Financial Manager


• Full report of rental income, expenses and profits.


• The system should be able to search for rent by month or by year and compare it to previous years.


• The system should rank the buildings by revenue and late payments.


3. IT Administrator


• Needs remote access to each system to review the database, repair it and alter it when necessary.


• Error reports via email.


4. Apartment Manager


• Search by tenant for previous payments, dates of payments and repairs.


• Create the report that gets sent to management


• More fields in the database for notes unless requirements become more specific

Business Rules

1. There is a 5-Day grace period the needs to be defined as Strict or Loose.


2. If a tenant has paid on time for a certain amount of months, will they be granted more leeway for on-time rent?


3. Each tenant will receive one phone call for missing rent on the 5th day after it's due, after which the report will be sent with a $100 late fee attached.

Security Rules

1. Only the apartment manager can input rent into the system


2. Reports will NOT be created with personal information. Just a lease number that can find the data in the system.


3. Social security numbers will be encrypted and credit history will not be saved.

Nouns

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:

Entities and Attributes

• Apartments


• Apartment_ID, Apartment_name, Address, City, State, Zip, Picture


• Units


• Unit_ID, Apartment_ID, Number, Floor, Bedrooms, Bathrooms, Picture


• Leases


• Lease_ID, Apartment_ID, Unit_ID, First_name, Last_name, Manager, Date_start, Active, Rent, Lease_PDF


• Receivables


• Receivable_ID, Apartment_ID, Unit_ID, Lease_ID, Payments_amount, Date


• Vendors


• Vendor_ID, Company, Phone, Contact


• Repairs


• Apartment_ID, Unit_ID, Vendor_ID, Date, Cost, Type, Receipt, Paid

Relationships

• Apartments one-to-many Units


• Apartments one-to-many Leases


• Apartments one-to-many Receivables


• Apartments one-to-many Repairs


• Units one-to-many Leases


• Units one-to-many Receivables


• Units one-to-many Repairs


• Leases one-to-many Receivables


• Vendors one-to-many Repairs

Crow's Foot Notation Diagram

Role of Each Entity

• Domain


• Apartments, Leases, Receivables, Repairs, Vendors


• Linking


• There aren't any many-to-many relationships


• Lookup


• State, Zip, Repairs > Type, Apartments > Apartment_name


• Weak


• Units - They are associated directly with the Apartment, so I felt it belonged here

Checklist

• All of the model requirements are represented by domain entities.


• All entities contain the appropriate attributes and meet the business requirements.


• All entities have a primary key.


• There aren't any many-to-many relationships.


• The relationships are valid (no cross relationships). All tables have appropriate foreign keys.


• All the lookup tables are used to support data integrity for states, zip codes and a few others.

Database Normalization

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.

Normalized

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.)"

Denormalized

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.

Security Analysis Plan

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

• Apartment Manager: SELECT private information from Leases. Data entry mistakes regarding INSERT and UPDATE. DELETE records accidentally.


• Accounting will be mostly viewing records instead of changing or adding. There is still a threat that an accounting user could travel into the inner workings of the database, so we'll block those permissions.


• Management needs access to most areas however they aren't the users that will be entering data. They will be similar to accounting users that can view all of the tables and reports (views), but cannot change the structure of the database.


• The database administrator needs access to all portions of the database which is certainly a threat itself. It should be someone trusted and a backup of the database should be stored off site with a different administrator's access.

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.