admin@kcseforecast.com

Databases

Databases

By the end of this lesson you should  be able to: Define a database Disadvantages of manual database Define a database management system State examples of DBMS softwares State functions a database management system software State database models State features of a database management software Create a database using microsoft access

DATABASES

Disadvantages of manual database

  1. Data duplication
  2. Time wastage and boredom during searching
  3. Poor data entry  and organization may be misleading
  4. Poor update of records

Database concepts

A database is a collection of structured and related data items organized so as to provide consistent and controlled access to the items. To create organize and maintain a database, A Database Management System (DBMS) software is used.

Examples of database management software

  • Microsoft access (Ms access)
  • Ms. SQL server
  • Oracle
  • FoxPro
  • Dbase1v
  • Lotus Approach
  • MySQL

Functions of Database Management Software

  1. Allow the user to add or delete records
  2. Update or modify existing records
  3. Organize data for easy access, retrieval, and manipulation of records
  4. Act as an interface between a database and other application programs
  5. Ensure data security by safeguarding it against unauthorized access and damage
  6. Keep statistics of data items in a database

Database models

  1. Flat file
  2. Network
  3. Hierarchical
  4. Relational
  5. Object-relational and object-oriented- currently to be developed

Flat files

In this model, a database holds only one set if data resembling manual files e.g. library catalog cards

Name

Serah Seki

Admission number

649

Total marks

680

Number of subjects

10

Average

68

Position

4

Hierarchical model

Data are arranged in a hierarchy (tree) form. Level 1 items which are accessed first are called root components. It’s not commonly used.                                                                Root (main data item)

Network model

Links are used to expressing the relationship between different data items to form a network of items.it's rarely used.

Relational Database model

Related data items are stored together in structures called relations or tables. Relationships are created so that a table relates to another. It’s most commonly used.

Object-oriented models

The next generation database model will look at records as objects that are independent and how they relate with each other in a database.

Features of a database management software

  • Tables
  • Queries
  • Report generators
  • Form interface
  • Programming language (module)
  • Automating tools, macros, and modules

 

Tables

They are used to hold related records. They are organized in rows and columns. A row represents a record while a column represents a field.

Queries

It is a statement used to extract, change, analyze, or request specific data from one or more tables. The statements are written in Structured Query Language (SQL).

Forms/Input screens

A form is a graphical interface that resembles an ordinary paper form but enables the user to view and enter data into a table.

Reports:  A report generator allows the user to generate reports from tables of queries

Macros: They automate frequently performed tasks like opening frequently used form

Programming module

It automates more complex databases. The Ms access has an inbuilt visual basic language as a module e.g. Print Student Report until Number of Students = 40

Data organization in a database

Data is organized from the simplest form called a field to a complex structure called a database.

Fields

It is a character/ a logical combination of characters that represent a data item. e.g. a student’s name in a class list.

Records

It is a collection of related fields that represent a single entry. e.g. Student report card with students’ name, admission number, class, total marks, and average grade.

Tables

It is a collection of related records e.g. students’ file in a school database

Database

It is the highest data organization hierarchy holding all related files or tables eg the School database with students’ and staff’s files.

 

Creating a database using Microsoft Access (2007/2010)

Starting Microsoft access

  1. Click the Microsoft Office Button, and then click New.
  2. Click a  blank database, write the file name then click create

Exiting from access: Alt + F4 or click on Microsoft Office button then close database

Guidelines on designing a good database

  1. Study the user requirements carefully in order to define data inputs, outputs, and relationships
  2. Design a draft database to determine the number of files or tables required
  3. Normalize the database by dividing the information into separate fields, records, and tables to allow flexibility in manipulation and avoid repetition of data entries.
  4. Define a field for each table as a primary key that will identify each record uniquely.
  5. Give the most important fields the first priority when constructing a table structure i.e the ones used in querying and sorting.
  6. Design data-entry forms needed for the database

Creating a table structure

  1. Click the Microsoft Office Button, and then click New.
  2. In the File Name box, type a file name. To change the location, click the folder icon to browse.
  3. Click Create. The new database is opened, and a new table named Table1 is created and opened in Datasheet view

Description of field data types

The type of data must be defined for the sake of manipulation and storage.

  1. Text: It includes alphabetic letters, numbers, spaces, and punctuations. It is used in fields that don’t require calculations like name and places. The maximum number of characters it accommodates is 255.
  2. Number: They contain numeric numbers 0 – 9 for mathematical manipulation
  3. Memo: It is made of alphanumeric data. It can accommodate 32000 characters.
  4. Date/time: It identifies a field with date and time with mathematical manipulation
  5. Currency: It identifies numeric values with decimals or fractions. It is used when dealing with monetary values like balance, amount sold, etc
  6. AutoNumber: It is a numeric value used in-case of automating value increase in a field
  7. Yes/No: It is a logic field whose entry is yes or no, true or false like answering whether one is a male or female.
  8. OLE Object: OLE stands for Object Linking and Embedding. The field is used with graphic user interface applications for inserting graphical objects like pictures, drawings, and charts.

Field properties

  • Field size: This allows the user to set the number of characters instead of the default 50. For numeric fields, we use integer, long integer, byte, single and double. Integer and long integer accept numbers with no decimal, byte accept numbers between 0 to 255, single accepts up to 38 decimal places while double accommodates up to 308 decimal places.
  • Format: Determines the appearance of information on the screen when printed.eg format to scientific, currency, percentage or general
  • Decimal places: for number and currency fields that carry decimal numbers
  • Input mask: It automatically formats the field entry into specified format e.g. a number  02000100409874 with an input mask of  000-(00000)-000000 displays 020-(00100)-409874. It is used to format phone and address entries
  • Caption: A more descriptive name e.g. StuName described as student name
  • Default value: The value that appears automatically if nothing is entered e.g. = Date () displays the current date in a date field.
  • Validation rule: a logical expression that restricts the value to be entered in a field.e.g. >=0 And <,=100 restrict numbers to between 0 and 100
  • Validation text: The message that appears when a validation rule is violated. e.g. “Enter a number between 0 and 100”
  • Required: determines if an entry must be made in a field before proceeding to the next field.
  • Allow Zero Length: allow the user to proceed without any entry in a field
  • Indexed: Facilitates the organization of records for easy search. A primary key is an example of an index set to No duplicate

Primary key and indexes

An index is a key(s) used to speed up searching and sorting records in a table while a primary key is an index that uniquely identifies each record stored in the table. A primary key prevents the user from making null or double entries and sorts (indexes) a datasheet once it is set in a field.

Setting a primary key

  1. Click the Microsoft Office Button, and then click Open.
  2. In the Open dialog box, select and open the database.
  3. In the Navigation Pane, right-click the table in which you want to set the primary key and, on the shortcut menu, click Design View.
  4. Select the field or fields you want to use as the primary key.

To select one field, click the row selector for the field you want. To select more than one field, hold down CTRL and then click the row selector for each field.

  1. On the Design tab, in the Tools group, click Primary Key.

 

A key indicator is added to the left of the field or fields that you specify as the primary key.

To set another field as an index rather than the primary key

  1. Open the Table in design view
  2. Click the indexes button. The index design grid is displayed.
  3. Type the name of the index in the Index Name column and select the corresponding field name in the Field Name column
  4. In the Sort Order column, select Ascending or descending then close the window

Manipulating data in tables

Adding new records

  • An arrowhead indicates that no new data entry is pending
  • The pencil symbol indicates that the current record entry is not yet saved
  • Asterisk symbol 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

Deleting records

  1. You can highlight part or all of the data, or just position the cursor in the field.
  2. If you select all of the data in the field, press DELETE.       -or-

On the Home tab, in the Records group, click Delete

Editing fields

Double click the field you want to edit and replace the cell content

Searching for records

To find and replace a field

  1. In your table, select the field (column) that you want to search.
  2. On the Home tab, in the Find group, click Find
  3. Keyboard shortcut  Press CTRL+F.
  4. The Find and Replace dialog box appears.
  5. To find data, in the Find and Replace dialog box, click the Find tab. To run a find-and-replace operation, click the Replace tab.
  6. In the Find What box, type your search string. To replace data, enter a replacement string in the Replace With box. Note    Do not enter wildcard characters in the Replace With box unless you want your records to contain them.
  7. Note    If you want to search the entire table, use the Look In list to change the field that you want to search, or to search the entire table instead.
  8. NB You can use wildcard s e.g. asterisks if not sure like typing J *

Copying and moving records                                         

  1. Select the record to be copied
  2. On the home tab, clipboard group click copy or cut to move
  3. Open the target datasheet and click paste on the clipboard group

Sorting records

  1. In datasheet view select the record to be filtered
  2. On the home tab, sort and filter group, click ascending or descending

Modifying a database

Modifying a datasheet: Perform the following:

  • Adjust the column size,
  • Adjust row height
  • Reorder fields  -Select the column by pointing the desired file name, drag the column right or left and drop

Modifying table structures: Save the table with another name before you modify it by clicking save As

To modify the original table: Open the table in design view,  select the field(s) to be modified and make necessary changes then click the Save button.

NB: Access refuses to implement changes if the table contains data. Exit without saving and delete all the records then import the back-up in the design view.

Importing tables

  1. On the External Data tab, in the Import group, click Access
  2. Note    The External Data tab is available only when a database is open.
  3. In the File name text box, type the name of the source database or click Browse to display the File Open dialog box.
  4. Select Import tables, queries, forms, reports, macros, and modules into the current database and click OK.
  5. In the Import Objects dialog box, click each tab and select the objects you want.
  6. To cancel a selected object, click the object again.
  7. Click Options to specify additional settings. Click OK.

Form design

A form is an interface that enables the user to view and make data entries into an underlying table or query more easily. A form is designed using graphical objects called controls. A control is a visual object like a textbox, check box, command buttons that are placed on a form design grid. The controls are drugged to the required position.

Types of controls

  • Bound-the source of data is a field in a table or query
  • Unbound-It is not connected to any data source

Creating a form layout using a form wizard

  1. On the Create tab, in the Forms group, click More Forms, and then click Form Wizard
  2. Follow the directions on the pages of the Form Wizard.

Note    If you want to include fields from multiple tables and queries on your form, do not click Next or Finish after you select the fields from the first table or query on the first page of the Form Wizard. Instead, repeat the steps to select a table or query, and click any additional fields that you want to include on the form. Then click Next or Finish to continue.

  1. On the last page of the wizard, click Finish.

Create a form by using the Blank Form tool

  1. On the Create tab, in the Forms group, click Blank Form
  2. Access opens a blank form in Layout view , and displays the Field List pane.
  3. In the Field List pane, click the plus sign (+) next to the table or tables that contain the fields that you want to see on the form.
  4. To add a field to the form, double-click it or drag it onto the form. To add several fields at once, hold down CTRL and click several fields, and then drag them onto the form once
  5. Use the tools in the Controls group on the Formatting tab to add a logo, title, page numbers, or the date and time to the form. 
  6. If you want to add a wider variety of controls to the form, switch to Design view by right-clicking the form and then clicking Design View. You can then use the tools in the Controls group on the Design tab. 

Data manipulating in a form

Adding and displaying records

The form provides the user with a navigation button located at the bottom which displays the first, previous, next, last, and adds records

To format controls, drag the pointer to the required size. To remove the control, click and drag It

Querying a database

Queries are the fastest ways to search for information in a database. It enables the user to display specific records and perform calculations from one or multiple tables. Either a select query or an action query is used to analyze tables.                                                                

Select query

It is the most commonly used In searching and analyzing data in one or more tables. It lets the user specify the search criteria and display records that meet them in a dynaset

Action query

They are used to make changes to many records at once