INNTEL TECHNOLOGIES
Inteltechnologies254@[Link]
DATABASES
A database refers to a collection of related data that is organized for ease of access, update and retrieval.
1. Manual data management system
Manual file management refers to the creation and manipulation of files on physical entities like books,
ledgers, folders and file cabinets.
Disadvantages of manual data management systems
1. Redundancy. It refers to unnecessary duplication of data in multiple files.
2. Lack of flexibility. It is difficult to search for a required document or file.
3. Labour intensive. More people may be required to handle the files
4. Poor data sharing. Data in different manual files may not be shared easily.
5. Lack of integrity. Poor storage and maintenance of data may lead to unreliable and misleading
reports.
2. Electronic data management systems
There are two types of electronic filling methods
a) Flat file system
It refers to plain text data files that contain records with no structured relationships. A good
example is a spreadsheet file created to keep the records of vehicles in stock.
b) Database system
Is a shared collection of related logically related data designed to meet the information needs of
an organization.
In a database system, data elements are integrated and share among different users and files
through a database software known Database Management Software (DBMS) such as Microsoft
Access, Oracle, Fox pro etc.
Main functions of DBMs software
1. Allows authorized users to add or delete records.
2. Allows users to modify or update the existing records
3. To keep statistics of data items in database.
4. Organize files and records for easy access.
5. Ensures security and integrity of data by safeguarding it against unauthorized access.
Features of DBM software
Most database management software consist of tools and features used for creating and manip ulating
databases. Some of the features are
• Tables
• Queries
• Forms
• Reports
a) Tables
A table is a database structure used to hold related records. Tables have rows and columns with
each row representing a record while each column represent fields in each record.
b) Queries
A database query is a statement (question) used to request foe information from a database.
Queries are written using a database language called structured query language (SQL).
Instead of looking through all the records manually, a query lets you ask specific questions about your
data and get only the information you need. Think of a query as a question you ask the database, and it
returns the answer.
If a table is like a big spreadsheet, a query helps you:
• Pick certain rows (records)
• Choose specific columns (fields)
• Apply conditions (e.g., “only show customers from Nairobi”)
• Perform calculations (like totals or averages)
Real-life situation example
Scenario: School database
Imagine a school keeps student records in Access, including:
• Name
• Class
• Marks
Now, the principal wants to know:
👉 “Which students scored above 80 in Mathematics?”
Instead of scanning every record manually, you create a query that:
• Selects students
• Filters where Math marks > 80
The query will instantly show only those high-performing students.
The user creates a query by writing SQL statements such as
Select Name, marks
From Students
Where Marks >”80”
2
Explanation:
• SELECT * → gets all columns from the table(like Name, Class, Marks)
• FROM Students → specifies the table name
• WHERE Marks > 80 → filters only students with marks greater than 80
c) Forms
A form is a graphical interface used for entering, viewing or editing data from a table or a query.
It makes data entry in databases neat and simple.
Real-life example
Scenario: Hospital patient registration
A hospital keeps patient records in Access. Instead of typing directly into a table, they create a form
with fields like:
• Patient Name
• Age
• Gender
• Diagnosis
A receptionist uses this form to:
👉 Enter new patient details
👉 Update existing records
The form might include buttons like:
• “Save”
• “Next Patient”
• “Search”
Once the receptionist captures this details using a form, the data is directly updated on the table behind
it or linked to it.
d) Reports
A report displays data from a database in a more professional way. It is the report that provides
the user with means to specify what is to be printed on a report and the layout which the report
takes.
Database models
This refers to how data is stored in databases. Different database software use different database models
to store, organize and manipulate data.
3
There are five types of database models. These are:
• Hierarchical database model
• Network database model
• Relational database model (to be discussed in details in Ms Access)
• Object-relational model
• Object oriented database model
1. Hierarchical Model
• Data is organized like a tree structure (parent → child)
• Each child has only one parent
📌 Example:
A school system where:
• School → Classes → Students
👉 One class belongs to one school, and students belong to one class.
2. Network Model
• Similar to hierarchical, but more flexible
• A child can have multiple parents
📌 Example:
A student can:
• Belong to multiple clubs
• Be taught by different teachers
3. Object-Oriented Model
• Stores data as objects, similar to programming
• Combines data and behavior
📌 Example:
A “Student” object might include:
• Name
• Age
• Methods like “calculate grade()”
• Comments
• Likes
4
4. Relational Model (most common)
• Data is stored in tables (rows and columns)
• Tables are linked using keys (like IDs, Adm no etc)
Used in systems like Microsoft Access
📌 Example:
• Students table
• Courses table
• Results table
👉 You can connect the tables using Student ID or Course ID.
In ms access, we use relational database management system which organizes data into a logical
structure known as schema. A database schema is as shown below
A database schema in Access is made up of characters, fields and records that makes up a table
database.
• Character.
Is a letter, number or a symbol entered on the database to represent a word (field)
• Field
Is a set of characters that defines a single data item. For example in a student register, each
column like surname, first name or gender represents a field.
• Record
Is a collection of related fields that represent a single data entity. Each row in a database table
represents a record.
5
Consider the fig below
• Table
A database table is a file that contains related records. A database table comprises of fields
(columns) and records (rows)
GETTING STARTED WITH MS ACCESS
6
GETTING STARTED WITH MS ACCESS
To launch ms access, proceed as follows,
• Click the Start button
• Scroll through the list of programs
• Click on Microsoft Access
• In the startup window such as the one shown below click blank Desktop Database
• Click create button to display the access application window as shown below.
Creating database tables.
When Ms. Access is first launched, it creates a default table 1 that can be customized as follows
• In the task pane on the left side of the application window, click the icon representing a table
• On the home ribbon/tab, click View and then click Design view. A save As dialog box appears
that requires you to save the table by entering the desired name of the table.
• Using the table design view, enter a unique name for each field (column headers). A field must
start with a letter.
7
• Choose appropriate data type for each field before adding another field. There are several data
types used in Access.
➢ Short text- we use this data type for fields that do not need calculations. Can be
alphabetical letters, numbers or a combination of both.
➢ Long text – is data type that takes up to 255 characters
➢ Number- this data type is made up of numeric numbers 0-9. Mostly used if a certain field
requires mathematical manipulation
➢ Date/time – this data type identifies a field as either date or time
➢ Currency- used to identify values that have decimals or fractions. We use this data type
when dealing with monetary values such as fee balance.
➢ Yes/no – this is a logical data type for an entry that is either yes or no or true or false.
For example, a field may require answer whether you are a male or a female.
➢ Attachment – used to store one or more files containing pictures, charts among others
➢ Calculated- is a data type that is used to store data created from a formula
➢ Auto number – used to increment the values by one
• Click save to apply the changes. Ms access will prompt you to create a primary key, click yes.
Primary key
A primary key refers to a field that uniquely identifies each record in a table. It prevents the user
from making duplicate entries into a table. For example in students table in a school, the adm
number can be used as the primary key because it is unique for different students. Each table
must have a primary key. A table has only one primary key.
To set a primary key, proceed as follows
➢ Open the table in design view then click on design tab
➢ Select the field you wish to set as the primary key by clicking in the row header on the
left of the field name
➢ Click set primary key button
Creating database table in design view
• Click on create tab
• Select table design
• A table is created. Enter the field names and assign the data types
• Set the primary key.
• Click save and enter the name of the table
• Switch to datasheet view to start entering records
Field properties
When creating more complex tables you may need to use field properties to specify details related to
each field in a table.
a) Field size
This allows the user to set the number of characters in a field
b) Format
8
This property determines how information appears in the screen or when printed. For example
you can format a numeric value to scientific, currency or general format.
c) Precision
This is used to specify the total number of digits that can be stored on the left and right of a
decimal point.
d) Decimal places
This applies to number and currency data types. This allows you to specify the number of
decimal places.
e) Input mask
This property is used to format a field entry into a specified format. For example if you enter a
number such as 02000100409874 and the input is set as 000-(00000)-000000, it is displayed as
020-(001100)-409874. This is mostly used to format phone numbers and address entries.
f) Caption
It refers to a more descriptive name for a field to be used in a table. For example the caption for
stuName could be student name.
g) Validation rule
This is a rule that restricts the values to be entered in a field. For example if you want to restrict
marks entered in a field to values between zero and a hundred, type >=0 and <=100 in the
validation rule box
h) Required
This is property that ensures an entry is made in the field before proceeding to the next field or
record. For example if surname is required you cannot proceed before entering it.
i) Allow zero length
This is a validation rule that allows the user to proceed without making an entry in the field set
as zero length
j) Indexed
This is a field that is used to speed up searching and sorting records in a table.
Data entry and manipulation
a) Typing data into a table(entering records)
To enter a record, proceed as follows;
• Double click the table to display to display it in datasheet view
• Click on the first row in the table and start typing. Use the arrow keys to move to the next
cell.
The following symbols may appear on row headers as you enter records
➢ An arrow head – indicates that there is no new data entry or edit that is pending
➢ Pencil – means that the current record entry is not yet saved
➢ Asterisk – it marks a blank record below the current entry
➢ Record locked – indicates that the current record is being edited by another user in
a networked environment.
9
b) Deleting records
To delete a record proceed as follows:
• Click the row header of the record you want to delete.
• On the home tab, under records group, click delete command
Or
• Click the row header of the record you want to delete.
• Right click the mouse and select delete command
c) Editing field content
To edit the content in a field,
• Click inside the cell
• Type the new content to replace the existing one
d) Find and replace
To find and replace a field,
• On the home tab, under the find group click find
• In the find what and replace with boxes, type the word or phrase to search and replace
with respectively
e) Copying and moving records
To copy or move a record, proceed as follows,
• Select the record(s) to be copied or moved
• On the home tab in the clipboard group, click copy button to create a duplicate, or cut
button if you wish to move the records
• Open the target datasheet. On the home tab click paste
f) Sorting and filtering records
Sorting-this refers to arranging records in a sequential order either numerically or alphabetically.
To sort records,
• In datasheet view, select the records to be sorted
• On the home tab, click either the sort button for Ascending (AZ) or Descending (ZA)
Filtering – this refers to displaying records that meets given criteria
To filter records,
• Open the table in datasheet view
• On the home tab in the sort & filter group, click filter button that looks like a funnel.
A small down arrow appears on each field.
• Click the down arrow on the field to be used for setting the filtering criteria
• Point to text filters to specify the filter criteria from the sidekick menu displayed
• To filter specific values, use the checkbox. Only records with the selected field will be
displayed.
• To remove the filter, click toggle filter in sort & filter group.
10
Modifying database structure
a) Modifying table datasheet
• To adjust filed width, point to the column boarder between the column headers
then drag to the required size.
• To adjust row height,
➢ Point to the border between row headers. The mouse pointer changes to
double arrow
➢ Drag to the required height
b) Reordering table fields
To reorder fields,
• Select the column you wish to move by pointing to its header
• Drag the column to the right or left on top of the field you want to appear and then
drop it.
c) Establishing relationships
A relationship is a connection created between two or more tables so that can be linked
and shared properly. Relationships are created using a foreign key. A foreign key is a
field that links another table primary key
There are three types of relationships
a) One-to-one relationship
This is a relationship where the primary (reference table) has only one related record in
the 2nd table and vice versa
b) One-to-many relationship
This is a relationship where a record in the primary table corresponds to more than one
related record in the 2nd table
To create a one to many relationship, proceed as follows,
• Create two tables one named student table and another named subjects. The tables
may have the following fields.
Students table (Admission number, surname, first name, gender, class, DOB)
Subjects table (subject code, admission number, subject name)
11
• Enter records of at least 5 students in the students table and 5 subjects in the
subjects table
For example
Students table
AdmNo Surname First name Gender class DoB
1245 Matlina Lucy F 2E 2009
1246 Maitha Joseph M 3S 2007
1247 Mwatha Kipkelong F 4S 2008
1248 Omondi Simon M 3E 2008
1249 Koech Kibet M 4W 2006
Subjects table
Subject code AdmNo Subject Name
C001 1245 MATHS
C002 1247 PHY
C003 1249 ENG
C004 1248 KISW
C005 1248 CHEM
• After creating the tables, click on database tools tab, then click on relationships
button to display show table dialog box
• In the show table dialog box, select the student table then click add button. Do the
same to the subjects table to add the two tables in the relationship window
12
• Drag the admno field from the student table and drop it into the admno in the
subjects table. In the edit relationship dialog box that appears, click the create
button to establish a relationship as shown below
c) Many to many relationship
This occurs when each record in the primary table corresponds to one or more records in
the child table. Likewise, one record in the child table may have one or more related
records in the primary table. For example, a teacher may teach more than one subject
while a certain subject may be tought by different teachers
Consider the following tables
Teachers table
TeacherID Surname Firstname Gender
T001 Kamau John Male
T002 Wanjiru Mary Female
T003 Otieno Peter Male
Subjects table
SubjectCode SubjectName
S01 Mathematics
S02 English
S03 Biology
S04 History
Allocation table
TeacherID SubjectCode Term Year
T001 S01 1 2026
T001 S02 1 2026
T002 S03 2 2026
T003 S04 1 2026
T002 S01 2 2026
13
Set the appropriate primary keys
In the teachers table the primary key is TeacherID, in the subjects table, the primary key is the
SubjectCode while the primary key in the allocation table is a combination of TeacherID and
SubjectCode.
To make two fields the primary key as in the allocation table, hold down the shift key and click the
primary key button on both TeacherID and SubjectCode
To create many to many relationship for the tables above,
• Open your database and go to Database Tools → Relationships.
• Add the three tables: Teachers, Subjects, and Allocation.
• Drag TeacherID from the Teachers table to TeacherID in the Allocation table.
This creates a one-to-many relationship (one teacher → many allocations).
• Drag SubjectCode from the Subjects table to SubjectCode in the Allocation table.
This creates another one-to-many relationship (one subject → many allocations).
Overall this becomes a many to many relationship.
. Explanation of the Relationship
• A many-to-many relationship exists between Teachers and Subjects.
• Each teacher can teach multiple subjects, and each subject can be taught by multiple teachers .
• The Allocation table acts as the junction table linking the two, while also storing attributes like
Term and Year
14