WELCOME TO THIRD TERM

TOPIC:
DATABASE MANAGEMENT SYSTEM (Access / MySQL)

WEEK1 YEAR 11

Prepare by CAMILLUS PATRICK

 SCHEME OF WORK
WEEK TOPIC CORE /EXTENDED
CONTENT
1 Databases (access)

2 Databases II (Mysql)

3 Boolean logic

4 Boolean logic II

5 File handling

6 Presentation Packages

7 Spreadsheet

8 Spreadsheet II

9 Revision

10 Examination

11
 Objective
In this lesson, you will:
Describe a database
Define database key terms (table, record, field, primary key, foreign key)
Describe a flat file database
Describe a relational database
Define a single-table database from given data storage requirements
• Including:
– fields
– records
– validation
Suggest suitable basic data types
Understand the purpose of a primary key and identify a suitable primary key for a given
database table
 Introduction

When large amounts of data need to be

collected and organized it is often stored in a
database.
A database is a structured set of data. It is
structured in tables that are easy to search and
update.
A flat file database is a database that contains
a single table.
MICROSOFT ACCESS  
Microsoft access is a package that can construct, expand, maintain and manage your database.
OR
Microsoft access is a database tool designed to store large amount of data, extract information from an existing database.
 
TERMINOLOGIES:
A database management: is a collection of one or more tables.
A table: is a building block of a database. It arranged in column and row.
Column: is horizontal group of cell that identifies the content of other field while Rows: is a vertical group of cell that
identifies the record.

A table contains the following:

File Name
Field
Data types
Character
Description

 
File Name: is used to identify the name of a table. It can be alphabet or number.

 
A Field:

A field is the horizontal record in a table. It is a label that describes the record in the table.
 Data Types:
Data type are use to describe the types of data that are to enter in the field
 
Types of data type:
- Auto-number
- Text
- Number
- Memo
- Date/time
- Currency
- Yes/No
- OLE object (Object Linking and Embedding)
- Attachment etc.
 

 Character:
Character is the smallest unit of data in a table is either an alphabet or numbers that enter to
the cell.
 
 Description:
This is used to identify the particular location of something in a table.
 
TYPE OF DATABASE MANAGEMENT SYSTEM (DBMS)
(1)MS Access
(2)Fox base
(3)Fox pro
(4)Database version ii, iii,& iv
(5)Oracle
(6)MySQL
 
 USES OF MS ACCESS
(1)It’s used for personal information
(2)It used for keeping customer record in the bank
(3)It used for pay roll of a company.
(4)It help to store information for reference, reporting and analysis
(5)It also helps to analyze large amount of information and manage related data more efficiently than MS
access.
 
FILE CONCEPT IN MS ACCESS
(1)Design view
(2)Datasheet view
(3)Relationship
(4)Query
(5)Form
(6)Report
DESIGN VIEW:
This is used to enter data first in table view and also used to editing data field in the
table
 

 
DATASHEET VIEW:
Is used to view or transfer all the field record in design view into the table view.
 
 
 

QUERY:
Query is used to accessed and co-ordinate data and display in a table.
It can access a single table or multiple tables’ e.g. to access customer who live in Nigeria
or U. K.

DESIGN QUERY:
Query Design has two major sections to display;
(1) It displays the table that is used for the query with the available fields.
(2) It displays those fields that have been selected for used in the query output.
FORM:
A form is used to specify the fields from the customer & account table to make
simple calculation and display in the table output.
 

REPORT:
The report is similar to query, it display data from one or more tables and display
the record. The report is printed out on paper.

HOW TO CREATE A NEW DATABASE:

Design view table appear with file name, data type, description. As shown below:
After filling the Field, click on save icon
Creating Relationship:
Creating relationship means to link two or
more table together to become one table
Steps:
Click on Database tool on the tab
Click on relationship
Then show table dialog box appear
Click on customer table and click Add
Click on Account table and click Add
Click close
Click on Customer ID field and drag it
over on top of the customer ID field on the
Account table
Click create
Click on save icon and click close.
Creating Query :( Using query wizard)
Steps:
Click on Create tab
Click on Query Wizard
The query dialog box appear
Click OK
Click on Customer table and pack all the field from available field to
the selected field by clicking this sign: (>>)
Click on Account table and click on account name pack them one
after the other by clicking this sign: (>)

 Click Next button

 Another box appear, click Next button
 Give name of the query and click Next
 Click Finish for the information to appear
in the query output.
 Creating Query Design:
To “Creating the Customer and Account Query” follow the steps:
Click on Create tab, and click Query Design
A dialog box appear

 Click on Customer table and click Add

 Click on Account table and click Add
On the Design query box that shows below:
 you are to select the Field on either
Customer or Account table
 Sort the table and filter the record on Criteria. E.g. filter the record to display
those customer that lives in Nigeria and those customer that are on Saving
account
 Under the Criteria where you want to filter out, type this: ‘Saving’ in that
criteria
 Then click on Run to display those customers that are on saving account.
 FORM
Creating A Form :( using form wizard)
Steps:
Click on create tab and click on “More Form”
Click Form Wizard and click OK
Move the fields from the available fields over to the selected side
Click on Next button
Choose Layout on either:
i.Column – Place the labels to the left field and view one record at a time
ii.Tabular – Place the field labels at the top screen while the record display below and this display all the
record at the same time
iii.Datasheet – this display data labels as the table at the same time
iv.Justify – it display a single record at a time
After choosing one of the layout above:
Select the style to use in the table and click Next button
Give a Name, and click on Finish button to display the records.
How to format the record in (Form and Report)
-After creating form or report, click on View and click on ‘Layout View’
-The environment will change, then click on the field and make an adjustment
-Click on View again and click on Form View to view your record.
 
To Adjust the Height or the Weight of the cells in (Form or Report)
-After creating your form, click ‘Design View’
-Click on ‘Property Sheet’@ the right hand side of the standard bar
-Select all the items and move the cursor to height box, and type-in the number and press enter in your
keyboard to increase your height and do the same to the weight.
-Click on View again and click on Form View
 
 
How to apply chart in Access:
After you create your form or report:
-Left click on view and click on Design View
-Click on chart icon in the bar and drag the mouse to draw it on the page
-In the chart wizard box that appear, select the table you want to use
-Click Next button
-Select the name field from the available side to field for chart side and click Next button
-Pick your chart type & click Next, Next again & give the name for your chart, then click Finish.
Note: After you click finish, you have to go back and click on View, & click Form View to view your chart.
 REPORT
Create A Report :( using report wizard)
Steps:
Click on Create and click report wizard
New Report Wizard box appear
Select Customer table and click “OK”
Select the fields from the customer table that will display on the report and click Next
button
Another box appear, click Next
Specify the layout of the report, and click Next
Select the style of the report and click Next
Give report a Name: (Customer Report)
Click Finish button to create your report and click save icon to save your report
 

To Edit or Format a Report:

Click on View
Click on “Layout View” and make your necessary formatting and click on Report View to
display it out.
 Class activity 1
 
Create Customer Table with the following information below:
Customer ID - Number
Surname - text
Other Name - text
Gender - text
Marital Status - text
Date of Birth - date & time
State of Origin - text
Local Govt Area - text
Nationality - text
Contact Address - text
Telephone No - Memo
E-mail - text
 
Create Account Table with following information below:
Customer ID - Number
Account Name - text
Account Number - Number
Date Opened - Date & time
Branch - text
Account Balance - Currency
Picture - Attachment
 
Create the following below:
1.Relationship.
2.Query Wizard.
3.Query Design.
4.Form (using form wizard.
5.Report (using report wizard.
 
Class activity 2
Create the first table: Name “Staff Record” with the following field
Staff ID - Number
Surname - text
Other Name - text
Date of Birth - date & time
Gender - text
Marital Status - text
State of Origin - text
Nationality - text
Religion - text
Date Employed - date & time
Contact Address - text
Telephone No - Memo
E-mail - text

Create the second table: Name “Next of kin Details” with the following information:
Person ID - Number
Next of kin Name - text
Gender - text
Marital Status - text
Date of Birth - date & time
Age - Number
Person - Brother/Sister
Contact Address - text
Telephone No - Memo
E-mail - text
Picture - attachment
Create the third table: “Account Table” with the following field:
Customer ID - Number
Account Name - text
Account Number - Number
Account type - text
Date Opened - Date & time
Branch - text
Balance - Currency

Create the following below:

1.Relationship.
2.Query Wizard.
3.Query Design.
4.Form.
5.Report.
 Assignment
(To make calculation on Access)
Create the first table Name: “Worker Details” with the following field:
Workers ID - Auto Number
Surname - text
Other Name - text
Gender - text
Date of Birth - date & time
State of Origin - text
Local Govt Area - text
Marital Status - text
Nationality - text
Contact Address - text
Phone Number - Memo
E-mail Address - text
Picture - Attachment

Create the second table: “Workers Allowance” with the following field:
Workers ID
Basic Salary - Currency
Transport Allow - Currency
House Allow - Currency
Medical Allow - Currency
Food Allow - Currency
Tax - Currency
Gross Income - Currency
Net Pay - Currency
Annual Salary - Currency
Manager Comm - Currency
Question Zone
#1) What do you understand by ‘Database’?
Answer: Database is an organized collection of related data where the data is stored and organized to serve some specific purpose.
For Example, A librarian maintain a database of all the information related to the books that are available in the library.

Q #2) Define DBMS.

Answer: DBMS stands for Database Management System. It is a collection of application programs which allow the user to organize, restore
and retrieve information about data efficiently and as effectively as possible.
Some of the popular DBMS’s are MySQL, Oracle, Sybase, etc.

Q #3) Define RDBMS.

Answer: Relational Database Management System(RDBMS) is based on a relational model of data that is stored in databases
in separate tables and they are related to the use of a common column. Data can be accessed easily from the relational
database using Structured Query Language (SQL).

Q #4) Enlist the advantages of DBMS.

Answer: The advantages of DBMS includes:
•Data is stored in a structured way and hence redundancy is controlled.
•Validates the data entered and provide restrictions on unauthorized access to the database.
•Provides backup and recovery of the data when required.
•It provides multiple user interfaces.
 Question Zone
 What do you understand by Data Redundancy?
Answer: Duplication of data in the database is known as data redundancy. As a result of data redundancy, duplicated data
is present at multiple locations, hence it leads to wastage of the storage space and the integrity of the database is
destroyed.

Q #6) What are the various types of relationships in Database? Define them.

Answer: There are 3 types of relationships in Database:

•One-to-one: One table has a relationship with another table having the similar kind of column. Each primary key relates
to only one or no record in the related table.
•One-to-many: One table has a relationship with another table that has primary and foreign key relations. The primary key
table contains only one record that relates to none, one or many records in the related table.
•Many-to-many: Each record in both the tables can relate to many numbers of records in another table.

Q #7) Explain the terms ‘Record’, ‘Field’ and ‘Table’ in terms of database.

Answer:
Record: Record is a collection of values or fields of a specific entity. For Example, An employee, Salary
account, etc.

Field: A field refers to an area within a record that is reserved for specific data. For Example, Employee ID.

Table: Table is the collection of records of specific types. For Example, the Employee table is a collection of
records related to all the employees.