Skip to content

Database Management System I – SS2 Data Processing Lesson Note (First Term)

Lesson note at a glance

Class
SS2
Subject
Data Processing
Term
First Term
Reading time
6 min
Start of this note, 1 of 8
Take this term’s notes offline
Reading here is free. Download all 8 SS2 Data Processing First term notes as PDF to print and Word to edit.
Download all three terms, ₦2,000
Instant download. Secure payment by Paystack. Card, bank transfer, USSD or OPay. Payment stuck? WhatsApp us.

A database is an organised collection of data stored in a structured way. A database management system (DBMS) is software that lets users define, create and maintain a database.

Behavioural objectives

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

Advertisement
  1. define a database and a database management system (DBMS);
  2. state the functions of a DBMS;
  3. name examples of database management packages;
  4. explain the terms row, column, field, character and record;
  5. create a database, a table and fields with data types in MS Access;
  6. set a primary key in an MS Access table.

What is a database management system?

A database management system is software that enables users to define, create, maintain and control access to a database.

  • Interface: it sits between the database and the end users or application programs.
  • Interaction: it works with the user, other applications and the database to capture and analyse data.
  • Purpose: it allows the definition, creation, querying and administration of databases.
  • Organisation: it keeps data consistently organised and easy to reach.
  • Database administrator: the person who controls the database system, its access and its security.

What are the functions of a DBMS?

A DBMS lets users create, edit and update data in database files, and it also performs the functions below.

  • Concurrent access: allows many users to use the same database at the same time.
  • Security: sets rules that decide the access rights of each user.
  • Data integrity: improves the accuracy and consistency of the data stored.
  • Data dictionary: provides a description of the data held in the database.
  • Querying: allows users to search for and retrieve the data they need.

What are examples of database management packages?

Common database management packages include Microsoft Access, MySQL, Oracle, Microsoft SQL Server and FileMaker.

  • Microsoft Access: a DBMS from Microsoft that stores data in its own format based on the Access Jet engine. It can import or link directly to data in other databases.
  • MySQL: an open-source DBMS and one of the most popular.
  • Oracle: an object-relational database management system.
  • Microsoft SQL Server: a Microsoft DBMS that stores and retrieves data for other applications. Those applications can be on the same computer or on other computers across the network.
  • FileMaker: a cross-platform relational DBMS that began as an MS-DOS-based program.
Advertisement

What are the common database terms?

The common database terms are character, field, record, row, column, table and database.

  • Character: a single symbol in a file, such as a letter, digit or sign.
  • Field: a group of related characters in a file, such as a surname.
  • Record: several related fields put together and treated as one entity, such as all the details of one student.
  • Row: a horizontal line of data in a table. It holds one record.
  • Column: a vertical line of data in a table. It holds one field.
  • Table (file): a collection of related records arranged in rows and columns.
  • Database: a collection of related tables.
Character Field Record Table (file) Database
Data units from the smallest to the largest. Based on standard data processing texts.

What is Microsoft Access?

Microsoft Access is a relational database management system used to create and modify databases.

  • Database package: software used to design a database on a computer.
  • Common example: MS Access is the package used in this lesson.

How do you use MS Access to create a database?

You load MS Access, create a blank database, then create a table inside it.

Step 1: Load MS Access

  1. Click on the Start menu.
  2. Point to All Programs.
  3. Point to Microsoft Office.
  4. Click on Microsoft Office Access.

Microsoft Access start window

Step 2: Create the database

  1. From the displayed window, click on Blank database.
  2. On the right-hand side of the window, where the arrow is pointing in the picture above, click inside the file name text box and type the name you want for the database.
  3. Click on the Create command button. A database with the file name you gave will be created.

Files are created as tables in the database.

Step 3: Create a file (table)

  1. Click on the Create menu and select Table.
  2. At the All Tables tab, right-click on any of the tables.
  3. Select Design View.
  4. In the Save As dialogue box, type the table name you want (for example, Student Table) in the Table Name text box.
  5. Click OK.
Advertisement

Design View and Save As dialogue box in MS Access

What are rows and columns in an MS Access table?

A table in MS Access is organised into rows and columns, as in the picture below.

A student table in MS Access showing rows and columns

  • Row: contains one record made up of several fields.
  • Number of records: the rows show how many records are in the table. The picture above has six records.
  • Column: usually represents one field and holds one type of information.
  • Fields in the picture: there are three data fields: REG. No., Surname and First name.

What is the difference between a row and a column?

A row runs across the table and holds one record. A column runs down the table and holds one field.

Point Row Column
Direction Horizontal Vertical
Represents A record A field
Holds Details of one item, such as one student One kind of information, such as Surname
Count shows Number of records Number of fields

How do you create fields with data types?

A data type is the kind of data a field accepts as input.

  • Field name: a name that suits the information the field keeps.
  • Data type: assigned to each field name to control what can be typed in.
  • Example: a field with the Number data type will not accept a name such as Ade as input.

What are common MS Access data types?

The common data types are Text, Number, AutoNumber, Date/Time, Currency and Yes/No.

Data type Use Example field
Text Letters, and also numbers and symbols that are not used in calculations Surname, First name
Number Numeric values used in calculations ID Number, Age
AutoNumber A unique number given automatically to each new record Record ID
Date/Time Dates and times Date of birth
Currency Money values School fees
Yes/No Only one of two values Fees paid

How do you set the data type for a field?

  1. Create the table and open it in Design View.
  2. Under the Field Name tab, type the field name.
  3. Under the Data Type tab next to the field name, click the drop-down menu.
  4. Select the data type that suits the field, such as AutoNumber, Number or Text, as shown below.
Advertisement

Data type drop-down menu in Design View

  • ID Number: given the Number data type, since ID numbers are numeric.
  • Surname and First name: given the Text data type.

What is a unique identifier (primary key)?

A primary key is a field whose value is different for every record, so it identifies each record uniquely.

  • Default key: MS Access usually sets a primary key on the first field.
  • Example: REG. No. suits a student table, since no two students share one.

How do you set a primary key?

  1. Open the table in Design View.
  2. Right-click on the field you want as the primary key.
  3. Select Primary Key.
  4. Check that a key symbol appears beside the field. If it does not, repeat the steps.

Setting a primary key in an MS Access table

Evaluation

  1. Define a database management system.
  2. State four functions of a DBMS.
  3. Name three examples of database management packages.
  4. Explain the terms field and record.
  5. State the steps to set a field as the primary key in MS Access.

Assignment

Write out the steps to create a database in MS Access and a table named Student Table. Then list three fields for the table, give the data type for each, and name the field you would use as the primary key.

This term's Data Processing topics

SS2 First Term · 8 topics

  1. Database Management System I
  2. Database Management System II: How to Create Database
  3. Data Model
  4. Data Model II: Types of Data Modelling
  5. Data Modeling
  6. Data Modeling II: Standard Data Model & Examples
  7. Normalization
  8. Entity-Relationship Models
Advertisement

Lesson Notes for Other Classes

Print it or edit it in Word

Every note is free to read online. A PDF or Word copy of one subject costs ₦1,000 for one term, or ₦2,000 for all three terms.

Sign in first, then choose your class and subject at checkout.