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
Jay Chun Wang
Maksim Ivanenko
Alan Law
Table of content
Table of content
2OVERVIEW
5Methodology.
8Project team roles and skills.
8Planning.
10Identify business value.
10SYSTEM REQUEST
10Technical Feasibility Analysis
11Economic Feasibility Analysis
12FEASIBILTY ANALYSIS GLOSSARY
15Organizational Feasibility Analysis
16Project Management.
16Work plan.
16Staffs project.
17Projects standards development
17Identify projects risks.
18Analysis.
19As-Is system.
19Gather information:
19Identify improvement opportunities.
261)
Business process automation. 262)
Business process improvement. 293)
Business process reengineering. 29Immediate Recommendations
31To-Be system.
32Data flow Diagram
32Entity Relationship Diagram
36Home Sweet Home Realty Business Rules
39Operational constraints.
39Structural constraints.
40Normalized table schema.
42Relational tables
44Database implementation in MS Access
45Data entry forms
45Queries
47Tables
50User Manual
52Web page prototype
63Summary
64Part 1: Automation of Process (immediate implementation)
69Part 2: Technology Enhancements (implementation after 3 months)
69Part 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 BenefitsDouble turnover over 3-year period
Cash benefits estimate $376,465 NPV.
Return on investment at least 201%.
Intangible
Costs and BenefitsImproved 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
ManagementJean 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
StakeholdersStaff 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 |
| 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 | 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:
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