Make your own free website on Tripod.com

 

Home Sweet Home

System Analysis Report

 

 

Final project

IT 350 and IT 360

DeVry Institute of Technology, Fremont

 

 

Prepared by

Hae Jeoung Hwang-Florip

Manuel Uson

Jay Chun Wang

Maksim Ivanenko

Alan Law

Table of content

 

Table of content 2

OVERVIEW 5

Methodology. 8

Project team roles and skills. 8

Planning. 10

Identify business value. 10

SYSTEM REQUEST 10

Technical Feasibility Analysis 11

Economic Feasibility Analysis 12

FEASIBILTY ANALYSIS GLOSSARY 15

Organizational Feasibility Analysis 16

Project Management. 16

Work plan. 16

Staffs project. 17

Projects standards development 17

Identify projects risks. 18

Analysis. 19

As-Is system. 19

Gather information: 19

Identify improvement opportunities. 26

1) Business process automation. 26

2) Business process improvement. 29

3) Business process reengineering. 29

Immediate Recommendations 31

To-Be system. 32

Data flow Diagram 32

Entity Relationship Diagram 36

Home Sweet Home Realty Business Rules 39

Operational constraints. 39

Structural constraints. 40

Normalized table schema. 42

Relational tables 44

Database implementation in MS Access 45

Data entry forms 45

Queries 47

Tables 50

User Manual 52

Web page prototype 63

Summary 64

Part 1: Automation of Process (immediate implementation) 69

Part 2: Technology Enhancements (implementation after 3 months) 69

Part 3: Explore new possibilities of e-commerce (optional). 70

 

 

OVERVIEW

Home Sweet Home (HSH) is a relatively small, family-owned, yet rapidly growing real estate office with a vision on the future. During the latest bullish state of the economy, HSH benefited tremendously from the boom in housing prices in the local area. For the 1998/99 year, HSH had an operating surplus of $250,000, which was a 13% increase over the previous year. As a result, HSH gradually expanded its reach in the local area to take advantage of the steady rise in housing and real estate demands. HSH is also in the property management business acting as an agent for client landlords resulting in approximately 10% of its total revenue.

As with any small business, HSH is undergoing change. Currently, HSH is focusing on adapting to the rapidly growing real estate business and preparing for the future. Alison, the daughter of the original owner, now is in charge of HSH supported by six additional staff members varying in job duties and skills. The business has good credit, significant cash on-hand, and just purchased with a loan the next-door premises with expansion in mind. Over the years, Alison’s father developed a solid rapport with local banks, solicitors, surveyors, building societies and other industry pillars in the community. HSH is convinced it can stay competitive in the region due to its reputation and traditional delivery of a high quality product. But to succeed, HSH realizes it needs to change the way it manages its information.

The above-mentioned loan was secured partly on the basis of the business plan. The business plan suggests it is imperative HSH improve its current processing system to cope with the anticipated growth in business. A computerized system to manage all HSH’s information would enable HSH to be more effective, efficient, and ultimately, more profitable. By offering a higher quality product, HSH would have the opportunity to sell more property. Alison’s goal is to double the turnover in 3 years.

Instead of initially undergoing the computerization of its current system on its own, HSH decided to take the precaution of commissioning our firm, the Giant Consulting Group, to go ahead and investigate their existing information and make recommendations for a future system. We will be presenting the results of our detailed analysis, including feasibility, new system recommendations, and associative risks, to HSH on the August 29, 2001.

Part of the initial process will be reviewing the need for a new system, the system’s functionality, the value the system will provide, and any possible constraints with HSH. We will also conduct a feasibility analysis (technical, economical, and organizational aspects) for building the system and calculate its return on investment. This process will be done in close conjunction with HSH management.

Next our consultants will be performing in-depth analysis using a variety of tools and data models to arrive to a new system concept. The analysis phase is a 3-step process:

Understand the current or "as-is" system

Identify improvement opportunities

Develop the new or "to-be" system concept

First will be to understand the current or "as-is" system in place. This will be performed using process and data models that explain how the business operates and how information is processed. Through our expertise, we first examine and analyze the current system before recommending ways to improve it; the basis is keeping the company’s integrity and reputation intact.

The second step is to identify improvement opportunities through the use of a variety of analysis and information gathering techniques. Some analysis techniques we may use include root cause analysis, duration analysis, activities costing, and technology analysis. Information will also be attained through interviewing staff, observing processes and activities, reviewing documents, and possibly conducting joint application sessions with HSH staff. Only after analyzing the attained information will we be able to derive our recommendations package for a new system.

The third step our consultants will be presenting recommendations for a future system. First and foremost, the need to computerize the current system using a database management system is the top priority. The future system will be known as the "to-be" system. To support our recommendations package, we will be presenting some of the following:

"To-be" process and data flow diagram model

Applied business rules

Entity relationship diagram

Working database model and user manual

User/viewer screen examples, including forms, queries, and reports

Other recommendations, such as using e-business tools, digital picture viewing, and the Internet may be included but in later stages.

HSH, after our proposal, will have a much clearer picture how their business uses and processes information. Our recommendations for automating their current information system will not only benefit HSH, but their customers as well. Increased efficiency, accuracy, quality, and profitability are all targeted outcomes. HSH realizes the need to change their current system to stay competitive. By doing so, HSH will be prepared to take on the enormous growth of their industry and will stay competitive well into the 21st century.

Giant Consulting Group

Hae Jeong Hwang-Florip

Manual Uson

Jay Wang

Maksim Ivanenko

Alan M. Law

Methodology.

We decided to abolish long development times and the difficulty in understanding a system from a paper-based description (which are disadvantages of structured design) and focused on Rapid Application Development approach.

Our group chose through away prototyping methodology, because it’s balances the benefits of well-thought-out analysis and design phases with advantages of using prototypes to refine key issues before a system is built.

 

Our selection of through away prototyping was based on:

User requirements are unclear or unstable;

Our familiarity with technology is in a learning stage;

Systems complexity;

Users high priority is systems reliability;

Short time schedules;

Good schedule visibility.

Project team roles and skills.

Like other small organizations, we decided not to distinguish roles among our group members and utilize everyone’s skills for projects educational purposes. Each group member had a chance to experience responsibilities of:

Business Analyst Analyses the key business aspects, identifies how system will provide value, designs new processes and models. System Analyst Identifies how technology can improve business processes, designs new business processes and information system, ensures that system conforms to information systems standards. Infrastructure Analyst Ensures conformation to infrastructure standards, identifies infrastructure changes needed to support the system. Change management Analyst Develops and executes changes in management plan and training plan. Project manager General management of projects team, develops and monitors projects plan, assigns resources, primary point of contact for the project. Programmer Writes programs to implement system. Technical writer Prepares documentation to support project.

 

Planning.

Identify business value.

Enclosed business request provides basic information about proposed system.

 

 

SYSTEM REQUEST

 

Project Name: Home-Sweet-Home (HSH) Automation

Project Sponsor: Alison Schwab

Phone: (123) 123-4567

E-mail: Alison@hsh.com

 

Business need: To automate and simplify steps required selling real estate property: Increase efficiency and profit by lowering operating costs.

 

Functionality: By creating a centralized database, we should be able to eliminate unnecessary steps and shorten the processing time dramatically. The project emphasizes on automation; however, we will also introduce a company website for customers to access property information available by HSH.

 

Customers can research on company website for properties, request information by E-mail or fax directly from the website. All information is linked to company’s centralized customer database.

Schedule appointments to visit the properties online

Expected value:

Tangible: 200% turnover over 3 years

Intangible:

Improved customer satisfaction

Improved recognition of HSH

Special issues or constraints:

With the strong economy and real estate property boom in the past 12 months, it is essential for the business to gear up and prepare itself for the increasing business. Also facing stiff competition from new Estate Agencies in the area, HSH must retain a market edge against competitions by upgrading its service. With HSH’s strong tradition and reputation, the company will be able to provide customers with a better service.

Technical Feasibility Analysis

Familiarity with Application (low)

No experience with internet-based real estate applications

Minimal database management experience

Familiarity with Technology (moderate)

No formalized IT department

One staff member with moderate computer skills

Owner will little IT knowledge

Project Size (moderate)

Estimate project will be moderate in size with room for growth

Implement centralized system to manage activities

Overall rating: risky.

Economic Feasibility Analysis

 

Development Costs

$64,000 one time development cost

 

Annual Operating Costs

$46,000 annual operating cost

 

Overall Benefits (Cost Savings and Revenues)

$616,210 in annual benefits

 

Overall Tangible Costs and Benefits

Double turnover over 3-year period

Cash benefits estimate $376,465 NPV.

Return on investment at least 201%.

 

Intangible Costs and Benefits

Improved customer satisfaction

Reduced paper-load

Dramatic decrease in duration process

Improved quality and efficiency

 

Overall rating: excellent.

 

Memo: 4 represents total of 3 years

 

  Determine Cash Flow  
  BENEFITS Year 1 ($) Year 2 ($) Year 3 ($) Total ($)  
  Increased Sales (6%)   253,500 268,710 522,210  
  Improved Customer Service   10,000 10,000 10,000  
  Reduction in Mailings/Supplies   12,000 12,000 12,000  
  Improved Customer Service   25,000 25,000 50,000  
  Total Benefits   300,000 315,710 616,210  
  DEVELOPMENT COSTS        
  Labor      
  Planning, Analysis, & Design 5,000     5,000  
  Programming 20,000     20,000  
  Web Design 5,000     5,000  
  Training 2,500     2,500  
  Consultation 1,500     1,500  
  Hardware      
  Infrastructure/Network 10,000     10,000  
  Equipment & Peripherals 15,000     15,000  
  Software 5,000     5,000  
  Total Development Costs 64,000     64,000  
         
  OPERATIONAL COSTS        
  Hardware Maintenance 1,500 1,500 1,500 4,500  
  Software Upgrades 1,000 1,000 1,000 3,000  
  Software Licenses 100 100 100 300  
  Communication Charges 3,000 3,000 3,000 9,000  
  Labor (3%)        
  IT Manager 20,800 21,424 22,067 64,291  
  Staff Member 10,400 10,712 11,033 32,145  
  User Training 1,000 1,000 1,000 1,700  
  Utilities 1,200 1,200 1,200 3,000  
  Marketing Expenses 7,000 7,000 7,000 21,000  
  Total Operating Costs 46,000 46,936 47,900 140,836  
         
   
  TOTAL COSTS 110,000 46,936 47,900 204,836  
  NET BENEFITS (110,000) 253,564 268,810 411,374  
  ROI 201%  
  NPV 376,465  

FEASIBILTY ANALYSIS GLOSSARY

 

BENEFITS:

Increased Sales - selling 5 houses per week averaging $250,000 per house taking 3% brokerage fee. Anticipate 13% sales growth (adjusted to inflation @6%).

Reduction in office supplies and mailing to customers.

Increased productivity - anticipate new system to dramatically reduce processing duration time creating more resources for sale and new business opportunities.

Increased customer satisfaction - anticipate new system to attract at least 3 new customer transactions due to quality, advertising and service improvement. Reduction in complaints, business loss and problem resolution follow up.

DEVELOPMENT COSTS:

Planning, analysis, and design fee - current going rate.

Programming - current going rate.

Web design - current going rate.

Training - 40 hours @ $50 per hour.

Consultation - commissioned rate.

Infrastructure/Networking - basic rate for two buildings including wiring, server closet, and future expansion.

Equipment and Peripherals - one server, four computers, monitors, input devices, laser printer, scanner, digital camera, web cam, fax/phone, Nextel’s, backup tapes, discs, cabling.

Software - server, operating system, office applications, maintenance and security software.

OPERATING COSTS:

Hardware maintenance - basic repairs and upgrades.

Software upgrades and additions - upgrade versions, new licensed software.

Communication charges - ISP rate @ $50 per month and ISDN line rental @ $100 per month.

IT Manager salary - $20,800 yearly.

Staff member - $200 per week, $10,400 per year with 3% annual raise.

User training - $1,000 yearly.

Utilities - $1,200 per year.

Marketing - advertisements, publicity and promotions

 

Organizational Feasibility Analysis

Project Champion(s)

Agency Manager- Alison Schwab

Senior Management

Jean Claude- strong support because it will better organize and streamline workload

Users

Customers will benefit with convenience, accessibility, and quality improvement

Employees productivity increase

Other Stakeholders

Staff personnel could be assigned different tasks due to computerization

Systematic employee training required

Will require flexibility and multitasking from employees

Overall rating: excellent.

 

 

Project Management.

 

Work plan.

  Planning Analysis Design Implementation
Industry Standards 15% 20% 35% 30%
Actual 5 person-days 7 person-days 10 person-days 9 person-days

 

 

Staffs project.

 

Giant group members are equally responsible for systems final outcome.

Everyone applies learned academic skills to bring necessary task solution.

Use all available outside class recourses that are applicable to the current project.

Motivate each other to make assignment a success.

Minimize conflict among group members and promote group cohesiveness.

 

Projects standards development

 

Refining original estimates.

Tracking tasks.

Coordinating project activities.

Documentation.

Managing scope.

Mitigating risk

Avoid scope creep (addition of extra requirements to a predefined set of tasks) when possible.

 

 

 

Identify projects risks.

Risk assessment example.
Risk: The development of this system likely will be slowed considerably because project team members have not used SQL, MS Access and html language prior to this project.
Likelihood of risk: High probability of risk.
Potential impact on the project: The risk will increase the time to complete project by 50%.
Ways to address this risk: Adequate training will reduce the initial learning curve. Outside resources could be involved. Create general prototype and drop more complicated non-essential features.

 

Possible solutions:

Publicize;

Avoid;

Eliminate root cause.

 

Analysis.

 

As-Is system.

 

Gather information:

Interviews.

Joint Application Design.

Questionnaires.

Observation.

Document Analysis.

Benchmarking.

 

 

Information Gathering Plan
Step Technique Specific Activities
Understand the As-Is System Interviews · June to interview Alison - Agency Manager - using open ended questions for desired outcome and expectations and closed ended questions for specifics of expected growth and system capacity required. · Alan to interview Jean - Office Manager - using open ended questions for management view of the issues and expectations. A series of closed ended questions for staff productivity and expertise. · Jay to interview Kate & Iain - Office clerical staff - using closed ended questions to understand the details of the existing system and a view of day-to-day operations and system interface issues. · June to interview Past Vendors (Property Sellers) - for feedback on the process. · Max to interview Harry of Harry Signs - for possible improvements · Max interview the Local Press - for external view · Alan to interview 5 Tenants - for account handling · Manuel to interview the Bank Manager - for financial history
  JAD Sessions · Max to interview Sanah, Cath, Nick - Tours and Customer Liaison - using a JAD session to understand the use of the current information and current tour process giving a view of the as-is system.
  Questionnaires · Jay to distribute and collect questionnaires from 50 of the Landlords - for services provided and satisfaction. · Manuel to distribute questionnaires to all of the Potential Buyers using HSH during the analysis phase for feedback on ease of use of the services and systems.
  Document Analysis · Max and Alan with review the process and data models for the listings, accounting, file storage, contact management, and any other systems, which are used for information, flow.
  Observation · Jay to join the HSH staff for fifteen days as a real estate sales person to understand first hand and observe the day-to-day processes.
Identify Improvements Problem Analysis · Develop a list of features for the new information system that is used to gather, distribute, and advertise information about the inventory of real estate handled by HSH.
  Root Cause Analysis · Team analysis of the data gathered in the information gathering stage will determine the fundamental problems with the current system.
  Duration Analysis · Team will analyze the length of time required to gather file and advertise a home sale listing in the current, as is process.
  Activity-based Costing · Alan to determine which activities in the current system has the largest cost to HSH Real Estate.
  Informal Benchmarking · June to visit other real estate agency web sites to gain information about how other agencies market their web site and property listings.
Develop To-Be System Concept Basic System Concept · Team to produce a list of recommendations that would be combined to make up a bare bones description of the changes to be implemented.
  JAD Sessions · Max to facilitate a JAD session with the entire HSH staff to provide details on how the new system will operate
  Develop Data Flow Diagrams · Giant Team will develop a series of DFD fragments which will be combined to form the final Level 0 DFD for the to-be system

 

Identify improvement opportunities.

Business process automation.

Automation by applying computer technology

Provide new tools to increase efficiency by speeding up or simplifying employee’s work.

Examples:

a. MS suite (mail merge feature, spreadsheets, calendar; publisher etc);

b. New computers;

c. Prearranged forms and other documents;

d. E-mail and fax vs. postal mailings;

e. Bookkeeping software (Quicken);

f. HSH LAN and Internet connection;

g. Digital camera, scanner, printer, PAD, Nextel’s;

 

Home Sweet Home

1739 Realty Road

DreamsVille, NV

Phone: (123) 123-4567

Fax: (123) 123-4576

Alison@hsh.com

April 17, 2002

<<F_Name>> <<L_Name>>

<<Address >>

<<City>>, <<State>> <<Zip>>

 

Dear <<F_Name>> <<L_Name>>:

Thank you for choosing "Home Sweet Home" estate agency.

Our mission is to provide you with quality service in local Real Estate Market.

We are proud of our strong tradition in excellence and hope you’ll enjoy our services.

Features at a glance:

Friendly staff assistance

In-house paper processing

Comprehensive marketing

Fast and easy financial approval

Convenient property tours

Personalized recommendations

References to respected business partners

Compatible prices

 

For more information please visit our website at www.hsh.com. We’ll appreciate your comments and recommendations. Our customer service representatives could be reached at custserv@hsh.com or by phone (123) 123-4577.

We look forward to serving your real estate needs for years to come.

Sincerely,

Alison Schwab

Owner and General manager

Home Sweet Home

1739 Realty Road

DreamsVille, NV

Phone: (123) 123-4567

Fax: (123) 123-4576

custserv@hsh.com

April 17, 2002

<<F_Name>> <<L_Name>>

<<Address >>

<<City>>, <<State>> <<Zip>>

 

Dear <<F_Name>> <<L_Name>>:

We pleased to bring you new property available for your review:

Fact sheet:

 

<<Location>>

<<Size>>

<<Rooms>>

<<Baths>>

<<Style>>

<<Price>>

<<Comments>>

<<Description>>

For additional information and/or to schedule tour
please e-mail: custserv@hsh.com or call (123) 123-4577.

 

 

Sincerely,

Alison Schwab

Owner and General manager

Business process improvement.

Improvement in efficiency and effectiveness

Implement centralized database approach to integrate and share data throughout organization. Introduce On-line business communication and search through World Wide Web. Integrate web server with database.

ØGradual implementation

ØTraining

ØRevise management policies

ØSystem support, maintenance & security

 

 

Business process reengineering.

Fundamental rethinking and radical redesign of business process.

Explore possibilities of e-commerce in a real estate market

ØWeb based business world

Ø24/7 operation time

ØHuge info resources

ØUnlimited market area

ØReal time transactions

ØUsers convenience

ØHigh risk

ØRequires expertise and quick adjustments to new technology and market fluctuations

 

 

 

 

 

Side by side Comparison
  BPA BPI BPR
Risk Low Medium High
Cost Low Medium High
Implementation time Fast Slow Long
Breadth of analysis Narrow Moderate Good
Benefits Immediate Gradual Questionable
Increase in process duration Fast Faster Real time
Knowledge Basic Intermediate Competence
Potential business value Low Medium High

 

 

Immediate Recommendations

As-is system Improvements Benefits Risk
Paper filing Computer processing and storage Accuracy, efficiency, better organization and retrieval Low
Typing Document preparation automation Quality, time, cost Low
Management Administrative improvements, training, communication Efficiency, productivity, customer satisfaction Medium
Advertisement Centralized targeting, Eliminating redundancies, new technology methods Increase customers response and sales, improve company’s image Medium
Bookkeeping Follow up/updates, training Debt collecting, bill handling Low
Long process duration Implement automation and on-line approval process Quick turn around, increase in profits, customer satisfaction Medium
Inadequate equipment New computers, licensed software, Nextel’s, Internet link Better way of communication, new opportunities for growth, increase in quality and quantity of operations, avoid potential law suits Low

 

To-Be system.

Data flow Diagram

 

 

 

 

 

 

 

 

 

 

 

 

Entity Relationship Diagram

 

Home Sweet Home Realty Business Rules

 

Operational constraints.

 

Declarative.

Home Sweet Home is an independent Real Estate Agent; a small business owned and operated by Alison Schwab by following standard State and Federal rules and regulations.

Employee is a person, hired for pay by HSH to conduct business practices according to agreed on contract and regulations.

Customer is a person or organization who used or potentially could use services of Home Sweet Home Realty.

Seller is a HSH customer, wishing to put on the market property with proven rights of ownership or representation.

Buyer is a HSH customer, who is potentially able to acquire available property.

Contractor is a HSH customer, who could be engaged in HSH operations based on specifics of contract or agreement between concerned parties.

Property is owned immovable possessions, such as land and/or manmade constructions.

Contract is a written agreement among involved parties that specifies their obligations, which are enforceable by law.

Tour is an opportunity of personal examination of presented property.

Recommendations are an optional advises, guidelines, prospects and/or predictions provided to customers without any legal liability from HSH employees.

Assistance is an optional accountable service provided by HSH employees to customers.

Links are directions for obtaining additional and/or optional information or services from parties outside HSH scale of operation.

File access is secure system that provides authorization to obtain data from sources inside HSH structure.

 

 

 

Procedural.

HSH owner assigns manager, whose responsibility is to supervise activities of all other employees, keep track and convey development of operations to owner.

Every employee entitled for lunchtime, scheduled brakes, sick leave and vacations.

During business hours HSH must always have at least 1 office employee and 1 field representative available for customers.

Every new customer needs to submit contact information, which should be recorded in HSH files.

HSH employee schedules appointments with customers using company’s timetable.

After each course of action HSH employee need to update related existing files.

HSH employee needs to verify buyer’s financial status before processing any offers.

After receiving property records, HSH employee must forward acquired information to marketing.

Seller is charged $50 handling fee for each property withdrawn from HSH market.

HSH collects commission and/or fee right after successful deal closure.

Structural constraints.

Relationship constraints.

Every customer identified by unique customer ID number.

Every property identified by unique property ID number.

Every employee identified by unique employee ID number.

Any seller could have more than one property for sale.

Any buyer could bid on any available property offers.

Every contractor could have any number of contracts with HSH.

Employee could have any number of skills.

Any Customer could schedule any number of different tours.

Seller could promote property by various methods.

Buyer could have only one property pending for sale.

Each contract identified by its contract ID number.

One employee could supervise many other employees.

Property status could be posted either for sale or rent.

Process status could be either available, pending or sold.

Super type/subtype.

Customer could be categorized as buyer, seller and /or contractor.

Information could be sorted as contact info, request for services, for file access and contract offer.

Services could be specified as tour arrangement, recommendations and assistance.

File access could provide entrance to property’s attributes and/or links to other businesses.

Contractor could be classified as lawyer, advertiser, banker, appraiser, business developer, educator, home improvement agent, caterer, etc.

Assistance could be clustered as appraisal, maintenance, advertisement, financial, rent collection, moving services, security, etc.

Examples:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Normalized table schema.

 

Normalization is the process of decomposing relations with anomalies to produce smaller, well-structured relations.

 

First normal form

Relations contain no multivalued attributes.

Second normal form

Any partial functional dependencies have been removed from 1st normal form.

Third normal form

Any transitive dependencies have been removed from 2nd normal form.

List of all HSH entities attributes.

Customer_id Proreprty_id Employee_id Contract_id  
F_name L_Name Address City  
Zip Phone Fax E-mail  
Size Price Picture Rooms  
Baths Location Style Description  
Type Status Time Comments  
Skill Availability Pay_rate Hours_worked  
Nextel_# Notes COMMENT Category  
STATUS Preference Financial Restriction  
Name Directions Charge Bill  
Business Category Advise    

Normalized tables

Customer
Customer_id F_name L_Name Address City
Zip Phone Fax E-mail Category
Financial Preference Status Comments Notes

 

Property
Property_id Size Price Picture Rooms
Baths Location Style Description Status
Type Time      
Employee
Employee_id Skill Availability Pay_rate Hours_worked
Nextel_# Notes COMMENT Name  

 

Contract
Contract_id Customer_id Employee_id Property_id Business
Charge Bill      

 

Links
Name Directions      

 

Assistance
Business Customer_id Employee_id    

 

Recommendations
Advise Employee_id Comments Notes  

 

Tour
Time Property_id Customer_id Employee_id  

 

Relational tables

 

Database implementation in MS Access

 

Data entry forms

Main Menu Form

Customer Information Form

Property entry form

 

 

Appointment (Tour) form

 

Links report

 

 

Queries

To find out all property records with 2 bedrooms

SELECT *

FROM property

WHERE number_of_rooms=2;

Property ID Size Price Number_of_Rooms Number_ of_ Baths City Type Status Comments Garage
1 2000 $320,000.00 2 3 San Jose Townhouse For Sale Like new 2
5 1300 $324,000.00 2 1 San Francisco Condo For Sale Close to BART 1
7 1400 $275,000.00 2 2 San Leandro Condo For Sale Friendly neighborhood 1


 

 

To find out all property records with price range less than $2,500.

SELECT *

FROM property

WHERE price<=250000;

Search Property Under 250k
Property ID Size Price Picture Number_of_Rooms Number_ of_ Baths City Type Status Comments Garage
2 1700 $200,000.00   1 1 Union City Condo For Sale Great shape 1
6 900 $2,400.00   1 1 San Francisco Apartment For Lease Near supermarket 1
11 1900 $229,000.00   3 2 Fremont House For Sale Redesigned 1

 

 

 

 

 

To list all sellers

SELECT *

FROM Customer

WHERE category="S";

Seller Query
Customer ID Contact First Name Contact Last Name City State/Province Postal Code Phone Number Email Address Notes Category
1 Alan Law Fremont CA 95555- (555) 555-5555 alanl@yaboo.com Well informed S
2 Jay Wang San Jose CA 95131- (222) 222-2222 JayM@hotmail.com Contact on cell S
3 Max Smith Sunnyvale CA 95051- (111) 111-1111 max@juno.com Cool dude S

To list all possible buyers in database

SELECT *

FROM Customer

WHERE category="B";

 

Buyer Query
Customer ID Contact First Name Contact Last Name City State/Province Postal Code Phone Number Email Address Notes Category
5 June Hwawng Sunnyvale CA 95051- (123) 233-3333 juneh@msn.com Loves flowers B
4 Dick Tracy Milpitas CA 99999- (113) 454-3566 dtracy@yaboo.com Classy B
6 George Michael London UK 91543- (123) 456-7899 gmichael@aol.com He’s very picky B
7 June June Seoul KR 12222- (088) 299-9999 jj@hotmail.com She’s picky B

 

 

Tables

 

Customer
Customer ID Contact First Name Contact Last Name City State/Province Postal Code Phone Number Email Address Notes Category
1 Alan Law Fremont CA 95555- (555) 555-5555 alanl@yaboo.com Well informed S
2 Jay Wang San Jose CA 95131- (222) 222-2222 JayM@hotmail.com Contact on cell S
3 Max Smith Sunnyvale CA 95051- (111) 111-1111 max@juno.com Cool dude S
4 Dick Tracy Milpitas CA 99999- (113) 454-3566 dtracy@yaboo.com Classy B
5 June Hwawng Sunnyvale CA 95051- (123) 233-3333 juneh@msn.com Loves flowers B
6 George Michael London UK 91543- (123) 456-7899 gmichael@aol.com He’s very picky B
7 June June Seoul KR 12222- (088) 299-9999 jj@hotmail.com She’s picky B

 

 

Property
Property ID Size Price Picture Number_of_Rooms Number_ of_ Baths City Type Status Comments Garage
1 2000 $320,000.00   2 3 San Jose Townhouse For Sale Like new 2
2 1700 $200,000.00   1 1 Union City Condo For Sale Great shape 1
3 2300 $600,000.00   4 3 Milpitas House For Sale Close to park 1
4 1800 $465,000.00   3 2 Fremont House For Sale Magnificent view 2
5 1300 $324,000.00   2 1 San Francisco Condo For Sale Close to BART 1
6 900 $2,400.00   1 1 San Francisco Apartment For Lease Near supermarket 1
7 1400 $275,000.00   2 2 San Leandro Condo For Sale Friendly neighborhood 1
11 1900 $229,000.00   3 2 Fremont House For Sale Redesigned 1
12 6000 $2,000,000.00   5 3 Saratoga House For Sale Big backyard 1

 

 

Employees
Customer ID Contact First Name Contact Last Name City State/Province Postal Code Phone Number Email Address Notes Category
8 Jean Claude DreamsVille NV 11232-222 (103) 933-2233 jean@exite.com Manager E
9 Kate Bush DreamsVille NV 27112-1122 (566) 556-6666 Bush@aol.com Secretary E
10 Ian Scherman DreamsVille NV 11111-1222 (111) 111-1111 Ian@ryry.com Computer guy E
11 Sanah Dole DreamsVille NV 11111-1112 (111) 112-2211 dole@apple.com Field agent E
12 Cath Lee DreamsVille NV 11111-1112 (111) 112-3111 Lee@jeans.com Field agent E
13 Nick Carter DreamsVille NV 11111-1112 (112) 112-2213 Carter@bsb.com Field agent E

 

 

 

User Manual

Written and designed by:

Hwang-Florip, June

Uson, Manuel

Wang, Jay-Chun

Ivanenko, Maksim

Law, Alan

 

 

Introduction

Chapter I Entering & Viewing Customer Database

Chapter II Viewing Buyer & Seller Report

Chapter III Property Info Report, Entry & Print

Chapter IV Appointments

Chapter V Quit Application

 

 

 

 

 

 

 

User’s Guide

 

 

Version 1.00

(August 2001)

 

 

 

 

Copyright 2001 H.S.H. (DeVry Institute of Technology, Fremont) IT 350 & 360

 

Introduction

This application was designed to be user friendly and developed with users that had little or no knowledge in mind. By keeping the lay out simple, a GUI (graphic user interface) menu was created as a guide. Whether the applicants want to view a property, schedule an appointment or even enter in new customer information, they are only a click away.

Fig. 1.0

 

 

 

 

 

Chapter I: Entering & Viewing Customer Database

 

I. To viewing a list of the customer database, click View Customer command button. A report of the customers in the database will generate automatically. (See Fig. 1.1)

 

Fig. 1.1 Customer Information Report

 

 

II. To enter new customer information, please simply click on New Customer command button. Then the application will load the page:

 

 Fig. 1.2 Customer Info Entry form

 

When prompt the new customer entry page, you have several options. To add a new customer into the database, simply click on Add Record, and then enter in all the fields.

The user will have the option to scroll through the database. Simply click on ">" to go to the next customer or "<" to go back to the previous customer.

To delete a customer, simply use the ">" or "<" buttons to select the customer, then click Delete Record.

To save and exit, simply click on Exit button, application will return to the main menu.

Note: Please be aware, once the customer information is removed, it cannot be retrieved.

 

 

Chapter II Viewing Buyer & Seller Report

 

To view a report of all the sellers or buyers in the H.S.H. database, simply click on either View Buyer or View Seller button. These reports are for viewing only, thus cannot be edited. To edit, add or delete buyers or sellers, you must return to the previous section and do it from the New Customer command button. Buyer and Seller reports are presented in the following given examples:

 

Fig. 2.0 Seller Report

Fig. 2.1 Buyer Report

 

 

Chapter III Property Info Report, Entry & Print

 

I. To view a report of all the properties available in the H.S.H. database, simply click on View Property command button from the menu page:

Fig. 3.0 Property Info Report

To print this report, simply click on the Print Property command button.

 

To access the property information database, simply click on Property Entry command button from the menu page, application will prompt the user to the page:


Fig. 3.1 Property Entry Form

The property entry page allows the user to find, add and delete in the database. To find information such as "3 bedrooms" or "San Jose", simply click on the command button Find Record. The application will prompt the user to enter the key words to search for.

To add a new property into the database, click on Add Record to begin entry. Enter information in all fields that apply. To delete a property off the database, select Delete Record.

The user has the option of selecting which property to edit or delete by selecting the ">" or "<" command buttons to move from one record to another.

To save & exit the property information form, select Exit, the application will automatically save the changes and return to the menu page.

 

 

Chapter IV Appointments

To schedule an appointment, simply click on the button Appointment:

Fig. 4.0 Appointments Form

To view the appointments already scheduled, simply click on Next Appointment or Previous Appointment to access the database.

To schedule a new appointment, select the Enter New Appointment command button and enter all fields that apply.

To remove a scheduled appointment from the database, use the Next or Previous Appointment to select the record to delete, and then select Remove Appointment to permanently remove the record from the database.

 

To save the changes or additions, simply click on Save Exit button to save and return back to the menu page.

 

 

Chapter V Quit Application

To quit the H.S.H. application, simply click on the STOP command button to completely exit the program.

 

 

Web page prototype

 

 

 

Summary

 

Introduction

Home Sweet Home (HSH) is a real estate office in the process of change. Change in terms of the growing demands in the real estate industry, their own business, and the way they process information. With the anticipated growth in the property segment in conjunction with the continuing boom in the economy, HSH is not only looking to reap some of the immediate rewards of the real estate surge, but also looking to stay competitive, to become more efficient, and to be prepared to grow well into the 21st century.

We, Giant Consulting Group, were commissioned by HSH aid them in their decision process for a new information system. We analyzed HSH's need, functionality, and feasibility for a new system. We considered a new system’s value, impact, and effectiveness. Our investigation led to various improvement ideas including new ways of processing information using technology that is both user-friendly and easy to learn. The process was to first investigate the current system, then use that information to produce recommendations for a new system. The analysis areas covered is as follows:

 

 

Overview

System request

Feasibility study

Work plan

Analysis plan

Information gathering

Logical process modeling of current (as-is) system

Logical process modeling of new (to-be) system

Entity-relationship diagram of to-be system

Business rules

Normalization

Recommendations

The conclusion of our analyses summarized in the recommendations for a new system. The recommendations, based on our analysis, divided into three stages that will enable HSH to manage the influx of information due to the growth of their business and prepare HSH for the future.

 

System request

The business need for an automated system is high due to the growth in business. The functionality of a new system will automate and centralize the current processes resulting in an increase in efficiency making it easier to store, retrieve and manipulate date. A company website is an option for customer access, services, and overall convenience. Expected value is to double the turnover over 3 years, improved customer service, and improving quality of work. Special constraints include keeping competitive in the local area as it slowly expands in the region.

 

Feasibility study

Technical analysis is overall risky. No IT department, minimal database or internet-based real estate applications experience. Project size moderate but room for growth. Economic analysis is excellent. Return on investment is 201%, net present value $376,465. Install network infrastructure, upgrade computers, install software to manage database system and create IT department. Organizational analysis is excellent. Project champion, senior management, users, and stakeholder strongly support new system.

 

Work Plan

In conjunction with HSH management, a work plan was developed to record and track the tasks needed to accomplish the project. The plan’s function is not only to identify each task and responsibility, but to estimate the time it would take to complete each task as well.

 

Analysis Plan

The analysis plan is used as an outline to gather information about the current system, to identify its strength and weaknesses, then analyzing them to produce a concept for the new system. By using this plan, we are able to conclude whether a new system is needed or not, and it is.

 

Information Gathering

Various techniques used to gather information include interviews with staff, documentation analysis, and observation analysis. The information attained not only is used to understand the current system, but to collect information for improvements and the functionality of the new system. It was important to gather information and input from all the stakeholders.

 

Logical Process Modeling (as-is system)

Used data flow diagramming to depict the current system’s information process. Data flow diagramming incorporates the processes, data stores, data flows, and external entities of the business. Does not identify whether the process is computerized or manually accomplished. HSH’s current data processing and storing system is not centralized and not interrelated making it difficult to organized, store, update, and retrieve data. Decomposing each process reveals in greater detail the processing of information and its pathways.

 

Logical Process Modeling (to-be system)

Again we used data flow diagramming to create a centralized, process information system for the business. All external entities, processes, and data flows lead to a centralized data store for better organization. The centralized data store would facilitate data processing by being the main storage for all entity information, thus increasing data accuracy and efficiency.

 

Entity-Relationship Diagram (ERD)

This tool was used to show how information is created, stored, and used by the new system. The ERD displays the individual pieces of information how they are organized and related to each to each other. This is done through the use of entities, attributes, and relationships. The ERD also displays the cardinalities, or constraints, of the new system as well as the high-level business rules. We use the ERD (and our logical process to-be model) as the basis for designing our database system.

 

Business Rules

Declared business rules which was also expressed in the ERD. The business rules define or constrains the business including influencing the behavior of the business. Both the operational and the structural constraints of the business were considered. We use the business rules that impact the design HSH’s new database system.

 

Normalization

In a logical designing for a database, normalization must first be conducted. Normalization is the process of decomposing relations with anomalies to produce smaller, well-structured relations. Our proposed new database system is in the third normal form (3NF) which states that the database consists of minimal redundancy and free of data anomalies.

 

Recommendation

 

Part 1: Automation of Process (immediate implementation)

Provide new tools by applying computer technology and other equipment to increase efficiency by speeding up or simplifying employee’s work.

We recommend utilizing MS suite (mail merge feature, spreadsheets, calendar; publisher etc, new computers, prearranged forms and other documents, e-mail and fax vs. postal mailings, bookkeeping software (Quicken), HSH LAN and Internet connection, Digital camera, scanner, printer, PAD, Nextel’s etc.

 

Part 2: Technology Enhancements (implementation after 3 months)

A centralized computer database management system is recommended to improve

processing of information at HSH.

The advantages of this improvement system include:

Centralized data storage for data organization, update, and manipulation

Increased efficiency and productivity due to reduction in paper work and filing

Easy and ready to use data entry forms, templates, and form letters

Readily available reports, tables, and mailing options

Queries regarding customers, buyers, sellers, contacts, property and marketing trends

Joining tables in a relational manner to maximize database functionality

Administrative improvement by storing staff information in a database

Improved data sharing, quality, consistency, and accessibility

 

HSH process will require a network infrastructure between the two premises as well as an upgrade in the existing computer hardware. New hardware (computers, server, printer, scanner) and applications software including a database management program is needed to run the system. After the initial development costs, the operating costs is affordable. Overall, upgrading this system will decreases the amount of time spent on paperwork while improving data sharing and data integrity. The end result is improved customer satisfaction, more efficient employee time management leading to more profitability. In addition to our analyses we included prototype of Microsoft Access database management system.

Part 3: Explore new possibilities of e-commerce (optional).

The third possibility, after successful integration of automation and improvement stages is to develop enhanced HSH website. The website will be linked to our centralized database and other servers. It will provide information about HSH’s services, enable customers to register, and show available property using video, make financial transactions on line, bid on available property etc. The website also could enable customers to get direct access to HSH database regarding, their status, property updates or any other real estate questions. Some of the advantages of the website include:

Providing real time transactions to customers

Convenience

24/7 accessibility

On-line financial transactions

Powerful low cost marketing tool

Expanding new business contacts and links

Embracing new technology achievements

Providing huge info resources

Searches and queries

Market updates

Virtual tours

Property listings

 

Conclusion

 

The need for HSH to update their information process is imminent. By using a database program to manage their information, HSH becomes more productive and efficient allowing more opportunity for profit. Our recommendations, including the prototype tables, forms, and reports could be used to greatly improve HSH’s way of processing information. The centralized system is powerful in terms of data sharing, accessibility, and is highly feasible. A new system makes HSH well prepared for the future.

GIANT CONSULTING GROUP

Hae Jeong Hwang-Florip

Manuel Uson

Jay Wang

Maksim Ivanenko

Alan Law