Reference no: EM132508899

Unit 4 Database Design and Development - BTEC HND in Computing

ASSIGNMENT

Learning Outcome 1 Use an appropriate design tool to design a relational database system for a substantial problem

Learning Outcome 2 Develop a fully functional relational database system, based on an existing system design

Learning Outcome 3 Test the systems against user and system requirements

Learning Outcome 4 Produce technical and user documentation

Aim & Objective

This coursework is designed to demonstrate the broad understanding and knowledge of the module, assessing and evaluating the student's strength and level of analysis; divided into four learning outcomes. The coursework should be submitted as one document in a report format in final submission.

Scenario

Careers4Good Recruitment Ltd:

Carrers4Good Recruitment Ltd is an international career recruitment agency who is looking to develop an effective Relational Database Management System for a Human Resources System to cater for their growing business. As a junior database developer, you have been contracted to develop a database for Carrers4Good Recruitment Ltd which will satisfy all users and systems requirements.
The database should contain information about old and new employees, job roles, companies they work for, salaries as well as Carrers4Good Recruitment Ltd Staff details.

For Carrers4Good Recruitment Ltd Staff'- name, address, phone number and related information to store.

For Employees- the database should store their details such as name, address, salary, National Identification Number and job details, contract type, qualifications held, timesheet, trainings attended through Carrers4Good Recruitment Ltd

Employers are the different consultants/organisations/companies the employees can work for: the database should store, company's name, address, industry domain or type of business offered (IT, Education, Finance, Business, Agriculture, Fashion, Media etc)

One employee can only work for many employers at a given period of time as stated in the work contract, many employees can have two or more roles in an organisation. A member of Staff at Carrers4Good Recruitment Ltd can have access to many employee and employers' details. An employee can only have one or more contracts when working for an employer.

Section 1: LO1

It is important to show the different components of the case study data that illustrates the logical structure of the tables that makes up the database. In this view, you are required to illustrate the data structures and relationship of the tables extracted by designing the Entity Relational Diagram. Your implementation should illustrate at least four (4) inter-related tables resolving

"many to many" relationships if there are any. It is necessary to explain any assumptions made for the user and system requirements.

From the tables extracted, ensure to list all the attributes. The aim of normalization is to reduce duplications. You are to produce a well normalized database up the third Normal Form following your listing specifically identifying the primary and foreign keys. The effectiveness of database design is usually assessed through testing. Assess your database design in relation to the user and system requirements.

Section 2: LO2

After a successful database design, the next step is to develop the database using the structured query language. Using your design as a guide, use a suitable DBMS, for example Microsoft SQL Server 2012 (or above) to develop your database system. The tables created must be populated with records of at least five (5) entries for each table. Produce screenshots of the tables you have created and data you have inserted in the tables. To reflect your understanding of database security and maintenance, you are required to assess how these are ensured in your implementation of the fully functional database system in accordance with users and system's requirements.

Using Structured Query Language produce query report and ensure your implementation is justified to meet user requirements. Produce at least 8 query reports from a single table and multiple tables. Produce screenshots of the SQL statements and the query reports, including suitable annotations. To ensure usefulness of the report, you should assess whether meaningful data were extracted from the query report.
You should discuss the effectiveness of the DBMS you have developed; the query reports and the tools have used in developing the DBMS in relation to user and system requirements and suggesting improvements.

Section 3: LO3

It is necessary to test database and in the process of successfully carrying out testing, a test plan suffice. In your report, outline how the system has been tested against users and system's requirements. This test plan preferably to be in a table format illustrating at least six (6) records tested. Ensure to have "Test Description", "Expected Outcome", "Actual Outcome" as headings. The "Actual Outcome" heading should include a visual representation such as screenshots of results and annotations.

From the test plan created, you are to explain the different database testing techniques and assess with evidence, one of the testing techniques implemented on your database development. You are required to implement and test the verification and validation process with above query transaction from the database illustrating the understanding of the various features of SQL (update, sorting, joining tables, conditions using the where clause, grouping, set functions, sub-queries etc.). In your report, include recommendations on how you can improve your database development.

Section 4: LO4

Documentation helps in understanding the concept of database development. To reflect your understanding of technical and user documentation, you are required to produce a fully technical and user documentation for your designed database for the college. Your documentation should include diagrams showing movement of data through the system, and flowcharts describing how the system works.

Enhancing database development is paramount in completing the development cycle. You are required to assess any future improvements that may be required to ensure the continued effectiveness of the database system.

Attachment:- Unit04-Database Design.rar