Unit of Competency: Operate Database Application

Project 1: Operate Database Application

Make sure to be clear in all Projects (do it in your Network
Member)
Instruction: under this project you are expected to operate a Database
Application by performing the following 7 tasks based on the information
provided
Task 1: Create a database
 Create a new blank database called ‘Supermarket’
Task 2: Create the following tables
 Create three tables named ‘Supplier’, ‘Order’ and ‘Customer’ based on
the following information.
Table 1: Supplier
Field Data Type
SupplierID Text
SupplierName Text
City Text
SubCity Text
PhoneNumber Text
Table 2: Order
Field Data Type
OrderID Text
OrderItem Text
OrderDate DateTime
SupplierID Text
CustomerID Text
Table 3: Customer
Field Data Type
CustomerID Text
CustomerName Text
PhoneNumber Text
City Text
SubCity Text
Task 3: Create relationship
 Create relationship for the above three tables and save it by the name
DB_relationship
Task 4: Create a data entry form
Task 5: Enter the following sample data in the tables using the data
entry form in Tas
Supplier
SupplierID SupplierNa City SubCity PhoneNumb
me er
S01 ALTA A.A Arada +251-1-
121212
S02 DELL A.A Gullele +251-2-
131313
S03 SNAP A.A Bole +251-5-
151515
S04 HARON A.A Kirkos +251-4-
161718
Customer
Prepared by: G/selasssie.H-ICT Department January 2020 Page 1
Unit of Competency: Operate Database Application
CustomerID CustomerNa PhoneNumb City SubCity
me er
C01 Yonatan 911 111213 A.A Arada
C02 Ahmed 912 131415 A.A Gullele
C03 Meron 913 161718 A.A Bole
C04 Halima 914 192021 A.A Kirkos

Order
OrderI OrderItem OrderDate SupplierID CustomerID
D
O11 Dell OPTIPLEX 12/2/2015 S01 C01
780
O12 Toshiba Laptop 12/2/2015 S02 C01
O13 Hp LaserJet 20/5/2015 S03 C03
printer
O14 HP Scan Jet 20/5/2015 S03 C04
Task 6: Create Query
 Create a Query that should accept Customer name as a parameter and
then display ‘CustomerName’, ‘SupplierName’ and ‘OrderItem’. Save it
by the name ‘CustomerSupplierOrder’
Task 7: Create a report
 Create a report based on the above query
Project 2: Operating a Database
Instruction: under this project you are supposed and assigned to create new
database for Bridge Hotel. Database Name Bridge having the following four tables
(Booking, Credit Card, Customer, and Room).
Task 1:Create four tables structure under bridge database
Instruction: Under this task you are expected to create tablesbyperforming the
following activities based on the information provided.
Booking (book ID, Arrival Date, checkout ,room, customer ID, satellite TV
required (night))
Credit Card (card no, Expire date, card holder Name)
Customer (customer ID, First Name, Town, Telephone)
Room (Room No, Room Type, price per night, satellite TV required (night)))
Task 2: Create Form for the Above Four Table and insert three data’s for
each
Instruction: Under this task you should create the form and insert data to the
table through the form.
Task 3: Create Relationship between four tables
Instruction: Under this task you are expected to create the appropriate
relationship among the tables.

Prepared by: G/selasssie.H-ICT Department January 2020 Page 2

Unit of Competency: Operate Database Application

Task 4: Create Query that display Room No, Customer ID, Arrival
Date and Checkout
Instruction: Under this task you are expected to create the query as stated
on the task.
Task 5: Create the Report for the Query you design on Task 4.
Instruction: Under this task you should have to create report from the
query created on task 4.
Project 3: Operating a Database
1. Create a database by the name” book store”
2. Under book store create two tables
 Authour
(Author_id,author_name,sex,nationality)
 Book
(book_id, book_title, publisher_name, book_price and
Author_id)
3. Create relationship and save it by the name DBL2_B
4. Validation rule for author_id(‘A-01,A-02,A-03,A-04’)
5. Book titleshould not be null or blank (book title is required)
6. Validation rule for sex (m or f) using drop down list.
7. Crate a form for the above two table and create both tables in one
form

Book
book_i book_title Publisher_na book_price Author_id
d me
10002 Computer MICROSOFT $450.00 Author-001
introduction
10022 Database MICROSOFT $400.00 Author-002
Administration
10044 Computer JONE $800.00 Author-001
Prepared by: G/selasssie.H-ICT Department January 2020 Page 3
Unit of Competency: Operate Database Application
Network
10023 Internet JONE $2000.00 Author-002

8. Create a query that displays both book and Author table.

9. Create a report that display all publisher and all books (Be for you do
this first do the query)
10. Create Query that can accept Publisher name and displays all
information of publishers and save it as Paramter_Query
11. Export the Book table in to Excel and MS-word.
12. Import the book table in to Ms-access by the name book1 from
the excel.
Project-4: Operating a Database
1. Create database by the name Human resource
2. Create the following table structure under human resource database
 Department table
Field name Data type Description
Dep-code Department code
Dep-name Department name
3. Determine data type of the above table
 Employee table
Field name Data type Description
Emp-ID Employment id
Emp-name Employment name
Dep-code Department code
Sex Sex
Birth Date Birth date
Salary Salary
4. Determine data type of the above table
5. Assign proper primary key for each table so that you will not have
duplication record.
6. Create a validation rule and validation text for the following fields
Fields Validation rules Validation Text
Dep-code To accept only Please type the correct value of
D-001, D-002, D-003, D-004 Dep-code
Sex To accept only F or M Please type the correct value of Sex
7. Create A Combo Box for bothDep-name

 Dep-name= Administration, Finance, Human Resource and Information

Technology
8. Define the possible relationship between two tables
9. Enter the following data for the department and Employee
 Department table
Dep-code Dep-name
D-001 Administration
D-002 Finance
D-003 Human Resource
D-004 Information Technology
 Employee table
Emp-ID Emp-name Dep-code Sex Birth Salary

Prepared by: G/selasssie.H-ICT Department January 2020 Page 4

Unit of Competency: Operate Database Application
Date
Emp-001 Tesfayehaylu D-001 M 01/01/83 $1,450.
00
Emp-002 Senaytefikad D-002 F 01/05/73 $4,536.
u 00
Emp-003 Sara hagos D-003 F 19/01/96 $8,963.
00
Emp-004 Girma belay D-004 M 12/08/89 $2,589.
00
10. Create a form named as Employeefor the Employeetable
 Add Navigation Buttons (First Record, Last Record, Previous
record, Next Record)
 Add Record Operation Buttons (Add New record, Save Record,
Delete Record)
11. Enter the following Records for the Employee table using the
form
Emp-ID Emp-name Dep-code Sex Birth Salary
Date
Emp-005 Haderahagos D-004 M 16/02/87 $12,500.
00
Emp-006 Kibromfilmon D-004 M 06/06/90 $12,500.
00
Emp-100 Gere negus D-001 M 25/07/80 $10,500.
00
Emp-012 Maronhadush D-002 F 17/11/79 $6,000.0
0
12. Create and save query by the name query 1 that contain all fields from
both tables.
13. Create and save query by the name updatesalary that contain
update Employee salary 50 %(under salary update to: [salary]*1.5)
14. Create a query that displays list of Employment name
“Information Technology” Department only.
15. Create report and save by the name report 1 from above query
16. Create a report for Employee table grouping by their
Department.

Project 5: Operate Database Application

General Instruction: Read the following project work and perform
accordingly.
A Garment Company has 100 employees. The company would like to have a
report that contains the Name, Birthday, Salary and Gender of the employee
based on their education level (MA, BSC, Diploma and Certificate). Assume
you are working in that company’s IT Department and expected to design
and develop a database according to the requirement. The database
expected to have the following features
 Employee list entry form

Prepared by: G/selasssie.H-ICT Department January 2020 Page 5

Unit of Competency: Operate Database Application
 Query on employee with their educational level
 Report of employees categorized by their education
The candidate is expected to design and develop a database which has all
the features and functions stated above using Microsoft Office Access 2007.
1. Set primary Key for each table
2. Determine a proper data type for each field
3. Set proper input mask for EmpIDautomatically to start by EMP- and
followed by three digits
4. Set a validation rule for Education to Accept only MA, BSC, Diploma
and Certificate in Education Level Table
5. Set a default value for Gender to M
6. Create a r/ship b/n each tables and save it by the name My _Relation.
The following the database objects expected to be developed in the project
Table 1 Employee List
EmpID Name Birth Gend Salary Education Level
Date er
EMP-001 BereketAyele 6-7-1969 M 2900 MA
EMP-002 HenokTamirat 8-8-1970 M 2500 BSC
EMP-003 MelatChufe 9-6-1980 F 500 BSC
EMP-041 AbuyeMenges 7-3-1980 M 550 Diploma
ha
EMP-005 YadessaChala 2-5-1980 M 1228 Certificate
Table 2 Education Level
Id Education
Level
1 MAPK
2 BSC
3 Diploma
4 Certificate
7. Calculate the age of each employees
8. Create a parameter query that accept employee’s name and display
their educational label.
9. Create a query that add 10% on employee’s salary and save by
Bonus.
10. Make a copy of employee table by the name copy employee and
save the query by copy.
11. Delete employees whose educational level is Certificate and save by
Delete.
12. Create data entry form for employee table
13. Create a report that provides information of employees with their
educational level.

Prepared by: G/selasssie.H-ICT Department January 2020 Page 6

Unit of Competency: Operate Database Application

Techno star college

Operate Database Application Exercise

 Create a database called Abnet

 Under sample database create the following tables using design view

Student table

Field Name Data Type Field Other information/validation rule

size
Id Text 8 Id field must accept value TSC_R or TSC_E
followed by three digit number
Name Text 20 This field must accept values in capital letter
Sex Text 6 The value of this field must be male or female
Age Number Age must be b/n 18 and 65
Registration Date and
date time
Occupation Text Values (it support, hardware and networking,
database, web and multimedia)
Level Text values (one, two, three, four)
Program Text 10 values(regular, extension, weekend)
Cid Text 10

Course table

Field name Data Type Field Validation rule

size
Cid Text 10 The field must accept values that starts
with(DBA,HNS,WMT )
Cname Text 10
Ccredit Number 2

Prepared by: G/selasssie.H-ICT Department January 2020 Page 7

Unit of Competency: Operate Database Application
Tid Text 4

Teacher table

Field name Datatype Field size Other information/validation rule

Tid Text 4
Name Text 20 Require
Sex Text 6 This field must accept Values (female or
male)
Age Number Age must be b/n 20 and 60
Salary Currency Required field
Empdate Date and time Required field
Profession Text Require field
COC certified Yes/No Required field

• What are the primary key and foreign key in each table?

• Create the relationship b/n the three tables

• Design a form for each table and each form must have navigation buttons (first record, next
record, last record and previous record), additional functionalities (add new record,
delete record, print record and close form) and control structures (combo box, list box,
radio button, option group and check box) for multi valued fields.

• Enter the following values to each table using the form created above

Student table
ID Name Sex Age Regdate Occupation Level Program CID
TSC_R001 Helen Female 18 08/08/08 Database One Regular DBA001
TSC_R002 Berhe Male 17 08/08/08 Hardware Two Regular HNS001
TSC_E001 Hawa Female 20 08/08/08 Database One Extension WMT001
TSC_E002 Fana Female 21 08/08/08 Web Three Extension DBA002
TSC_E003 Belay Male 21 08/08/08 IT support Two Extension HNS002
TSC_E004 Hafte Male 22 09/08/08 Web One Extension HNS003
TSC_R003 Lula Female 25 09/08/08 IT support Three Regular WMT002
TSC_R004 Selam Female 30 09/08/08 Database Four Regular DBA003

Course table
CID CNAME CCREDIT TID
WMT001 Web site development 3 001
WMT002 Internet programming 4 002
NHS001 Server configuration 2 001
HNS002 Resolve network problems 3 003
HNS003 Network security 4 004
DBA001 Fundamental of database 3 002
DBA002 SQL statements 2 003

Prepared by: G/selasssie.H-ICT Department January 2020 Page 8

Unit of Competency: Operate Database Application
DBA003 Data conversion 4 004
DBA004 Operate data base application 4 005
Teacher table
TID NAME SEX AGE SALARY EMPDATE PROFESION COCPASSED
001 Hailay Male 32 $6000 12/02/2008 Web Yes
002 Haile Male 30 $6000 12/02/2008 Data base Yes
003 Welu Male 28 $6000 01/01/2007 Hardware Yes
004 Bsrat Male 28 $6000 01/04/2008 Hardware Yes
005 Girmay Male 25 $6000 09/08/2008 Database No

• Create a single form that contains all the fields in course and student table and insert the
values in to course table and student table respectively.

WMT003 Php programming 3 001

And

TSC_R005 Belay Female 30 09/08/08 Data base Four Regular DBA003

1. Create a Query that displays all extension students whose occupation is data base.

2. Create a delete Query that deletes a name called Helen from student table.

3. Create an update query that updates sex of students from female to male.

4. Create a query that displays the following field names with their values (teacher name,
student name and course name)

5. Create a parametric query that accepts student name and displays each student’s
information.

6. Create a query that displays list of students where their name starts with a letter “H”.

7. Create a report that displays all students’ information in ascending order of their name
grouped by occupation (include also sum, average, minimum and maximum age of
students in each occupation).

8. Create a report that displays the list of data base students grouped by sex.

9. Create a report that displays teacher name, profession, student name, occupation and
course name.

10. Create a switch board that contains all the forms created above and make it a startup form.

11. Create a query that displays total number of students in each occupation?

Prepared by: G/selasssie.H-ICT Department January 2020 Page 9

Unit of Competency: Operate Database Application

Prepared by: G/selasssie.H-ICT Department January 2020 Page 10