Grade 10:- ICT
Unit 4
Application Software
The spreadsheets are one of the most widely used application software in the world. These are used everywhere from a small store to a large manufacturing house. These can be used for calculating accounts, managing inventory, managing payroll, managing and analysing the sales and marketing.
Revision of Spreadsheet
The spreadsheet is an application software which provides the users powerful tools and features to analyze, share, and manage the data with ease
Starting Spreadsheets
MS-Excel 2007 is a GUI based spreadsheet package that is part of Microsoft Office 2007 suite of software.
For exploring massive amounts of data, Excel 2007 grid is 1,048,576 rows by 16,384 columns. The columns now start from A and end at XFD. Fill handle is a small black square displayed at the bottom right corner of the active cell.
Fill handle is a small black square displayed at the bottom right corner of the active cell.
Fill handle
The fill handle is used to fill adjacent cells with the same data or consecutive data. The entries that are automatically inserted in the adjacent cells are dependent on the contents of the active cell.
Using a Spreadsheet
After creating a worksheet, you can enter data (for example, words, a number, or a formula) in the active cell.
Editing Data
For editing data (in case we have made mistakes or some changes are required),
Change Data in a Cell
After entering data into a cell, you can edit the data by pressing F2 while you are in the cell you wish to edit.
Using the Four Mathematical Operators on Data to Create Custom Formula
In MS-Excel, you can enter numbers and mathematical formulae into cells. When entering a mathematical formula, precede with an equal (=) sign.
Definition of a Chart
A chart is a graphical representation of the figures in a spreadsheet. Charts help in easy understanding of complex or detailed data. These reflect information in such a way that it can be easily understood just by having a look at the chart. MS-Excel 2007 supports various types of charts to display data in meaningful ways.
Using Spreadsheet to process Data
Spreadsheet can be used to process data. In MS-Excel, you can enter numbers and mathematical formulae (formulae are equations that perform calculations on values) into cells.
A formula starts with an equal sign (=). Before using custom formula, let us identify the difference between absolute and relative cell references.
Difference Between Relative and Absolute Cell References
A reference identifies a cell or a range of cells on a worksheet and tells MS-Excel about where to look for the values or data that you want to use in a formula. With references, you can use data contained in different parts of a worksheet in one formula or use the value from one cell in several formulae.
Relative Reference: It is the default addressing used by Excel. A relative cell reference in a formula, such as A1, is based on the relative position of the cell that contains the formula and the cell the reference refers to.
Absolute Reference: Use absolute reference when you do not want Excel to change the cell addresses relatively, when you copy a formula. An absolute cell reference in a formula, such as $A$1, always refers to a cell in a specific location. If the position of the cell that contains the formula changes, the absolute reference remains the same.
Using Custom Formula
Using a custom formula is as simple as the other commands of Excel.
A formula in Excel contains cell addresses, operators and built-in functions.
Using IF condition
IF function of Excel is a conditional construct that displays one or the other value in a cell based on the condition applied. If the condition is true, one value is displayed otherwise the other value is displayed. The syntax of IF function is: IF (Condition, Value if True, Value if False) The three items between the parentheses are the arguments of IF function.
Using Built-in Functions
Now you are familiar with the formulae in Excel. It is very easy to write these formulae but, in some situations, we have to solve complex problems that are difficult to be solved with these basic formulae. Excel has several built-in functions to make your work easy and fast. There are many functions, grouped under various categories like: math and trigonometry functions, statistical functions, date functions, etc
AutoSum : It calculates the sum of the selected contiguous cells directly after the selected cells.
SUM ( ): It adds all the numbers in a range of cells.
AVERAGE( ): It returns the average (arithmetic mean) of the arguments.
4. MAX( ): It returns the largest value in a set of values.
5. MIN( ): It returns the smallest number in a set of values.
Formatting worksheet
Formatting refers to the act of changing the appearance of data in cells. In MS-Excel, you can format text, format cells, adjust columns and rows and print worksheets.
The following are the disadvantages of manual data maintenance by using paper files:
1. Data redundancy
2. Slow data access
3. Security problems
4. Integrity problems
DATABASE APPLICATION
Every organization maintains data for its business, employees and finance. This data need to be maintained in such a manner that it is easily available and can be presented in desired formats. The data also needs to be updated regularly.
Introduction to Database
What is Database?
A database is a collection of related data that is organized, so that it can easily be accessed, managed and updated.
Microsoft Office Access 2007 is one of the database creation and management programs. It is a Database Management System (DBMS) included with Microsoft Office suite.
The common areas where data bases are used are:
Uses of Database
A database stores information in an organized way, and makes it easy to get information in and out.
1. Government
2. Business
3. Educational Institutions
4. Non-Profit Organizations
5. Household and Family Management
6. Everyday Life
Common Database Applications
Databases are used in many applications, covering almost the entire range of computer software. Databases are the preferred method of storage for large multiuser applications, where coordination between many users is needed.
Some examples of database applications are:
1. Computerized library systems.
2. Automated Teller Machines (ATMs).
3. Flight reservation systems.
4. Computerized inventory systems.
Any application that involves storing and maintaining a large amount of data in an organized manner can be set up as an Access database.
Database terminology
Table: A table in a database is a predefined format of rows and columns that define an entity. It is also called a relation.
Record: A database record consists of one set of tuples in a table. In other words, a row of information in a table is called a record.
Field: A field is a single unit of data stored as part of a database record. Each record is made up of one or more fields, which correspond to the columns in a database table. Fields are often called attributes.
Data Type: A data type specifies the type of data that a column in a table represents in a database.
Primary Key: Every table should contain a field that uniquely identifies each record stored in it. This field is called the primary key of the table.
Designing a Database Structure
Before you start creating a database, it is essential to create a good database design. Planning the design structure is important, as an improper design will cause changing the design structure. A good database design ensures that the database is easy to maintain and store data. To plan and design a database, the points to be considered are:
1. The purpose of the database.
2. The number of tables and the type of information each table will contain.
3. Columns that will form each table. 4. Relationship between tables.
Overview of a Table
A database table contains data about a particular subject. In a table the data is arranged in rows and columns. To get the most flexibility out of a database; the data needs to be organized into tables so that redundancies do not occur.
Creating a table
One must plan and design a table properly for a new database before creating it. You can use Design view to create an Access table. Using Design view is the preferred method for creating a table because it provides you with the most options and enables you to precisely define your table.
Add, edit, Delete and Sort records
After creating an Access table, you can add, edit, delete and sort records in it.
Overview of Query
Queries are one of the most important features of database management system. The process of accessing the database and retrieving data selectively is known as querying. Queries often serve as the record source for forms and reports. Query is a database object, which is used for extracting data from one or more tables based on specific conditions. The set of records returned by a query is known as Dynaset.
Sorting Records
Sorting is arranging records in a table, so that the records can be retrieved quickly and easily. By sorting, you can put a column of information in alphabetical, numerical, or date order. You can sort in ascending order (alphabetical from A to Z, lowest number to highest number, earliest date to latest date) or descending order (alphabetical from Z to A, highest number to lowest number, latest date to earliest date).
Query Types
MS-Access supports many types of queries. The major categories are:
1. Select Queries
2. Make Table Queries
3. Append Queries
4. Update Queries
5. Delete Queries
Overview of a Form
A form is a database object that you can use to enter, edit, or display data from a table or a query. Forms are sometimes called “data entry screens”. Forms provide an easy-to-use format for working with the data, and you can also add functional elements, such as command buttons, to them.
You can program the buttons to determine which data appears on the form, open other forms or reports, or perform a variety of other tasks.
Different Methods of Creating a Form
In Access 2007, forms can be created using the following tools:
Form Tool:
Split Form Tool:
Form Wizard:
Blank Form Tool:
Performing Calculations in a Form
Expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data. Precede each expression with the = operator.
In Access 2007, you can use calculation controls to display the results of a calculation. A calculation control object containing a formula is created using a text box control object.
Main/Subforms
Form is a graphical user interface for the database. Users can enter, edit and even delete data through a form. A subform is a form that is inserted in another form. The primary form is called the main form, and the form within the form is called the subform. A form/subform combination is sometimes called a hierarchical form, a master/detail form, or a parent/child form.
Overview of a Report
Reports are what you use to summarize and present the data in the tables.
Each report can be formatted to present the information in the most readable way possible. A report can be run at any time, and will always reflect the current data in the database.