COMPUTER O LEVEL(FORM FOUR) NOTES – MANAGEMENT OF DATABASE INFORMATION SYSTEM

MANAGEMENT OF DATABASE INFORMATION SYSTEM

Querying Database

Queries are the fastest way to search for information in a database. A query is a database feature that enables the user to display records as well as perform calculations on fields from one or multiple tables.

You can analyze a table or tables by using:

1. Select query
2. Action query

Action query: These queries are used to make changes in many records at once. They are mostly used to delete, update, add a group of records from one table to another, or create a new table from another table.

Types of Action Queries in Microsoft Access:

1. Update – updates data in a table.
2. Append query – adds data in a table from one or more tables.
3. Make Table Query – creates a new table from a dynaset.
4. Delete query – deletes specified records from one or more tables.

Select Query

A select query is used for searching and analyzing data in one or more tables. It lets the user specify the search criteria and displays the records that meet those criteria in a dynaset or analyzes them depending on the user requirement.

Creating a Select Query

1. Ensure that the database you want to create a query for is open.
2. Click the Query tab, then New.
3. In the New Query dialog box, choose either to create a query in Design View or using the Wizard.
4. To design from scratch, click Design View. The Show Table dialog box appears, from which you can add a table or tables you wish to include in the query.
5. Click the table from the Table/Query list and then click Add.
6. Click Close to close the Show Table dialog box.
7. The query design grid opens. In Microsoft Access, it is called Query by Example (QBE).

Specifying the Search Criteria

To search for a particular set of records, enter a conditional statement or statements in the Criteria row.

Example: If you have a table called Employees with one of the fields as Salary, you can display all employees earning more than Shs. 5000 by typing >5000 in the Criteria row under the Salary column.

To define criteria, use either relational or logical operators.

Relational Operators Include:

• Less than: <
• Greater than: >
• Greater than or equal to: >=
• Less than or equal to: <=
• Not equal to: <>
• Equal to: =

Logical Operators Include:

• AND – used to display values in a specific range.
  Example: If you type >4000 AND <6000 in the Criteria row under the Salary column, all employees who meet this condition will be displayed.
• OR – used to display either one of two values.
  Example: To get employees either in Dar es Salaam or Morogoro.
• BETWEEN – used to display data in a particular range.
  Example: Instead of typing >4000 AND <6000, type BETWEEN 4000 AND 6000.
• NOT – used to display all records except those you do not want to see.
  Example: If you type NOT 8000 in the Salary column of the Employees table, all employee records will be displayed except those with a salary of 8000.

Modifying and Updating a Query

To Delete Fields from the Query Grid:

1. Open the desired query in Design View.
2. Select the field column you want to delete.
3. Choose Delete from the Edit menu.
4. Click the Save button to save the changes.

To Adjust the Column Width in a Query:

1. Open the desired query in Design View.
2. Position the mouse pointer at the boundary that separates columns, then drag it to the required size. Alternatively, double-click the boundary to auto-fit the cell content.
3. Click the Save button to save the changes.

To Modify a Criteria Statement in a Select Query:

1. Open the desired query in Design View.
2. Modify the criteria statements as desired.
3. Click the Save button to save changes.
4. To test whether the changes have taken effect, click the Run button to display the results of the query.

Generating Reports

• A report is a flexible means of presenting and displaying your data in a database.
• In a report, data cannot be modified or deleted; you can only view data.
• Reports are used to summarize and present information from a database, while tables are used for identification purposes. Good database software should enable generating reports and labels.

Creating Reports

Report layout is designed by placing controls on the report designer parts of a report.

• Report Header: Contains unbound controls that display the title of the report.
• Page Header: Contains headings or table data items to be displayed in every column.
• Detail: Holds bound controls that display data items for the table or query it was created from.
• Page Footer: Holds controls to be displayed on every page, such as the page number and date.
• Report Footer: Used to display summaries from a report, such as the grand total for numerical data in a particular field column.

Creating a Report in Design View

1. In the database window, click the Reports tab, then New.
2. In the New Report dialog box, click Design View.
3. Click the name of the table or query you want to generate a report from.
4. Click the OK button. You will get a report design grid where you can place data controls.
5. From the View menu, click Field List.
6. To design the layout, drag each field from the Field List to the layout grid and drop it where you want the data column to appear.
7. Once you finish placing controls, click the Save button.
8. In the Save As dialog box, enter the name of the report and click OK.
9. To view the report, click the Print Preview button or alternatively click Print Preview from the File menu.

Modifying a Report Layout

To Modify Headers and Footers:

1. Open the report in Design View.
2. Click the report header or footer you want to modify.
3. Make the necessary changes and then click Modify.
4. Click the Print Preview button to view the changes.

To Add More Controls onto the Report Layout:

1. Open the report in Design View.
2. Display the Field List by clicking the Field List button or using the View menu.
3. Select one or more fields in the Field List and drag them to the report design grid.

To Resize or Move a Control:

1. Click the control to select it. Position the mouse pointer on the placeholder until it changes to a double-sided arrow.
2. Drag the pointer to resize the control.
3. To move a control, select it and place the mouse pointer on the placeholder until it changes to a hand, then drag.

Printing the Reports

Before printing, you should set the page options, i.e., margins, paper size, and orientation.

1. Open the database that contains the report you want to print.
2. Click the Reports tab, select the report you want to print, and then click the Preview button.
3. On the File menu, click Print.
4. Set the printer options, i.e., printer type, print range, and number of copies.
5. Click OK to print.

Practical Activity

You are the database manager for Tanesco SACCOS society, and you are expected to create a database called Tanesco.

1. Design two tables in the database, Employee and Bill, with the following fields.

2. Enter the following records in the Employees table.

3. Enter the following in the Bill table.

Explain why duplicate PNO is acceptable in the Bills table and not in the Employees table.

4. Create a calculated query based on the two tables that will calculate and display the following:

• (a) The total bill for Rashid in the month of January.
• (b) The total bill for all the employees.