Practical tasks to do:
You are to develop a data model (ERD), database and database application(Querys /forms Reports) to support the requirements of the following MHA systems:
1. Tenant records
2. Building/Apartment records and associated data- lenders, tracts
3. You need to be able to track all of the data and transactions discussed above- ie the transaction required to execute the Reports / Add/ Delete Data.
Study the Sample Reports for the system and see if you need to modify any of your relations.
4. Calculate the potential file size for your database based upon the tables that you have chosen. Work on 10 rows per table.
5. Use the Query Builder to develop 5 Queries that would be useful for the MHA. Print a copy of each query and on it, discuss why it would be useful information. At least 3 of the queries must be complex queries and use at least 2 related tables.
7. Create the reports based on any 3 of the queries above. Design these yourself, do not use the Wizards. The reports should do some calculations using the SQL Aggregate functions so use some fields that can be used in calculations. You must build reports M1 to M3- see samples of these for you to study.
8. Design and Create a Data Entry Form that could be used by the Secretary to enter data into the Apartment table. Apply UI Guidelines
9. Produce a Data Dictionary for your Final DBMS using the style we have studied in
class.
10. Draw a Data flow Diagram of the rent collection system ( Context and Level 0)
Rent Collection System ( bFb Notes taken in interview)
Consider the following description
"George the rent collector employed by MHA visits each apartment in his section of the Tract at least once a month to collect rent from the tenants. He visits around 50 buildings per month. If the tenant answers the door he collects the rent and issues a receipt to the tenant. The receipt contains the Name, Flat No, Building ID, Date & Amount paid. His receipt book is in triplicate, the white copy goes to the tenant, the blue to the office and the yellow stays in the book. At the end of each day George tears out the blue slips and enters the details into a summary work sheet for each Building. The blue copies get stapled to the work pad for each building. He also banks all the money he has collected and keeps the bank docket for the office. Each week he travels to the head office, trying not to become a victim of a drive by shooting, and hands in his daily work pads to Delores the hard working secretary.
She stacks them in her in-tray where they rest for a few days. After a few cups of tea Delores lifts the pile of weekly sheets out of the in tray and, for each tenant, enters the details onto their files.
Later she then goes through the tenant files and checks to see if there are any people in arrears. If they are, she pulls out a standard letter from a file and sends one off to the tenant. The tenant file is transferred to another collection of files of people who owe, this is rent sorted by building. In the Building File the a red sticker is placed next to the tenant name ( a list of tenant appears on the front cover ).
When the terrified tenant finally pays up, Delores amends the tenant file and removes the red sticker from the front of the Building File.
Report to Management:
Write a Report to Management explaining what your DB Application has achieved and what it can do. Include the following
1. Statement of any assumptions
2. Statement of Business Rules
3. Select and justify a system implementation strategy.
4. Discuss the security and data protection measures that the MHA should put into place to ensure the privacy / confidentiality and safety of its data.{ HW SW Procedures People)
5. Critically analyse the existing records system and discuss the problems that may arise from the old data storage and processing procedures. Produce a table of these with the discussion in the next column for each issue. Discuss how the data integrity could be compromised with the current system. Explain why a database application is more appropriate that a Spreadsheet Application.
(Explain the terms data integrity and data redundancy in your answer)
Background:
The Metropolitan Housing Agency (MHA) is a non-profit organisation that advocates the development and improvement of low income housing. MHA serves as an information clearinghouse and provides three basic services. First, it works with politicians and community groups to foster the development of legislation that encourages the development of low income housing through tax incentives and developmental zoning preferences. To accomplish this, MHA provides information about low income housing to government. Second, IMHA strives to raise community awareness about the need for low income housing through speeches, seminars, displays at conferences, and other public relations activities. Finally, MHA provides information about the availability of low income housing to other agencies that work with the low income and homeless populations.
Staff and Facilities:
The agency employs a director on a half-time basis, a full-time secretary/administrative assistant, and a varying number of unpaid part-time volunteers. Both the director and the assistant are paid $20,000 p.a. The agency is located in a building owned by a developer who supports the agency's work. Rent is minimal ($350 per month).
Finances:
MHA's total budget is less than $100,000 per year. This includes salaries, rent, utilities, and operating expenses. The budget is supported by funds from several sources, including the state government, and several businesses. The director of the agency, Brenda Campbell, feels constant financial pressure. Each year she must reapply for funds from her sponsors. Considerable effort is required just to maintain the program. Further, she would like to expand the MHA activities, but she is unable to do so due to limited funds. Brenda estimates that at least one third of her time is devoted to financial matters and fund raising.
Data:
MHA maintains data about the location, availability, and condition of low income housing in eleven different census tracts (regions) in the metropolitan area. Within the boundaries of these tracts are approximately 250 different buildings that provide low income housing. On average, each building contains twenty five flats or units.
MHA keeps data about each census tract, including geographical boundaries, median income, council members and members of parliament, principal businesses, principal investors in property in the area, and other demographic and economic data. It also maintains a limited amount of crime data. {Do NOT attempt to store ALL of this- just what is required to create the reports}
For each building, MHA stores the name, address, size, owner(s) name and address, mortgagor(s) name and address, renovations and repairs, and availability of facilities for handicapped people.
In addition MHA keeps a list of each of the units within each building.
This list includes the type and size of unit; number of bedrooms, baths, and kitchen and dining facilities; location in the building; and any special remarks. MHA would like to maintain data about the average occupancy rates for each unit, but has been unable to collect or store such data to date. MHA does keep data about whether or not a given unit is occupied, however.
Information Systems:
MHA uses an old Compaq 80386 processor computer for word processing and spreadsheet applications. The system includes a dot-matrix printer. The word processing application is used not only for general business correspondence and typing reports but also for the production of form letters using the mail merge facility of the word processing program.
The spreadsheet application serves two functions. The first is to maintain the MHA budget. Since MHA is a small organisation with a modest budget, this spreadsheet is quite simple. The second function is to maintain the apartment inventory data.
MHA's experience with this application has been mixed, at best.
The Apartment Inventory Application:
The Apartment Inventory Application is used to produce three reports about the nature and availability of low income housing. Figures M-1, M-2 and M-3 show examples of these reports (note: only a sample of the MHA data is shown in the reports)
MHA has experienced three major problems with this spreadsheet application. First it is time consuming to prepare the reports. Second, these reports do not contain all the data that MHA maintains and wishes to report. Data was omitted to reduce the complexity and time required to produce the reports. Finally, MHA personnel would like to be able to query their data on an ad hoc basis. This is impossible with the spreadsheet application.
MHA Case:
Request for tender documents:
Proposed: Housing Management record System
Background:
The Metropolitan Housing Agency (MHA) is a nonprofit organisation that advocates the development and improvement of low income housing. MHA serves as an information clearinghouse and provides three basic services. First, it works with politicians and community groups to foster the development of legislation that encourages the development of low income housing through tax incentives and developmental zoning preferences. To accomplish this, MHA provides information about low income housing to government. Second, IMHA strives to raise community awareness about the need for low income housing through speeches, seminars, displays at conferences, and other public relations activities. Finally, MHA provides information about the availability of low income housing to other agencies that work with the low income and homeless populations.
The MHA is seeking to upgrade its Administration Records system, which currently comprises a mixture of machine and manual records, including various computer systems and a document delivery system based on a Castledex filing system.
A new Management Information System (MI5) is required to subsume the entire existing information infrastructure, providing a distributed database permitting access from all desks, Centres, stores and administrative offices.
The successful tenderer will provide an integrated hardware, network and software solution, complete with pricing, delivery schedules, service plans and warranty conditions.
All tenderers must have 1509000 quality assurance, and all equipment must conform to the NI-IS Office Conditions of Supply ISO and BSA standards.
Metropolitan Housing Agency Case:
The Apartment Inventory Application:
The Apartment Inventory Application is used to produce three reports about the nature and availability of low income housing. Figures M-I. M-2 and M-3 show examples of these
MHA has experienced three major problems with this spreadsheet application. First it is time consuming to prepare the reports. Second, these reports do not contain all the data that MHA maintains and wishes to report. Data was omitted to reduce the complexity am time required to produce the reports. Finally, MHA personnel would like to be able to query their data on an ad hoc basis. This is impossible with the spreadsheet application.
Requirements:
The new systems will subsume the entire existing infrastructure and provide access from all offices. This must permit real time processing of the database.
Interface Design:
Data will be processed from work stations in a GUI environment
Screens are to be designed only in black text on a white background or grey background, in various point sizes of a Sans serif font.
The MHA colours ( Grey, Magenta and black ) and the only colours to be used on the screens, with the MHA logo on the top right corner of every screen and a minimum of clutter.
Existing Records System
The MHA uses many different systems of recording information about clients and services offered to them :
i) A manual system dating from 1873 including a client history in a wallet stored in a Compactus Unit
ii) A staff records system held in Personnel as payroll - a computerised system- a progress report( manual ), and standard personnel files (held in the compactus paper storage system)