Project – JSS3 Social & Citizenship Studies Lesson Note (First Term)

A database project is a practical task in which students build a working database to solve a real problem, here a Library Management System for the school library.

Behavioural objectives

1. State the problem a school library database solves.
2. Design two linked tables with suitable fields and data types.
3. Explain the purpose of a primary key, a form, a query and a report.
4. Create a query that filters records using criteria.
5. Produce a report that groups and counts records.

What problem are we solving?

A library with thousands of books cannot run well on memory or scraps of paper.

• Lost books: books go missing and nobody knows where they are.
• Unknown borrowers: we cannot tell who has which book.
• Overdue books: we cannot tell when a book should have been returned.

What is the project goal and plan?

The goal is to create a Library Management System that tracks the books and the students who borrow them.

• Goal: keep a record of every book and every borrower in one system.
• Plan: a good database does not use one giant list, so we create two tables that are linked.

1. Books table: information about the library’s property.
2. Borrowers table: information about the students.

What key terms do we need?

• Database: an organised collection of data stored on a computer.
• Table: a grid that stores data about one kind of thing, such as books.
• Field: one column in a table, such as Title.
• Record: one row in a table, such as the details of one book.
• Primary key: a field that gives every record a unique value, such as Book_ID.
• Data type: the kind of data a field can hold, such as text or number.

How do we design the tables?

We decide every field and data type before we build, because a missing field makes the system fail later.

Table 1: Books_List

Table 2: Student_Borrowers

How are the two tables linked?

• Link: Book_Borrowed_ID in the borrowers table holds a Book_ID from the books table.
• Result: the system can match each student to the book they borrowed.

Why does consistency matter?

• Rule: type the same data the same way every time.
• Example: if one person types “JSS 3” and another types “Junior Secondary 3”, a search will not match them.
• Tip: agree on one format for Class, such as JSS3, before entering data.

What is a form and why do we use it?

A form is a screen for entering and editing records one at a time instead of using the table grid.

• Use: the library prefect can type in a new book’s details or update a student’s information.
• Benefit: it is easier and safer than working in the table grid.

What is a query and how do we use it?

A query is a question you ask a database to find specific records.

• Goal: find all students in JSS3 who have borrowed a book.
• Criteria: the condition a record must meet to appear in the results.

Steps to create the “Who has what?” query

1. Open Query Design.
2. Add the Student_Borrowers table.
3. Add the fields FirstName, Surname, Class and Book_Borrowed_ID to the design grid.
4. Type JSS3 in the Criteria row under the Class field.
5. Type Is Not Null in the Criteria row under Book_Borrowed_ID, so only students who hold a book appear.
6. Run the query and read the results.

• Real-world example: a shop can run a query on its inventory database to see which items are running low.

What is a report and what should ours include?

A report is a printable summary of data from a table or query.

At the end of the week, the School Librarian needs a paper showing the library’s status.

• Grouping: books are grouped by Category, so all Science books appear together.
• Counting: the report counts how many books there are in total.
• Professional look: add the school name and the date at the top.

What are the project tasks?

1. Setup: create the two tables described in the design section.
2. Input: enter at least 10 books and 5 students.
3. The test: create a query that shows only Fiction books.
4. Final step: generate a report called “Weekly Library Summary” and show it to your teacher.

Evaluation

1. State three problems a library faces without a database.
2. What is a primary key? Name the primary key in each of the two tables.
3. Why should data be typed consistently?
4. Explain the difference between a form and a report.
5. Write the field and criteria you would use to show only Fiction books.

Assignment

Complete the four project tasks and list the three database objects you created: tables, query and report. Give each a one-line purpose.