Practical Database Management – SS1 Digital Technologies Lesson Note (Third Term)

Practical database management is the process of building and using a database on a computer. You create a table with fields and data types, enter records through a form, ask questions with a query, and present results in a report. This note uses a School Library Database as the example.

Behavioural objectives

By the end of the lesson, students should be able to:

1. Create a database table with fields and suitable data types.
2. Enter records using a form.
3. Build a query using a table, fields and criteria.
4. Explain what a report is and what it can contain.
5. List the four steps of the database workflow.

How do you start your first database?

Before you touch the computer, decide what your database is for. In this lesson, we will create a School Library Database.

Step 1: Creating the table

The table is the foundation. When you open your DBMS (for example, Access), you start by defining your fields (columns) and their data types.

What are data types?

You must tell the computer what kind of information will go into each field, so that it does not get confused.

• Text: for names or titles.
• Number: for quantities or ages.
• Date/Time: for birth dates or book return dates.
• AutoNumber: useful for a Primary Key, because it gives every new record a number automatically (1, 2, 3 and so on).

How do you enter data (records)?

Once your “skeleton” (the table structure) is ready, you can add the “flesh” (the data).

Manual entry and forms

You can type directly into the table, but most professionals use a form.

What is a form?

A form is a window that shows one record at a time. It works like a digital “fill-in-the-blanks” sheet. A form has these advantages:

• It is easier on the eyes.
• It helps prevent mistakes, such as accidentally deleting someone else’s row.
• You can add buttons to save a record or go to the next person.

What is a query?

A query is a specific question you ask the database.

Imagine you have 5,000 books in your library database. A teacher walks in and asks: “Which Science books are currently borrowed by SS1 students?” You do not look through every row. You run a query.

How to build a query

1. Select the table: tell the computer where to look.
2. Select the fields: choose what you want to see (for example, Book Title and Student Name).
3. Set the criteria: this is the “filter”. You might type =”Science” in the Subject column.

The database hides everything else and shows only the Science books.

What is a report?

A table or a query looks fine on a screen. If you want to print it for a meeting or give it to your Principal, it needs to look professional. A report is the final, polished version of your data, formatted and ready for printing.

What can a report contain?

• Headers and footers: you can add the school logo and the date.
• Calculations: you can add a “Grand Total” at the bottom (for example, “Total Books Borrowed this Week: 45”).
• Grouping: you can group students by their house (for example, all “Red House” students listed together).

What is the database workflow?

To manage a database practically, follow this simple four-step cycle:

1. Create: set up your tables and fields.
2. Input: use forms to add your records.
3. Process: use queries to find specific information.
4. Output: create a report to show your results to others.

Practical tips for the lab

• Save often: databases save data differently from Word documents, but it is still good practice to click “Save” after designing a table.
• Check your spelling: if you search for “Biology” in a query but you spelled it “Bilogy” in the table, the query will find nothing.
• Keep it simple: do not create 10 fields if you only need 5.

Class practical exercise

1. Open Access or LibreOffice Base: create a table called “My_Friends”.
2. Define fields: add FriendID (AutoNumber), FirstName (Text) and PhoneNumber (Text).
3. Input: use a form to add 5 of your classmates.
4. Query: create a query to find only friends whose names start with the letter “A”.
5. Report: generate a simple report showing their names and numbers.

Evaluation

1. What is a data type? Name the data type you would use for a book return date.
2. Give two reasons why a form is better than typing directly into a table.
3. List the three steps for building a query.
4. State two things a report can contain that a table cannot.
5. Name the four steps of the database workflow in order.

Assignment

In your notebook, write the four steps of the database workflow. Beside each step, write one sentence explaining what you do at that step in the School Library Database.