Database Design Concepts Units 17

{`Pearson BTEC HND Diploma in Computing and Systems Development
Unit number and title Unit 17: Database Design Concepts
Assignment title Events R Us Database
`}

Purpose of this assignment

This assignment is concerned with developing skills required of an effective database designer and developer. It gives learners opportunities to develop an understanding of the concepts and issues related to databases and database design as well as practical skills allowing to translate that understanding into the design and creation of complex databases.

Case Scenario

You are employed by a database design and management company called Best Solution Database Ltd. Your client, an event management company called Events R Us, wants Best Solution Database Ltd to create and maintain for them a database which will hold details of their employees and their skills in different project areas. According to the client’s requirements:

Details of each employee include a unique employee number/id, employee name, employee grade and which department they work in. As per the client’s requirements each employee may work on many projects but must not belong to more than one department.

Details of each department include a unique department code, department name and telephone number. Each department has a different function (e.g. there may be an HR department, Marketing department etc).

The database should store details of job assignments for each project. Each job assignment will show that a given employee works on a given project.

Details of each project include a unique project code/number, project name, project budget, project type (e.g. long term, short term, overseas etc).

As an employee of Best Solution Database Ltd you are required to design and implement according to that design a fully functional database system to help Events R Us carry out their tasks efficiently.

You should concentrate on implementing the core functionality and do not try to implement any extra functionality such as finance and human resources except where it is directly related to the core requirements or to the extension task.

Task 1 : (LO1 & LO 2: P1.1,P1.2,P2.1,P2.2,Part of M1, Part of M2,Part of M3)

a) State the various employment roles that will be available in Best Solution Database Ltd as a result of creating and maintaining the employee database system for Events R Us.

b) Discuss how Best Solution Database Ltd will deal with problems relating to integrity, security, recovery and concurrency while creating the employee database system for Events R Us.

c) State and describe the advantages and disadvantages of relational databases over paper based systems and flat file systems that Events R Us will have after introducing the new employee database management system.

d) Prepare a presentation and provide the printout of the slides explaining the following concepts:

i) entities

ii) attributes

iii) key fields

iv) foreign keys to build relationships

v) compound keys

vi) many to many relationships

vii) the three level (ANSI SPARC) architecture for the DBMS

viii) different use of databases in the real world

ix) popular database models that are used in real world

x) the need for Database Management Systems (DBMS)

e) Based on the given scenario describe the process of normalisation (with diagrams) up to 3NF(i.e. UNF, 1NF, 2NF, 3NF).

f) Provide ERDs for UNF, 1NF, 2NF, 3NF with explanation.

Task 2: (LO 3:P3.2, Part of P3.1, Part of M1,Part of M2)

a) Complete the design of the database for the given scenario and implement that design in MS Access. Use screenshots, where appropriate, to show the evidence of your work.

Create a Data Dictionary showing the structure of all the tables.

Implement the tables in MS Access enforcing all relevant integrity constraints. Implement additional validation rules, input masks etc, where appropriate. Populate your database.

Implement the relationships in MS Access enforcing relational integrity.

Use SQL code as an alternative way to create the tables and insert data in them. Create at least four complex database queries to be used with the reports. Show evidence of executing the code and the queries.

Task 3-a) (LO 3: Part of P3.1, Part of M2)

Implement data entry forms and reports based on the tables and the queries developed in the previous tasks. Add automation features to your forms/reports using macros or VB code. Provide evidence of that implementation via annotated screenshots.

Task 3-b)(LO 3:Part of P3.1, Part of M2, D2 )

Develop a test plan and test your database system in accordance with that test plan. Amongst other things your testing must demonstrate that entity integrity, referential integrity, and data integrity has been enforced. Document your test results via annotated screenshots. Provide evidence of how you have resolved any problems uncovered during testing.

A high quality report of testing must be delivered on time for D2.

Task 4 (LO 3: P3.3,D1)

Evaluate the effectiveness of the database solution and suggest methods for improvement.

A very strong evaluation must be provided forD1.

Task 5 (LO 3: P3.4, Part of M3)

Create a user manual of the developed system (contents page – introduction – step by step instruction how to use the system accompanied by relevant screenshots).

Task 6(LO 2 and LO 3: D3)

Realistically extend the given scenario by adding other functions that could be integrated into the existing database system. Provide evidence of the design and implementation of extra tables, input forms and reports with the supporting code.

Evidence checklist

Summary of evidence required by student

Evidence presented

Task 1

Discuss various human roles participating in the DBMS environment.
Define what are integrity, security, recovery and concurrency and discuss the related problems for the above scenario and possible solutions to those problems.
Discuss why the selected type of DBMS is required for the above scenario stating the advantages and disadvantages of that DBMS over a paper based system and a flat file system.
Prepare a presentation on the specified topics and provide screenshots of that presentation.

Give a brief overview of the ANSI SPARC architecture for DBMS

Discuss different popular database architectures being used in the real world.

Using the information given in the case study create an ERD for the Events R Us database. Identify primary keys, composite keys, and foreign keys for your tables stating which keys are weak or strong.

Normalise your data from UNF up to 3NF. Justify every decision to split a table stating the normalisation rules and functional dependencies involved. For each normal form draw the resulting ERD.

Task 2

Create a data dictionary for your database.
Using MS Access implement the tables and relationships specified in your ERD. Ensure that all relevant integrity constraints are properly implemented. Create additional validation rules and input masks where appropriate. Populate your tables with records. Include screenshots of the tables (design and datasheet view showing the data) and the ERD created in Access.
Produce SQL code for creating the tables and inserting data in them, and a set of complex queries selecting data from two or more tables, to be used with the reports. Show evidence of running the code and queries.

Task 3

Implement the data entry forms and reports in MS Access. Add automated features to the forms/reports (custom navigation buttons, search features etc) using macros or VB code. Include the screenshots of each form and report.
Develop a set of test cases and arrange them in a table showing the purpose, input data, expected results and actual results of each test. Include test cases to show that entity integrity, referential integrity and data integrity have been enforced. Document test results via annotated screenshots and source code (where appropriate). Show the evidence of how you resolved any problems uncovered by the testing.

Task 4

Provide a detailed comparison between your system’s capabilities and the original user requirements. Discuss possible future improvements and extensions to your system, without changing its core functionality.

Task 5

Create a user manual of your database system with the relevant screenshots and annotations.

Task 6

Provide evidence of substantial and creative extension of your system without changing its core functionality. That evidence should consist of extra design documentation showing additional tables, and queries, as wells as additional annotated screenshots showing the implementation of the extended design.

Sources of information:

https://www.udemy.com/blog/rdbms-concepts/
http://www.pearsonhighered.com/educator/product/Database-Systems-A-Practical-Approach-to-Design-Implementation-and-Management-5E/9780321523068.page

· Database Systems, 4th Edition, A Practical Approach to Design, Implementation and Management

Publisher:	Addison-Wesley
By:	Carolyn Begg,Thomas Connolly
ISBN:	978-0-32121-025-8

Glossary: normalisation, implementation, annotation, technical documentation, user manual, creative, entity, referential integrity, screenshot, test plan, test report, data dictionary, entity relationship diagram.

Achievement Summary

Qualification		Pearson BTEC HND Diploma in Computing and Systems Development	Assessor name
Unit Number and title		Unit 17-DATABASE DESIGN CONCEPTS	Student name

Criteria Reference	To achieve the criteria the evidence must show that the student is able to:			Achieved ? (tick)
LO 1	Understand databases and data management systems
1.1	Analyse the key issues and application of databases within organisational environments
1.2	Critically evaluate the features and advantages of database management systems.
LO2	Understand database design techniques
2.1	analyse a database developmental methodology
2.2	discuss entity-relationship modelling and normalisation
LO 3	Be able to design, create and document databases
3.1	apply the database developmental cycle to a given data set
3.2	design a fully functional database (containing at least four inter-relational tables) including user interface
3.3	evaluate the effectiveness of the database solution and suggest methods of improvement
3.4	provide supporting user and technical documentation