Database Management Systems

Assignment Task:

Introduction:

This Assignment is based on the following scenario. Its approach is incremental, with tasks to be completed during the teaching weeks 4 to 9. It is designed to go through a relational database design, build (code), test and document the development cycle.

Your task as a group is to:

1. Develop a working database using SQL based on the following scenario
2. Present a 10-minute presentation of your proposed solution and strategy as if to the client
3. Document your group’s work in a written report
4. Give advice and recommendations on how to keep this database safe from accidental and deliberate threats which may lead to significant data loss in your written report.

– This is worth 50% of your total mark for the module.

Scenario:

A small counselling & psychotherapy business require an information system to store client details, record data and assist counsellors in decision making by producing routine reports on the outcome of the sessions.

The organisation consists of one Managing Director (MD), one Personal Assistant (PA), 4 Qualified Counsellors and 6 Support Staff; they currently record counselling sessions on paper without a database system (see session log: page 6). Your task is to suggest and design a suitable database system for this business.

The proposed system should:

• Store client and session details in MySQL or WebSQL
• Incorporate meaningful data search capabilities to identify bookings, room availability, billing and any other relevant information will help management team with the running of this business.

For this project you are required to develop a suitable database model. Your database must be in the 3rd normal form at a minimum and must not allow data duplications/redundancies. In addition, you must populate your database with data suitable to run complex queries on involving more than one table. Moreover, you must create two appropriate user views and triggers help automate regular tasks and manage user views.

Total marks for assignment: 100

Specific Marking Criteria and Guidance:

You should work in groups of 2 to develop your database, presentation and report. The emphasis of your database model should have been on creating a database model in the 3rd normal form with built in constraints to enable the system to maintain the accuracy and consistency. You should show a good understanding of the concept of data loss and/or inconsistency due accidental, unintentional and/or deliberate user acts.

Specific Marking Criteria and Guidance: Database

In order to pass, your database should be designed using correct data types, primary and foreign key fields. ER model must not allow data redundancy/multiplicities. Database populated with relevant data and simple search capabilities documented in the project report.

In order to achieve a 2.2 mark, your database should be designed using correct data types, primary and foreign key fields. ER model must not allow data redundancy/multiplicities. Database populated with relevant data and complex search capabilities documented in the project report. Security features of the model must be discussed referencing relevant update sources.

In order to achieve a 2.1 mark, your database should be designed using correct data types, primary and foreign key fields. ER model must not allow data redundancy/multiplicities. Database populated with relevant data and complex search capabilities documented in the project report. There must be evidence of the use of natural join and successful execution complex queries including use of aggregates functions. Triggers must add value to the database functionality such as limiting the incorrect bookings. Minimum of two user views must be present. The collection of these database objects must add value to the daily functions of the business. The development cycle must be clearly recorded in the project report and security features discussed in depth with up-to-date referencing.

In order to achieve a First Class your database should be designed using correct data types, primary and foreign key fields. ER model must not allow data redundancy/multiplicities. Database populated with relevant data and complex search capabilities documented in the project report. There must be evidence of the use of natural join and successful execution complex queries including use of aggregates functions. Triggers must add value to the database functionality such as limiting the incorrect bookings. Minimum of two user views must be present. The collection of these database objects must work seamlessly together adding value to the daily functions of the business. The developer must make the security and usability the priority of their design. The development cycle must be clearly recorded in the project report. Furthermore, a highly functional working model of a database must have been developed, which is suitable to implement for a real business.

Specific Marking Criteria and Guidance: Presentation

Your presentation should take about 10 minutes with another 5 minutes for questions about the suitability of your design and security features of your final design proposal.

Your presentation should be aimed at the client. You should present as if you were database developers pitching your solution to the client’s IT director and Chief Executive.

You should book a time slot for your presentation using the online booking form at:

http://goo.gl/forms/C1OlQ4Livg.

Make sure you enter your student details correctly to the booking form.

You will NOT be given any technical assistance during your presentation so you should practice on a lecturer’s computer prior to their presentation by running it from a flash memory stick.

In order to pass, your presentation should clearly demonstrate a usable database and database strategy appropriate to the client. Your presentation should present the strategy in clear and logical way demonstrating appropriate to the needs of the client.

In order to achieve a 2.2 mark, your presentation should demonstrate an awareness of how the proposed solution relates to the client’s business.

In order to achieve a 2.1 mark, your presentation should show how the particular development decisions you have made relate to the client’s business needs and wider strategy.

A First Class presentation would demonstrate a critical awareness of the role of databases in contemporary business and the challenges and opportunities they present (e.g. in terms of security, privacy etc...) backed up with comprehensive academic research. The proposed project solution must be a highly functional working model of a database which is suitable to implement for a real business without compromising the business goals and objectives.

Specific Marking Criteria and Guidance: Report

Each student should submit an identical copy of the report through turnitin via student portal. (All group members Name, Surnames and Ids must be clearly printed on the cover page)

Your report is designed to document: the development work, if screen-shots/code present it must be annotated.

The Project Report should have the following structure and content:

Cover Page:

Assignment title, module code, full name and student number (all members), as well as submission date.

Table of Contents:

A list of all of the sections, headings and sub-headings, appendices and figures should be provided as well as all page numbers as clearly as possible.

Introduction:

This should summarise your response to the task and the strategy you are presenting.

Development Log

This is the substance of your report. You should take your reader through the decisions you made and the work you did in developing your database. You should use the client’s brief (in the question) to structure your log, clearly demonstration how you addressed each of the client’s task and demands.

Headings and sub-headings should be used to clearly indicate the different sections addressing all the sections of the marking criteria.

You should clearly note how each member of the group contributed. It is not necessary for every group member to contribute to every part of development. If one member concentrated on one aspect, that should be noted and explained why that was the best use of your group’s resources.

You are required to include your tables including designs, ER diagrams, data entry, queries, query results, triggers, user views and safety features in the body of your report. Screen-shots must be annotated. You must show evidence of testing at every stage of your development cycle.

Conclusions:

These draw out the implications of your findings. Deductions are based on the facts described in the findings and analysis. Remember not to include any new material here.

References:

All the named sources you have quoted from or reproduced in your report. Please use The Harvard System for all references. Details for the Harvard System can be found on the student portal.

Appendices:

Detailed documentation of points made in the findings. Examples include: technical data, questionnaires, letters sent, tables, charts or leaflets. An appendix contains supplementary information that you consider to be too long, complicated or not quite relevant to include in the main section of the report, but is still relevant to your reader. Each appendix should be referred to in your text. You should not include something as an appendix if it is not discussed in the report.

In order to pass, your report should present your development work and strategy in clear and logical way demonstrating how you addressed the needs of the client.

In order to achieve a 2.2 mark, your report should demonstrate how you addressed the specific needs of the client’s business

In order to achieve a 2.1 mark, your report should show how your group’s development decisions you have made in relation to the client’s business needs and wider strategy.

A First Class report would demonstrate a critical awareness of how your group sought to develop a database in the light of discussions around the role of databases in contemporary business and the challenges and opportunities they present e.g. in terms of security, privacy etc.. Use of time and division of tasks must be realistic and demonstrated clearly in the report.