DATABASE MANAGEMENT SYSTEMS (DBMS)

A database management system (DBMS) is a software package designed to define, manipulate, retrieve
and manage data in a database. A DBMS generally manipulates the data itself, the data format, field names,
record structure and file structure.

Some DBMS examples include:

 Ms-Access
 MySQL
 SQL Server
 Oracle
 dbase
 FoxPro
A database is an organized collection of data, generally stored and accessed electronically from a computer
system.

FUNCTIONS OF DBMS SOFTWARE

1 Allows user to add and delete records.

2 It is used to update and modify existing records.
3 Organizes data for easy access, retrieval and manipulation of records.
4 Acts as interface between a database and other application programs.
5 To ensure security of the data in the database by safeguarding it against unauthorized access and
corruption. (damage)
6 It keeps the statistics of the data items in a database.

Data organization in a database

Data is organized from the simplest form called a field to a very complex structure called a database. i.e.
a) Field
This is a character or a logical combination of characters that represents a data item. E.g. in class list, the
student name is a filed.
b) Record

1 |P a ge
This is a collection of related fields that represents a single entity. E.g. a student’s report card may contain
the students name, adm.no, class total marks, average grade.
c) Tables/Schemers/relationals
A table is a collection of related records .e.g. a students’ file in a school database contains the details of the
students in the school.
d) Database
This is the highest in data organization hierarchy that holds all related files in tables. E.g. a school database
may contain staff tables/files.

INTRODUCTION TO MS-ACCESS
Ms-Access is a database management system package used to create, maintain and manipulate databases.
Starting/Launching/Loading Ms-Access
Procedure 1 (windows 7)
i. Click the start button
ii. Click Microsoft office folder
iii. Click Microsoft office access 2007
Procedure 2 (windows 8)
i. Start typing Access on the start screen
ii. The search opens automatically as you type
iii. From the search results, click Microsoft office Access.

Creating a ne w database
Procedure
i. Load Ms Access
ii. Click the office button
iii. Click new
iv. Click blank Database icon
v. Specify the filename of the database
vi. Specify where to save the database by clicking on the location button
vii. Click ok, then click create.

2 |P a ge
Creating tables

i. Load the database

ii. Click the create tab on the ribbon
iii. In the tables group area, click the table design icon
iv. Define the field names and their data types e.g.

Field name Data type

ADM NO number
NAME Text
AGE Number
COURSE Text
FEES PAID Currency
GENDER Text
v. Specify the primary key
vi. Save the table e.g. Students Record Table
vii. Change from design view to datasheet view and enter the records.
STUDENTS RECORD
ADM NO NAME AGE COURSE FEES PAID GENDER
151200 John Kamau 22 Programming 5,500 Male
151202 Marion Chebet 20 Computer Packages 4,500 Female
151204 Zacharia Okinyi 23 Computer Hardware 5,500 Male
151203 Blessing Mwangi 24 DICT 18,300 Male
151205 Anne Arunga 21 ICT 17,500 Female

FIELD DATA TYPES

The type of data to be used in a database must be clearly defined for the purpose of manipulation and
storage. For example, if a field is to be used for calculations, it must be defined as number.

The data types allowed in Ms Access include;

1. Text
This type includes the alphabetic letters, numbers, spaces and punctuations.
Use this data type for fields that do not need to be used for calculations such as names, places,
identification numbers etc.
2. Number

3 |P a ge
These are fields made up of numeric numbers 0-9 that are to be manipulated mathematically.
3. Memo
This is a field made up of alpha numeric characters (both alphabetic and numeric data).
Instead of using text, use this type if you need to enter several paragraphs of text as it accommodates a
maximum of 32,000 characters.
4. Data/Time
This is used to identify a field as either a date or time. This is because; date and time values can be
manipulated mathematically in a database.
5. Currency
This is used to identify numeric values that have decimals of fractions.
Use this datatype especially when dealing with monetary values such as fee balance, amount sold, salary
etc.
6. Auto number
This is a numeric value used if you wish Ms Access to automatically increase the values in a field. E.g.
when entering a list of 40 students and you have a filed labeled student No., the numbers will increase by
one every time you enter a new record.
7. Yes/No
This is a logical field where an entry is either Yes/No, True/False. E.g. a field may require you to answer
whether you are male or female.
8. OLE object
It stands for Object Linking & Embedding.
This type of field is mostly used with graphical user interface application for inserting graphical objects
such as pictures, drawings, charts etc.

PRIMARY KEY
This is a field or a set of fields that uniquely identify each record in a table.
The field that is to be assigned a primary key should not have null values and duplicates. (Repetitive
values)
Importance/Advantages of primary key
1. It helps in easier retrieval of stored data in a table.
2. It ensures that there is no repetition of records.
4 |P a ge
3. It ensures that no record is skipped i.e. all records must be entered.

Procedure of assigning a primary key

1. Be on the design view of the table you want to assign a primary key
2. Select the field that you want to assign the primary key
3. Right click on it and choose primary key form the menu that appears.
Or
1. Select the field then click the primary key option from the ribbon.

RELATIONSHIPS

A relationship is an association established between common fields in two or more tables.

Types of relationships

1. One- to- One

In this relationship, for a particular field in one table (main/parent table) there is only one matching record
in the related table and vice versa.

2. One- to -Many

In this relationship, for a particular field in one table, there are several matching records in the related table.
This is the most common type of relationship.

3. Many- to- Many

In this relationship, for particular records in one table, there are several matching records in the other table
and vice versa.

Creating a relationship

i. On the database window, click the database tools tab

ii. Click relationship option from the ribbon
iii. On the show table dialog box that appears, choose the tables you want to relate, click add then close
the dialog box.
iv. The selected tables appears on the relationship window
5 |P a ge
 v. Identify the common fields from one table, drag and drop it to the other common of the other table
vi. In the edit relationships dialog box, ensure that you have the correct fields then click create.
vii. Repeat the same to the other tables and when through, save the relationship and close the window.

QUERIES

A query is a database tool that we use to extract, filter or ask some information from the database.

FUNCTIONS OF QUERIES

1. They are used to sort records in a table

2. You can perform calculations in queries.
3. You can make changes in the data held in your tables.
4. You can ask questions about the data by setting it in a certain criteria.
5. Queries can be used as a source of data when creating forms and reports.

Creating queries in design view

i. Load the database from where you want to create the query
ii. Click the create tab on the ribbon
iii. In other group area, click query design icon and the show table dialog box is displayed
iv. Click the tables tab, select the table from where you want to create your query then click add and close
v. Drag and drop the field names to the bottom grid or double click on the fields one at a time
vi. Type your criteria in the criteria section e.g. “female” in the gender field to extract female records only.
vii. In the results group area, click the run icon t display the query details
viii. Save the query and close.

COMPUTED FIELDS IN QUERIES

Computed fields are the fields created using one or a combination or two or more fields previously found in
a table and all the fields are treated as a single field. E.g.

FEE BALANCE:([TOTAL FEE]-[FEE PAID])

6 |P a ge
PROFIT:([SELLING PRICE]-[BUYING PRICE])

FORMS

 A form is an interface that enables the user to view and make data entries into an underlying
table/query more easily.
 In Ms Access, a form is designed using graphical objects called controls.
 A control is a visual object such as a textbox, checkbox, command button or shapes that you can place
on a form design grid to display data or perform actions.
There are two types of controls you can place on the grid i.e.
a) Bound control
b) Unbound control
 Bound control is the one whose source of data is a field in the table or query.
 Unbound control is the one that is not connected to any data source.

Creating forms using the form wizard

i. Load your database

ii. Select the table or query you want to use to create the form
iii. Click the create tab on the ribbon
iv. In the forms group area, click more forms icon and from the menu that appears, click form wizard
and the form wizard dialog box is displayed.
v. Under the available fields, use the chevrons (>, >>) to select which fields will appear on your form.
vi. Click next
vii. Determine the layout you would like for your form e.g. columnar and click next
viii. Determine the style you would like for your form e.g. Equity then click next.
ix. Type the title of your form
x. Click the finish tab to display your form.

7 |P a ge
REPORTS
Reports are used to summarize and present information from a database.
Parts of a report
1. Report header
 It contains the unbound controls that display the title of the report.
2. Page header
 Contains headings or labels of data items to be displayed in every column.
3. Detail
 Holds bound controls that display data items for the table or query it was created from.
4. Page footer
 Holds a control that is used to display on every page such as page number and the date.
5. Report footer
 Used to display summary from a report such as the grand total for numerical data in a particular
field column.
Creating reports using report wizard
i. Load your database
ii. Select the table of query to create the report from.
iii. Click the create tab on the ribbon
iv. In the reports group area, click the report wizard icon and the report wizard dialog box is displayed.
v. Under the available fields, use the chevrons to select the fields to be displayed on the report then
click next.
vi. Determine whether you want to add any gro uping levels on your report. If no just click next.
vii. Determine the sort order you want for your records. (Ascending/Descending). If not just click next.
viii. Determine the layout of your report e.g. Tabular and also specify orientation then click next.
ix. Determine the style you would like for your report e.g. Apex then click next.
x. Type the title of your report then click finish.

8 |P a ge