Learning Outcomes

- Implement the theory of databases as they are configured and managed within up to date business information systems.

- Implement an appropriate database design for a given scenario.

- Implement a reliable database application of reasonable complexity.

- Be able to interrogate databases using SQL statements that are appropriately constructed using the functions, operators and structures covered within formative class exercises

Brief (Details about the application domain)

For 30 years East Lancashire Hospice has continuously provided a special way of caring for people suffering from life limiting illnesses throughout Blackburn, Darwen, Accrington and Clitheroe.

Volunteers are one of the hospices greatest resources and there are currently 800 on the register. In a typical month volunteers within the hospice alone put in 1500 hours of effort - not including work in the charity shops, fundraising activities or as volunteer drivers.

Volunteers work in teams in all areas throughout the hospice. These areas include; reception, day therapy, inpatients unit, driving, flower arranging, gardening, diversional therapies, complementary therapies, catering, administration and bereavement. They are also vital in offering fundraising support at events and in the charity shops and warehouse.

Tasks

The Hospice requires a Microsoft Access 2013 relational database to manage and organise information about their volunteers. Their current database is a large flat file which is becoming increasingly difficult to both maintain and access information.

Using the un-normalised structure provided you should complete the following tasks:

Relational Modelling
- Model the Data using ERM and ERD techniques
- Normalise the data to 3rd Normal Form

Database

Using your relational model produce a database using Microsoft Access 2013, your implementation should include the following:
- Tables based upon the results of your relational model
- Data input mechanisms (MS Access Forms)
- Pre written queries
- Formatted reports that detail the following:
   o A single Volunteer's details
   o Departments and their Volunteers
   o Volunteer Safety Training completed, grouped by category and order date ascending
   o Volunteers who have not completed Safety Training
- Appropriate input masks and constraints.

Testing

You should devise a test plan and generate sufficient sample data to test the functionality of all aspects of your database.

Documentation
- Technical database documentation (as a minimum your schema)
- User documentation

Submission Requirements

1. Database design documentation including your ERM and Normalisation process
2. Access 2013 database
3. Completed test plan
4. Documentation

1. Design and Implement an appropriate relational database for the given scenario using the appropriate database design techniques discussed on the course. Database design is fit for purpose and implemented appropriately. Implementation is based upon design work.

Clear evidence of structured design. Relationships are clearly derived and data is normalised to 3NF

Thoroughly documented design process indicates clear understanding of the application domain. Domain assumptions are appropriately documented

2. Efficiently store data so as to eliminate data anomalies and redundancy. Implementation provides effective structure for the application

Implementation provides efficient structure for the application against most of the use cases identified in the brief

The data structure provides an efficient structure for all the use cases provided. Clear consideration has been given to the ease with which the data structure could be adapted to support future development

3. Allow the user to interrogate the database using the interface described so as to return meaningful and timely result sets based upon input. A simple range of queries allow the user to extract information from the application

Queries have been implemented which capture the full range of use cases detailed in the scenario

Users are easily able to extract complex/compound sets of relevant information from the application

Assignment Details

4. Produce design and test documentation for your solution. Documentation clearly covers all aspects of the brief. Testing is structured

Documentation is professionally presented and testing covers all aspects of the application

Testing and documentation cover all aspects of the application domain and follow a coherent strategy. There is clear evidence of the application of further reading

It needed to be in access 2013 i need

1. A single Volunteer's details

2. Departments and their Volunteers

3. Volunteer Safety Training completed, grouped by category and order date ascending

4. Volunteers who have not completed Safety Training Technical database documentation (as a minimum your schema) and User documentation