Ethiopian TVET System

Training, Teaching and Learning Material

Ethiopian TVET-System

INFORMATION TECHNOLOGY SUPPORT SERVICE

Level II

LEARNING GUIDE # 1

Unit of Competence: Operate Database Application

Module Title: Operating Database Application
LG Code: ICT ITS2 LO1 01
TTLM Code: ICT ITS2 TTLM 0511

LO 1: Opening and designing DB application and principles

TTLM Development Manual Date: 05-2011 Page 1 of 14
3rd Revision Author: ADDIS ABABA TEGBARE-ID
Ethiopian TVET System
Training, Teaching and Learning Material
Introduction Learning Guide # 1

 This learning guide was developed to provide you the necessary information regarding the following
 Opening and designing DB application and principles
 Creating DB objects
 This guide will also assist you & you will be able to

 Open and design database application incorporating basic design principles

 Create database object according to database usage, as well as user requirements
 Modify database object as required
 Add and modify data in a table according to information requirements
 Add, modify and delete records as required & Save and compile database objects

Learning Activities
1. Read the specific objectives of this Learning Guide.

2. Read the information written in the “Information Sheets 1”.

3. Accomplish the “Self-check1”.

4. Submit your accomplished Self-check1. This will form part of your training portfolio/range.

5. Read the “Operation Sheet” and try to understand the procedures discussed.

6. Do the “LAP test” (if you are ready) and show your output to your teacher. Your teacher will evaluate
your output either satisfactory or unsatisfactory. If unsatisfactory, your teacher shall advice you on
additional work. But if satisfactory you can proceed to Learning Outcome 2.

TTLMInformation Sheet-1
Development Manual Opening and designing DB application and
Date: 05-2011 Page 2 of 14
rd
3 Revision Author: ADDIS ABABA TEGBARE-ID
Ethiopian TVET System
Training, Teaching and Learning Material

Database

 A database is a collection of information that is organized so that it can be easily accessed,

managed and updated.
 Data is organized into rows, columns and tables, and it is indexed to make it easier to find relevant
information. Data gets updated, expanded and deleted as new information is added. Databases
process workloads to create and update themselves, querying the data they contain and running
applications against it.
 A database can best be described as a way of storing large amounts of information.
 The data can be retrieved and we can even ask questions of the data and get answers.
 For example: You may want to know how many Students enrolled in every occupational
level.
 Ms Access (Ms Office Access 2007) is a database management tool that enables one to store
relevant data.
 This also has the capabilities to retrieve, sort, summarize report and result immediately and
effectively.
 It can combine data from various files (tables) through creating relationships and can make data
entry more efficient and accurate through the use of forms.
 Microsoft Access (MS Access) enables to manage all important information from a single
database file.
 Within the file, can use the different objects/items:

 Tables - A table is a collection of data about a specific topic, such as products or suppliers.
 Using a separate table for each topic means that you store that data only once.
 This results in a more efficient database and fewer data-entry errors.
 Queries – Queries used to view, change, and analyze data in different ways.
 You can also use them as a source of records for forms, reports, and data access pages.
 Forms - A form is a type of a database object that is primarily used to enter or display data in a
database.
 You can also use a form as a switchboard that opens other forms and reports in the
database, or as a custom dialog box that accepts user input and carries out an action
based on the input.
 Reports - A report is an effective way to present your data in a printed format.
TTLM Development Manual Date: 05-2011 Page 3 of 14
3rd Revision Author: ADDIS ABABA TEGBARE-ID
Ethiopian TVET System
Training, Teaching and Learning Material
 Because you have control over the size and appearance of everything on a report, you can
display the information the way you want to see it

 Modules - A module is essentially a collection of declarations/announcement, statements, and

procedures stored together as named unit to organize your Microsoft Visual Basic code.
 A module is a file that holds code or pieces of code in a Visual Basic application. Each form or
report of a database has a (separate) module.
 Microsoft Access has two types of modules: standard modules and class modules.
 Standard modules contain general procedures that aren't associated with any other
object, and frequently used procedures that can be run from anywhere within your
database.
 Class modules (Form and report modules) are modules that associated with a particular
form or report. Form and report modules often contain event procedures that run in
response to an event on the form or report.
 The major difference between a standard module and a class module that is not
associated with a particular object is one of scope and lifetime.
 Data access pages - A data access page is a special type of Web page designed for viewing and
working with data from the Internet or an intranet— data that is stored in a Microsoft Access
database or a Microsoft SQL Server database.
 The data access page may also include data from other sources, such as Microsoft Excel.

 In MS Access, data is stored once in one table, but can be viewed from multiple locations.
 When the data is updated in a Table, Query or Form, it is automatically updated everywhere it
appears.
Establishment of Ms Access Database
 All Ms Access databases files are saved with extension .mdb.
 A database should have a separate table for every major subject/topics, such as Students records,
Students grades, etc.
Production data or Treatment information
 Data should not be duplicated in multiple tables.
 Microsoft Access provides three methods to create a database
 Database Wizard (though easy, the wizard offers limited options to customize the database)
TTLM Development Manual Date: 05-2011 Page 4 of 14
3rd Revision Author: ADDIS ABABA TEGBARE-ID
Ethiopian TVET System
Training, Teaching and Learning Material
 Using a template (This method works best if one can find and use a template that closely
matches the specific requirements)
 Creating a database directly (This is the most flexible method, but it requires one to
define each database element separately).
Basic Database Concept

 In studying MS Access, it is necessary to understand some basic elements of a database before

proceeding to it.

Database Elements

 Data are raw facts. It tells the truth about something; a person, a place, an object, etc.
Example:

(1) “Noel” Name is a data. “Noel” is my name, so it tells something about a person. “Noel” is a
Name Noel

(2) “Male” Gender is Noel’s gender. So it tells something about “Noel”. Noel is Male.

Male

 Information is a collection of data (raw facts) which is contained in 1 file (table in Access)
Example:
IDNumber LName FName Mname Gender Bday Address
MOE-0001 Mekonen Abate Arega Male 11-05-1978 Addis
(this is an information about a person named “Noel”)
Example of a table (file):
IDNumber LName FName Mname Gender Bday Address
MOE-0001 Mekonen Abate Arega Male 11-05-1978 Addis
MOE-0002 Mengestu Abera Negera Male 11-05-1978 Addis
File
MOE-0003 Kalid Mohamed Abas Male 11-05-1978 Adama
MOE-0004 Bekele Abebe Teferan Male 11-05-1978 Gonder
MOE-0005 Niguse Reta Yemane Female 11-05-1978 Jijiga
…

TTLM Development Manual Date: 05-2011 Page 5 of 14

3rd Revision Author: ADDIS ABABA TEGBARE-ID
Ethiopian TVET System
Training, Teaching and Learning Material

Naming Convention
 Is manner/way of naming files and variables.
 Having a poor naming convention can only add confusion, so it's important that you start with a
good scheme, and think about what the scheme will mean to you.
 Here are the three things to consider in establishing a naming convention:

1. Does my convention make sense to me?

 Is must be simple and understandable to you.
2. Will my convention make sense to other people?
 Time will come that other people will look into your database program, so they must
able to understand it.
3. Can I be consistent in implementing and enforcing my convention?
 All throughout my database work, your naming convention must not change.

General

 Here are some basic simple rules when it comes to the name of any object in a database (including the
name of the database itself):
 Do not use spaces in object names. Example: “SalesByQuarter” or “SalesByQtr” or
“QtrlySales”
 Avoid using reserved words. Example: do not name your file as “Date” because “Date” is a
reserve word in Ms Access.
 Do not use dashes in database names. Example: “Quarterly-Sales” better name it as
QuarterlySales” or better use underscores instead of a dash“Quarterly_Sales”. (Dash “-“ was
often recognized by a computer as an operator (subtraction))
 Start object names with a letter. Example: "2005_Sales" and columns and columns named
"1", "2," "3," and so on (to represent months). Ms Access might treat it as a number and not as
a filename.
 Keep names short but meaningful. This is self-explanatory and fairly logical,"SlQ" is too
short. “SalesFiguresForCompanyByFiscalQuarter" is just silly.

TTLM Development Manual Date: 05-2011 Page 6 of 14

3rd Revision Author: ADDIS ABABA TEGBARE-ID
Ethiopian TVET System
Training, Teaching and Learning Material
 Use sensible case. Example: Avoid using names in ALL CAPS GetArticleStatsByWeek or
getArticleStats_ByWeek.

Self - Check 1 Opening and designing DB application and principles

Name: ______________________________________ Date: ________________________________________

Time started: _______________________________ Time finished: _______________________________

Directions: Answer all the questions listed below.

1. In Ms Access, a way of storing large amounts of information was called a? (1 points)

2. Is a database management tool that enables one to store relevant data? (1 Point)

3. List and the different Ms Access objects. (7 points)

4. Ms Access database file was saved with what extension name? (1 point)

5. What are the 3 methods to create a database in Ms Access? (3 points)

6. A collection of related data is called (1 point)

7. File in Ms Access is synonymous to what? (1 point)

8. What are the three things to consider in establishing a naming convention? (3 points)

TTLM Development Manual Date: 05-2011 Page 7 of 14

3rd Revision Author: ADDIS ABABA TEGBARE-ID
Ethiopian TVET System
Training, Teaching and Learning Material

Note: Satisfactory rating – above 15 points Satisfactory & below 14 points Unsatisfactory
(You can ask you teacher for the copy of the correct answer)

Self - Check 1 Opening and designing DB application and principles

(Answer Key)

1. Database

2. Ms Access (Ms Office Access 2007)

3. a. Tables
b. Queries
c. Forms
d. Reports
e. Modules
f. Macros
g. Page

4. .mdb
5. a. Database Wizard
b. Using a template
c. Creating a database directly

6. Information

7. Table

8. a. Does my convention make sense to me?

b. Will my convention make sense to other people?
TTLM Development Manual Date: 05-2011 Page 8 of 14
3rd Revision Author: ADDIS ABABA TEGBARE-ID
 Ethiopian TVET System
Training, Teaching and Learning Material
c. Can I be consistent in implementing and enforcing my convention?

Operation Sheet Opening and designing DB application and principles

Create a new database

Get to know the Getting Started with Microsoft Office Access page
When you first start Access, or if you close a database without closing Access, the Getting Started with
Microsoft Office Access page is displayed.

This page is a starting point from which you can create a new database, open an existing database, or
view featured content from Microsoft Office Online.
Create a database by using a template
Access provides you with a wide variety of templates that you can use to speed up the database creation
process. A template is a ready-to-use database containing all the tables, queries, forms, and reports
needed to perform a specific task. For example, there are templates that you can use to track issues,
manage contacts, or keep a record of expenses. Some templates contain a few sample records to help
demonstrate their use. Template databases can be used as they are, or you can customize them to better
fit your needs.
If one of these templates fits your needs, using it is usually the fastest way to get a database started.
However, if you have data in another program that you want to import into Access, you might decide it is
better to create a database without using a template. Templates have a data structure already defined,
and it might require a lot of work to adapt your existing data to the template's structure.
1. If you have a database open, click the Microsoft Office Button and then click Close Database to
display the Getting Started with Microsoft Office Access page.

TTLM Development Manual Date: 05-2011 Page 9 of 14

3rd Revision Author: ADDIS ABABA TEGBARE-ID
 Ethiopian TVET System
Training, Teaching and Learning Material
2. Several featured templates are displayed in the middle of the Getting Started with Microsoft Office
Access page, and more become available when you click the links in the Template Categories pane. You
can download additional templates from the Office Online Web site. See the next section in this article for
details.
3. Click the template that you want to use.
4. Access suggests a file name for your database in the File Name box — you can change the file name, if you
want. To save the database in a different folder from the one displayed below the file name box, click ,
browse to the folder in which you want to save it, and then click OK. Optionally, you can create and link your
database to a Windows SharePoint Services 3.0 site.
5. Click Create (or Download, for an Office Online template).
Access creates or downloads the database and then opens it. A form is displayed in which you can begin
entering data. If your template contains sample data, you can delete each record by clicking the record
selector (the shaded box or bar just to the left of the record), and then doing the following:
On the Home tab, in the Records group, click Delete.
6. To begin entering data, click in the first empty cell on the form and begin typing. Use the Navigation Pane to
browse for other forms or reports that you might want to use.
Download a template from Office Online
If you can't find a template that fits your needs on the Getting Started with Microsoft Office
Access page, and you are connected to the Internet, you can explore the Office Online Web site for a
larger selection.
1. On the Getting Started with Microsoft Office Access page, under More on Office Online, click Templates.
The Templates home page on Office Online is displayed in your browser window.
2. Use the Office Online navigation and search tools to find the Access template that you would like to use, and
follow the instructions to download it. When you download a template, a database file is downloaded to
your computer and opened in a new instance of Access. In most cases, the template is designed to open a
data entry form so that you can begin entering data immediately.
Create a database without using a template
If you are not interested in using a template, you can create a database by building your own tables,
forms, reports, and other database objects. In most cases, this usually involves one or both of the
following:
 Entering, pasting, or importing data into the table that is created when you create a new database, and then
repeating the process with new tables that you create by using the Table command on the Create tab.
 Importing data from other sources and creating new tables in the process.
To learn more about how to plan and design a database, or about creating relationships, forms, reports,
or queries, follow the links in the See Also section of this article.
Create a blank database
1. On the Getting Started with Microsoft Office Access page, under New Blank Database, click Blank
Database.

TTLM Development Manual Date: 05-2011 Page 10 of 14

3rd Revision Author: ADDIS ABABA TEGBARE-ID
 Ethiopian TVET System
Training, Teaching and Learning Material

2. In the Blank Database pane, type a file name in the File Name box. If you do not supply a file name
extension, Access adds it for you. To change the location of the file from the default, click Browse for a
location to put your database (next to the File Name box), browse to the new location, and then
click OK.
3. Click Create.
Access creates the database with an empty table named Table1, and then opens Table1 in Datasheet view.
The cursor is placed in the first empty cell in the Add New Field column.
4. Begin typing to add data, or you can paste data from another source, as described in the section Copy data
from another source into an Access table.
Entering information in Datasheet view is designed to be very similar to working in a Microsoft Office
Excel 2007 worksheet. The table structure is created while you enter data — any time you add a new
column to the table, a new field is defined. Access automatically sets each field's data type, based on the
data you enter.
If you do not want to enter information in Table1 at this time, click Close . If you made any changes to
the table, Access prompts you to save changes to the table. Click Yes to save your changes, click No to
discard them, or click Cancel to leave the table open.
IMPORTANT If you close Table1 without saving it at least once, Access deletes the entire table, even if
you have entered data in it.
Open an existing Access database
1. Click the Microsoft Office Button , and then click Open.
2. In the Open dialog box, browse to the database that you want to open.
3. Do one of the following:
Double-click the database to open it in the default mode specified in the Access Options dialog box or the mode
that was set by an administrative policy.
Click Open to open the database for shared access in a multi-user environment so that you and other users can
read and write to the database.
Click the arrow next to the Open button and then click Open Read-Only to open the database for read-only
access so that you can view but not edit it. Other users can still read and write to the database.
Click the arrow next to the Open button and then click Open Exclusive to open the database with exclusive
access. When you have a database open with exclusive access, anyone else who tries to open the database
receives a "file already in use" message.
Click the arrow next to the Open button and then click Open Exclusive Read-Only to open the database for
read-only access. Other users can still open the database, but they are limited to read-only mode.
If you can't find the database that you want to open
1. In the Open dialog box, click My Computer or, click My Computer in the Look in drop-down list.
2. In the list of drives, right-click the drive that you think might contain the database, and then click Search.
TTLM Development Manual Date: 05-2011 Page 11 of 14
3rd Revision Author: ADDIS ABABA TEGBARE-ID
 Ethiopian TVET System
Training, Teaching and Learning Material
3. Type your search criteria in the Search Results dialog box, and then click Search to search for the database.
4. If the database is found, double-click it to open it.
5. You must click Cancel in the Open dialog box for the database to open. Then, close the Search
Results dialog box.
NOTE You can directly open a data file in an external file format, such as dBASE, Paradox, Microsoft
Exchange, or Excel. You can also directly open any ODBC data source, such as Microsoft SQL Server or
Microsoft FoxPro. Access automatically creates a new Access database in the same folder as the data file,
and adds links to each table in the external database.
 To open one of the most recently opened databases, click the file name for that database in the Open Recent
Database list on the Getting Started with Microsoft Office Access page. Access opens the database with
the same option settings that it had the last time you opened it. If the list of recently used files is not
displayed, click the Microsoft Office Button and then click Access Options. In the Access
Options dialog box, click Advanced. Under Display, enter the number of documents to display in the Recent
Documents list, up to a maximum of nine.
 If you are opening a database by clicking the Microsoft Office Button and then using
the Open command, you can view a list of shortcuts to databases that you have previously opened by
clicking My Recent Documents in the Open dialog box.
To learn more about opening existing Access databases, see the links in the See Also section of this
article.
Create a custom blank template
When you create a new blank database, Access opens a new table in which you can enter data, but it
creates no other objects in the database. If you want other objects, such as forms, reports, macros, or
additional tables, present in all new databases you create, you can create a custom blank template that
contains those objects. Then, the next time you create a new database, it will already contain those
objects in your template. In addition to these objects, the template can include tables prepopulated with
data, as well as any special configuration settings, database properties, references, or code that you want
to have in all new databases.
For example, suppose you have a collection of macros that you like to use in all your projects. If you
create a blank template containing those macros, Access includes them in any new databases you create.
You can create blank templates in the Office Access 2007 file format, the Access 2002-2003 file format, or
the Access 2000 file format. The template must be named [Link] for the Office Access 2007 file
format, and [Link] for the earlier file formats.
 If the default file format is set to Access 2000 or Access 2002 - 2003, Access uses [Link] as the blank
template file name. The new database is created in the same file format as [Link]. For example, even if
your default file format is Access 2000, if the template named [Link] is in Access 2002-2003 file format,
any new databases you create will be in Access 2002-2003 format.
 If your default file format is set to Access 2007, Access uses [Link] as the file name for the blank
template.
o create a blank template, do one of the following:
 Create a new database (you can name it Blank or give it a temporary name), and then import or create the
objects you want to include in the template.
 Make a copy of an existing database that already contains the objects you want in the template, and then
delete any objects you don't want.
TTLM Development Manual Date: 05-2011 Page 12 of 14
3rd Revision Author: ADDIS ABABA TEGBARE-ID
 Ethiopian TVET System
Training, Teaching and Learning Material
After you have the objects you want in the template, you must save it to a specific location.
1. Click the Microsoft Office Button , and then point to Save As.
2. Under Save the database in another format, click the file format you want for the template.
3. In the Save As dialog box, browse to one of these two template folders:
System template folder For example, C:\Program Files\Microsoft Office\Templates\1033\Access
User template folder For example:
 In Windows Vista c:\Users\user name\AppData\Roaming\Microsoft\Templates
 In Microsoft Windows Server 2003 or Microsoft Windows XP C:\Documents and Settings\user
name\Application Data\Microsoft\Templates
NOTE A blank template in the System template folder overrides blank templates in any user template
folders.
4. In the File name box, type [Link] (or [Link], if you are creating an earlier-version template),
and then click Save.
Now that the new template is in place, when you create a new blank database, the objects in the template
are included in any new database you create by default. Access opens a new table in Datasheet view, as it
does when you create new blank databases without using a template.
To stop using the blank template, delete or rename the file named [Link] (or [Link], for earlier
versions of Access).

LAP Test Opening and designing DB application and

principles

Name: ______________________________________ Date: ________________________________________

Time started: _______________________________ Time finished: _______________________________

1. Show all the necessary steps & ways to open new MS Access 2007 Database. (5 Points)

2. Show all the necessary steps to open Existing MS Access 2007 Database. (5 Points)

TTLM Development Manual Date: 05-2011 Page 13 of 14

3rd Revision Author: ADDIS ABABA TEGBARE-ID
Ethiopian TVET System
Training, Teaching and Learning Material

TTLM Development Manual Date: 05-2011 Page 14 of 14

3rd Revision Author: ADDIS ABABA TEGBARE-ID