Note of Database Management System (MS-Access)

Table of contents:

Definition of Data. 2

Definition of Information. 2

Definition of Database. 2

Definition DBMS (Database Management System) 4

Definition of RDBMS (Relational Database Management System) 6

MS-Access (Microsoft Access) 7

Objects of MS-Access are: 7

Elements of Database. 8

Table Relationship. 12

Data Type. 12

The common field properties. 13

Keys. 14

Basic Database Terms. 15

Difference between:

Definition of Data

Data is collection of raw facts and figures about a person, place, thing etc. which can be recorded in the form of text, number, image, graphic, audio, video etc. It may not give a significant meaning. For example:-Unique, Brt. 10 etc.

Definition of Information

Information is processed data which gives a complete sense or meaning. Simply, it is the result provided by the computer after data processing. For example: Unique is a student, 10 is a roll number of Ritesh etc.

Different between data and information.

S.N	DATA	INFORMATION
1	Data is collection of raw facts and figures.	Information is processed data.
2	Data is unorganized.	Information is organized.
	Data is not arranged.	Information is arranged.
3	Data is meaningless.	Information is meaningful.
4	Data is input.	Information is output.
5	Data is not helpful in decision-making.	Information is helpful in decision-making.
6	Data is raw material for information.	Information is the final product of data.

Definition of Database

A database is an organized collection of interrelated data/information about any subject or topic that can be used for multiple purposes. It is used to store, organize, update, manage and extract data. Database is evolved from the concept of set theory in mathematics.

Database can be computerize and non-computerize (manual). A database may have single table or multiple tables. The data in a database are organized in rows and columns. For example: Telephone directory, mark ledger, attendance register, dictionary etc.

Different between computerized and non-computerized database.

Computerized	Non-Computerized
It can store large volume of data.	It is limited by physical storage available.
It is very fast to find a specific record	It can take a while to manually search through all of the records.
Data can be sorted into ascending or descending order on multiple criteria.	Difficult to sort data on more than one criteria
The database can be kept secure by use of passwords.	The only security would be locking up the records.

Objectives of database

v Organized data storage.

v Convenient retrieve of selected data.

v Presented the selected data in a convenient format.

v Safety and security of data.

v Integrity, sharing and availability of data.

The advantages of managing database information in access are

v It controls data redundancy which means duplication of data.

v It allows sharing the existing data by different programs

v Large volume of data can be stored and updated easily.

v It provides high security of data as well as maintains accurate database.

Definition DBMS (Database Management System)

DBMS is a software package that enables user to create and maintain a database. It also allows the user to modify, update, organize and retrieve information from the database.

I.e. DBMS = Database + Management System.

Where, Database is a collection of data and Management System is a set of programs to store and retrieve those data.

The primary goals of a DBMS are to provide a convenient environment to retrieve and store database information. For example: dBase, FoxPro, MySQL, MS SQL Server, Oracle, Sybase, MS-Access, clipper, DB/2, IBM DB2, File Maker etc.

Functions of DBMS

Provides data Independence
Concurrency Control
Provides Utility services
Inserting data into the database.
Retrieving/ viewing data from the database.
Updating data in existing database.
Deleting data from the database.
Removing file from the database.

Features/Advantages of DBMS

Large volume of data can be stored and updated easily.
It reduces the data redundancy (duplication of data).
It guarantees the consistency of the data.
Data independence.
Providing Backup and Recovery.
Restricting unauthorized access.

g. It allows sharing the existing database by different programs.

h. It provides high security of data as well as maintains accurate database.

Disadvantages of DBMS

Problem associate with centralized.
Cost of software, hardware and migration.
Complexity of backup and recovery.
Higher operating cost.
Need well trained person to handle it.

Applications of DBMS

a. Banking: For maintaining customer information, accounts, loans and banking transactions.

Universities: For maintaining student records, course registration and grades.

c. Airlines: For reservation and schedule information.

d. Railway Reservation: - For checking the availability of reservation in different trains, tickets etc.

e. Telecommunication: For keeping records of calls made, generating monthly bills etc.

f. Finance: - For storing information about holidays, sales and purchase of financial instruments.

g. Sales: - For customer, product and purchase information.

Types of DBMS

According to database structure, DBMS are following types:

a. Relational Database Management System (RDBMS)

b. Object-Oriented Database Management System (ODBMS)

c. Distributed Database Management System (DDBMS)

d. Hierarchical Database Management system (HDBMS)

Different database and DBMS

Database	DBMS
It is collection of related data and information	It is a software package to manage database
It consists of data	It manages data stores in database
It is a part of a DBMS	It is a software system which contains database
For example: Dictionary, phone book, attendance diary, result sheet etc	For example: FoxPro, dbase, MS-Access etc

Definition of RDBMS (Relational Database Management System)

RDBMS is most widely used software or program that stores database in multiple tables on the basis of a key field. It allows a user to view or retrieve records from the multiple linked tables continuously at a time.

For example: MS-Access, Oracle, MySQL, MS SQL Server etc.

Different between DBMS and RDBMS

DBMS	RDBMS
DBMS does not support client/ server architecture.	Most of the RDBMS supports client/ server architecture.
DBMS does not support distributed database.	Most of the RDBMS supports distributed database.
In DBMS there is no tight security of data.	In RDBMS there are multiple levels of security
Normalization is not present in DBMS.	Normalization is present in RDBMS.
DBMS does not impose integrity constraints.	RDBMS imposes integrity constraints.
It supports single user.	It supports multiple users.
For example: dBase, FoxPro, MySQL, MS SQL Server, Oracle, Sybase, MS-Access, clipper, DB/2, IBM DB2, File Maker etc.	For example: MS-Access, Oracle, MySQL, MS SQL Server etc.

MS-Access (Microsoft Access)

MS-Access is a RDBMS developed by Microsoft Corporation. It is used to store and manipulate large amount of data in the form of tables. It allows the user to create database and store data in multiple tables.

The file extension of MS-Access is .mdb (earlist version) and .accdb (latest version). An executable file name of MS-Access is msaccess.exe.

Objects of MS-Access are:

a) Table

b) Query

c) Form

d) Report

e) Pages

f) Macro

g) Modules

Features of MS-Access

a. It provides the flexible ways to add, edit, delete and display the related data.

b. Queries help to view, change and analyse the data in different ways.

c. Forms are used for viewing and editing the information.

d. Reports are used for summarizing and printing the data.
e. Present your data dynamically on World Wide Web.

Table: -

Table is primary object or building blocks of database that stores large volume of data in the form of rows and columns. A single table is used to store data of a specific purpose or subject.

The importance of table is:

a. Different properties of a field can be set in a table.

b. It provides options for primary key which helps to make data consistent.

There different ways to create a table in MS-Access are:

a. Create table in Design view.

b. Create table by using Wizard.

c. Create table by entering Data.

Elements of Database

a. Field: - A field is a piece of record about a person or place or thing. It is the smallest unit of the database. It is also known as attribute. In above table Id, Name, Address, Roll is known as fields.

· Record: - The collection of multiple related fields in a row is known as record. It gives complete information about a person or thing. It is also known as tuple. A collection of interrelated fields is called a record. For example: in table below contains 2 records and each record has 3 related fields namely SN, Name and Roll.

b. Tables: -A database table is composed of records and fields that hold data. It organizes the data in the rows and columns form. Where, a row defines a record and a column defines a field.

c. File:- File is the collection of all related records.

Query

Query is an object of database that is used to view, retrieve, change and analyze records from a table or multiple linked tables based on specified condition. When a user changes data in the query, the data in the table also changes. It can use as a source of records for forms and reports.

The purpose of query in MS-Access is:

a. To view records including some fields or all the fields of a table or multiple linked tables.

b. To perform mathematical calculation of a data.

c. To sort the records on the basis of one or more key fields.

d. To perform mass update, delete or append new records to a table.

The advantages of query are:

a. We can filter, perform calculations and summarize data.

b. Large volume of records can be updated or deleted at a same time.

c. It retrieves and display records including some fields or all the fields of a table or multiple linked tables.

The types of query are:

a. Select query: - Select query is a most common type of query that retrieves data from one or more tables and displayed the result in a datasheet. You can also use a select query to group records and calculate sums, counts and average, minimum and maximum.

b. Action query: - An action query is also type of query that makes changes or updates many records in just one operation. The different types of action queries are:

i. Delete query: -This query deletes a group of records from one or more tables.

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

iii. Append query: -Append query adds a group of records in the table to the end of the record.

iv. Make table query: -This query creates a new table from all or part of the data in one or more tables for a backup copy of a table.

c. Crosstab query: -This query is used to calculate and restructure data for easier analysis of your data. Crosstab query calculates a sum, average, count or other type of total.

d. Parameter query: -This query enables a user to enter different criteria each time when the query is run and displayed the records on the basis of the specified criteria. It is useful when you have to perform the same type of query often with different values.

e. SQL query: -An SQL query is a query that is created using SQL statement. SQL can be used in formulating interactive queries for handling data. The SQL standard also contains components for defining, altering, controlling, and securing data.

The way of creating queries:

a. Design view

b. Wizard

Form

Form is a type of database object that is primarily used to create an interface for entering data in a table or multiple linked tables. A form displays a complete record at a time, so it is more convenient to view and modify record through the form.

Types of form

a. Data entry

b. Control form/ switch board

c. Massage/ dialog box (display)

Different ways to design form are:

a. Creating form using Design view.

b. Creating form using Auto form tabular/ columnar.

c. Creating form using Form wizard.

Advantages of forms over table

· It allows to design the layout of field on screen in any arrangement.

· It shows only the information we want to see.

· It can display one complete record at a time.

A form has three views:

a. Design view

b. Form view

c. Datasheet view

Report

Report is a type of access database object that present data in effective way in a printed format. It allows a user to print documents according to the user specifications of the summarize information through queries or tables.

The importance’s of report are:

a. It displays the information the way we want to view it.

b. It presents the information retrieved through queries or tables.

c. It presents the information in designed layouts by adding necessary titles, setting font colour or font size, etc.

Types of report

a. Individual report

b. Grouping/ Subtotal report

Different ways to create report

a. Creating form using Design view.

b. Creating form using Form wizard.

Pages

Data entry screen for WebPages. The data access page is the user interface for the database. They are used in viewing and entering data from a website or internet.

Macros

A macro is a saved series of keystrokes that can be replayed on demand. Macros are small programs used to automate the repetitive task.

Simply, an action or set of actions you can use to automate tasks. Access macros executes a list of actions typically used to open files, display user prompts, print reports, or perform a variety of other activities limited only by your imagination.

Modules

A collection of declarations, statements and procedure stored together as one named unit is called module.

Front End database file contains the following object
Queries	Macros	Forms
Modules	Reports

Table Relationship

The logical linkage between two or more than two tables with the help of unique key field is known as relationship. To prevent the duplication of information in a database by repeating fields in more than one table, table relationships can be established.

Simply, relationship is an association of several entities in an entity relation model.

The types of table relationships are:

a. One to One: - Relationship between two primary keys of two tables. E.g. principal and college, department and head etc.

b. One to Many: - Relation between primary key and non-primary key of two related tables. E.g. class and students, father and children etc.

c. Many to Many: -A link table between two tables can create many relations with the help of primary key and non-primary key. For example: teachers and students, customer and items etc.

Data Type

A data type is a feature of a field that determines the kind of data that can be stored in the field. Each field can store data relating of only a single data type.

a. Text: - Text data type is default data type of MS-Access. It stores alphanumeric characters. The maximum storage size of text data type is 255 characters and default size is 50 in MS-Access 2003.

b. Memo: - Memo data type is also stores alphanumeric characters up to 65,535/64,000 characters. It is used for long text such as remarks, comments etc.

c. Number: - Number data type stores only numeric values. Long integer is the default number data type in MS-Access.

i. Byte: - 1 byte.

ii. Integer: - 2 bytes.

iii. Long integer: - 4 bytes.

iv. Single: - 4 bytes.

v. Double:-8 bytes.

d. Data/Time: - Date/Time data type stores only date and time value. Its storage size is 8 bytes.

e. Currency: - Currency data type stores only currency value. Its storage size is 8 bytes.

f. AutoNumber: - AutoNumber data type generates unique sequential number automatically. Its storage size is 4 bytes.

g. Yes/No (Logical): - Yes/No data type stores a logical value that can be only one of two possible values such as Yes/No, True/False, On/Off etc. its storages size is 1 bit.

h. OLE Object: - OLE Object data type allows pictures, graphics, sound, video, audio etc. its storage size is up to 1 GB.

i. Hyperlink: - Hyperlink data type stores hyperlink addresses of documents. Its storages up to 2048 characters.

j. Attachment: - Attachment is used to attach any document or any other file in the table.

k. Lookup Wizard: - Lookup Wizard data type displays the data from another table by using list box or combo box. Its storage size is 4 bytes.

The common field properties

a. Field size: - It allows a user to set the maximum number of characters that can be stored in a field.

b. Format: - It allows a user to specify the appearance of the value when displayed.

c. Input Mask: - It specifies a pattern and controls the value of a record for all data to be entered in the field.

d. Caption: - It allows a user to set an alternate name for the field. It can contain up to 2048 characters.

e. Default Value: - It specifies the value that is automatically entered in a field when a new record is added.

f. Validation Rule: - It controls and limits the values that can be accepted into a field. It can contain up to 2048 characters.

g. Validation text: - It specifies the message to be displayed to the user when the data enter in the field is not matched to the validation rule. It can contain up to 255 characters.

h. Required: - It specifies whether data must be entered in a field or not.

i. Indexed: - It speeds up searching and sorting of records based on a field.

Keys

A key is the data item that exclusively identifies a record.

a. Primary key: - A primary key is a field in a table which uniquely identifies each row/record in a database table.

A primary key’s main features are:

· Primary keys must contain unique values.

· A primary key column cannot have NULL/empty values.

· Primary key should not be duplicated/ redundant/ repeated value.

Note: -Primary keys enforce entity integrity by uniquely identifying entity instances.

b. Foreign key: - An attribute of one entity whose values depends on the primary key of another entity is called foreign key. Foreign keys enforce referential integrity by completing an association between two entities.

c. Candidate key: - Candidate keys are defined as the set of fields from which primary key can be selected. It is an attribute or set of attribute that can act as a primary key for a table to uniquely identify each record in that table.

d. Super key: -Super Key is defined as a set of attributes within a table that uniquely identifies each record within a table. Super Key is a superset of Candidate key.

e. Index key: - it speeds up the searches and sorting operations. A table can have index attached with it. The index is used to keep the data in organized form and helps faster retrieval of data.

Basic Database Terms

a. Entity: - Entity is the distinguishable objects of real world. For example: students, customer, employee etc.

b. Attributes: - They are the set of properties possessed by an entity. For example: Name, Address, Contact no. of student.

c. Tuple: - Each record row in a table is tuple.

d. Data processing: - it is defined as the processing of data to make it more usable and meaningful. Data processing is not a single step rather it is a series of steps that gets the data and result the information.

e. Database system:- A database system is a way or technique of collecting the similar information. It consists of two way parts: Database Application and Database Management System

· Database Application is a software package which provides the view, retrieve and update information stored in the DBMS.

· DBMS helps to organizes and maintains the information.

f. Sorting: - The process of arranging all the records in a table either ascending or descending order based on field or fields is known as sorting.

The advantages of sorting are:

a. It helps to find specific information quickly.

b. It helps to arrange data in alphabetical order.

g. Filtering: - The process of viewing required record of a table that matches the specified criteria is known as filtering.

h. Formatting the table: - The process of changing of appearance of a table using different options is known as formatting the table. For example: change the column width, row height, hiding columns, changing the font, style and size of the text and table border etc.

i. Database Administrator (DBA): - An information specialist who has responsibility for the database is called a database administrator. His/her duties fall into four major areas: Database planning, implementation, operation and security.

j. Referential integrity: - It is a set of rules used by RDBMS to make sure that the relationships between tables are valid and that the related data is not accidentally changed or deleted.

k. Write down the use of Lookup Wizard and Hyperlink data types.
The use of Lookup Wizard is that it creates a field that allows choosing a value from another table or from a list of values by using a list box or combo box.
The use of Hyperlink is that it stores hyper like addresses like email addresses, websites, database objects or other field.

l. Define indexing. Mention its types.
Indexing is one of the important properties of database that speed up searching and storing of records using on the field.
Its types are:

· Primary indexing

· Secondary indexing

· Clustering indexing

m. How is sorting different from filtering?

Sorting arranges the record either in ascending or descending order whereas filtering selects the required records and displays the result.

n. What are the different controls used in form?
The different controls used in form are text box, combo box, list box, check box, label, command bottom, tab control, etc.

Difference between:

Forms	Report
Forms are used to enter, view, and edit information.	Reports are used only to view information.
Forms are usually displayed on the screen.	Reports can be previewed on the screen, but they are usually printed.
Forms generally provide a detailed took at records and usually for the people who actually work with the database	Reports are often used to group and summarize data, and are often for the people who don’t work with the database but who use its information for other business task.

Select query	Action query
It is a query which extracts specific information from one or more tables	It is a query which makes changes to many records in just one operation.
It cannot make changes in tales.	It can do changes in tables by update, append.

Labels: Database Management System (MS-Access)

What & How to do

Friday, May 26, 2017