SS 3 First Term

Learning Objectives

• Describe databases and the role of a DBMS.
• Use SQL to read and write data.
• Use SQL to update and delete records and design tables.
• Explain primary keys, foreign keys and entity relationship diagrams.

Introduction to Databases and DBMS

A database is an organised collection of structured data stored electronically. Unlike a set of separate files, a database keeps related data together so that storage, retrieval and updating are efficient and safe. A school database might hold tables for students, teachers, subjects, grades, fees and attendance, all linked so that one query can cross-reference them. If 800 students, 60 teachers and all fee and grade records were kept in separate spreadsheets, the same student would appear in many files with different information, specific records would be hard to find, data could be lost or corrupted, and no link would connect a student's grades to their fee record. A database solves all of these problems.

The components of a database work together as a structured whole. A table is a collection of related data arranged in rows and columns, like a spreadsheet. A record, or row, is one complete entry in the table, such as one student's full information. A field, or column, is a single piece of information such as name or score. A primary key uniquely identifies each record, so no two students can share the same admission number. A foreign key is a field in one table that refers to the primary key of another table, creating the link between them, for example a student_id in a Grades table pointing back to the Students table.

A Database Management System (DBMS) is the software that allows users to create, read, update and delete data in a database. It handles storage, security, backup and concurrent access, so that many users can work with the same data safely at the same time. Widely used systems include MySQL, an open source server-based system popular for web applications and school management; SQLite, a lightweight file-based system ideal for mobile apps, small desktop programs and learning, because the whole database lives in a single file; PostgreSQL, an advanced open source system for large enterprises; and commercial systems such as Microsoft SQL Server and Oracle Database used by businesses and banks. For learning, SQLite is the simplest to set up because it needs no server, while MySQL is the typical choice in real-world applications.

SQL Fundamentals

SQL (Structured Query Language) is the standard language for creating and managing databases. Keywords such as CREATE, INSERT, SELECT, UPDATE and DELETE are conventionally written in uppercase, though SQL is not case-sensitive. The CREATE TABLE statement defines a new table by naming the fields and their data types, including TEXT for text, INTEGER for whole numbers and DATE for dates, and by declaring which field is the primary key. The NOT NULL constraint marks a field that cannot be left empty. The INSERT INTO statement adds a new record; its values must match the order and data types of the fields. The SELECT statement retrieves data, using an asterisk for all fields, naming specific fields when only some are wanted, adding a WHERE clause to filter records and an ORDER BY clause to sort them, with DESC for descending and ASC for ascending.

Filtering and sorting are the everyday work of database queries. One typical query finds all SS3A students, another names scores above 80, a third lists students alphabetically, a fourth counts records, and a fifth computes an average score. The COUNT and AVG functions summarise a whole table. These patterns are used again and again in real database work and in the capstone project. Practical exercise, a school Fees table holds student_id, amount_paid, amount_due and payment_date. Write the CREATE statement with student_id as primary key, insert three records, select the fully paid records where amount_paid equals amount_due, and select student_id and amount_due sorted by amount_due in descending order.

SQL UPDATE, DELETE and Database Design

The UPDATE statement modifies existing records. Because it changes every record that matches its condition, it must always include a WHERE clause; without it, every record in the table is changed, which is almost always an accident. UPDATE Students SET score =88 WHERE admission_no = 'a given number' raises one student's score, while UPDATE Students SET class =' SS3C' WHERE class = 'SS3B' moves an entire group at once. The DELETE statement removes records and must also use a WHERE clause, because DELETE without WHERE empties the whole table. Destructive operations like deleting every score below 40 should be confirmed and preceded by a backup in any real system. Collectively these four operations, INSERT, SELECT, UPDATE and DELETE, are known as CRUD, Create, Read, Update and Delete, and every data-driven application performs them.

Database design, or schema design, plans the tables, fields, data types and relationships before any SQL is written. Good design prevents duplication and protects data integrity. For a school management system the designer first identifies the entities, Students, Subjects and Grades. Next come the primary keys, admission_no for Students, subject_code for Subjects, and for Grades a combination of student_id and subject_code. Then come the foreign keys, Grades.student_id references Students.admission_no, and Grades.subject_code references Subjects.subject_code. Finally the CREATE TABLE statements are written with all keys in place. Once tables are linked by foreign keys, a JOIN combines data across them, for example producing a report of each student's name, subject and score. Scenario question, a school library needs Books, Borrowers and Loans tables, where Loans carries book_isbn and borrower_id as foreign keys, and a loan is recorded, returned and reported through INSERT, UPDATE and SELECT queries.

Primary Keys, Foreign Keys and Entity Relationship Diagrams

In a relational database, keys link tables together and ensure every record can be uniquely identified. A primary key uniquely identifies each record in a table. No two records can share the same primary key value, and it cannot be left empty. For example, admission_no is the primary key of the Students table. A foreign key in one table refers to the primary key of another table. It creates a link and enforces referential integrity. In the Grades table, student_id references Students.admission_no, ensuring every grade belongs to a real student.

Primary key: uniquely identifies each record (e.g. admission_no)

Foreign key: links tables by referencing a primary key (e.g. student_id in Grades)

An entity relationship diagram (ERD) is a visual diagram showing tables and their relationships. A one-to-many line from Students to Grades means one student can have many grade records. Drawing an ERD before writing SQL helps catch design problems early.

Practical Work

1. Name the main objects of a database.
2. Write a SELECT statement with a WHERE clause.
3. Write an UPDATE statement to change one record.
4. Draw a simple entity relationship diagram for two tables.

Scenario-Based Questions

1. A school database has a Students table with columns admission_number, name, class and a Fees table with columns fee_id, admission_number, amount_paid and total_fee. Explain what a primary key is and why admission_number is a suitable primary key for Students. Then explain what a foreign key is and how the admission_number in Fees links the two tables.
2. Write the SQL statement to create a Students table with admission_number as INTEGER PRIMARY KEY, name as TEXT NOT NULL, class as TEXT and score as INTEGER. Then write an INSERT statement to add one student: admission_number 1024, name 'Chioma Eze', class 'SS3A', score 82.
3. A school admin accidentally runs UPDATE Students SET score = 0 without a WHERE clause. Explain what happens, how many records are affected, and why the WHERE clause is essential in UPDATE and DELETE statements.
4. A school needs to list all SS3A students with scores above 75, sorted by score descending. Write the complete SQL query and explain the role of each clause (SELECT, FROM, WHERE, ORDER BY).
5. Compare a database with flat files (separate text or spreadsheet files for each type of data). List two advantages of a DBMS over flat files for a school managing students, grades and fees.
6. A programmer designs a school database and creates three tables: Students, Subjects and Enrolments. Enrolments links Students to Subjects. Explain the purpose of this linking table and describe the primary and foreign keys it should contain.
7. A clinic stores patient records in a database. Write SQL queries to: (a) find all patients aged above 60, (b) count the total number of patients, (c) delete a patient whose patient_id is 205. Explain the danger of running query (c) without confirming the correct patient_id.