Do Visit This Link To Get Other Materials Of Class 10 Computer Science:- Click Here

 MS- Access[ Database]

Database Terminology :

1) Data : Data is the raw fact which may or may not give any sense. It is a set of static value which are stored in a table.

 

 2) Information :  It is the proceed data or output which is obtained after manipulating raw data.

 

3) Data Processing : The act of manipulating raw data in order to obtain meaningful information s called data processing. The steps of data processing are :

Data collection à Filing ---> Codingà Data EntryàData updating à Data Processing à Decodingà Report Printing

 

4)  Database : Database is an organized and systematic collection of  data of a particular subject which is easily accessed , edited and managed.

Eg. Telephone directory, marks ledger, Salary sheet, flight schedule, dictionary etc.

 

a)                  Importance / Advantages of  database:

·                     Data can be stored in a small area

·                     Saves time in searching, updating and editing data in computerized data base.

 

5)                  Computerized VS Non computerized database :

 

Database organized in computer using electronic device and software is called computerized / electronic database. Whereas, database in sheet of paper, organized manually is known as non- computerized database. Computerized data base access data conveniently. Data updating, editing and deletion of records will be faster, stores large number of data in a small device.

But non computerized database is very difficult to access, update and search data. It creates problems while handling large amount of data.

 

6)                  DBMS( Database management System):

It is the collection of software that manages, manipulate and analyze data in database. It collects the data in table, process and analyze them using query and provides required information through report.

Example : MS- Access, Dbase, Foxbase, Foxpro, Oracle, Clipper, Sybase,

 

7)                  RDBMS( Relational Database management System):

RDBMS is the collection of software that manages database by linking and sharing in two or more tables. It update and  analyze the data from one or more database. Eg.Oracle, Ms Access, MS SQL Server etc.

 

Functions of DBMS :

·                     Maintains data validity

·                     Controls data redundancy( duplication)

·                     Provides facilities for sharing of data

·                     Maintains standardization of data

 

8)                   Elements of data base :

 

Field : Field is the column heading of a database. It is the smallest unit of table where similar type of data are stored. It is also known as attribute. Eg, Name, Age, Address, Phone etc.

 

Record: A record is a collection of multiple related fields, which gives a complete information about an individual. It is a row in a database which is also known as tuple.

(i)                       

7. MS- Access : It is a relational database management system that arranges data in tables, allows user to query data, and provides information through report. It is developed by Microsoft Corporation US, and available under MS – Office family.

Features of MS- Access :

·                     Relational database management system.

·                     It incorporates SQL and VB language.

·                     Query, form report are the useful objects.

 

 

8. Objects / Elements of MS- Access( DBMS):

(i)  Table                     (ii) Form          (iii) Query        (iv) Report

 (v)  Pages                   (vi) Modules   (vii) Macros

 

Table :

Meaning :

It is the primary object and basic structure of DBMS. It stores/collects the data about the specific topics in row and columns. It is also known as relation.

 a) Importance/Advantages of Table :

(i)                 It facilitates storing, grouping and sharing data

(ii)               It is the basic structure of DBMS for storing information

 

 b) Elements/ Parts of a table :

i) Fields, Records, primary key, cell etc. are the elements of table.

 

ii) Primary Key : A primary key is the field that uniquely identifies record in a table. Primary key that consists of two or more attributes(fields) is called composite primary key. The main feature of primary key filed are – it does not hold duplicate data, nor it accepts blanks(null) data.

Advantage/ Importance of primary key:

·                      Primary key field controls data redundancy( duplication of data).

·                     It allows to create relationship between two or more tables.

·                     Facilitates data searching and sorting.

 

 Sorting : Grouping records in ascending or descending order on the basic of one or more field(s) is known as sorting. Its advantages are ; (i) Since, it arranges the data in particular order, it helps to locate the data easily in a table. (ii) It increases the readability of data in a table.

 

9)                  Filtering : It is the process of extracting data from underlying table. It provides the data from the table, which meets the given criteria. There are three types of filtering;

i.                    Filter by form

ii.                  Filter by selection

iii.                Advanced filter

 

10)              Indexing :  It is the process of organizing data such way, so that  searching and retrieval of data would be faster. MS- access supports two types of indexing;

i.                    Unique index : Accepts no duplicate value

ii.                  Duplicate index: Accepts duplicate value in the fields.

Advantages: It control data redundancy. It speeds up data searching in the table.

 

11)              Methods of creating /opening table.

There are basically three ways of creating table. 

a)                  Design View

b)                  By entering data( Data sheet view)

c)                  Using wizard

 

a)                  Design view : Design view allows user to create table by defining field size and field properties. A design view window is split into two parts i.e. Filed grid pane and field property pane. User can easily modify the table structure in this view.

 

b)                  Datasheet view :It allows user to create table by entering data directly. Field names are defined by the user as a column heading by renaming the exiting fields. Data can be entered, edited and deleted in this view, easily.

 

c)                  Using wizard : It allows user to create table by selecting the pre- defined fields name, size and properties.

 

 

Design View VS Datasheet view

 

i.                    Fields size and properties can be defined in design view, where as they can not be defined in datasheet view.

ii.                  Table structure can be modified in design view but not in datasheet view.

iii.                Direct data entry, editing and deleting are possible in datasheet view, where as they are not possible directly in design view.

 

Data Type supported by MS- Access

 

Data types are attribute for a field which determines what type of data the field contains. Access supports following  Ten types of data.

 

Data Type

Description

Size

Example

1. Text

Accepts alpha numeric characters. It is default data type.

0 to 255 characters. 

Name, Address

 

2.Memo

Alpha numeric characters with punctuation marks. Used for long description

0 to 65,535 character

Remarks, Description, Auto biography

3. Number

Byte, Integer, Single, Long integer, Double, decimal

1, 2,4,8,8 bytes

Population, Area, Length

4. Currency

Monetary data with currency symbol.

8 bytes

Tax, Fee, Salary

5.Date & Time

Date and Time value. Date range 100 to 9999 years

8 bytes

Date of Birth, Time of Arrival

6.Autonumber

Sequential or random number, data entry not required

4 bytes

Serial number, Code number

7. Yes/No/

    Boolean

Logical data type. Format: True/False, On/Off

1 bit

Gender, cash/credit

8.OLE Object

Picture, sound, video

Upto 1 Gb

Map Of Nepal, music video

9. Hyper link

A link address

Upto 2048 chr

URl, Email address

10. Look up wizard

Data from another table/ combo box or list box

4 bytes

List of month, List of districts

 

The Field Properties:

 

The field properties area are the attribute of field which provides the control over a field. Different data types have different filed properties .They are as follows .

 

Field Property

Description

Size

Sets the maximum size of data accepted by the field.

Format

Displays data in different format.

Text and Memo data type uses ; @,&,>,<

Date & Time uses ; General Date/time, Long date/Time, short date/Time, medium date/Time

Yes/No data type uses; On/off, True/False

 

Caption

Label or explanatory name for the field name. Maximum upto 2048 characters.

Default Value

Automatically repeats/sets the value in a particular field of each record ,while entering data in the table. It is not applied to Auto number and OLE Object.

Validation Rule

To limit the values to be accepted by the field.  Eg; not null,  >100,  between 1 and 100 etc.

Validation Text

Sets the message, when validation rule is violated . Eg; ‘Enter less than 100’ etc.

Required

Sets whether the value required or not. It is set to Yes, if data entry is required for the field, other wise No.

Indexed

Speeds up data searching and retrieval. It has two option.

i. No( no indexing)

ii. Yes (duplicate OK)

     Yes( No duplicate)

Input Mask

Facilitates user by providing Pattern/ way of data to be entered and viewed .

 

Rules for naming Field:

i. A field name can be upto 64 characters.

ii.  It can contains letters, numbers, spaces and other symbols except perid(.), exclamation(!), acent grave(~), and brackets ([]).

FORM

 

Form is a database object that can be used to enter, edit, or display data from a table or a query. It provides the interface between MS Access and user  because it displays the records on the screen. There are two types of from;  i. Main form  ii.  Sub form

 

Methods of creating form :

i)                    Design view

ii)                  Auto Form

iii)                Form wizard

 

Layout of Form :

i)                    Data sheet

ii)                  Tabular

iii)                Columnar

iv)                Justified

Control of Form:

 

Tab, Radio Button, Check box, List box, Combo box,

 Option group, command button etc.

 

QUERY

 

It is an object of MS Access which is used to view, change and analyze the data in different ways from table or query. It asks a questions about the data stored in the tables and returns the results. It uses criteria to analyze and extract the require information.

Types of Query:

1. Select Query : It retrieves the data from one or more tables or queries and displays information bys using given criteria.

 

2. Action query : The query which directly changes or moves one or more records in one operation . It deletes, update and group the records.

a) Update query: It makes global changes to a group of records in one or more tables.

b) Delete query: It deletes one or more records from one or more tables.

c) Append query : It adds a group of records from one or more tables to the existing table at the end of record.

d) Make table query : It creates a new table from all or part of the data in one or more tables for a backup copy of a table.

 Besides above, Cross tab query, SQL query and Parameter query are also the different types of query.

 

Criteria :  Criteria are the expression that defines on e or more condition in order to obtain specific set of records or data from query or filter. Eg. Like , Not like,  Between , <>100 etc.

 

Operators : Operators are the sing or symbol or words that are used to compare and calculate the data in query. Access supports the following type of operators.

i.                    Arithmetic operator : +, -, *, /, \. MOD

ii.                  Comparison Operator:  =, >, <. >=. <= , Like, Not Like, In, Between

iii.                Relational operator:  AND, OR, NOT

 

REPORT

 

It is a database object which provides the information in printed form. It groups and presents the formatted information from underlying table, query and SQL statement.

 

 Methods of creating Report :

iv)                Design view

v)                  Auto Report

vi)                report wizard

 

Layout( Print view)  of Report :

i.                    Tabular

ii.                  Columnar

iii.                Justified

 

Different parts of Report :

      i.  Report Header : It contains the title of report.

      ii. Page Header : Information contains at the first of each page

      iii. Detail: Display detail information from selected fields.

      iv. Page Footer : Information contains at the last of each page

      iv. Report footer: It contains the information at the end of report.

Page Orientation of Report :

 

·                     Portrait : Vertical Orientation of page

·                     Landscape : Horizontal orientation of page.

 

 

Differences

 

Data

Information

i.  It is a raw or un proceeded fact.

ii. It may or may not give sense.

i. It is proceeded output or result.

ii. It provides fact output or sense .

Field

Record

i. It is a piece of information.

 

ii. It is a column heading or vertical orientation of table

i. It is a complete information about an individual

 

ii. It is a row or horizontal orientation of a table.

 

Database

DBMS

i. It is an organized collection of data.

ii. It may be computerized or non computerized.

i. It is the collection program or software.

ii. It is an electrical or only computerized .

Sorting

Indexing

i. Group the records in order

 

ii. It is not a field property.

i. Performs fast retrieve of data from table.

ii. It is a field property.

Query

Form

i. It is used to extract, and change the data from one or more tables and queries.

 

ii. The source of query is table and query itself.

 

iii. It is a question about the data stored in a table.

 

iv. Data sorting can be performed.

i. It is used to enter and edit the data.

 

 

ii. The source of form is Table.

iii. It is an interface between user and MS – Access.

 

iv. Data sorting can not be performed.

Query

Report

i. It is used to extract, and change the data from one or more tables and queries.

 

ii. It can be select query and action query.

 

iii. The source of query is table and query itself.

i. It is used to display the information in printed form.

 

ii. Report can be columnar, tabular and justified.

 

iii. The source of Report is Table, form and query.

Text

Memo

i. It accepts alpha numeric character.

 

 

ii. It accepts maximum 255 characters.

i. It accepts alpha numeric characters as well as punctuation marks.

ii. It accepts maximum 65,535 characters.

Action Query

Select Query

i. It changes(delete, update) the data.

 

ii. It does not display information but changes the data directly in table, when it is run.

i. It only retrieves required data on the basis of criteria.

 

ii. It displays information on the screen when it is run.

Number

Currency

i. It accepts numeric data i.e. byte, integer, long integer, single , double etc.

 

ii. It accepts long integer, by default.

i. It accepts numeric data preceded with currency sign.

 

ii. It accepts two decimal places, by default. Eg. $4.00

`