Application of Database Developmental Cycle
Task 1: Apply the database developmental cycle to a given data set or a case of your own
Myreadingroom[1.1] details that the database development lifecycle, DDLC, contains six phases: the initial study, the design, implementation and loading, testing and evaluation, operation, and maintenance and evolution.
The first phase of the DDLC is the initial study. This involves investigating the company’s current solution, determining what is causing it to fail and providing a future solution to resolve the issues, whilst ensuring that it is in the company’s capabilities. This can be achieved in four stages: analysing the company, defining the problems and constraints, defining the objectives, and defining the scope and boundaries.
To analyse[1.6] is to examine something methodically and in detail. An understanding of what is currently in place helps to decipher the problems and constraints of the current way of working, whilst helping to determine what the company’s objectives are.
Whilst developing a database there is always an objective[1.7], something planned to be achieved, or there can be several, depending on the requirements. During the initial study consideration should be made as to what the the desired outcome is, as well as the scope and boundaries of the solution.
The issue I have been asked to resolve is that there is no way for my client to determine if they have, or want to purchase, a dvd, blu-ray or tv series. The objectives they have defined for the outcome is a fully functional system which allows a users to view and update a media. My client also requires the ability to generate a wish list, as well as view a list of all current media.
The solution I have proposed is a Microsoft Access database as there are minor limitations to the system. The only limitation I believe may occur is that the data held may grow to be astronomical so a desktop database application may not be sufficient in the long term. A review should be undertaken throughout the lifecycle of the application to determine if it needs to be transferred to a relational database, hosted on a server. As Access allows you to customise the database to your requirements, my understanding is that there will be no limitations of the data stored.
The second phase is the design[1.1]. This is defined as the most crucial phase in the database development lifecycle as this phase revolves around ensuring that the users requirements and objectives are met. If they are disregarded by the system developer, it would ultimately be deemed a failure and the DDLC should commence again.
The implementation and loading phase of the database development lifecycle consists of installing the database management system, creating the database and loading, or converting, the data. Installation of the database platform should only occur if it is required. If the platform is already existent, or the install of the database has taken place, creation of the database can occur.
The proposed design of the application is a main table which is essentially based on a series of lookups from other tables within the application. This is to ensure data integrity and remove any duplication that may otherwise need to occur. As part of the proposal for the table layout and relationships, consideration should be made as to the performance of the system. Due to Microsoft Access being preinstalled as part of Office, an installation does not need to occur, however creation of the database, designed in the previous phase, is required.
The proposed solution for the application is as follows, with each relationship defined utilising a one-to-one relationship[1.8], where a row in one table is linked to one and only one row in another.
From there the data needs to be loaded, and possibly manipulated, into the database. As there is no current solution in place a request has been given to my client in order for the main core data to be imported. Once this has been completed, data should be added through the application itself.
The third phase, testing and evaluation, consists of testing the solution, tuning the database and finally evaluating the database and its application program. Testing is often referred to as User Acceptance Testing, UAT, in Yorkshire Coast Homes and involves both yourself and the superusers reviewing the system to determine if it is fit for purpose and if navigation throughout is fluid. From there, the system needs amending to introduce any of the anomalies found as part of UAT.
My client will undertake the testing of the application which involves ensuring that data is able to be added to system, without any errors or having to go back to any previously accessed fields. In addition to this, they should also review if the application performs adequately and transactions are easily completed without delay.
The penultimate phase is operation, which is essentially making the system live and operational. This involves the superusers training any users, within their department, how to use the system to undertake their job role. This results in the application moving from a project state to becoming part of the day to day workings of a department.
As my client is going to be the only user of the database at launch, no training needs to occur; and the transition to live should have minimal impact to them as they have worked alongside myself to deliver the application.
Throughout the lifecycle of the application being used, maintenance needs to occur and the application needs to evolve as the industry does. This phase is known as maintenance and evolution.
Google[1.2] informs that the purpose of system maintenance is to sustain the capability of a system to provide a service. This phase consists of four maintenance types, which will occur as regularly as required. They are periodic, corrective, permissions and adaptive.
Periodic maintenance refers to something of a regular occurrence. For example, in Yorkshire Coast Homes, the main housing system has a regular nightly queue which imports any monies received from customers and processes each night, alongside abandoning any no longer required repairs. Another example of periodic maintenance, which should occur in any organisation, is a backup[1.3], the copying and archiving of computer data so it may be used to restore the original after a data loss event. These can occur as regularly as an organisation requires.
Periodic maintenance is partnered with corrective maintenance. This is most commonly known as recovery[1.4], the process of salvaging inaccessible data which has been lost, corrupt, damaged or formatted. As a database administrator you must always be prepared to restore any loss of data for any user, however this form of maintenance should only occur as and when required.
The permissions element of maintenance consists of three aspects: adding, amending, removing and reviewing. Each aspect of the permissions are closely linked as a user may change job roles, requiring the adding or removing of permissions, or a third party decision may require a change. An example of where this has occurred in Yorkshire Coast Homes is access to tenant’s rent accounts. As part of a historic decision, everyone within the organisation had access to the rent account. Following a change in the law, users permissions were removed so that those who can access it are only those who require it within their job role, whether it be for informing a tenant of their rent, chasing any arrears or for IT to support any technical issues.
The final maintenance type is adaptive. This is the process of adding features to the current system, or enhancing it. This can also be referred to as evolution. This is the process of the system evolving throughout time as either an organisation, or the technology utilised, advances. An example of where this has occurred in Yorkshire Coast Homes is our Total Mobile solution[1.5], a digital workforce management solution. We recently upgraded to the latest version of software, as the version we were previously running was old technology, which didn’t support Android.
Through the final phase of DDLC for the Media Database, the periodic maintenance that should occur is the copying of the database. This is to provide a recovery point in the event of data corruption or loss of data. Both the permissions element and adaptive maintenance have limited impact on the application. Currently only one user accesses and updates data, however periodic reviews should be undertaken to determine if my client requires further users to have access. From there, adaptive maintenance may need to occur to lock down certain parts of the system my client may not want users to access. In addition to this, Microsoft may upgrade the Access application which may potentially corrupt or remove a functionality currently used within the application. Reviews should be undertaken to determine if an upgrade to a new version of Access may corrupt the application. If a decision is made to undertake an upgrade, the DDLC lifecycle should commence again.
Task 2b: Evaluate the effectiveness of the database solution and suggests methods of improvement
My client asked me to produce a fully functional system which allows users to view and update a media. The solution I proposed was a Microsoft Access database which utilised relationships and joins, rather than duplicating data.
The first objective, a fully functional system which allows a users to view and update a media, has been achieved utilising the Media Information form. As you can see from the screen print below the form details the title of the media, the media type and category. It also includes the purchase information and the genre(s).
The second and third objective are closely related as my client requested the ability to generate a wish list, as well as view a list of all current media. This information can be accessed from the main form, as you can see from the print screen below.
When selecting either the ‘Wish List’ or ‘Purchased’ icon the following report appears, detailing the information.
As each of the objectives have been achieved, with the addition of my client being able to access sold media, I would deem the development of the database a success.
One factor within applications that should always be considered is performance[2b.2], how well something works. There are three elements that I would deem beneficial to the application’s performance. These are the storing of data, the navigation and functionality.
When defining the tables and relationships in the design phase of the database development lifecycle, I ensured that the data is stored with a series of joins, rather than duplicated data. This data is then collated in a query and presented in a form to provide the information required to my client. This ensures data integrity[2b.1], the assurance of the accuracy and consistency of data. It also enables the end user to access the information required without having to navigate throughout the whole of the system.
The application also benefits from having a limited number of ways to access the data. Providing the information required in one form, and a few reports, provides consistent information. As well as all of the information being provided, the inputting of information is simplistic as when entering data and moving through each field, it navigates in a top to bottom method rather than a muddled method.
In addition to navigation through the form, navigation through the application is simple also. Each report and form has been provided with an exit icon, as well as the form having a create, save and delete button. These are controlled by macros. The macros have also been created in a managed way as rather than having multiple macros in one location, they are all their own macro. This removes the need for a triggered action to search a long macro before undertaking it, saving time and increasing performance.
As well as successes in the system there are also failures[2b.3], the state or condition of not meeting a desirable or intended object. There are two failures which have simple resolutions. There are inputting data into the data dictionary form and the search functionality.
In order for information to be entered into the data dictionary, the superuser must bypass the initial launch to display the tables. From there information can be entered into both of the data dictionary tables in order to update the related query. There are two issues with this method: firstly, having to enter data in both tables separately, and secondly, there is no password protection so anyone can update the tables. The solution for this issue would be to create an input form which it’s sole purpose would be to update the data dictionary. In addition to this, the form can be password protected so that only users with the password can update information.
The second, and final issue, is the search functionality. As the database has minimal information currently, the only search available in the system is the one provided by Microsoft Access. As more information is entered, the harder it will be to locate the required media. The recommendation for this issue would be to provide a search option on the home screen with the ability to query any value within the database which may need to be updated, such as returning a list of wish list items. Although there is currently a report in the system available, this does not provide the ability to update the data. A form would be required for this.
To conclude, the application is fit for purpose and meets the objectives defined by my client. However, there are some minor failures of the system which can be easily addressed.
Task 2c: Provide supporting user and technical documentation
Supporting your system
The Media Information database is a desktop application database developed within the application Access, which is both provided and supported by Microsoft. Any issues relating to the core application is supported until Microsoft decide to cease cover, which is currently set to be October 2020[2c.1].
In the event support has expired, you can quite simply convert to the latest version of Access. In order to do this, make a copy of the database so you don’t corrupt the information.
With the copied database open, select ‘File’, ‘Save As’.
Select the ‘Save as type’ drop down menu and change it to the latest version.
Ensure full testing is undertaken so that you know that functionality has not been lost, including adding new media types or genres. From there, you are able to delete, or archive, the previous database version. **Please note: DO NOT delete the previous database until testing has been completed, as it may result in corruption of your data**
Overview
The database provided is referred to as the Media Information database. It is a series of tables which logs whether a media type is owned, the format, genre, and many other things.
Upon launch of the application, the following screen is displayed:
This screen allows the user to navigate through the system. An exit application button is also provided to close the database.
Upon selecting the ‘Media Information’ icon the following screen is displayed:
The main screen details the media title and any information held regarding it, including the status of purchase.
Along the bottom pain, there is the ability to navigate throughout the records. In addition to this, the user has the ability to create, save and delete records. A ‘Close’ button is also provided to return to the home screen.
A number of blue boxes open the listed report for the user to peruse. Each report which opens correlates to the title detailed on each icon. The report displayed will look similar to this:
As you can see from the screen above, there is also a ‘Close’ button provided. This is available on each report to navigate back to the home screen.
The final icon on the home screen is ‘Data Dictionary’. This report details, the table name, a description, what fields are included and the data type. The information is displayed as follows:
Maintaining the database
Throughout the lifecycle of a database, review and development must occur to determine if it is still fit for purpose. As a result, additional fields or tables may be added.
If this is required there are a few things which need to be considered or remembered:
- The database is currently set up to provide lookups rather than having to manually enter information. This is the preferred method for data integrity.
- There are multiple relationships defined, detailed below
- You must remember to update the Data Dictionary tables
Adding fields or tables is easily achievable through the backend, which can be accessed from the left hand panel of the application.
The current tables in use are as follows:
The core tables begin with Media, whereas those which help provide the Data Dictionary, begin with Data Dictionary.
If you want to add additional tables into the system, try to add tables with a lookup to the original database. In order to do this, create your table with a primary unique key and the columns required. Then add the lookup column into the required table.
For future reference, the database as it currently stands is configured like this:
- The main tables included and the relationships defined
B) The data dictionary tables and the relationships defined
Functionality help
As part of the configuration for the forms and reports, there are a series of macros enabled which allow fluid interactions throughout the application, without having to exit it completely.
These are the macros that are currently in use:
When creating macros, ensure that a user friendly name is assigned to enable yourself, and support, to easily determine what it is for as a later date. This can easily be done by selecting the ‘Properties’ of the tab and creating a description in the ‘Caption’ field. This will ensure that it is displayed correctly in the macro overview window.
In some instances, there may be things you don’t know. As Office is one of the market leaders, there are many forums and online help sites available for use. The sites I would recommend are https://support.office.com/en-gb/access and https://answers.microsoft.com/en-us/msoffice/forum/msoffice_access?auth=1. If these sites do not provide the solution, Youtube is always an alternative or a Google search.
References:
[1.3] https://en.m.wikipedia.org/wiki/Backup
[1.4] https://en.m.wikipedia.org/wiki/Data_recovery
[1.5] https://www.totalmobile.co.uk
[1.7] https://www.vocabulary.com/dictionary/objective
[1.8] http://www.databaseprimer.com/pages/relationship_1to1/
[2b.1] https://en.m.wikipedia.org/wiki/Data_integrity
[2b.2] http://dictionary.cambridge.org/dictionary/english/performance
[2b.3] https://en.m.wikipedia.org/wiki/Failure
[2c.1] https://support.microsoft.com/en-gb/lifecycle?p1=13615