Presentation Guidelines and Rubric
Overview: For this assignment, you will create a short presentation that summarizes the key points of your database management system solution. When
creating this presentation, imagine that your aim is to present a proposal to management. This proposal should summarize key parts of your solution. Your final
project was divided into five parts: Organization; Analysis and Design; DBMS; Data Model; and Law, Ethics, and Security. Each of the slides you are required to
create corresponds to one of these five areas.
Your presentation should include a cover slide, a minimum of 5 content slides, and speaker’s notes. Speaker’s notes are a more in-depth description of the
material on the slide, covering what you would say while giving your presentation. There should be notes for every slide. Please refer to the Creating an
Effective PowerPoint Presentation resource for some tips regarding how to prepare effective presentations. You may use PowerPoint, Prezi, or other
presentation software.
Specifically, address the following in your presentation, with one slide per bullet point:
•
•
•
•
•
A summary of the problem that the organization faced, briefly describing how different departments and operations were impacted by the problem
The physical design of the database that you chose to apply to this situation, based upon the conceptual and logical models that you developed
A recommendation and defense of the best DBMS products that you found, looking at this from the perspective of broad, high-level needs of the
organization as a whole
A summary of the enterprise data model that you have developed, with reference to the operating rules within the group that allow for this model
A summary of the security management plan for your design, including key legal and ethical requirements
Guidelines for Submission: Your presentation should be at least 5 slides in length and should include a cover slide and speaker’s notes.
Critical Elements
Proficient (100%)
Needs Improvement (70%)
Not Evident (0%)
Value
Describes problem and explains how
various stakeholders are negatively
impacted by the issue
Describes problem, but does not
elaborate on how it is impacting the
organization
Does not discuss or summarize the
problem faced by the organization
15
Database Design
Presents a database design relevant to
solving the problem that builds on the
conceptual and logical models crafted
earlier
Presents a database design that is either
irrelevant to the problem or is not built
on previously crafted conceptual and
logical models
Does not present a database design
15
Recommendation and
Defense of DBMS
Products
Presents a robust and convincing
recommendation of DBMS products to
solve the problem and defends that
choice with specific details
Recommends DBMS products, but does
not defend that choice with specific
details
Does not present a recommendation for
DBMS products
15
Enterprise Data Model
Presents a comprehensive enterprise
data model for the chosen group
Does not present a comprehensive
enterprise data model
15
Security Management
Plan
Presents a recommendation for a
Presents a recommendation for a
highlevel security management plan that highlevel security management plan, but
applies to the whole organization
it is lacking in detail or does not apply to
the whole organization
Does not present a security
management plan
15
Cover Slide and Speaker’s
Notes
Presentation includes a cover slide and
thorough speaker’s notes that are clear
and organized
Presentation includes a cover slide and
speaker’s notes, but the notes are
unclear, do not thoroughly address the
content on the slides, or are
disorganized
Does not include a cover slide and
speaker’s notes
15
Articulation of Response
Submission has no major errors related
to grammar, spelling, syntax, or
organization
Submission has major errors related to
grammar, spelling, syntax, or
organization that negatively impact
readability and articulation of main
ideas
Submission has critical errors related to
grammar, spelling, syntax, or
organization that prevent
understanding of ideas
10
Summary of Problem
Presents an enterprise data model, but
does not clearly show how it is relevant
to the group that was chosen
Earned Total
100%
database management system solution
Scenario selected - Wild Wood Apartments
ANALYSIS OF ORGANIZATION
Introduction
The Wild Wood Apartments is an organization which deals with the leasing of apartments for
rentals. It is a very large company that comprises of large buildings that are widespread in Oregon,
California, Idaho, and Washington. Twenty sets of different apartments are owned by this
company. There are around sixty different apartments in every complex with varied dimensions.
Each of the apartment contains more rentals apartments and are leases within 6 months or year.
The purpose of this project is to build a centralized database system by using SQL that helps the
supervisors of Wild Wood Apartments to carry out the business with efficiency and ease regularly.
By improvising the central database of wild wood apartment’s complexes, it is easy to keep a track
of the daily business of every apartment and to send the daily reports.
Problem /challenges
This includes problems or challenges that the Wild Woods Apartment experiences while managing
the apartment and all of its related Items. Some of the problems experiences include having to
maintain each of the apartment information which relates to occupancy, leases, payments and
maintenance requests and expenses to name but just a few. This is enormous information for an
individual to manage and thus the need for having a good system to make the work easy for them.
Other than the above apartment information management other challenge is that the managers are
expected to produce a complete and a successful report in regards to the above outlined issues and
send them to the company headquarters back to California. By doing this the manager encounter
logistic and reporting challenges while trying to achieve this and on tight deadline.
Business requirements
Some of the business requirements for the Wild Wood Apartment includes:
•
To Allow the apartment management to generate reports on tenants, rent and expenses
among other important report
•
To allow the Apartment managers to insert new leases information, and tenant’s related
information.
•
To be able to track the apartments occupants and if there is any vacancies be able to known.
•
To be able to track each of the tenants rent payments and deposits.
•
To be able to track the apartments repair/maintenance information as requested by the
tenants.
Limitations of current system
Some of the noted limitation with the current system include:
•
Time consuming while managing and reporting the current finance status.
•
There lack Consistency in information, this is as a result of the manual operation that result
to data loss.
•
There is concern of the system reliability and even Verifiability of apartments’ related
information.
•
The apartment management, owner and even the tenants have limited access and even
concern of data breach.
•
Tedious process of printing reports and visual representation is not effective
•
Maintenances and repair requests
How the problem or challenge impacts the various departments and operations within the
organization.
The challenges identified affect he smooth running of the Apartment Company through the
following ways.
i.
The tedious and time consuming process of generating the required reports leads to
time wastage and thus dragging the important decision making processes within the
company and the apartment management.
ii.
There is missed deadline for requested repair and maintenance due to slow data
processes and even inefficiency, this can result to reduced revenue as more and more
tenants avoid the apartment due to no maintenance.
The overall almost all the departments of the company which include IT, finance and other are
affected to the challenges.
DATABASE ANALYSIS AND DESIGN
Conceptual model
A conceptual design of the database includes a high-level design, relational Schema, and relational
database management system (Davis, 2011). The conceptual model is independent of all the
physical considerations of the database such as the database management system and the operating
systems. The things that need addressing in the conceptual design of a database are entities and
relationships, information regarding entities, the constraints or business rules that must hold for
the entities and relationships
Logical model
Its purpose is to represent an application data in two-dimensional relational relations that
corresponds to the tables created in a relational database. In other words, the logical design
translates the conceptual design into a logical design that can have an implementation on chosen
DBMS. The output of this process is a relational schema and normalized relations. The logical
model of the database design offers a graphical view of the database, and this helps one to analyze
the structure of the database via entities and relationships
Keys
Cunit_ID – complex ID
Mng_ID –Manager ID
Exp_ID Expense ID
RevID Revenue ID
List of the Attributes and Entities
Details of the apartment Complex
•
ID of the apartment
•
Name of the apartment
•
Location
Details of the complex unit
•
ID of the complex unit
•
Unique number of the apartments in the complex
•
Detailed description of the apartment
•
Rent pricing
•
Rental status
•
Size/type
Details of the Manager
•
ID of the Manager
•
ID of the apartment in the complex
Tenant’s details
•
ID of tenant
•
Apartment
•
First name
•
Last name
•
Start date of lease
•
End date of lease
•
Phone number
Details for the expenses
•
Id for the expense
•
Type of expense
•
Date on which Expense took place
•
Id for manager accountable
•
Amount in expense
Details for the Revenue
•
Id for the revenue
•
Type of revenue
•
Date on which revenue was collected
•
Id for manager accountable
•
Amount for Revenues
Physical model
In this type of database design, there is the provision of a graphical view of the database structure
that helps one to analyze its tables, views, procedures and the difference that may exist between
them. It is the physical implementation of the logical database design that includes all the required
information that is in the requirement for database construction.
Physical design helps convert the logical design model of the database into a working database to
solve the problem at hand. The creation of the database, as shown in our physical database model,
will provide a database that can solve the problem that exists in Wild Woods Apartments
DBMS RESEARCH AND RECOMMENDATION
Database
Database or electronic database is called as a collection of data. Basically, data is organized
into tables. In tables there are rows and columns. For the convenience, database uses separate
tables for every data type. In this scenario, let us assume that there is data for apartments, units,
leases, payments, repairs, employers and expenses. For each of these data type, database uses
separate tables. Then DBMS will manage these entire set of tables.
DBMS
A Database Management System (DBMS) is a system software for creating and managing
databases and it is well-structured to allow users to do following tasks. (Rouse, 2015)
1. Data Definition
This is the process of creating, modifying and removing of definitions that define the
organization of the data.
2. Update
This function can be used to insert, modify and delete the actual data.
3. Retrieval
This provides information in a form directly usable or for further processing by other
applications.
4. Administration
This function is responsible for registering and monitoring users, imposing data security,
preserving the integrity of data and dealing with concurrency control. Information that has been
corrupted by some events (unexpected system failures) can be recovered using this.
Using a file system, it is difficult to access the exact data location. The reason behind this
could be the occurrence of same data in many places. If a data is needed to be updated, every place
has to be updated due to this.
Not only the manager or authorized person but also unauthorized persons can change the
data in this system and there is no backup for data. Therefore, it is important to have a DBMS for
Wild Wood apartment.
There are different types of databases.
1. Hierarchical databases
This is a very simple and fast database type. It stores data like a tree structure. This collects
all data together as a record type.
2. Network databases
This type is mostly used in large digital computers. This is somewhat similar to hierarchical
database but have an interconnected connection of records.
3. Relational databases
In this type of database, data files are connected using a common data or key field.
4. Object-oriented databases
This provides a database functionality to object oriented programs.
There are many types of database servers which can be used to create DBMS for wild wood
apartments. Most common and suitable databases are mentioned below.
1. MySQL
2. Oracle SQL
3. Microsoft Access
4. PostgreSQL
5. Microsoft SQL
Similarities and differences of the popularly used servers: MS SQL, Oracle SQL and
MySQL.
1. Chronology
Oracle was introduced by IBM Corporation in 1980 while Microsoft launched SQL in mid
1990s and these two were originally designed for commercial use. Contrastively, My SQL is an
open source database and it is a result of many individuals. Hence it is difficult to demarcate an
exact day of introduction for My SQL.
2. Licensing
Oracle and SQL can be identified as proprietary databases which have to be purchased,
even though My SQL is an open source database.
3. Interface
In Oracle and SQL, there are both GUI and SQL interfaces whereas only the SQL interface
is observed in My SQL.
4. Languages used
Most of the common languages are supported in Oracle and My SQL such as, C, C#, C++,
Ruby, Java and Objective C. In the case of SQL, some high-level languages are basically used;
namely, Java, Python, VB, Ruby, .net and PHP.
5. Operating Systems
For all three cases, Windows can be used as the operating system but Linux can only be
used for Oracle and My SQL. Solaris and OS X can also be used for Oracle and My SQL. Oracle
can be specifically used in AIX and HP-UX while My SQL can be used in FreeBSD.
6. Latest releases and their features
The latest edition of Oracle is Oracle database 12C which contains the following features.
•
New data redaction technology to enhance security of sensitive data
•
Oracle Advanced Analytics platform
•
New database handling for archiving Flash Data Archive (FDA)
•
Support for integrating with operating system processor groups
•
Support for data pump for database consolidation
•
Improvements to Oracle Application Express
SQL Server 2014 is the newest release of SQL and it consists of,
•
In-Memory Online Traction Processing (OLTP)
•
New solutions to handle disaster recovery
•
Updated SQL Server Data Tools for Business Intelligence (SSDT BI)
Analysis
Following table describes the various databases and their strengths and weakness.
Database
Strength
Weakness
My SQL
1. Easy to install and set up
1. Licensing cost is high
2.Open-Source
license
for 2. Stability issues
most users
3. Fast
Oracle SQL
1. Ability to group several 1. High cost
transactions into the same 2. More difficult to learn and
batch
operate
2. Multi-user support
3. Can work on both windows
and Unix server
Microsoft Access
1. Easy to install and set up
1. Database file may become
2. Easy to import data
corrupted or damaged.
3. Inexpensive
2.
Lack
of
Developer
Experience
3. Limitation of concurrent
usage
4. Only works on Windows
PostgreSQL
1. Works on both windows 1. Slower than MySQL
and Unix server
2.
Less
performance
in
2. High stability and reliability reading heavy data
3. No associated licensing cost
for the software.
4. Strong third-party support
Microsoft SQL
1. Gives good data recovery 1. Executes each transaction
support
individually in a sequential
order.
2. Licensing cost is high
(Sullivan, 2017)
Based on these features it is recommended Oracle SQL as the suitable database for the wild
wood apartments.
Recommendation
Grouping of several transactions of same batch for processing sets gives a great advantage
and scalability to Oracle SQL rather than other database servers. This means Oracle SQL expands
horizontally while most of other databases expand vertically. Vertical expansions use more server
memory and hard drive capacity than horizontal expansions. Since there are many transactions in
Wild Wood apartment, this feature of Oracle SQL can become beneficial in data management and
exploitation of space.
In addition to the above feature, Oracle SQL lets us use multiple servers on the same
database at the same time. Because of this service, Oracle SQL increases the processing power.
This will let manager, cashiers, etc. to log into the database at same time and interact with the
database simultaneously.
Oracle SQL gives us freedom to choose the operating system. This can be used in both
Windows and Linux servers while Microsoft Access and Microsoft SQL only can be used in
Windows. Linux is highly protective to most of viruses, which gives a high security for the
information. With the backward-compatible function, it is possible to upgrade Oracle SQL without
losing any data.
Hardware and software requirements of the system
Hardware requirements
Description
Project Requirement
Processor
Intel extended memory- Intel extended memory-EM64T,
Virtual Memory
Oracle Database Requirements
EM64T, AMD64
AMD64
10GB
4GB minimum
RAM
4GB
2GB minimum
Disk Space
100GB
5.22GB
As shown in the table project requirements are more than the requirements of Oracle
database requirements. This is done because new data is always added to the database. Therefore,
more space is needed to the database. Following are the software requirements which are needed
to the Wild Wood apartment database.
Software requirements
1. Operating system: Oracle SQL for Windows x64 is supported for Windows 7 or higher,
Windows server 2012, 2012 RS and 2016
2. Network protocol: Oracle supports for the standard network protocols named TCP/IP with
SSL, TCP/IP
3. Oracle database client: Any Oracle version which is 10.2.0.5 or higher support (Oracle, 2017)
DATABASE DATA MODEL
Wild wood Apartments
Operation Rules
Refers to definitions of constraints that act as the basis and influence of control in a
system Wilson C. (2016). From the above model the following operations rule can be made:
1. Each Apartment belongs to a particular apartment complex
2. Apartments lease for a six month or one-year long
3. Tenants can lease any number of apartments
4. Managers must be tenants
5. The managers should be able to admit new tenants, collect rent and close out leases of
related to apartments they manage.
6. Only managers get free rent and stipend
7. Managers are required to send reports concerning apartments they manage
Rule Reflection
Refers to how rules that defines operations of a system are implemented by a model in
context David Hay (2000) Group R, Inc.
The model portrays the following relationships, and we show how the model above supports
business rules
Each Apartment belongs to a particular apartment complex.
This rule describes the apartment units that tenants lease to live. A unit is contained in a
certain apartment complex for easy identification and also since the complexes site in different
physical locations. The model above models this in a relationship:
•
Apartment complex/ apartment unit
•
An Apartment complex has one or more apartment units
•
An apartment unit belongs to one Apartment Complex
•
This model affects this relationship which reflects the rule stated above.
Apartments lease for a six month or one-year long
This rule defines the basis regarding the renting time the apartment units. The model
above doesn’t have this rule modeled. Instead, the rule can be implemented by the DBMS since
it is a constraint.
Tenants can lease one or more apartment units
This rule defines that tenants can lease any number of units in any complex. By stating
this rule, one-to-many relationships introduced. The model affects the rule by defining this
relationship
•
Tenants/apartments units
•
A tenant can lease one or more apartment units
•
Only one tenant can lease an Apartment unit
Managers must be tenants
This rule gives a restriction that one had to be a tenant for them to be managers. The
model defines a one to one relationship which ensures that the manager is a tenant. A restriction
can be defined in the DBMS to ensure that data available in managers table as far as manager’s
ID is concerned first with the tenant's tables.
•
Manager/Tenant
•
A manager is a Tenant
•
An association of a tenant with a single Manager
The following rules can either implemented at the application-level or DBMS level
1. The managers should be able to admit new tenants, collect rent and close out leases of
related to apartments they manage.
This rule cannot be modeled but can be implemented at the application level together
with DBMS role-based access control method where a manager is restricted to perform
tasks concerning tenants who have leased the apartments they manage.
2. Only managers get free rent and stipend.
To accomplish this rule at the DBMS level where Managers are let off from rent
payment, and manage other benefits in stipends.
3. Managers are required to send reports concerning apartments they manage.
Implementation at the applications level where sessions are created to ensure that
authenticated and authorized managers allowed to access reporting modules.
LAW, ETHICS, AND SECURITY PLAN
Professional ethical and legal standards
Ethical refers to guidelines that software developers are expected to apply and follow
during programming code writing (IEEE, ACM). They form part of the employee-employer and
programmer-customer relationship. Good practices differ from the wrong ones during software
creation or crucial decision making with regards to programming project. The following needs to
be considered during software creation as part of legal and ethical standards: From the Code of
Ethics and Professional Conduct (ACM):
Privacy
Privacy refers to the right of online users concerning the use of their data about
provisions to third parties or display of information considered private to them. Developers
should write code that can protect people’s privacy as well as prevent unauthorized access to
private information.
Confidentiality
Confidentiality refers to secrecy to one’s information. Any additional information on the
project being worked on should be kept as a secret to other people who are not involved in such
project. This refers to information on documentation, plans, design and laid down structures.
Intellectual property
Intellectual property refers to the property ownership as well as value attached to that
property ( Bynum, Terrel). It divides the software community. Rights such as copyright which
protects an organization’s product from being copied or used to create new work by others which
deprives the owner income of their work. Copyright is made on the freedom of software which
can be outlined as the freedom to:
•
Run the software
•
Study the software with an option to view source code
•
Redistribution of the software copies
•
Release your changes after program change.
Encryption
Encryption refers to the conversion of plain text used for communication into some
cipher-text using relevant algorithms to prevent users who have unauthorized access to data from
understanding it. Lack of encryption. Could lead to issues such as identity theft to users who are
logged in. It guards data to be transited as well as stored critical data since breaches related to
security on stored data could lead to system compromise if unauthorized users were able to read
critical data details.
Need to avoid harming others
Computers with third party’s properties regarding software Fatal errors could lead to
informational and resources loss hence harming others. The software should not or minimize the
risk of harming others due to coding errors or flaws that are security related.
Honesty and Trust
This principle requires developers being honest and ware of limitation they have in
knowledge when writing computer systems. Anything wrong with the system code should be
reported immediately to avoid dire consequences that may follow.
Best practices to ensure ethical operations of the company
Accepting Full responsibility for own work
If a program possesses bugs and errors, developers should claim responsibility and agree
to revise, change, test or modify it.
Use of software that is obtained or retained illegally or unethically should be prohibited.
Wrongly obtained software poses threats to an organization’s system since patches to
security are not accessed by such software. Also, such programs may have been tampered with
leaving black holes that may compromise the organization’s network through that created
vulnerability.
Identification, definition and addressing economic, ethical, cultural, environmental and
legal issues related to work projects.
Programmers are required to report and inform on problems that may result by
conducting the project to the employer once they are identified.
Adequate testing, debugging and review of the software.
Developers should conduct appropriate tests to pieces of work they work on. They should
also do error checking and identification for system security holes to make sure that programs
are implemented and deployed.
Software approval if and only if it meets specifications and is safe
Developers should never assume that software is ready to use since it performs task
needed. They should make sure the system meets user specification and is safe for use. Unsafe
systems could lead to theft of data by hackers or money. Therefore, rigorous tests should be done
on a system before it’s approval.
Developers are improving the ability in the creation of reliable, safe and useful quality
software.
Technology is a field that keeps advancing year by year and so does the criminals
expertise. Well-structured and designed programs should be increasing also to curb such criminal
expertise and keep up with trendy security measures (Software Engineering Code of Ethics and
Professional Practice).
Not engage in deceptive financial practices
Programmers should not engage in deceptive financial practices. They may be forced to
engage in such activities due to economic issues, threats or due to wanting to get easy money and
taking advantage of their knowledge about how system work. The developers are prohibited
from such practices
Security Needs for Wild Wood Apartments DBMS
Authentication
The process of determining that users are who they claim to be. which ensures that only
intended users can access the database structures and records stored. Users who do not have
credentials are unable to access information not intended for them.
Data Encryption
Encryption protects data from being understood by unintended users who may gain
access to the DBMS. It ensures that data stored is in a format unreadable to unintended parties.
Which should add security over the preceding security layers which involve authentication and
authorization.
Integrity controls
Integrity controls are those that ensure that data stored is not altered with from the state it
was when saved last. Controls such as entity integrity and referential integrity are some of the
controls which maintain data consistency and accuracy over its life cycle.
Access Control
Refers to selective restriction to database records and structures to different authorized
users are assigned different responsibilities based on the defined basis by the database
administrator and policies in place. This ensures accountability and limited privilege are
achieved for effective database management.
Backups
Backups provide an alternative data source for recovery and restoration in case of an
event leading to data loss or DBMS damage. Images and copies of the Database are made on
external hard drives stored in different locations as the DBMS to facilitate recovery in case of
failure.
Security needs Comparisons
Wild Wood apartments managerial level discussion
The system has a managerial level that should ensure the following:
Authentication: The system asks for credentials and confirms that managing users are who they
claim to be.
Authorization: The system should only allow users of the managerial level to access the
managerial capabilities.
Access control: Only users with managerial control should be allowed to create users, edit user
data and print reports. The managers should also be limited to data of users who they manage.
Data privacy: The system should ensure that user tenant’s data is protected and not used without
their knowledge or accessed by other users who may lead to their harm.
•
Information Security management plan
•
This security management plan aims to safeguards customer’s data protecting it from
unauthorized access and to:
•
Ensure confidentiality and security of covered information and data.
•
Offer protection against anticipated hazards or threats as far information security is
concerned
•
Provide unauthorized system access protection that may inconvenience customers.
The plan provides mechanisms for:
•
Risk identification and assessment which may threaten information and data covered by
Wild Wood apartments
•
Policies and procedure management to control risks.
Risk identification and assessment
Risks exposed to Wild Wood apartments system include:
•
Data integrity loss
•
Data interception during transmission
•
Security compromise resulting from unauthorized access
•
Data and information unauthorized access
•
Data or systems corruption
Design and implementing security plan
Employee training
Awareness should be created for employees to handle sensitive data. They should be
made aware of the criticality of the data they handle and how a little compromise would impact
the company negatively. Training should include controls and procedures that direct users on
how to keep data confidential
Physical Security
Security physically of data covered by the organization should be limited only to
employees of Wild Wood apartments only. Files and hard copy documents should be kept in
cabinets which are lockable. Vaults and other strong rooms should be available to store valuable
documents.
Information systems
The information systems should be able to authenticate users before they get access to the
system resources. Only employees who should access critical database information. Strong
passwords should be enforced by the system same. Intrusion detection systems for threats should
be kept in place for prevention of threats before they attack
Management of system failures
Written plans and procedures should be kept in place to deal with system failure. In an occurrence
of system failure, responsibilities should be assigned on the procedures to be taken till system
recovery.
Purchase answer to see full
attachment