DATABASE MANAGEMENT PROGRAMS

DATABASE MANAGEMENT PROGRAMS

INTRODUCTION TO SPREADSHEET/DATABASE PROGRAM

Spreadsheet applications are programs made of rows and columns which are basically used for calculations.

Before the existence of spreadsheet packages, ledger were used by accountants and business men for their daily financial calculations.

TYPES OF SPREADSHEET PACKAGES

1.    Lotus 1,2,3

2.    Quartro Pro

3.    Ms Excel

4.    Peachtree Accounting etc

Among the above mentioned packages, we will concentrate with Microsoft Excel.

INTRODUCTION TO MICROSOFT EXCEL

Ms Excel is a spreadsheet package designed by Microsoft Cooperation. It belongs to a big application known as Ms office or office Suite.

DEFINITION OF MS EXCEL

This can be as a spreadsheet application used basically for calculations and data analysis.

STEPS TO OPEN EXCEL

1.    Click on start button

2.    Click on All program

3.    Click Ms Office

4.    Click on Ms Excel

Note: It can also be opened through the Desktop or Start button menu.

SCREEN ELEMENTS OR PROPERTIES OF MS EXCEL

1.    Title Bar:-  It contains the file and program a user is working with at a given time.

2.    Menu Bar:-  It contains all the commands or instructions used to communicate with Excel

3.    Standard Bar:-  It is the most frequently used bar. It contains shortcut commands which can be accessed easily without using the menu bar.

4.    Formatting Bar:-  It is a bar used to beautify a workbook. It is also a shortcut bar.

5.    Drawing Bar:-  It is a bar used to manipulate graphics in Excel.

6.    Horizontal & Vertical Scroll Bar:- These are bars used to navigate within an  environment.

7.    Status Bar:- It displays a detailed information concerning the environment a user is working with.

8.    Editing Window:-  It is an environment used for data manipulation

9.    Task Bar:-  It a bar that contains the start button with date and time.

TERMS OR CONCEPTS USED IN EXCEL

1.    Spreadsheet:-  It is the environment used for data manipulation. It is made of rows and columns.

2.    Worksheet:-  It is the current environment a user is working with at a given time.

3.    Workbook:-  A workbook is made up of worksheets. Note in excel, a file is saved as workbook.

4.    Cell:-  It is a rectangular box formed by the intersection of rows and columns.

5.    Active cell:-  It the thicker cell used for data manipulation.

6.    Cell range:- It is a group of cells that are selected together.

7.    Cell pointer:- It is used to indicate an active cell

8.    Cell address:-  It is a special way of naming a cell by considering the cell alphabetical and numerical position.

9.    Name box:- It is a box that displays the address of an active cell

10.Formula bar:- It is a bar used to edit the content of an active cell.

SIGNS AND OPERATIONS USED IN EXCEL

S/N	SIGNS	OPERATIONS
1	Plus                 +	For addition
2	Minus               -	For subtraction
3	Multiplication    *	For multiplying values
4	Division            /	For dividing values
5	Exponent          ^	To calculate the power of values
6	Percent            %	To calculate in percentage
7	Greater Than    >	For logical calculation
8	Greater Than or equal to   >=	For logical calculation
9	Less Than        <	For logical calculation
10	Less Than or equal to	For logical calculation

Other formulas include

Sum:-For addition of wide range of values

Min:-To calculate the minimum of values

Max:-For maximum value calculation

Average:- To calculate the average of figures

If:-Used for logical analysis

PRACTICAL APPROACH TO CALCULATIONS

As a sales manager of a certain organization calculate the following using the table below.

S/N	DESCRIPTION	QTY	PRICE/EACH	TOTAL AMOUNT
1	Mother board	5	10000
2	Hard disk	4	4000
3	Monitor	5	3500
4	Processor	1	5000
5	Joystick	10	500
6	Mouse	20	500
7	Printer	2	10000
8	Modem	2	8000
9	Adapter	3	5000
10	USB cable	1	500

Calculate:

1.    Total amount for each item

2.    The sub-total of the overall items

3.    In exception of the amount of mother board, calculate the sub-total for other items.

4.    Save the work.

STEPS TO SAVE A WORKBOOK

1.    Click on file menu

2.    Click on Save As

3.    Under save in, select the file location where you want to save the file

4.    Type the file name

5.    Click on save.

Also, through the standard bar, save can also be achieved.

STEPS TO OPEN A WORKBOOK

1.    Click on file menu

2.    Click on open

3.    Select the file location where you saved the file

4.    Click on the file

5.    Click on open.

As a secretary of a given organization, use the table below to calculate the following;

S/N	DAYS	ITEMS	QTY	AMOUNT BOUGHT	SELLING PRICE	TOTAL AMOUNT	PROFIT
1	Monday	Modem	2	16000	10000
2	Monday	Monitor	5	25000	3000
3	Monday	keyboard	10	5000	700
4	Tuesday	A4 paper	5	3500	850
5	Tuesday	Toner	4	12000	3500
6	Wed	Board	2	20000	15000
7	Wed	keyboard	20	10000	7000
8	Thursday	RAM	5	10000	3000
9	Thursday	Adapter	4	20000	6000
10	Thursday	Laptop	5	400000	90000
11	Friday	Hard disk	4	16000	5000
12	Friday	Speaker	2	6000	4000
13	Friday	Ms office	1	1000	1500
14	Friday	Corel draw	2	2000	1500

Calculate:

1.    The total amount and total profit

2.    The sub-total for each of the days and profit

3.    The grand total and grand profit

4.    The total amount and profit made between Mondays, Tuesdays and Fridays.

5.    The total amount and profit made between Wednesdays and Thursday.

Autofil:-This is a command used to automatically calculate the subsequent values in a column.

To Autofil, simply point at the Auto fil handle, allow it to turn to a thick cross, then drag it to last value in the column.

BANK TRANSACTION ANALYSIS

S/N	Customer Names	Transaction Date	Transaction ID	Cash Banked	Amount Withdrawn	Profit
1	Johnson	12/2/2012	010/001	1000000	250000
2	David	12/2/2012	020/002	300000	120000
3	Emeka	15/2/2012	030/003	5000000	3000000
4	Frank	20/2/2012	040/004	1000000	350000
5	Longinus	22/2/2012	050/005	4000000	1000000
6	Chidimma	28/2/2012	060/006	300000	150000
7	Sopuruchi	29/2/2012	070/007	9000000	2000000
8	Precious	2/3/2012	080/008	120000	500000
9	Princess	4/3/2012	090/009	200000	100000
10	Emmanuel	25/3/2012	0120	4000	1500

From the above table, calculate:

1.    Total money banked by all the customers

2.    Total money withdrawn

3.    Total Balance

4.    Save the work.

PAGE SET-UP:- This can be defined as a command used to adjust the look or appearance of a workbook.

When setting a page, users are advised to observe the margin, orientation, scarling and paper size.

STEPS TO SET-UP A PAGE

1.    Click on file menu or page layout

2.    Click on page setup

3.    The observe the necessary adjustment under margins, orientation, scarling and size.

BORDERS:- it is a command used to apply real lines in a worksheet especially the type to be produced as hardcopy.

STEPS TO APPLY BORDER:

1.    Select the area you want to apply the border

2.    Click on format menu

3.    Click on cell

4.    Click ,on alignment

5.    Click on outline and inside

6.    Click on ok.

HEADER AND FOOTER:-  These are commands used to display descriptive notes on top and bottom of a workbook.

STEPS TO APPLY HEADER AND FOOTER

1.    Click on insert menu

2.    Click on header or footer

3.     Type the text for the header and the footer

4.    Then double click on the working area.

FORM:-This can be defined as a command used to add or remove an information from a database.

STEPS TO APPLY FORM

1.    Position your cell in the first cell that start your database

2.    Click on data menu

3.    Click non form

4.    Use find next and find preview to select the information you want to delete

5.    Click on delete in order to delete the information

6.    Click on new order to add new information, and then add the information.

SORT:- This can be defined as a command used to arrange information in a database in ascending or descending order.

STEPS TO APPLY SORT

1.    Position your active cell at the beginning of the database

2.    Click on data menu

3.    Click on sort

4.    Under column sort by, select the field name you want to sort its contents

5.    Under order, select ascending or descending

6.    Click on ok.

FILTER:- Filter is a command used to select information that meet to a certain proposed information in a database.

STEPS TO APPLY FILTER

1.    Position your active cell in the first cell that starts the database

2.    Click on data menu

3.    Click on filter

4.    Then select the field name you want to filter its contents

5.    To filter any record of your choice, select number filter or text filter by

6.    Select any condition of your choice e.g greater than, less than

7.    Then select the value to be filtered

8.    Click on ok.

    

PAYROLL:- This can be defined as a database feature that contains information of workers in a giving company. It can also be defined as a feature used to determine the paying structure of workers in a company.

FEATURES OR PROPERTIES OF A PAYROLL

1.    Serial Number:- It is used to arrange information in a database sequentially.

2.    Names;-  It contains the names of workers in a certain company

3.    Sex:-  It is used to describe the gender of workers in an organization.

4.    Age:-  It shows the age of workers in a firm or organization

5.    Position:-  It describe the job title of workers.

6.    Basic Salary:-  This is the actual amount of money a worker earns at the end of the month. Not basic salary is not calculated.

7.    Allowance(s)/Bonus:-  This are additional money giving to a worker as a compensation. Allowance or Bonus is calculated using a specified or a giving percentage. E.g giving that Feeding Allowance is 4% and Basic Salary is 10,000. Then to calculate the Feeding Allowance: =4% of Basic salary. Which is equivalent to: =4%*10000.

8.    Gross Pay (G.P):-  This can be refers to the total amount of money a worker earns at the end of the month. In order words, it is the sum of Basic Salary with all the allowance. To calculate G.P: =sum(Basic Salary to all The Allowances) e.g =sum(B2:i2)enter.

9.    Tax or Levy:-  These are money removed from a workers salary. They are calculated using a giving percentage. E.g giving that Tax or Levy is 3%. To calculate it, =3% of Basic Salary and enter key. i.e =3%*B2 enter key.

10.Total Deduction:-  This is the total sum of money removed from a workers salary. In order words, it is the addition of tax and levy or debt. E.g tax=levy i.e i2+j2 enter.

11.Net Pay(N.P):- This can be defined as the money a worker goes home with after observing all the necessary payments. It can be referred to as a go home salary. It can be calculated as Gross Pay-Total Deduction i.e m2-o2 enter

METHODS OF CALCULATION IN EXCEL

1.    Using the function menu

2.    Using the Cell Address

1.    Using the Function Menu:- This involved the user to select form the predefined commands from his/her computer.

USING THE FUNCTION MENU

1.    Position your active cell were the answer will appear

2.    Click on insert menu

3.    Click on function or insert function

4.    Under select a category, click on All

5.    Under available functions, select any function of your choice e.g sum, min, max, average, IF etc

6.    Click on ok

7.    Type the logical statement in the space provided

8.    Enter the value for each of the expression

9.    Click on ok.

USING THE CELL ADDRESS

1.    Position your active cell where the answer will display

2.    Press equal to sign

3.    Type the logical statement and the values using the appropriate format

4.    Press enter from the keyboard e.g to calculate gross pay: =sum(j2:m2)enter

PAYROLL ANALYSIS OF A GIVING COMPANY FOR THE MONTH OF MAY 2012

S/N	NAMES	SEX	AGE	POSITION	BASIC SALARY	DEBT	NET PAY
1	BEN	M	25	MD	150000	10000
2	JOHNSON	M	30	DRIVER	50000	5000
3	UCHECHI	F	23	SECRETARY	100000	10000
4	DAVID	M	20	ADVISER	120000	12000
5	OGECHI	F	22	ASS/SEC	90000	8000
6	PRINCE	M	27	S/OFFICER	95000	9000
7	EMILIA	F	25	M/CONS.	100000	10000
8	DANIEL	M	20	SECURITY	60000	5000
9	ADA	F	28	COOK	80000	5000
10	BONIFACE	M	32	CEO	200000	20000

TIPS:

1.    Calculate the Net pay for each of the workers

2.    Calculate the total Basic Salary

3.    Calculate the Total Debt

4.    Calculate the total Net pay

PAYROLL ANALYSIS OF A NAMED COMPANY FOR THE MONTH OF JUNE

S/N	NAMES	SEX	AGE	POSITION	B/S	F/A	M/A	T/A	G/P	N/P
1	DAVID	M	25	DRIVER	70000
2	CHISOM	F	26	ELECT	100000
3	ANN	F	30	SEC	80000
4	BEN	M	30	S/OFFICER	90000
5	EBERE	F	27	ADVISER	120000
6	DAN	M	29	SECURITY	70000
7	STELLA	F	22	CLEANER	50000
8	FUBARA	M	26	MD	150000
9	PRISCA	F	30	CEO	200000
10	KENETH	M	26	ASS/MD	130000

From the above table, F/A = Feeding Allowance, M/A = Medical Allowance, T/A = Transport Allowance, G/P = Gross Pay, N/P = Net Pay.

TIPS:

Calculate the following:

1.    Feeding Allowance is 4% of Basic Salary

2.    Medical Allowance is 7% of Basic Salary

3.    Transport Allowance is 6% of Basic Salary

4.    Calculate the Gross Pay

5.    Calculate the Net Pay

INTRODUCTION TO MICROSOFT   ACCESS

Microsoft Access is a Database program designed by Microsoft Cooperation. It is also one of the components of the Microsoft Office. It is basically used in creating of Database.

DEFINITION:-Microsoft Access can be define as a database management program used in creating, updating and  retrieving of information.

STEPS TO LAUNCH MS ACCESS

1.    click on start button

2.    click on All program

3.    click on Microsoft Office

4.    click on Microsoft Access

SCREEN ELEMENTS OF MICROSOFT ACCESS

1.    Title Bar:-  It contains the file and program a user is working with at a given time.

2.    Menu Bar:-  It contains all the commands or instructions used to communicate with publisher

3.    Standard Bar:-  It is the most frequently used bar. It contains shortcut commands which can be accessed easily without using the menu bar.

4.    Formatting Bar:-  It is a bar used to beautify a workbook. It is also a shortcut bar.

5.    Drawing Bar:-  It is a bar used to manipulate graphics in publisher..

6.    Horizontal & Vertical Scroll Bar:- These are bars used to navigate within an  environment.

7.    Status Bar:- It displays a detailed information concerning the environment a user is working with.

8.    Editing Window:-  It is an environment used for data manipulation

9.    Task Bar:-  It a bar that contains the start button with date and time.

TERMS OR CONCEPTS USED IN MICROSOFT ACCESS

1.    Database:-  It can be defined as a feature that contains information of a related event.

2.    Field name:-  It is a name giving to a filed or column

3.    Field:-  It can be defined as a column reserved for a giving field name.

4.    Record:- It is the contents or details of a particular field name.

5.    Data type:- It describes the type of data that a particular field name will accept

6.    Primary key:-  This can be defined as a feature used to prevent duplication of data in a database.

WAYS OF CREATING A DATABASE

1.    Using wizard

2.    Using templates

3.    Using existing

4.    Using Blank database

Among the above mentioned, our point of reference will be on Blank database

STEPS TO CREATE A DATABASE

1.    Click on file menu

2.    Click on new

3.    Click on blank database

4.    Under file name, type the name for the Database

5.    Click on ok.

BASIC PROPERTIES OF A DATABASE

1.    Table

2.    Query

3.    Form

4.    Report

TABLE:-This can be defined as a feature used to store information in a database.

THINGS TO NOTE WHEN CREATING A TABLE

FIELD NAMES	DATA TYPE OR DATA ACCEPTANCE FORMAT
TEXT  FIELDS	SELECT TEXT
NUMERIC FIELDS	SELECT NUMBER
MONEY FIELDS	SELECT CURRENCY
DATE FIELDS	SELECT DATE AND TIME
PAID FIELDS	SELECT YES AND NO
ALPHA NUMERIC FIELDS	SELECT TEXT ALSO

STEPS TO CREATE A TABLE

1.     From your database, click on Table

2.    Click on new

3.    \click on design view

4.    Click on ok

5.    Type the field names and select their corresponding data type at the same time

6.    Create your primary key

7.    Save the table

8.    Click on view menu

9.    Click on data sheet view

10.Start entering the information

11.Update your table

QUERY:- This can be define as a feature in a database used to filter or extract an information or group of information in a database.

SYNTAX TO RUN QUERY WHEN THE QUERY CONDITION/SEARCH STRING IS EXACT.

Note:-A query condition is said to be exact when the search string is specific.

I.e when one is searching for a particular information in a database.

	FIELDS	SEARCH STRING
1	text fields	Enclose the text with a quotation mark. e.g type      like”John”
2	Number fields	Just type the number ordinarily e.g 40
3	Money fields	type the number ordinarily e.g 100000
4	Alpha numeric fields	They are mostly numbers, therefore just type the number e.g 5
5	Date fields	Enclose the date with ash e.g #12/12/2012#
6	Paid field	Just type yes or no e.g yes

STEPS TO RUN QUERY WHEN THE QUERY CONDITION/SEARCH STRING IS NOT EXACT

A query condition is said to be not Exact when the search string is unspecific i.e when one is searching for a group of information in a database.

STEPS TO CREATE QUERY

1.    Open the database you want to query

2.    Click on query

3.    Click on new

4.    Click on design view

5.    Select the table you want to use for the query

6.    Click on ok

7.    Click on add and close

8.    Under fields, select all the fields names in you want to query

9.    Under criteria, type the search string/criteria

10.Click on run

Form:-       form can be defined as a command used to select records to be produced as hardcopy in a database.

STEPS TO APPLY FORM:

1.    Open the database you want to produce its record

2.    Click on form

3.    Click new

4.    Click on form wizard

5.    Select the table or query you want to use for the form

6.    Click on ok

7.    Select the field names you want

8.    Click on next

9.    Select the layout of your choice and click on next e.g Tabular

10.Select the style of your choice and next

11.Type the title of the form

12.Click on finish

REPORT:- It is a command also used to produce a hardcopy of an information in a database.

STEPS TO APPLY REPORT

1.    Open the database that contains the information

2.    Click on report

3.    Click  on new

4.    Click on report wizard

5.    Select the table for the report and click on ok.

6.    Select the field names of your choice

7.    Click on next three times

8.    Select the layout and orientation of your choice e.g tabular and portrait.

9.    Click on next

10.Select the style of your choice and click on next

11.Tittle the report.

12.Click on finish.

NOTE: In creating query, form and report in a database, there must be a table.

STEPS TO PRINT

1.    Click on file menu

2.    Click on print

3.    Observe the printing options

4.    Click on print

S/N	NAMES	SEX	AGE	REG/FORM	COURSE	FULL/PAY	DEPOSIT	BALANCE	DATE	COMPLETED
1	PRINCE	M	20	500	ADV/DIP	20000	00.00	OO.OO	3/4/12	YES
2	PRINCES	F	18	500	DIP	00.00	10000	10000	10/4/12	NO
3	CHISOM	M	18	500	CERT	00.00	15000	5000	20/4/12	NO
4	PAMELA	F	19	500	ADV/DIP	20000	00.00	00.00	23/4/12	YES
5	JOHN	M	17	500	DIP	00.00	12000	8000	30/4/12	NO
6	CHRISTA	F	20	500	ADV/DIP	20000	00.000	00.00	5/5/12	YES
7	JOEL	M	22	500	CERT	20000	00.00	00.00	8/5/12	YES
8	PRISCA	F	16	500	DIP	00.00	13000	7000	10/6/12	NO
9	CHIDI	M	17	500	CERT	20000	00.00	00.00	15/7/12	YES
10	JOY	F	20	500	ADV/DIP	00.00	10000	10000	20/7/12	NO