By the end of this chapter, you will be able to:
Mastering these skills will help you manage databases efficiently, making sure information is accurate and useful in real-world business environments.
Type: Individual
| Tools & Equipment | Materials |
|---|---|
| Relational Database Management System (MySQL, PostgreSQL, or MS SQL Server) | Rewritable Compact Disc (CD-RW) |
| Data Modelling Tool (Draw.io, Lucidchart, or EdrawMax) |
| S/N | Item | Quantity |
|---|---|---|
| 1 | Relational Database Management System (MySQL, PostgreSQL, or MS SQL Server) | 1 instance per Candidate |
| 2 | Data Modelling Tool (Draw.io, Lucidchart, or EdrawMax) | 1 license per Candidate |
| 3 | Rewritable Compact Disc (CD-RW) | 1 Pc per Candidate |
| Items to be Evaluated | Marks Available | Marks Obtained | Comments |
|---|---|---|---|
| TASK 1: Map and Define Database Schema | |||
| Mapped entities from the ERD into six (6) relations (Award 1 mark for each correct relation mapped) | 3 | ||
| Specified attributes for the six (6) relations with appropriate data types (Award 1 mark for each relation’s attributes correctly specified) | 6 | ||
| Specified primary keys for the six (6) relations (Award 0.5 mark for each correct primary key specified) | 3 | ||
| Specified composite primary keys for the Exams and Subject-Teacher relations (Award 1 mark for each composite key correctly specified) | 2 | ||
| Enforced referential integrity by specifying foreign keys in referencing relations (Award 1 mark for each correct foreign key constraint enforced) | 4 | ||
| Printed the relational schema (Award 1 mark for clear printout of schema) | 1 | ||
| Sub-Total | 19 | ||
| TASK 2: Create and Populate Database | |||
| Created the database (Award 1 mark for successful database creation) | 1 | ||
| Created six (6) relations with required attributes and data types (Award 2 marks per relation correctly created with attributes and types) | 12 | ||
| Enforced entity integrity (primary key) constraints in the six (6) relations (Award 0.5 mark per relation enforcing primary key) | 3 | ||
| Enforced referential integrity (foreign key) constraints in the six (6) relations (Award 1 mark for each relation with foreign key constraints) | 4 | ||
| Utilized best practices in database design: normalization and consistent naming scheme (Award 1 mark each for normalization, naming consistency, and design clarity) | 3 | ||
| Inserted at least 5 records into each of the six (6) relations (Award 2 marks per relation for correct data insertion) | 12 | ||
| Sub-Total | 35 | ||
| TASK 3: Data Retrieval and Manipulation Queries | |||
| Retrieved students whose first name starts with letter 'M' using LIKE and WHERE clause (Award 1 mark each for LIKE usage, WHERE clause, and correct output) | 3 | ||
| Identified the group with the largest number of students using JOIN, COUNT, GROUP BY, ORDER BY DESC and LIMIT (Award 1 mark each for JOIN, COUNT & GROUP BY, ORDER BY DESC, LIMIT, and correct result) | 5 | ||
| Retrieved teachers who teach IT using JOIN and WHERE clause on subjectTitle (Award 1 mark each for JOIN, WHERE clause, and correct results) | 3 | ||
| Renamed subject titled 'ICT' to 'Digital Literacy' using UPDATE with SET and WHERE clauses (Award 1 mark each for UPDATE, SET and WHERE, and correct update) | 3 | ||
| Deleted teacher associated with the least performed subject using DELETE with subquery and JOIN (Award 1 mark each for DELETE query, subquery, JOIN usage, and correct deletion) | 4 | ||
| Sub-Total | 18 | ||
| TASK 4: Create and Use Database View | |||
| Created a view named performance using CREATE VIEW and AS clauses with subqueries and joins (Award 1 mark each for CREATE VIEW, AS clause, correct joins, and correct output) | 4 | ||
| Generated a report for an individual student's performance using query on the performance view with WHERE clause (Award 1 mark each for querying the view, WHERE clause, and correct output) | 3 | ||
| Sub-Total | 7 | ||
| PRODUCT CHECKLIST | |||
| Database relations correctly created with specified attributes matching ERD and data types (Award 5 marks for full schema accuracy and conformity) | 5 | ||
| Data inserted correctly into all six relations with integrity constraints enforced (Award 5 marks for complete, valid, and consistent data population) | 5 | ||
| All SQL queries produce correct and expected results as per instructions (Award 8 marks for correct and efficient query results) | 8 | ||
| Sub-Total | 18 | ||
| GRAND TOTAL | 97 | ||
Type: Individual
| Tools & Equipment | Materials |
|---|---|
| Relational Database Management System (MySQL Server 8.0) | |
| SQL Client Tool (MySQL Workbench or equivalent) | |
| Notebook and Pen |
| S/N | Item | Quantity |
|---|---|---|
| 1 | Relational Database Management System (MySQL Server 8.0) | 1 Pc per Candidate |
| 2 | SQL Client Tool (MySQL Workbench or equivalent) | 1 Pc per Candidate |
| 3 | Notebook and Pen | 1 Pc per Candidate |
| Items to be Evaluated | Marks Available | Marks Obtained | Comments |
|---|---|---|---|
| TASK 1: Insert Student Records | |||
| Candidate connects to the database using the SQL client (Award 1 mark for successful connection) | 1 | ||
| Candidate writes correct INSERT INTO statements for Students table (Award 1 mark for each correctly formatted INSERT statement up to 4) | 4 | ||
| Candidate inserts at least 10 distinct student records with valid data types (Award 1 mark for every 3 correctly inserted records) | 4 | ||
| Candidate commits the transactions or saves changes successfully (Award 1 mark for successful commit) | 1 | ||
| Sub-Total | 10 | ||
| TASK 2: Insert Enrollment Records | |||
| Candidate writes correct INSERT INTO statements for Enrollments table (Award 1 mark for each correctly formatted INSERT statement up to 4) | 4 | ||
| Candidate inserts at least 15 enrollment records linking students to courses with valid foreign keys (Award 1 mark for every 3 correctly inserted enrollment records) | 5 | ||
| Candidate ensures referential integrity is not violated during insertion (Award 1 mark if no foreign key constraint errors occur) | 1 | ||
| Candidate commits the transactions or saves changes successfully (Award 1 mark for successful commit) | 1 | ||
| Sub-Total | 11 | ||
| PRODUCT CHECKLIST | |||
| All 10 student records correctly inserted with accurate attribute values and data types (Award 1 mark for every 2 correct student records, max 5) | 5 | ||
| All 15 enrollment records correctly inserted linking existing students and courses (Award 1 mark for every 3 correct enrollment records, max 6) | 6 | ||
| Sub-Total | 11 | ||
| GRAND TOTAL | 32 | ||
At the start of this chapter we promised you would be able to:
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.