Departments› Software Development› Module III› Computerised Database Systems Management› Ch 5: To perform database maintenance
Software Development  ·  Level 6
Computerised Database Systems Management
Chapter 5: To perform database maintenance
📚 4 Topics
← Chapters ⬇ Download PDF
⚑ What you will be able to do

By the end of this chapter, you will be able to:

  • Prepare a clear and effective database maintenance schedule following your workplace procedures.
  • Monitor database performance accurately according to the system’s requirements.
  • Optimize database performance to ensure it meets application needs.
  • Generate detailed and accurate database maintenance reports following work procedures.

Mastering these skills helps keep databases running smoothly and efficiently, which is essential for any organization relying on reliable data.

Chapter Practical Activities

Practical 1: Perform database optimization on the Student Records database

Software Development · Level 6
Computerised Database Systems Management
PRACTICAL ASSESSMENT
TIME: 4 HOURS
⬇ PDFCandidate Instructions (Candidate Tool)

Type: Individual

INSTRUCTIONS TO CANDIDATE:
1.  You are required to perform the following task:
i.  Optimize the Student Records database by creating appropriate indexes, updating statistics, and performing query tuning on tables with primary dimensions up to 100,000 records.
2.  You have been provided with the following resources for the practical task:
Tools & EquipmentMaterials
Relational Database Management System (MySQL 8.0)
Database Client Software (MySQL Workbench)
Laptop or Desktop Computer
⬇ PDFResources Required (Cutting List)
S/NItemQuantity
1Relational Database Management System (MySQL 8.0)1 instance per Candidate
2Database Client Software (MySQL Workbench)1 instance per Candidate
3Laptop or Desktop Computer1 per Candidate
⬇ PDFAssessor Guide
Items to be EvaluatedMarks AvailableMarks ObtainedComments
TASK 1: Database Optimization Process
Verified the existing database structure and identified performance bottlenecks
(Award 1 mark for identifying slow queries, 1 mark for analyzing indexes, 1 mark for recognizing missing keys)		3
Created appropriate indexes on frequently queried columns
(Award 1 mark each for creating at least 5 effective indexes)		5
Updated database statistics to aid query optimizer
(Award 1 mark for running ANALYZE TABLE or equivalent, 1 mark for verifying statistics update)		2
Optimized slow queries using EXPLAIN plans and rewrote queries where necessary
(Award 1 mark for using EXPLAIN, 1 mark for rewriting query, 1 mark for testing improved query, 2 marks for demonstrating improved performance)		5
Backed up the optimized database schema and data
(Award 1 mark for performing backup, 1 mark for verifying backup integrity)		2
Sub-Total17
PRODUCT CHECKLIST
Database performance improved as evidenced by reduced query execution times and properly implemented indexes
(Award 2 marks for each of the four key improvements: indexes created, statistics updated, queries optimized, backup done)		8
Sub-Total8
GRAND TOTAL25
ASSESSMENT OUTCOME:   ☐ Competent    ☐ Not Yet Competent (competent if at least 50%)

Practical 2: Implement Query Optimization Techniques on Student Performance Database

Software Development · Level 6
Computerised Database Systems Management
PRACTICAL ASSESSMENT
TIME: 4 HOURS
⬇ PDFCandidate Instructions (Candidate Tool)

Type: Individual

INSTRUCTIONS TO CANDIDATE:
1.  You are required to perform the following task:
i.  Optimize SQL queries on the student performance database to reduce execution time by applying indexing, query rewriting, and use of views as per the provided database schema.
2.  You have been provided with the following resources for the practical task:
Tools & EquipmentMaterials
Relational Database Management System (MySQL Server 8.0 or higher)Sample Student Performance Database Script
SQL Query Editor (MySQL Workbench or equivalent)
⬇ PDFResources Required (Cutting List)
S/NItemQuantity
1Relational Database Management System (MySQL Server 8.0 or higher)1 Instance per Candidate
2SQL Query Editor (MySQL Workbench or equivalent)1 Instance per Candidate
3Sample Student Performance Database Script1 Copy per Candidate
⬇ PDFAssessor Guide
Items to be EvaluatedMarks AvailableMarks ObtainedComments
TASK 1: Apply Indexes for Query Optimization
Identified slow-running queries using EXPLAIN plan
(Award 2 marks for accurate use of EXPLAIN to identify bottlenecks)		2
Created appropriate indexes on columns used in WHERE and JOIN clauses
(Award 1 mark each for creating indexes on at least four relevant columns)		4
Tested queries after indexing to confirm performance improvement
(Award 2 marks for demonstrating reduced query execution time)		2
Sub-Total8
TASK 2: Rewrite Queries for Efficient Execution
Rewrote inefficient queries using JOINs instead of subqueries
(Award 1 mark for each query rewritten correctly)		3
Used WHERE clauses effectively to filter data early
(Award 2 marks for correct use of WHERE filtering)		2
Avoided SELECT * and specified necessary columns only
(Award 2 marks for optimized SELECT statements)		2
Sub-Total7
TASK 3: Create and Use Views for Simplified Querying
Created a view named 'student_performance_summary' joining relevant tables
(Award 3 marks for correct CREATE VIEW syntax and logic)		3
Queried the view to retrieve performance data for a specific student
(Award 2 marks for correct SELECT query using the view)		2
Sub-Total5
PRODUCT CHECKLIST
Indexes created on specified columns with correct syntax and effective in improving query speed
(Award 5 marks for correct and effective indexing)		5
Optimized queries execute correctly and return expected results with improved performance
(Award 5 marks for correctness and improved efficiency)		5
View 'student_performance_summary' correctly created and returns accurate consolidated data
(Award 5 marks for correct view creation and usage)		5
Sub-Total15
GRAND TOTAL35
ASSESSMENT OUTCOME:   ☐ Competent    ☐ Not Yet Competent (competent if at least 50%)
🔒

Free: practical guides, quick cards, workplace scenarios and more.

Create a free account
🔒Create and apply indexing strategies on database tablesPractical 3
🔒Partition and Manage Storage for Customer Orders DatabasePractical 4
🔒Prepare a detailed database maintenance schedule for a computerized databasePractical 5
🔒Apply Database Maintenance Techniques on a Student Records DatabasePractical 6
🔒Perform Full and Incremental Backup of a Computerized DatabasePractical 7
🔒Perform computerized database recovery from backupPractical 8
🔒Monitor Database Health and Resource Utilization Using Monitoring ToolsPractical 9
🔒Monitor and Report Database System Resource UtilizationPractical 10
🔒Monitor and Analyze Query Performance on a Sales DatabasePractical 11
🔒Monitor and Analyse Database Transaction Logs for Activity and IssuesPractical 12
🔒Monitor and Manage Active Database Connections in MySQLPractical 13
🔒Configure Database Alerting System for Critical EventsPractical 14
🔒Generate Database Maintenance Report for Institutional DatabasePractical 15
Am I competent?

At the start of this chapter we promised you would be able to:

  • Prepare a clear and effective database maintenance schedule following your workplace procedures.
  • Monitor database performance accurately according to the system’s requirements.
  • Optimize database performance to ensure it meets application needs.
  • Generate detailed and accurate database maintenance reports following work procedures.

Tick each one you can genuinely do.

So, are you there yet?

You're competent when you can confidently do 50% or more of what this chapter promised.

Sign in to record how you're doing.

← Ch 4: To manage database security