A Database Management System (DBMS) is required to store, retrieve and manipulate data from a database. according to Healey, the term DBMS will be used to refer to a software package for a storage, manipulation and retrieval of data from a database. a database is a collection of one or more data files or tables stored in a structured manner, such that interrelationships which exist between different items or sets of data can be utilized by the DBMS software for manipulation and retrieval purposes. The basic infrastructure to build other organizational information systems is a DBMS.

Key Issues and applications of the DBMS

There are several different issues related to data management in an organization environment. Filing system was adequate only for data storage, although not very efficient but it fulfilled the requirements of long-term data storage.

Bonuses and Discounts
give up to20% off
Place an Order

Data retrieval was one of the major issues in file-based systems but with the advent of Database Management Systems and its quick and speedy retrieval methodology this issue was resolved.

In the context of Dublin City Council Housing Project, some basic forms connected to a well-designed database will do the trick for data storage making it as effective and efficient as possible. Data retrieval can be performed more quickly compared to file based system. String based search and a few clicks is all that is required to get the required data from system.

Updating of data is also a problematic area when database management system is not used. For Dublin City Housing, some part of data is ever changing like the apartment in which a particular household is residing. People move to different places and this information will be modified more frequently than other data. With a database system, it will take a few clicks to get this updated across the whole system.

another key issue is analytical reporting. Imaging how hard it will be to figure out from a filing system that how many 2-bedroom apartments are vacant in the whole housing complex. With the native query language (SQL) of database, it is now a matter of seconds and you get your desired results.

These are the basic issues that database management systems cater to however the benefits and applications are innumerable.

Features and advantages of MS-access

  1. Compatibility – It is compatible with all Microsoft products since it is a product of Microsoft, as well.
  2. User friendly – Since it is a Microsoft Product, users are highly familiar with the interface as it has similar look and feel, which makes it user friendly. It is also very easy to use compared to other database systems.
  3. Scalable – It is possible that the housing development and apartments might grow with time. MS access has upgrade capabilities so in case Dublin City Council Housing need to grow they can upgrade to SQL Server.
  4. Backup – Dublin’s housing data can be easily backed up, frequently. So it is available in case of need.
  5. Cost effective – Dublin Housing will not have to spend heavily on its DBMS if MS access is being used. Setup and Maintenance cost is also less, as compared to larger database systems. as it is mentioned on the access Programmers website, Microsoft access is virtually free. If your computers already have the Microsoft Office Professional Suite of business products installed that is automatically bundled as part of Microsoft Office Professional, Microsoft access is already available and there is no additional database software that is required.
  6. Support – Microsoft access Support resources are available easily than any other desktop DBMS so providing support is not a difficult task.

L02 – Task 1

Classic SDLC – Database System

according to alexis & Mathews (2005), different phases of DDLC are requirements analysis, database design, DBMS evaluation, selection, implementation, data loading, testing, operation, performance tuning, and maintenance.

  • Requirements analysis – Company’s situation, problems and constraints are analyzed and defined. It is discussed what improvements a database management system will bring and what objectives it will help fulfill. Requirements are explicitly and granularly defined so the objectives are clear among teams of what to develop and deliver.
  • Database Design – alexis & Mathews (2005) mentioned that in a database design, first a conceptual design of the database is created. In the conceptual design stage, data modeling is used to create an abstract database structure that represents the real-world scenario.
    • Data analysis is performed. Data Items and attributes are determined.
    • Data modeling and normalization takes place. an Entity Relationship Diagram is developed (ERD is discussed in detail in the next section).
  • Evaluation and Selection – In this phase the best suited database management system is selected and evaluated by its features, compatibility, support availability, cost and hardware requirements.
  • Logical Design – Basically, in the logical design we create a normalized ERD in which dependencies and data redundancy is minimized.
  • Physical Design – Data Dictionary is created that defines data types for attributes, indexing and parameters.
  • Implementation – It is the actual implementation of logical design through Data Definition Languages. Tables and relationships are created, different integrity constraints are applied, and indexes are defined to get an operational database ready for data storage and retrieval.
  • Data Loading – Data is loaded into the system.
  • Testing and performance tuning – Database is thoroughly tested to ensure it produces accurate results and it is optimized, if required.
  • Maintenance – This phase involves support and training.

LO2 – Task 2

Entity Relationship Modeling and Normalization

The entity relationship model is high-level conceptual model where data is described in terms of ‘Entities’, ‘Relationships’ and ‘attributes’. For the Dublin Housing Project, the entities we can infer are ‘Housing Developments’, ‘apartment’ and ‘Household Member’. The relationships that we can define are: One Housing Development has Many apartments

One apartment has Many Household Member living in it. attributes for each entity are defined e.g. ‘Household Member’ will have attributes like ‘Name’, ‘Date of Birth’, ‘Sex’ etc.

The Entity relationship model of this scenario will be something like:

Figure: Sample ERD for Dublin City Housing
There are several advantages and disadvantages of ER model. The conceptual design is easier to understand and is more close to the real life entities. Understanding an ER model is easier as it is more intuitive than any other form of database design.

There are some disadvantages as well. If the physical database is modeled directly in line with ER model then there will be many issues like data redundancy. E.g: Each Household Member is part of a household and each household has its own set of attributes to identify itself.

From the ER model given above, we can see that the Household information will be stored in the same entity record as Household Member. So there will be set of attributes that will repeat their value for all the members belonging to the same household.

This will cause major integrity and consistency issues.

Normalization

Normalization is the process of decomposing entities into multiple related entities based on some dependency rules. The main objective is to reduce data redundancy as it can cause integrity and consistency issues. The normalized database is much more structured and easier to maintain compared to non-normalized database.

E.g. In the example above, Household Member module is decomposed into two models: ‘Household’ and ‘Member’ where the attributes are divided logically and a relationship is added between ‘Household’ and ‘Member’ entities.

Now the data redundancy for Household values will be removed.

The basic advantage of normalization is a maintainable database design, reduced redundancy, better consistency and integrity of data.

On the other hand, a special construct of ‘Join’ needs to be used while querying the database for required results. If there are many decomposed tables with large quantity of data, this can cause some performance over heads.

L03 – Task 1

SDLC database methodology – Dublin SDLC Plan

Planning Phase:

The main objective of this system will be to have robust storage of relevant data along with efficient retrieval and reporting schemes to assist in decision making.

  • Data Security
  • Data Integrity
  • Data Retrieval
  • Reporting

MS access is to be used as the database and front end engine. For a small unit like Dublin City Housing, MS access provides ample functionality and is easy and cost effective to develop.
Some basic reporting is also a part of it and that serves the purpose.

analysis Phase:

The following functional requirements are in place for this system:

  1. Management Housing Developments. This includes storage, retrieval, manipulation and deletion of data.
  2. Storage, Retrieval, Manipulation and Deletion of apartment related data.
  3. Management of Household Members (storage, retrieval, manipulation and deletion of data).
  4. Keeping track of household members’ movements from one apartment to another.

Data requirements are not very complicated. Here are some of the considerations and requirements for data:

  1. Every apartment should be part of one housing development.
  2. It should be made sure that a household member is part on only one household, being part of multiple households will cause data integrity issues.
  3. Every household will occupy only one apartment at a given time.
  4. Date formats should be properly validated as incorrect formats will cause data consistency issues and will hamper filtering and reporting.

Design Phase:

1. Conceptual Design:

2. Relational Schema Model:

3. Physical Design

LO3 – Task 2

[Database application (Forms, software application, tables etc) is submitted electronically]

Queries are as follows:

Query to get member and the apartment they are residing in:
select member.*, apartment.* from members
left join household on members.household_id = household.id
left join apartments on apartments.id = household.apartment_id

Query to get related apartments with one housing devlopment

select * from apartments
where housing_development_id =

Query to see movement of household sorted by most recent movement first

select mh.date_move, hh.name, from move_history mh
left join household hh on hh.id = mh.household_id
order by mh.date_move desc.

LO3 – Task 3

Suggestions

The developed database system fulfills all functional requirements. Different entities can be managed easily through the forms developed. System will help decision makers to make effective decisions through analytical reporting.

There are several enhancements and improvements that can be made in the system like extending it to handle rental payments for apartments, managing issues like civil works required.

On the technical side, the system should be easily upgradable and extendible. It should be equipped to be able to take advantage from upgrades and improvements to core technologies, which in this case is MS access.

LO3 – Task 4

User Manual

access information:

To access particular table, click on its drop down, then find and click on table option.

You will see all saved records as below:

Create input forms

In main menu, under “Create”, there is an option to create forms.

You can look form in different views by just ‘Right-clicking’ it.

Layout view:

Goto Layout view to format your form. You can apply a theme by accessing main menu ‘Format’ after selecting ‘Layout-view’.

Manipulate Data

You can manipulate data at table-view and form-view by clicking on the field.

Delete Information

There are two ways to delete records.

  • Select one/more records from table-view, right click and delete.
  • add a new button to existing form, and sets its operation to ‘delete’. Whenever you want to delete a record, find your record by previous or next button and press DELETE.

Bibliography

1. access Programmers, 2009. Management Benefits [online] available at: http://www.access-programmers.com/management-benefits.aspx [accessed 27 May 2015].

2. alexis & Mathews, 2005, Database Development Life Cycle. [online] available at: http://www.leon-leon.com/wp/2005/11/21/ddlc.html [accessed 27 May 2015].

3. Healy, R.G., 1991 Database Management Systems. [e-book] available at: http://www.wiley.com/legacy/wileychi/gis/Volume1/BB1v1_ch18.pdf [accessed 27 May 2015].