Wild Wood Apartments
The wild wood apartments is an apartment leasing company with 500 units and collects rent monthly. These properties are spread through California, Oregon, Idaho, and Washington. There are twenty apartment complexes with each containing around sixty apartments. The Wild wood apartments leases it’s properties for the contract duration of 6 months and year lease.
Due to distance and prospect of growth, The Wild wood apartments wants to build a centralized database system by using SQL which will enable the management and all level of employees perform their daily duties of business with efficiency, visibility and assist in business decision. This will also provide access to the tenants to manage their lease and will enable tenants to view their leasing agreement duration and they will also be able to pay their rent as well as monitor payment status.
This system, will enable managers and leasing agent to enter daily activities such as newly lease, monthly revenue, maintenance. Once this data is entered, the upper management can gain visibility and will allow them to generate monthly, quarterly and yearly reports.
Stakeholders:
· Tenants
· Property Managers
· Headquarters Management
Unique stake holder role:
Event though the wild wood apartments organization is that has legal rights of the properties. However, the local managers and managers will be responsible to manage run the properties.
Some of this responsibility of the local managers including marketing, maintenance and tenant and they will be the face of the company. The manager will be responsible keep a record of payments by the tenants for rents and he would be responsible to maintain and upkeep of the properties.
Problems of the current system:
Due to multiple locations of the properties, currently there is a lack of centralized system, real time information and ability to generate report. Currently, data is entered on excel spreadsheet, there is no repository for historical data that can be utilize as marketing material. This makes it finding information very difficult and data could be old and my not be readily available. Manual process results process and data inconsistencies.
Business Requirements:
Repository of historical data
Database includes the storage of payment details.
Must be able to manage maintenance data.
Ability to generate monthly, quarterly and annual report.
Maintenance of central data base.
Ability to handle payment transaction.
Should give the managers of property an entering, editing, view, and delete options of leasing details
Provide view s of all the records to the headquarters managers
All data must be accessible centrally
Current system limitations:
Decentralized data repository and source.
Duplicate data entry due to decentralized data repository
Data inconsistency
Insufficient and inaccessible historical data
Impact:
The new system will enable the property managers market their properties, exploit historical data and make sound decisions based on data and removes bias from their decision making. wild wood apartments property manager would be able to track in a centralized location all financial data, maintenance records. They will also be able to generate various report for the upper management. Thus, will result in streamlined process, increase efficiency, consistent data and increase revenue.
Database Analysis and Design
The wild wood apartments is an apartment leasing company with multiple units and collects rent monthly. There are twenty apartment complexes with each containing around sixty apartments. The Wild wood apartments leases it’s assets for the contract period of 6 months and year lease.
This system, will enable managers and leasing agent to enter daily activities such as newly lease, monthly revenue, maintenance. Once this data is entered, the upper management can gain visibility and will allow them to generate monthly, quarterly and yearly reports.
Business requirements
It should allow each manager to input the leasing details of apartment.
It should provide the edit option to edit the details of the apartment.
It should allow the access to the manager to view the details of the apartment.
It should provide the delete option for deleting unnecessary details of the apartment.
It should allow the manager to keep a track and to generate the reports of expenses.
It should provide view access to the manger from the headquarters to view all the details of the apartment.
The following is the use case diagram of the Wildwood Apartment management system.
database Design and Analysis
Conceptual model
List of the Attributes and Entities:
Details of the apartment includes
ID
Name
Details of the building
Address
Unique ID
Apartment Number
Count of apartment
Total count of the stairs
Type of Expenses
Expense Id
Expense type
Details for the expenses
Id
Date
expense type
Total amount
Keys used for the candidate
For building
Composite key: primary key: Id of the apartment in the complex, Number given for the building.
Unit of the apartment
Composite key: primary key: Includes Id for the apartment in that complex, Number given for the building, Number given for the apartment.
Logical model description
Following is the model for the logical data performed using Microsoft Visio which represents entire entities and attributes which are fitting for those entities. This model also identifies the foreign and primary keys for every entity.
Details of the apartment in the complex
Key for the apartment
Name of the apartment in the complex
Primary key: Key for the apartment in the complex
Details of the Building
Key for the building number
Required addressed
Count of the floors in the building
Total Apartment count
Total count of the Elevators
Details for the rental
Key lease number
Key unit
Key start and end date
Total amount
Total rent amount paid
Date for the payment
Late payment fees
Tenants first and last name
Total amount deposited
Primary key: key lease number
Foreign key: Key unit.
Details for the maintenance
Key for the maintenance
Key for a unit
Key expense
Key type
PHYSICAL MODEL
The entities of physical model are same as the logical model whereas it includes the data types of the attributes.
Database
The database is specified as a set of structured information which is stored in a system, generally in disks which can be accessed by several users in concurrent ways. In the main databases are split into areas of application. Which implies there will be a separate database set up for every data type storage, such as Human Resource data will be stashed away in a database which includes payroll, accounting and employee details, and there will be a different database for storing sales data and etc. and DBMS would manage all this database. (Systems) www.cs.ubc.ca)
DBMS
DBMS stands for Database Management system which is a set of structured programs, where various databases are being managed it. (Systems) www.cs.ubc.ca)
Responsibilities of DBMS
Tasks such us data accessing, data deleting, inserting of data and also in data updating and as the data integrity and security issues are handled using DBMS. Which is performed by executing various actions such as logging and locking the system, definition of rules for an application, supporting programs, which are streamed online and also in a batch, facilitating data recoveries and data backups, adding triggers, improves the performance, a catalog is maintained for preparing a directory to store database objects, buffer pools are managed by DBMS, it acts as a web interface, supporting programs, and it supports packages of interfaces which includes SQL that is common language for database and mainly used for relational systems of database.
Types of DBMSs
There are three various types of DBMS models, such as Relational, Hierarchical, Network.
The commonly used systems in these days are Oracle: Sybase which is similar to SQL server of Microsoft but it will be on other platform. Other one is Informix, Ingres and IBMs: SQL-DS: IMS, DB2 these are generally smaller but at the same time powerful than the other products such as paradox, d Base, Fox pro, MS Access and various other products. (Systems)
Factors for selecting Database Products:
An organization has to factor various things when selecting which database system to implement. Some platforms includes expertise needed, current environment architecture, hardware and operating system, amount of data, transactions occurred and required per sec is noted, have to observe the applications which are already in an organization, also need to take into consideration its support for distributed and also the heterogeneous computing, another factor to analyze can be cost.
As we have discussed above we have various DBMS products which are used to store and manage data, selection of the product depends on the influential factors of that product which can be used for our project. (Systems)
Possible Databases options:
Oracle SQL developer, MySQL, Microsoft SQL server , Microsoft access, Mainframes
System similarities and differences of to some of the commonly used database systems: Oracle, MS SQL server and Microsoft access.
There are some similarities and differences between these servers particularly in the areas of Language of command, Control on transaction, and their structure of objects of database.
Language:
One main difference between these relational database servers is the type of language used. Both, the oracle and MS SQL server uses a language query /SQL which is structured. Whereas MSSQL server use T-SQL/SQL transact query is also part of SQL and it is Sybase development and utilized by Microsoft. PL, SQL, SQL procedural language is utilized by oracle. These languages have various competences, syntax. Major difference is management of variables, procedures used for storage. In oracle PL/SQL groups procedures into packages which can’t be passed by MS SQL server. Based on the analysis T-SQL is simple and easier when compared to PL/SQL. (Stansfield, 2014)
Transaction control:
Transaction could be group of tasks which are taken as a single unit. Altering records of SQL queries and its collection have to be updated at the same time. If we are unable to update a record it will not update any records. On the other hand MS SQL by default would commit and execute every task disjointedly, and it makes is cumbersome to identify and correct or make any changes if errors. Oracle handles every connection of database as a new transaction. Queries in oracle are executed first and then commands will be issued, any changes made will be passed to memory and commit statement is required in order to make the changes. Then, it gives the commit statement the new transaction process gets started, which provides more flexibility and also helps handling of error control. (Stansfield, 2014)
Organization of database objects
All the objects are organized by MSSQL server consist of table send procedures by names of database, authentications are provided for database objects. In SQL every database comprises private and disk file over the server. All the objects of database are schemed which are subset selection of database. Both the servers are very efficient, utilized equally in their own ways based on the business requirements we may need to contemplate the product apt for the condition. (Stansfield, 2014) (Dicken, 2015)
Microsoft access
Microsoft access has visual user interface that makes is easiest compared to the other tools. And thus minimal learning curve. However, since this is not centralized database this makes it unlikely viable candidate.
Database
Strength
Weakness
OracleSQL developer
It provides high quality
Uninterrupted access will be provided
Provides integrity ,security
Very flexible
Can be choose easily
While application porting
Problem over normalization
At joins and unions
Loss of updates
MySQL
Good penetration over market
Easy to start
Provides open source license
It is fast
scalability
Commercial license
Issues at integrity
Back up issues
Small business use only
MS – SQL
Enterprise grade management system software
Good data recovery system
Costly
It has limited compatibility
Costly
Microsoft access
Provides graphical tools
Lacks security implementation
Contains data dictionary
Can be used for small and large business
Provides ODBC support
No proper back efficiency
No managed locking
Mainframes
Provides high level security
Hackers cannot know mainframes
High integrity
Multiprocessor management
Skills are lacked
High expensive for legacy for applications.
Attacks from inside.
As per the above anlysis suggest that I would utilize Oracle SQL or Microsoft SQL server to create database for Wild wood apartments
Hardware and software requirements of system
RAM16 GB /32-bit 64-bit, Processor-2GHZ or above, Disk space-100 GB
Data Model
Data modeling is the process of documenting a complex structure system design as an easily understood diagram, using text and symbols to represent the way data needs to flow. The diagram can be used as a blueprint for the construction of new software or for re-engineering a legacy application. (Rouse)
This is also where we can refine and optimize how data is stored.
The Wild Woods Apartments, is not unique and may have to utilize data model to refine and optimize how it stores data and how it retrieves it. Data modeling involves classifying data in a simple and organized way. One such method is the use of Visio Diagrams or online visual diagram tools to represent data visually. Second is the use of the Entity Relationship Diagrams, and other methods can be utilized.
Enterprise Data Model
Enterprise data modeling is by which we develop data elements in a shared relationship and views. Enterprise data modeling can also be utilized to bring cohesion by bringing various data sources, identifying entities, create integration and promote efficiency.
When developing enterprise data model, calibration of dimensions and entities gets connected to the similar characteristics which can be present in various tables and this process helps in enterprise data modeling. In addition, Enterprise data model can be utilized to standardize various objectives, group of database application and the operational rules that are identified. (modeling, 2015)
OBJECTIVES:
· To provide authentication to various group levels such as managers to enter new leasing of property, edit any value entered regarding the property, enable the manager generate high-level report as well as detail report of the properties as well as maintenance activities and progress.
· To Provide leasing agent a view to available properties, being able to enter new renter information.
· To Provide maintenance personnel view of a maintenance request, ability to update status and enable the personnel to enter to enter maintenance related notes and comments such as part number or other details.
· Provide headquarter managers a high-level report of all properties such as revenue and performance.
Enterprise data model:
OPERATING RULES
The rules of operating generally follow and should adhere to realize the main objectives.
The rules for the managers to enter new leasing of property, edit any value entered regarding the property, enable the manager generate high-level report as well as detail report of the properties as well as maintenance activities and progress.
The rules for the leasing agent should enable to view all available properties, being able to enter new renter information.
The rules for the maintenance personnel view of a maintenance request, ability to update status and enable the personnel to enter to enter maintenance related notes and comments such as part number or other details.
Provide headquarter managers a high-level report of all properties such as revenue and performance.
Introduction
Security plans are a crucial component in planning and implementing new database that meets legal and ethical demand. In today’s world, a carful security planning is needed to satisfy the security needs of tenants as well as to maintain your competitive market advantage by securing historical data.
Data Management Goals
Enable better decision-making, reduce operational friction, protect the needs of data stakeholders, train management and staff to adopt common approaches to data issues, build standard, repeatable processes while maintaining the legal, ethical and security obligations.
· Complying with regulations and with various institutions
· Various tiered authentication by enabling them to perform their daily duties.
· Enable to comply with any discovery or legal demand.
Legal compliance and ethical practice
Even though there are social and ethical issues that we needed to meet, it’s also crucial to understand the legal obligation of maintaining electronically stored data such as rule 26. One of the provisions in rule 26 is:
Unless otherwise limited by court order, the scope of discovery is as follows: Parties may obtain discovery regarding any nonprivileged matter that is relevant to any party’s claim or defense and proportional to the needs of the case, considering the importance of the issues at stake in the action, the amount in controversy, the parties; relative access to relevant information, the parties’ resources, the importance of the discovery in resolving the issues, and whether the burden or expense of the proposed discovery outweighs its likely benefit. Information within this scope of discovery need not be admissible in evidence to be discoverable. (Find Law)
In addition to the legal mentioned above, we also have to secure personal data from outside treats such as Personal data and confidentiality. Protecting personal data is only for health records and other transactional type of data, however, it’s also applicable in any type of situation where you collect individual s personal data such as social security number, date of birth, house hold data could be vulnerable to external and even internal an authored entity. Confidential data could be information which is related to individual’s education level occupation and income
Ethical practices
Compliance is not the only thing that should be sufficient. Nor does it mean just because you can do it and it’s with in the legal boundary it does not mean you should not consider some ethical issues. In additona to legal compliance, an organization such as Wild Wood apartment should consider best practices and approaches of measure and technical tools in determining the utilization and sharing of data ethically.
Security needs of the solution
Wild Wood Apartment as any organization there are various level of users with various level of access needs. The security the is going to be implemented it has be secure from inside and out site threats, ethical not only within legal requirement but also the fills the gap of the legal requirement and all this while allowing all entities to perform their daily duty with out any impediment.
Here are some of the security measure should allow:
· allow each manager to input the leasing details of apartment.
· provide the edit option to edit the details of the apartment.
· allow the access to the manager to view the details of the apartment.
· provide the delete option for deleting unnecessary details of the apartment.
· allow the manager to keep a track and to generate the reports of expenses.
· provide view access to the manger from the headquarters to view all the details of the apartment.
Here are some of the security measure should protect:
· it should protect personal data such as social security.
· It should protect the confidentiality such as occupation and income
· It should also protect any house hold information
Database Security Management Plan
There are few practical steps we should consider when planning a data management security system. Some of this are:
· Determine the security requirements: Security requirements are considered through the implementation of data security services and are an imperative part of the data security management system. Risk analysis is part of information security services and aids in scheming the potential effects of risks, their probability of incidence and identification of IT resources to guard. (Security News paper)
· Identification and evaluation of security risks: Businesses can classify security risks in IT resources s through data security services as it’s an important part of the data security management system. Risk analysis encompasses the scrutiny of each threat. Some enterprise data security businesses also perform the risk assessment. The risk assessment determines the probabilities of manifestation of the threats and helps in the assortment of security controls that should be applied.
References: http://mousegraphixdesign.com/school_projects/databases/HANDS%20ON%20DATABASE.pd
http://www.heuristichomesteader.com/heuristic_pdf/Database%20Design%20For%20Mere%20Mortals.pdf
http://scientific-journals.org/journalofsystemsandsoftware/archive/vol2no3
https://www.coursehero.com/tutors-problems/Operations-Management/8524924-Identify-the-stakeholders-for-Wild-Wood-Apartments-Make-a-list-of-t/
modeling, d. (2015, june 14). learndata model.com. Retrieved from http://www.learndatamodeling.com/edm.php
Moss, L. (2008, april 1). Enterprise Data Modeling – Is It Worth It? Retrieved from http://www.eiminstitute.org/library/eimi-archives/volume-2-issue-1-april-2008-edition/enterprise-data-modeling-2013-is-it-worth-it
Rouse, M. (n.d.). Data modeling. http://searchdatamanagement.techtarget.com/definition/data-modeling.
scottwambler. (2002-2006). Data Modeling. Retrieved from http://www.agiledata.org/essays/dataModeling101.html
Find Law. (n.d.). eDiscovery Federal Rule 26(b)(2):. http://technology.findlaw.com/ediscovery-wizard/rule-26-b-2.html.
Dicken, C. (2015). Databases for Web Developers. Retrieved from http://www.htmlgoodies.com/primers/database/top-5-databases-for-web-developers.html
Stansfield, J. (2014, march 13). Microsoft SQL Server vs. Oracle: The Same, But Different? Retrieved from http://www.seguetech.com/blog/2014/03/13/Microsoft-SQL-Server-versus-oracle
Systems, B. I. (n.d.). Brief Introduction to Database Systems. Retrieved from http://www.cs.ubc.ca/nest/dbsl/intro.html
metioned, N. (No mentioned). Brief Introduction to Database Systems. Retrieved from http://www.cs.ubc.ca/nest/dbsl/intro.html
Moufarrege, S. (n.d.). Advantages & Disadvantages of Microsoft SQL. Retrieved from http://www.ehow.com/list_7228389_advantages-disadvantages-microsoft-sql.html
Stansfield, J. (2014, march 13). Microsoft SQL Server vs. Oracle: The Same, But Different? Retrieved from http://www.seguetech.com/blog/2014/03/13/Microsoft-SQL-Server-versus-oracle
