Purpose of this assignment

This assignment considers the skills required to perform effectively as a Database design and business 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.

You 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.

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.

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.

 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.

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

A very strong evaluation must be provided for D1.

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

 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.

• 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 system 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

• 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.

• 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.

• Describe your system’s capabilities against user requirements together with discussing possible future improvements without affecting the existing one.

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

• 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.

• 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

Glossary: Normalisation, Implementation, Annotation, Technical Documentation, User Manual , Creative, Entity, Referential Integrity, screenshot, Test plan, Test report, data dictionary, entity relationship diagram.

Qualification

Assessor name

Bratati Sinha

Grade descriptor

Achieved?

(tick)

Grade descriptor

Achieved?

(tick)

D3: Demonstrate convergent /lateral /  creative thinking