Unit 17 Database Design Concepts

Unit 17 Database Design Concepts

Assignment front sheet

Qualification Unit number and title
Pearson BTEC HND Diploma in Computing and Systems Development Unit 17: Database Design Concepts
Student name Assessor name
Date issued Completion date Submitted on
06th  October 2015 30th November 2015
Assignment title Events R Us Database

 

LO  Learning outcome

(LO)

AC In this assessment you will have the opportunity to present evidence that shows you are able to: Task no.  Evidence

(Page no)

LO1 LO1-Understand databases and data management   systems

 

1.1

 

Analyse the key issues and application of databases within  organisational environments

 

1  
1.2 Critically evaluate the features and advantages of database management systems. 1  
LO 2 LO2- Understand database design  techniques 2.1 Analyse a database developmental methodology 1
2.2 Discuss entity-relationship modelling and normalisation 1
LO 3 LO3 -Be able to design, create and  document databases 3.1 Apply the database developmental cycle to a given data set 2 & 3
3.2 Design a fully functional database (containing at least four inter-relational tables) including user interface 2
3.3 Evaluate the effectiveness of the database solution and suggest methods of improvement 4
3.4 Provide supporting user and technical documentation 5

 

Learner declaration
I certify that the work submitted for this assignment is my own and research sources are fully acknowledged.

Student signature:                                                Date:

 In addition to the above PASS criteria, this assignment gives you the opportunity to submit evidence in order to achieve the following MERIT and DISTINCTION grades
Grade Descriptor  Indicative characteristic/s  Contextualisation
M1 Identify and apply strategies to find appropriate solutions ·         Complex problems with more than one variable have been explored

 

·         An effective approach to study and research has been applied

 

§  Evidence has been given that the student has a good understanding of database theory and the issues related to it.

To achieve M1, you must demonstrate

  • Standard methods used to design the proposed database solution.  This has been clearly documented to a high standard.(Task 1)

 

  • Within the planning of the programmed solution a number of components and options have been presented and a possible solution has been devised.(Task 2)

 

  • All features of system has been clearly identified and documented.(e.g. Data Dictionary, ERD)
M2 Select/design and apply appropriate methods/techniques ·         Relevant theories and techniques have been applied.

 

·         A range of methods and techniques have been applied.

To achieve M2, you must demonstrate

  • Design tools have been used.(Use MS-VISIO to produce ERD and show the steps of Normalisation)

 

  • Good user interface design i.e. careful consideration of the user interface that is appropriate to user’s needs.   (All tables, forms, reports and has been clearly identified and documented).

 

  • The logical data model has been structured correctly and an acceptable understanding of the normalisation process has been demonstrated and implemented.
  • Use of in built query designer and SQL source code to query the Events R Us database.

 

M3 Present and communicate appropriate findings ·         A range of methods of presentation have been used and technical language has been accurately used

 

·          The communication is appropriate for familiar and unfamiliar audiences and appropriate media have been used

 

 

To achieve M3 you must produce :

  • Well-presented documentation for both the user and technical documentation
  • User documentation(i.e. the database-User Manual) should be free from technicalities
  • Good representation of the overall system with sufficient detail.
D1 Use critical reflection to evaluate own work and justify valid conclusions
  • Realistic improvements have been proposed against defined characteristics for success

 

To achieve D2 students must provide:

 

  • A strong evaluation section that discusses realistic and relevant improvements that could have been made to the overall system.(Task 4)
D2 Take responsibility for managing and organising activities
  • Substantial activities, projects or investigations have been planned, managed and organised and the documentation provided has to be well organised

 

To achieve D2 students must show that

  • Student has worked independently finding their own solutions to problems encountered.

·  Work is well organised and well-presented and has been completed within the allotted time span. This can be evidenced through the completion of a high quality report delivered on time.(Task 3b)

 

D3 Demonstrate convergent/lateral/ creative thinking ·         Ideas have been generated and decisions taken

 

·          Innovation and creative thought have been applied

 

To achieve D3 students must show that:

 

  • The system has been extended sufficiently and creatively, and this has been implemented correctly i.e. more tables, more queries , more input forms and reports should be added sufficiently and creatively while not affecting the existing system.(Task 6)

 

Assignment brief
Unit number and title UNIT 17: DATABASE DESIGN CONCEPT
Qualification Pearson BTEC HND Diploma in Computing and Systems Development
Start date Wednesday 6th October 2015
Deadline/hand-in Wednesday 30th November 2015
Assessor
Assignment title Events  R Us Database
Purpose of this assignment

This assignment considers the skills required to perform effectively as a Database design and development employee. To give learners opportunities to develop an understanding of the concepts and issues relating to databases and database design as well as the practical skills to translate that understanding into the design and creation of complex databases.

Case Scenario 

Unit 17 Database Design ConceptsYou have got a database design and management company called Best Solution Database Ltd. Your client Events R Us – an event management company wants Best Solution Database Ltd.  to create and maintain their employee’s database to maintain details of its employees’ project area skills. According to the client’s requirement:

Each employee can be identified by their unique employee number/id, employee name, employee grade and which department they come from. As per the client’s requirement each employee may work in many projects but must not belong to more than one department.

Each department can be identified by a unique department code, department name and types of job. Department code is different for different types of jobs(e.g. Department codes are different for  HR department and Marketing department).

Employee’s database should store details of job assignment for each project. Each job assignment will show which project is being done by whom/which employee.

Each project should be identified by unique project code/number and project name, project budget , project type(e.g. long term, short term, overseas etc).

You as an employee of Best Solution Database Ltd are required to provide the design and implement the same design to produce a fully functional database system to help Events R Us to carry out its tasks efficiently.

You should concentrate on the core function and not try to implement support functions such as finance and human resources except where they are directly related to the core activity or to an 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 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 what Events R Us will have after introducing the new employee database management system.

d)   Prepare a presentation and provide the print out of the slides based on 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)  Design an appropriate database for the given scenario. The design documentation must include:

·            An entity relationship diagram (3NF stage only) with correct cardinality in MS-ACCESS.

·            A data dictionary specifying the structure of all the tables.

·            Design of the tables, input forms based on ERD

·            Designs of at least four complex database queries and four database reports that could be used by the organisation as described by the scenario. All reports must be created from complex database queries.

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

 Implement the design and provide evidence of the implementation. via annotated screenshots and source code.  Create all the required tables, forms, queries and reports. Include data validation methods where appropriate. 

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

Test the system using an appropriate test plan and test data,test report. Amongst other things testing must demonstrate that entity integrity, referential integrity and data integrity has been enforced and relevant reports have been developed successfully

A high quality report 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 for D1.

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

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

Task 6(LO 2 and LO 3: D3) 

 Realistically extend the given scenario and add other functions that could be implemented into the database system. Extra tables must be correctly added to the system along with supporting queries and reports.

Evidence checklist Summary of evidence required by student Evidence presented
Task 1
  • Discuss different popular database architectures being used in the real world.
  • Define what is integrity, security, recovery and concurrency problems and how these kind of problems can affect the above scenario and give the solution for the same.
  • Discuss the need of using that particular kind of DBMS being used for the above scenario, advantages and disadvantages of that particular DBMS over a flat file system and paper based system.
  • Give a brief overview of ANSI SPARC architecture for DBMS .
  • Screenshots of the presentation on the given topics.
  • Using the information given in the case study create an ERD for the Events R Us Database.
  • Show tables’ composite key, primary key, foreign key and whether they are weak or strong.
  • Normalise your tables in 1NF,2NF and up to 3NF from un-normalised version and show ERD for each normal form
Task 2
  • Using MS Access, design the RDBMS for the above scenario using relevant screenshots .Provide the evidence of established relationships and the type of relationship (which have been established) i.e. Provide ER diagram with cardinality.
  • Provide supporting documentation, data dictionary, design of each table, each form and report and queries and different test plans and test cases where appropriate.
Task 3
  • Using MS Access, implement the RDBMS for the above scenario using relevant screenshots and source codes of each table, each form and report and queries where appropriate.
  • Testing must be done to prove that entity integrity, referential integrity and data integrity have been enforced. Provide evidence via annotated screenshots and source code(where appropriate). Provide supporting documentation, different test plans and test cases and test report where appropriate.
Task 4
  • Describe your system’s capabilities against user requirements together with discussing possible future improvements without affecting the existing one.
Task 5
  • Create a user manual of your database systems with the relevant screenshots and annotations.
Task 6
  • Provide design and annotated screenshot of the system has been extended sufficiently and creatively, and this has been implemented correctly i.e. more tables, more queries , more input forms and reports should be added sufficiently and creatively while not affecting the existing system.
Sources of information:

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

Publisher: Addison-Wesley
By: Carolyn BeggThomas 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  Bratati Sinha
 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
Higher Grade achievements (where applicable)
 Grade descriptor

 

 Achieved?

(tick)

 Grade descriptor  Achieved?

(tick)

 M1: Identify and apply strategies to find appropriate solutions

 

 D1: Use critical reflection to evaluate own work and justify valid conclusions
 M2: Select / design and apply appropriate methods / techniques

 

D2: Take responsibility for managing and organising activities
 M3: Present and communicate appropriate findings

 

D3: Demonstrate convergent /lateral /  creative thinking

Assignment Feedback 

Formative Feedback: Assessor to Student

 

Action Plan 
Summative feedback
Feedback: Student to Assessor 
 Assessor Signature

 

   Date
 Student  Signature

 

   Date