Problem Statement Suppose your organisation is well-renowned alma mater for multiple courses in Melbourne, Australia. The university contains a large number of employees in different departments and possesses a large database for the same. The employee database of your organisation is organised in the form of relational database. Due to slower access of data and some discrepancies in the data, the management wants to monitor the performance of database. You are working as a database developer as well as monitor in your nation, The management wants you to undertake database-management system modelling in practice and to monitor the performance of given employee target database prototype. Before monitoring, you are required to implement physical database by creating relational database in the form of tables and fill the fields within tables with the help of DBMS software tool. To monitor the database, you are required to identify technical considerations affecting the implementation of a database and monitor the various performance Issues related to database. Then, modify the database accordingly to fulfil the Rational requirements. Organisational Requirements The basic requirement of the nation to monitor database performance is given below: • Check whether the mapping of the database fields and columns in the backend is compatible with those mappings in the front-end. • Validation of the length and naming convention of the database fields and columns. • Validation of the presence of any unused/unmapped database tables/columns. • Validation of the compatibility of the ⚫ Data type Field lengths of the back-end database columns with that of those present at the front-end of the application. Check whether the database fields allow the user to provide desired user inputs as required ⚫ Check whether the required Primary Key • Foreign Key constraints have been created on the required tables. • Check whether the references for foreign keys are valid. ⚫ Check whether the data type of the primary key and the corresponding foreign keys are the same in the two tables. System Requirements The minimum system requirements are: • Access to proper internet connection • Your system should contain any internet browser either Mozilla Firefox or Google chrome or Safari compatible with HTML5 • Either Windows 10 or higher version or Linux OS with latest updates should be installed DBMS software for testing required • MySQL ⚫ SOLFORADEA . Oracle • Microsoft SQL Server • Bestare SQL ⚫ DB2 ⚫ Apache JMeter You are working as a database developer as well as monitor in your organisation. The management of the organisation wants you to undertake database-management system modelling and to monitor database performance. • The purpose of this task is to undertake database-management system modelling and to monitor database performance. • • To install and download DBMS software from given list of software tools. The assessment task will be completed in your training organisation Advance Computer Lab. Your trainer/assessor will supervise you in performing this assessment task. . The student will work as database developer as well as monitor in this assessment. This assessment task requires the student to undertake database-management system modelling and to monitor database performance. It includes: ➤ Identification of technical considerations affecting the implementation of a database ➤ Analysis and monitoring of performance issues during the implementation of a database ➤ Modify a database to meet project and client performance requirements. To do so, you must complete the following activities: ⚫ Activity 1: Undertake database management system modelling ⚫ Activity 2: Monitor database performance • Activity 3: Seek client feedback and sign-off Task Environment: This assessment task will be completed in a simulated environment prepared by your training AOR. The simulated environment will provide you with all the required resources (such as the equipment and participants, etc.) to complete the assessment task. The simulated environment is very much like a learning environment where a programmer can practice, use and operate appropriate industrial equipment, techniques, and practices under realistic workplace conditions. The roles and responsibilities of the Database Monitor are: . To review the database prototype as appropriate, to determine the acceptance criteria and performance standards • To load the test data according to the technical sequence detailed in the relevant documentation • To generate a test schedule for the database, of tasks to be performed and the results expected • To evaluate database performance against the acceptance criteria and performance standards . To identify discrepancies in the results, when the expected outcomes do not meet the acceptance criteria To identify areas needing enhancement, and document the changes to be made • To modify the database according to project standards • To repeat performance testing until the expected results are achieved • To present test results in a document, and provide to the client for feedback • To incorporate client change requests as appropriate ⚫ To obtain client sign-off to the monitoring process Roles and responsibilities of trainer/supervisor are: • To provide the target database prototype to the student. • To provide the system with an active internet connection for monitoring the database. • To provide internet browser and software tool using which employee database has to be designed. • To assist the students in downloading different software tools required for database Activity 1: Undertake database management system modelling This part of the activity requires you to determine the acceptance criteria and performance standards by reviewing the employee database prototype. Description of the activity This activity requires you to determine the acceptance criteria and performance standards by reviewing the employee database prototype. Then load the test data and generate a test schedule for the database. To do so, you need to perform following steps: Step 1: Create relational database for given employee data using SQL queries keeping in mind, standard naming conventions, relationships between tables, primary and foreign keys concept. Provide screenshots of relational database in a document using Template 1. Step 2: Review the employee database prototype as appropriate, to determine the acceptance criteria and performance standards and document the acceptance criteria and performance standards using Template 1. Acceptance Criteria cost implications ⚫technical and logistical considerations timeframe Performance Standards database naming conventions database standards related to ⚫ keys ⚫ data types Step 3: Load the test data for monitoring according to the technical sequence detailed In the Step 1 documentation. Step 4: Generate three following test schedules for the database, of tasks to be performed and the results expected and record the details in a document using Template 2. Structural Testing Functional Testing ➤ Non-functional Testing Template 1: DBMS modeling