Database Design and SQL

What is a database?

A database is an organised collection of related data stored so it can be accessed, managed and updated easily. A DBMS (database management system) such as MySQL or MS Access controls it. Examples in Kenya: KCSE results, M-Pesa transaction logs, school fee records, county land registries.

Terms

• Table: data arranged in rows and columns.
• Record (row): one entry, e.g. one student.
• Field (column): one attribute, e.g. name.
• Primary key: uniquely identifies each record (admission number).
• Foreign key: a field linking to a primary key in another table.

Relationships

• One-to-one: one learner has one NEMIS UPI number.
• One-to-many: one class has many students.
• Many-to-many: students take many subjects and each subject has many students (solved with a link table).

Designing a database

1. Identify the purpose.
2. List entities (students, subjects, teachers).
3. Define fields and data types (text, integer, date).
4. Choose primary keys.
5. Set relationships.
6. Apply normalisation.
7. Test with sample data.

Normalisation

• 1NF: each field holds one value; no repeating groups.
• 2NF: 1NF and every non-key field depends on the whole primary key.
• 3NF: 2NF and no non-key field depends on another non-key field.

Benefits: less redundancy, easier updates, better integrity.

SQL basics

SQL (Structured Query Language) has:

• DDL: CREATE, ALTER, DROP
• DML: INSERT, UPDATE, DELETE, SELECT

Create a table:

CREATE TABLE students (
  admno INT PRIMARY KEY,
  name VARCHAR(50),
  class VARCHAR(5),
  marks INT
);

Insert:

INSERT INTO students VALUES (101, 'Achieng', '12A', 78);

Select with condition:

SELECT name, marks FROM students WHERE marks >= 50 ORDER BY marks DESC;

Update and delete:

UPDATE students SET marks = 80 WHERE admno = 101;
DELETE FROM students WHERE admno = 105;

Aggregate functions

COUNT, SUM, AVG, MIN, MAX with GROUP BY:

SELECT class, COUNT(*) FROM students GROUP BY class;

Joins

If marks are in a separate table:

SELECT students.name, results.score
FROM students JOIN results ON students.admno = results.admno;

Data integrity and security

Use validation rules, backups, user permissions and passwords. The Data Protection Act, 2019 requires careful handling of personal data, including learners' details.

Worked example

Count students scoring 60 or more in each class: SELECT class, COUNT(*) FROM students WHERE marks >= 60 GROUP BY class;

Try it

• Design two tables for a school library and link them with a foreign key.
• Write a query listing all students in class 12B.
• Normalise a table that stores student name, subject1, subject2 and subject3 in one row.