HABESHA COLLEGE

DEPARTMENT OF WEB DEVELOPMENT AND DATABASE

ADMINISTRATION
OPERATE DATABASE APPLICATION

Prepared By:- Addisu A

 OBJECTIVES
 At the end of this chapter, trainees will be able to
 Understand fundamental concepts including database, table,
record, field, field name and primary key.
 Understand the use of simple data type.
 Create a table in Ms Access using Design View.
 Add records to a table using Datasheet view.
 Delete a record from a table.

2
 CHAPTER ONE
CREATE DATABASE OBJECTS
 What is Database ?
 Database is any collection of interrelated data

organization. The data may be in the form of text,

number or image.
 Database is made up of fields and records. And it

designed to organize and store large amount of

data.
 What is Microsoft Access ?

 Microsoft Access is a powerful DBMS that functions in the

windows environment and allows you to create and process

data in a database.
3
 Table: tables are the main units of data in a database. A table is
a collection of data about a specific a specific topic: it is made
up of one or more fields.
 Field: a field is a column in a table.

 Field Name: it is the name of the field and each field name

must unique.
 Record: a record is a row in a table and is a set of values

defined by fields. Each record contain the data for one person
or thing as specified by the intersecting fields.
 Data Type: data types are the properties of each field. A field

only one data type, such as Number, text or date.

 Primary Key: a primary key is a value that can be used to

identify a unique record in a table.

 Design View: it provides the tools for creating fields in a table.
4
 Datasheet View: it allows you to update and delete information

from a table.
STARTING MICROSOFT ACCESS
 Click on Start Button.
 Click on all program and the click on Microsoft office
 Click on Microsoft office power point.
New Blank Database
1. On the File tab select New.
2. Click on Blank database.
3. In the File Name box, type a name for your
database.
4. Click on the browse button to the right of the File
name box to browse for a location for your
5. database.
6. Click on Create.
7. A new database will be created with a new default
5
Table.
8. Click on Design View to start working with this Table.
CREATE TABLE OBJECT
1. Create the table object
2. Define the fields in the table including their name, data
type and description
3. Set the properties for each field
4. Create appropriate indexes
5. Set the primary key
6. Save the table

6
DATA TYPES
 Text: Used to store text information (Alphanumeric character) up to
255 bytes.
 Memo: Used to store large block of text up to 64,000 characters (About
20 pages) in length.
 Number: Used to store numeric data that can be used in mathematical
calculation.
 Date/Time: Used to store date and time.
 Currency: Used to store monetary values.
 Auto number: To store numeric values (sequential value) that MS-
Access automatically increment the value by 1 starting from 1.
 Yes/No: Used to recording logical Yes/NO, True/False, and On/Off
values.
 OLE object: Used to store Graphics, Drawing, Photographs, Signature
and other objects that were created in other application.
7
 Lookup Wizard - Used to create a field that allow you to choose a
value from a list of values using combo box (List box).
SETTING FIELD PROPERTIES
 Field properties are a set of characteristics that provide
additional control over how a field works. The available field
properties in Ms-Access are;
1. Field Size - Sets the maximum allowable number of
characters stored in a field. The default field size for Text data
type is 50.
2. Format - Affect how a field data is displayed. You can use
predefined format or set your own.
3. Input Mask - Display formatting characters in a field. You
can use predefined masks or customize your own.
E.g. "(205)"-00-00-00 (205)-41-12-26 "(115)"-000-000(115)-
111222 "MTVET\"000 = MTVET\001Mask Characters Their
8
usage C - Accept any character or space.
CONT…
4. Caption - Used to describe a field name on table datasheet view.
E.g. Fname = First Name Lname = Last Name
5. Default Value - a value that is automatically entered in a field
when records are created.
6. Validation Rule - An expression used by MS- Access to
determine whether or not an item entered in a field should be
stored as value.
7. Validation Text - A text that appears when invalid data is entered
in a field.
8. Required - Setting the forces a user to make an entry in a field. If
this field property is set to Yes MS-Access doesn't allow a null
value to be stored in this field.
9. Indexed When this field property is set to Yes (No Duplicates)
MS- Access creates and maintain an index of values entered in to 9
the field.
MODIFYING DATABASE OBJECT
 Adding and removing fields :You can modify the structure of your
table by adding and removing fields in design view. Note, however,
that using the cut, paste or delete functions will remove the field and
any data in that field.
 Deleting fields
1. Select the field or fields to be deleted using the row selector.
2. Press the Delete key on the keyboard, select Delete Rows from the
Design tab or right-click on the row selector and choose Delete
Rows.
 Inserting fields
1. Click in the field above which you wish to insert a new row. To
insert multiple rows, select the required number of rows using the
row selector.
10
2. Select Insert Rows from the Design tab or right click and choose
Insert Rows.
CREATE RELATIONSHIP
 A relationship in Access helps you combine data from two different tables.
Each relationship consists of fields in two tables with corresponding data.
 Setting The Primary Key

To set a primary key;

 Switch to table design view

 Select the field that you want to define as a primary key

 Click on Edit menu - Select Primary key command OR

 From standard toolbar click on Primary key button ().

 Removing The Primary Key - If the primary key field is used in

relationship, you must delete the relationship before removing the primary
key.
To remove the primary key;
 Switch to table design view

 Select the field that you want to delete it's primary key
11
 Click on primary key button
FOREIGN KEY
 A foreign key is a field (or fields) in one table that references the
primary key in another table.
 There are essentially three types of relationships:

1. One-to-one: - For every record in the primary table, there is

one and only one record in the foreign table.
2. One-to-many: - For every record in the primary table, there
are one or more related records in the foreign table.
3. Many-to-many: For every record in the primary table, there
are many related records in the foreign table, and for every
record in the foreign table, there are many related records in
the primary table.
 Referential integrity: Referential integrity is a fundamental

concept in relational database management systems (RDBMS)

that guarantees the accuracy and consistency of data across 12

tables by enforcing relationships between them.

CREATING RELATIONSHIP BETWEEN TABLES
1. In any database with at least two tables or queries,
select Database Tools > Relationships.
2. If the Relationships pane is blank, drag the tables from
the Navigation pane to the Relationships pane.
3. Drag the primary key from the parent table to the top of the
foreign key in the child table.
4. Select Enforce Referential Integrity.
5. Select Cascade Update Related Fields and Cascade Delete
Related Records
6. Select Create. You’ve added the relationship between the two
tables, so now you see a line that shows the relationship type
(one-to-one or one-to-many) and indicates which fields are
related. 13

7. To save the relationship, select Save .

14