KCSE ONLINE

Esoma Online Revision Resources

Order this CD Today to Experience the Full Multimedia State of the Art Technology!

Objectives

In today's lesson, we shall look at different types of records and one of the best ways of storing these records by using a computer.By the end of this lesson, you should be able to:

  1. Define a database
  2. Explain the concepts of a database
  3. Explain data organization in a database
  4. Create a table
  5. Edit a table
  6. Design a form to view and enter data into a table
  7. Query a database for specific information
  8. Generate reports from the tables or queries and create labels
  9. Print queries, forms and reports

 

Databases

Welcome

Hello! Welcome to the topic of databases.Today, We shall discuss the meaning of databases. And identify some concepts used in databases as well as how a database is organised.

Definition of a Database

A database is a collection of related files stored in a computer for controlled access,security and integrity of data.

Introduction

In real life situations, information on;
Students records,Patient records in hospitals,Employee records,Stock in a supermarkets is manually stored in books, papers, and files. Similarly, computers can be used to store patient records in hospitals,stock details in supermarkets and employee details in an organization. Information saved in a computer is safer and economical.

Data Organisation in a Database

A database is made up of related files. A file is composed of related records. Records are made up of related fields. Fields contain characters. A character is the smallest unit of data.A field is a basic unit of information within a record. A record is a combination of related fields containing information pertaining to one person, place or thing. A file is a collection of related records.

The folowing diagram illustrate data organizational in a database.

Database concepts


Advantages of Databases over Flat Files

Reduced data redundancy i.e. there is no repetition of records stored, since data is normalized.There is improved data integrity i.e. data is always consistent and accurate since all related records are updated at the same time and it is not easy to input wrong data.

Have better security features e.g. there is controlled access to data.
Have centralized records that can be accessed by remote terminals simultaneously.

Records can be linked with other applications to enhance sharability.

Records can be manipulated using specific criteria.


Organization Structure of a Database

A database is made up of related files. A file is composed of related records. Records are made up of related fields. Fields contain characters.A character is the smallest unit of data.A field is a memory location that contains a data item.A file is memory location with a unique name that contains related records.

Database models

Models are characterized by the way data items relate to each other. Models include:

  1. Hierarchical
  2. Networked
  3. Relational
  4. Object oriented

Click on the links above to see an example of each model.

Hierarchical Model

These are records that are related in a predefined order. E.g. grandparent, parents, children. In this model, each record has one parent record and many children. The level below is subordinate to the one above it. Its also known as a tree model.


Network Model

In this model, records are interlinked. This model allows each record to have multiple parent and child records, forming a lattice structure.

Relational Model

A database based on the relational model is made up of tables which are linked by a unique field common to the tables (key field). This model allows the definition of data types, and retrieval operations and integrity control. This is the most commonly used model. Examples of Relational Database Management System (RDBMS) are Ms-Access, Oracle and SQL (Structured Query Language).

Object Oriented Model

Object oriented Is a model that utilizes Object Oriented Programming technique to manipulate data by creating unique classes and objects which can be reused. Research is on going to in this area.

Features of a d/base

A database must contain the following standard features
The data types and field properties of individual fields must be defined at design time.
It must provide the user with tools for generating reports.
Data should be analyzed by use of queries
It should provide a user interface to enable the user to enter and view data into the database

Creating and Managing a Database : Introduction



Creating and Managing a D/base

Objectives

Welcome to lesson two. In this lesson, we shall discuss creating and managing a database. By the end of this lesson, you should be able to;

  1. Create a table
  2. Edit a table
  3. Design a form to view and enter data into a table
  4. Query a database for specific information
  5. Generate reports from the tables or queries and create labels.Print queries, forms and reports

Creating a database


Starting Microsoft Access


Creating Tables

Tables are used to store all the data in the database. Each row in table contains one record. Records are made up of fields that contain a unique data item. Tables are also called relations.
There are three ways to view your table: Table wizard, Datasheet view and Design view. We shall use design view in this lesson to create tables.
When you create a table using Datasheet view, data is entered into a datasheet grid, which consists of rows and columns labeled Field 1, Field 2, Field 3, e.t.c. The data type is determined by Ms-Access, based on the data entered.
When you create a table using Wizard, the wizard will walk you through a four step process that is easy to follow. The table created however may not meet your exact requirements.


Creating a Table

Creating a table using design view



Data types

Text

Memo

Number

Currency

Time/Data

Yes/No

OLE object

Look-up wizards

Auto number

Hyperlink

Field properties

The field properties of a table are found in the lower part of the design view as shown.The properties include:

Introduction

A relationship is an association between two common fields in two different tables. They are used to enforce data integrity and avoid data redundancy the tables are linked together using a key field so that any manipulation of data is effected on all similar records. Relationships allow querying of different related tables to obtain a dynaset. There are three types of relationships
One-to-One relationship
One to-Many Relationship
Many to many relationship

Click pane to view types

One-to-One Relationship

One record from primary table is associated to only one record in the related table. It is represented by a straight line in Access. This type of relationship is not common since most of information related this way can be in one table. This relationship however is used to divide a table with many fields, isolate fields for security purpose or store information that is a subset of a table.


One-to-Many Relationship

A record in the primary table is associated to many records in the related table but a record in the related table has only one matching record in the primary table. This is the most commonly used relationship.



Steps for creating one to many relationships

Step 1: Close any tables you have open. You can't create or modify relationships between open tables.
Step 2: Press F11 to switch to the database window .
Step 3: Click Relationships on the toolbar. If you haven't yet defined any relationships in your database, the Show Table dialog box is automatically displayed. If you need to add the tables you want to relate and the Show Table dialog box isn't displayed, click Show Table on the toolbar.
Step 4: Double-click the names of the tables you want to relate, and then close the Show Table dialog box. To create a relationship between a table and itself, add that table twice.
Step 5: Drag the field that you want to relate from one table to the related field in the other table. In most cases, you drag the primary key field (which is displayed in bold text) from one table to a similar field (often with the same name) called the foreign key. The related fields do not require to have the same name but MUST be of the same data type and contain the same kind of information.

Step 6: - Set the relationship options if necessary. The options are Enforcing Referential Integrity, cascade update related records and cascade delete related records.
Step 7: Click on create button to create the relationship. The relationship is indicated by a join line between the two tables.

 

Many-to-many relationship:

A record in the primary table has many matching records in the related table and B, and a record in related table can have many matching records in Table A. This type of relationship is only possible by defining a third table (called a junction table) primary key consists of two fields: the primary keys from both tables. A May-to-Many relationship is a two One-to-Many relationship with a third table.

Many-to-Many relationship

A record in the primary table has many matching records in the related table, and a record in related table can have many matching records in the primary table. This type of relationship is only possible by defining a third table (called a junction table) whose primary key consists of two fields: the primary keys from both tables. A Many-to-Many relationship is a two One-to-Many relationship with a third table

 

Editing a relationship

 

Editing tables

As you learnt in word processing and spreadsheets, we have similar editing feature for Ms Access. These include copy, paste, sorting, deleting, adding data and formatting.

Queries

A query is a set of instructions used to select records from one or more tables based on a certain criteria. The records selected are called a dynaset and can be viewed, analyzed and sorted.
The queries are very useful for creating user defined data which can be exported and used in other applications.


Types of Queries

There are several types of queries:


1.
Select queries: It's used to extract data from tables based on a criterion or specified value.
2. Action queries:
- Update queries : It makes changes to a group of records or all records in one or more tables.

- Append queries : Adds a group of records from one or more table to the end of another table. The tables do not need to have the same number of fields for them to be merged.


- Delete queries : It deletes a group of records from one or more tables.
- Make table queries : Creates a new table from one or more tables.


3.Cross tab queries : It calculates and restructures tables to make it easy for analysis.

Creating a Query

Creating a select query using design view


 

Formatting data and controls in a form in design view

Since you have already come across formatting in word processing and spreadsheet this area will be easier to handle. To make any changes in the form you must do it in design view. Under the design grid you select the text or object go to properties to effect desired change.



Creating Reports

These are records that are related in a predefined order. E.g. grandparent, parents, children. In this model, each record has one parent record and many children. The level below is subordinate the one above it. Its also known as tree model.

Bound controls

They are in direct connection with a database. They enter data in the database or show data from the database. They can change data, or they can show changes in data as those occur. If you want a control in a form or report to be a bound control, first make sure that the form or report is based on a table or query. Examples of these controls include text boxes, List boxes and combo boxes.

Unbound controls

They display information that does not originate from a table or a query. Examples include labels, lines, rectangles, and pictures

Calculated controls

They do not display data that is stored in a table or query. Instead they are used to display calculated values and may contain a formula of an expression e.g. = [Marks]*0.75. They perform calculations using database data, but they do not change it.Text box: - Are used to display and accept a wide variety of data e.g. names, addresses and admission number.
List box: - A control that displays options of values from which the user can choose one value.
Combo box: - it is like a list box and text box combined into one control. It allows the user either to type a value or select a value form a list of option.
Label: - Used to display descriptive text such as headings on a form, column titles, captions or brief instructions. They do not display values from a field or expression. They are static, they do not change s you move form record to record. They are automatically included when most controls are created.
Command: - Use to execute stored procedure e.g. save, Exit, print
Option: - Used to select one of the two items provided. When having more than two items option groups is used.
Check box: Used to select one or more of the items provided ) end of text to be hyperlinked.

Introduction

Formatting data and controls in a form in design view

Since you have already come across formatting in word processing and spreadsheet this area will be easier to handle. To make any changes in the form you must do it in design view. Under the design grid you select the text or object go to properties to effect desired change.

Introduction

Calculated field

Calculated controls are used to manipulate values. They display the result of an expression. An expression is a combination of arithmetic and Boolean operators, field names, functions and constant values and the expression must begin with an equal sign. The fields generated are called calculated field.


Manipulating data using a form


Step 1
In form design view, drag and drop the text box control into the detail grid.
Step 2: Either delete the Label section or label the text box then type the expression directly into the text box control. For example, the sum of the students Total mark can be calculated by using the expression = SUM (marks), Student total score can be calculated using =Eng+Maths+Kiswahili.

Other examples of expressions include:
= date ( ): - Returns the current date according to the system clock
= now ( ): - Returns the current date and time
=date ( ) + 10: - Adds 10 days to the current date
= [first name] & & [last name]: - Joins the first name with the last name with a space between the two fields
= [marks] * 0.75: - multiplies the marks by 0.75
Step 3: Click on form view on the view menu.

Creating Reports

Introduction

A report is a summary of analyzed data that is output in a predefined format.
A report has the following sections
Report header: - It is at the beginning of the report. It contains the name of the report and describes the information listed in it.
Page header: appears just below the report header and displays column headings and page numbers
Group header and footer: - If records are sorted into groups based on a common value, the group header is placed at the start of every group while the group footer after the last record of the group.
Detail section: it contains data for each record.
Report Footer: - It appears at the bottom of the last page of the report before the page footer of that page. It contains summary information such as grand totals.
Page footer: - Last item on each page of the report. It contains a page number or other descriptive information.

Creating Reports

A report is a summary of analyzed data that is output in a predefined format.


A report has the following sections:

Report header: - It is at the beginning of the report. It contains the name of the report and describes the information listed in it.


Page header:Appears just below the report header and displays column headings and page numbers


Group header and footer: - If records are sorted into groups based on a common value, the group header is placed at the start of every group while the group footer after the last record of the group.

Detail section: it contains data for each record.


Report footer: -It appears at the bottom of the last page of the report before the page footer of that page. It contains summary information such as grand totals.

Page Footer: - Last item on each page of the report. It contains a page number or other descriptive information.

How to create a report

Step 1: Click on Reports under the database window.
Step 2: Click on Design View button and select new. A dialogue box appears. Click on Design view then choose the table or query where the data will come from. A design report window appears.
Step 3: Drag and drop the fields in the table or query shown in the window to the detail grid.
Step 4: Move the labels to the page header so that they are not repeated in a tabular report format.
Step 5: In the report header and footer section insert user defined text that will be common in all reports.
Step 5: Go to view on the menu bar and click on Print preview.

Background

In real life situations, information on;
i.) Students records
ii.) Patient records in hospitals
iii.) Employee records
iv.) Stock in a supermarkets among others is manually stored in books, papers, and files.

Similarly, computers can be used to store;
(i) patient records in hospitals
(ii) stock details in supermarkets and
(iii) employee details in an organization.

Information saved in a computer is safer and economical.


















Databases


Order this CD Today to Experience the Full Multimedia State of the Art Technology!

For Best results INSTALL Adobe Flash Player Version 16 to play the interactive content in your computer. Test the Sample e-Content link below to find out if you have Adobe Flash in your computer.

Sample Coursework e-Content CD

Other Goodies for KCSE ONLINE Members!

Coursework e-Content CD covers all the topics for a particular class per year and costs 1200/- ( Per Subject per Class ).

Purchase Online and have the CD sent to your nearest Parcel Service. Pay the amount to Patrick 0721806317 by M-PESA then provide your address for delivery of the Parcel. Alternatively, you can use BUY GOODS TILL NUMBER 827208 Ask for clarification if you get stuck.

Install ADOBE Flash Player for Best Results

For Best results INSTALL Adobe Flash Player Version 16 to play the interactive content in your computer. Test the link below to find out if you have Adobe Flash in your computer.

Search

Subject Menu