MS-ACCESS (DATABASE MANAGEMENT SYSTEM)

   MS-ACCESS (DATABASE MANAGEMENT SYSTEM)

Types of software :

1.      System software (which includes)

a)      Operating system

b)      Language processors

c)      Utility programs /device drivers etc.

Examples of operating systems:

MS-DOS, WINDOWS10/11, UNIX, LINUX, ANDROID, IOs etc.

 

2.       Application software:

Examples of application software:

MS-WORD (Word processor)

MS-EXCEL ( Spreadsheet )

MS-ACCESS (RDBMS)

MS-POWERPOINT (Presentation )

 

QBASIC – High Level Programming Language

C - Middle Level Programming Language

HTML – To create hypertext documents (web pages)

Web browsers – Microsoft Edge, Mozilla Firefox, Google Chrome etc.

 

Data – Data relates to certain facts about certain event, task or transaction. Data are basically treated as raw or un processed facts.

Processing – The systematic manipulation of data to generate information is termed as processing.

Information (output) – The result of processed data is referred as information.

MS-ACCESS- Microsoft access is one of the most popular database management program used in windows environment.

Features of MS-ACCESS

a)      Storage of multiple databases.

b)      Using databases individually or in group.

c)      Creating queries and generating reports.

d)      Designing and printing sophisticated databases etc.

 

Database Management System ( DBMS )

A DBMS is a software which provides an environment that is both convenient and efficient to use in storing and retrieving database information.

A collection of data and a set of programs makes the environment for database management system.

 

Why use  DBMS ? ( Advantages)

a)    Easy to add new data.

b)    Easy to modify data.

c)     Able to delete old records.

d)    Able to arrange data in a necessary sequence.

e)    Data can be sorted, indexed and organized the way a user wants.

f)      Queries or search can be done easily in a database.

g)    Necessary data can be easily filtered and retrieved etc.

What is a database?

A database is an organized collection of meaningful and inter-related data. 

*note* the (files/database) created in MS-ACCESS will have the extension of .mdb

for example students.mdb,        employee.mdb,        report.mdb etc.

Database/ RDBMS objects

a) Table

b) Queries

c) Forms

d) Reports

        Table- A table is a collection of data about a specific topic. Table is where data is stored as a record.

             Advantage of table- Table makes a database more efficient and reduces data entry errors.

             Components of a table - A table consists of columns and rows.

             Column- Each column represents a field, a field stores only a specific category of information.

             Row – each row in a table is called record and it consists of number of related fields.

 Example of a table in a database, consisting of columns, rows, fields and records:

 

COLUMNS

   

Sno.	Name	Class
1	Arya	VII
2	Biku	VIII
3	Chunki	IX

 

         Fields- An item or a field of a record is a unit of meaningful information about an entity or a field is a piece of meaningful information in a table of a database. ( a number of fields forms a record)

          Records- A record is a collection of fields or related data items. A record is an information        about a  particular entity in a field.  (each record should have a field to uniquely identify it)

          Queries- Queries allows to manipulate the data of a table that meets a certain criteria or            condition and displays the information on screen in a simple format.

           Uses of query: To bring data from multiple tables and sort it in a particular order.

                                       To perform calculations on group of records.

     Forms- Forms are used  mainly for data entry or to display existing data in a user friendly format. Forms are used to view, enter or update information in a database.  Forms present one record at a time  on the screen.

          Reports- Reports are specially formatted  collection of data organized according to your specification for summarizing and printing listings of database data. Reports are designed to be viewed and printed.

           Uses of report:

a)      Creating mailing labels

b)      Add a logo or picture

c)       Show totals in a chart

d)      Group t records into categories and calculate totals etc.

Data Type – each field of a database file should be classified into data type or field type to represent the nature of record in a field. For e.g. text, number etc. 

Types of fields (data types)

i)       Short Text

ii)     Long Text

iii)    Number

iv)   Date/time

v)     Currency

vi)   Auto number

vii)  Yes/no

viii)Ole object

ix)   Hyperlink

x)   Attachments

x)     Lookup wizard

 Short Text – (default) text or combination of text and numbers , up-to 255 characters.

 Long Text - Lengthy text or combinations of text and numbers. Up to 64,000 Characters

 Number – Used for data to be included in mathematical calculations. 1,2,4 or 8 Bytes

 Date/Time – Used for dates and time data. 8 Bytes

Currency- Used for currency values. 8 Bytes

Auto Number – A unique sequential (increment by 1) number or random number assigned by Microsoft access whenever a new record is added to a table. Auto number fields cannot be updated.

Yes/No – yes and No values and fields that contain only one of two values (Yes /No , True/False or On/Off )

OLE (Object Linking and Embedding) – Used for OLE objects (such as MSWord documents, MS-Excel spread sheets, pictures, sounds). Up to about 2 GB

Hyperlink – Used for hyperlinks. Up to 2048 Characters

Attachments -To attach any supported type of file Up to about 2 GB

Lookup wizard –Used to create a field that allows you to choose a value from another table Dependent on the data type of the lookup field 

Field properties- Field properties are the attributes assigned to a field. Field properties are set to control over the contents of a field 

 List of field properties:

i)       Field size

ii)     Format

iii)    Caption

iv)   Default value

v)     Validation rule

vi)   Validation text

vii)  Input mask

viii)Decimal places

ix)   Required

x)     Allow 0 length

xi)   Indexed

Field size –  Determines the size of the field. For text data , maximum is 255 characters and for numeric data it varies depending on field setting as byte, integer , long integer, single and double.

Format – It allows you to display data in a different format than the way you stored in the database.

Input Mask – input mask allows to have control over data entry by defining validation for each character that is entered in the field. You can use input mask wizard for additional information or you can even create input masks as required.

Caption – caption field is used when you want to display an alternate name for the field to make the field name more explanatory. It can contain up-to 2048 characters.

Default value – default value is the one that is displayed automatically for the field when you add a new record to the table. 

Validation Rule – The data validation rule enables to limit values that can be accepted into a field. Validation rule can be automatic or you can define a method for this purpose.

Validation Text- Validation Text is the error message that appears if the data entered is invalid according to the specified validation rule.

Required – you can enter a Yes value for required if field should always receive a value during data entry time.

Indexed – Indexed property is used to set an index on a field. It speeds up searching and sorting of records based on a field.

Starting Microsoft Access

When we start MS-ACCESS, Access will provide windows with three options.

1)      Blank Access Database – to create a new database format.

2)      Access Database Wizard Page and Project - to create a database from wizard.

3)      Open an existing file – to open the existing file from any storage device.

 

Creating a new database:

When creating a new database, the database dialogue box will display three options for creating tables:

Create table in design view: 

     Create table using wizard: 

     Create table by entering data:

      Entering and Editing Data 

    Table Datasheet and its Formatting 

     Table datasheet is simply the display of records in row and column format. Using the datasheet view, you can add, modify, search or delete records. There are mainly two views of Table. 

a) Design View – Related with table structure. You can add, edit or delete field and its properties. 

b) Datasheet View – Related with records. You can add, modify, search or delete records   

Primary key- A primary key is a special field or group of fields that contains unique data for each record. It identifies the record in a database. The primary key does not accept duplicate value for a field and it does not allow a user to leave the field blank or null. 

Uses/Importance of Primary Key 

 To identify each record of a table uniquely. 

 To reduce and control duplication of the record in a table. 

 To set the relationship between tables.

 (When a primary key is set, the filed will get a small key symbol)

Field	Datatype
Rollno.	Number
Name	Text

 Γ

 

Table wizard – table wizard is a facility that provides many pre-defined sample table formats.

Closing the database- After working with the database in order to retain changes , a database should be saved and closed.

Opening an existing database – The process of retrieving the saved database is referred as opening a database.

Adding a field – The process of creating a new field to accommodate new data or records in a database. (Fields can be added column or row wise) 

Deleting a field – The process of removing unwanted field from a database.

Adjusting column width (size) and row height – It is a process of increasing or decreasing the column width (size or row height to accommodate data in the cell.

Cell – the intersection of column and rows is called a cell.

Editing  the table structure – The process of changing the field name, data type, field size etc.

Hiding a field – If a table is too long, all fields may not be seen on the screen, one can temporarily hide one or more fields of a table to work with the table in a convenient manner.

Importance of hiding a field:

a)      To view only those records upon which a user wants to work.

b)      To take the printouts of only certain fields.

Sort – The process of arranging records of a field in a database is called sorting. Sorting can be done in two ways:  a) Ascending (a-z)                            b) Descending (z-a) 

Filter – Filter means to find records based on whether they contain a specific value in a particular field. Filtering data allows to select and restrict to display the records based on criteria that you specify.

Filters can be applied in four ways:

a)      Filter by form

b)      Filter by selection/ filter excluding selection

c)      Filter for input

d)      Advance filter / sort

Filter by form – This technique allows you to choose a type of value that you want the filter records to contain in empty fields.

Filter by selection – This technique retrieves only records that contain the selected value in a data sheet or a form.

Filter for input – filter for input is used for filtering records that uses a value or expressions that you enter to find only records that contain the value or satisfy the expression.

Advanced filter – advanced filter allows you to apply more than one criteria or condition on multiple fields.

Arithmetic, Relational and logical functions used in MS-ACCESS

Arithmetic operators: + addition, - subtraction, * multiplication, / division, ^ exponential, % percentage.

Relational operators: = equal to, > greater than, < less than, >= greater than equal to, <= less than equal to, <> not equal, & text concatenation.

Logical operators:

AND – returns TRUE if all its arguments are TRUE, returns FALSE if one or more arguments are FALSE.

OR – Returns TRUE if any argument is TRUE, returns FALSE if all arguments are FALSE.

NOT – Reverses the value of its argument. Use NOT when you want to make sure a value is not equal to one particular value.

 

Examples of DBMS/RDBMS

a)      Dbase III+

b)      Dbase IV

c)      Sybase

d)      Foxprow

e)      Oracle etc.