Database
Management System - SEE COMPUTER SCIENCE 2080
Data
Data are raw form
of any facts, figures or entities which does not give any meaning.
For example,
Aaradhya, 1000, account, balance etc. are raw data individually does not give
any meaning.
Information
Information is the
organized collection of inter-related data which has significant meaning.
For example, Aaradhya
has 1000 balance in his bank account gives significant meaning.
Database
A collection of
systematically organized inter-related data is called a database. The purpose
of data base is to store, organize and retrieve data. E.g. Dictionary, Marks
Ledger, Telephone Directory, Attendance Register etc.
Database
can be computerized and non-computerized (manual).
The database which
is managed in file or ledger without using computer system is called manual database
(non-computerized).
The database which
is managed by using computer system is called computerized database.
Difference
between computerized and non-computerized database
|
Computerized
Database |
Non-Computerized
Database |
|
It can store large
volume of data. |
It is limited by
physical storage available. |
|
We can search data very easily. |
It can take a while
to manually search through all of the records.
|
DBMS (Database management system)
DBMS is a
computerized system that stores data, processes them and provides information
in an organized form. E.g. MS-Access, Oracle, MySQL, Fox Pro etc.
Advantages of DBMS
a)
It
reduces data redundancy which means duplication of data.
b)
It
allows multiple users to access the same data simultaneously.
c)
Large
volume of data can be stored and updated easily.
d)
It
provides high security of data as well as maintains accurate database.
RDBMS (Relational Database
Management System)
RDBMS
is a type of DBMS that uses a relational model to organize and manage data,
allowing users to easily retrieve and manipulate it. E.g.: MySQL, Oracle, MS
Access etc.
MS-Access
MS-Access is a
relational database management system developed by Microsoft Corporation which
is used to store and manipulates large volume of data in multiple tables.
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 indifferent ways.
c)
Forms
are used for viewing and editing the information.
d)
Reports
are used for summarizing and printing the data.
Record
(Tuple)
A record is a row in a table which contains
information about single items in a database. Record is complete set of
information.
Field (Attributes)
A field is a column in a table which contains
information about a certain type for all records. Field is a smallest unit of
information.
Value
A value is the each specific piece of information in a
table.
|
Symbol_No |
Name |
Address |
Class |
|
25612 |
Ram Thapa |
Kathmandu |
10 |
|
25617 |
Shyam Shrestha |
Dhading |
8 |
|
25635 |
Rita Dhungel |
Pokhara |
10 |
Fields are : Symbol_No, Name, Address and Class
Records are : 25612,
Ram Thapa, Kathmandu, 10 ; 25617,
Shyam Shrestha, Dhading, 8 ; 25635, Rita Dhungel, Pokhara, 10
Values are: 25612, 25617, 25635, Ram Thapa, Shyam Shrestha,
Rita Dhungel, Kathmandu, Dhading, Pokhara, 10, 8, 10.
Data type
Data
type is an attribute for a field that determines the type of data that can be
stored in that field.
Data types of MS-Access:
|
Data Type |
Description |
Maximum
Data / Space |
Default Field Size |
|
Text |
Used
for text or combinations of text and numbers, as well as numbers those don't
require calculations, such as phone numbers |
Up
to 255 Characters |
255 |
|
Memo |
Lengthy
text or combinations of text and numbers. |
Up
to 64,000 / 65535 Characters |
|
|
Number |
Used
for data to be included in mathematical calculations. |
1,2,4
or 8 Bytes |
Long
Integer |
|
Date/Time |
Used
for dates and time data |
8
Bytes |
|
|
Currency |
Used
for currency values. |
8
Bytes |
|
|
Auto
Number |
Used
for unique sequential (incrementing by 1). |
4
Bytes |
|
|
Yes/No |
Used
for data that can be only one of two possible values, such as Yes/No (Boolean
values) |
1
Bit |
|
|
|
|
|
|
|
OLE
Object |
Used
for OLE objects (such as MS[1]Word documents,
MS-Excel spread sheets, pictures, sounds) |
Up
to about 1 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 settings or attributes that allow users to control various
aspects of data entry, validation, formatting, and behavior within the database.
|
Field
Properties |
Description |
|
Field Size |
Field
size is a field property which is used to set the maximum size for data
stored in the field that is set to the Text or Number data type. |
|
Caption |
Caption
is a field property which gives alternative name given for any field. The
maximum size for this is 2048 characters. |
|
Default
Value |
Default
Value is a field property that is displayed automatically for the field when we
add a new record to the table. |
|
Format |
Format
is a field property that allows to display data in a format different from
the way it is actually stored in a table. |
|
Input
Mask |
Input
mask is a field property that controls the value of a record and sets in a
specific format. |
|
Validation
Rule |
Validation
Rule is a field property which is used to limit the values that can be
entered into a field. |
|
Validation
Text |
Validation
Text is a field property which displays an error message that appears if the
data entered is invalid according to the specified validation rule. |
|
Required |
Required
is a field property which is used to specify whether a value is required in a
field or not. |
|
Indexed |
Indexed
is a field property which speeds up searching and sorting of records based on
a field. |
Objects of Ms Access
Table, Form, Query, Report, Pages,
Macros, and Modules
Table
Tables
are the primary building block of database which stores and manages large
volume of data into rows and column.
Importance of table
· Tables store and organize data efficiently, reducing data
redundancy and inconsistencies.
· Tables enable the use of data validation rules to ensure
data accuracy and completeness.
· Tables are essential for creating forms, queries, and
reports that extract useful insights and information from data.
· Tables provide security features to restrict access to
sensitive data and ensure data privacy.
Methods to create table
Design
view
Datasheet
view
Using
wizard
Design View – Related with table structure. We can
add, edit or delete field and its properties.
Datasheet View – Related with records. We can add,
modify, search or delete records.
Form
Form
is one of the MS-Access database objects which provides graphical interface to view,
modify and add data in a table or multiple linked tables.
Importance of form
· Forms provide an easy-to-use interface for data entry and
manipulation, improving data accuracy and completeness.
· Forms can include validation rules to ensure data quality
and prevent errors.
· Forms enable users to customize the look and feel of data
input screens.
· Forms can be linked to other objects, such as tables or
queries, to simplify data management.
Methods to create form
Design
view
Using
wizard
Report
Report
is one of the MS-Access database objects used to present information in an
effective and organized format that is ready for printing.
Importance of report
· Reports provide a formatted presentation of data that is
easy to read and understand.
· Reports enable users to summarize, analyze, and visualize
data in meaningful ways.
· Reports can be exported or printed for sharing with others.
· Reports can be based on tables, queries, or other reports,
providing flexibility and ease of use.
Why is Report created?
Reports are created in MS Access to
present data from one or more tables in a formatted and organized manner,
allowing users to easily view and analyze the information.
The data sources for
report are table and query
Methods to create report
Design
view
Using
wizard
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.
Queries
can be used to filter data, to perform calculations with data, and to summarize
data.
Its types are select query and action query.
Select query is a type of query which is used to select
and display the relevant data from the database.
Action query is a query that makes changes to or
removes many records in just one operation.
Examples of action query are update query and delete query.
An update query is a type of query that makes
entire changes to a record or group of records in one or more tables
A delete query is a type of query that deletes a
record or group of records from one or more tables.
An append query is a type of
action query used to add records from one or more tables or queries to another
table, based on specified criteria or selection rules.
A make-table query is a type of query used to create a new table based
on the results of a select query.
Advantages of query
· Queries enable users to retrieve specific data from tables
or other data sources, based on specified criteria.
· Queries allow users to filter, sort, and group data in
meaningful ways.
· Queries enable users to perform calculations and aggregate
functions on data to extract useful insights.
· Queries can be used to create reports and forms that extract
meaningful information from data.
Methods to create query
Design
view
Using
wizard
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. Sorted data is easier to
handle than unsorted data.
The advantages of sorting are:
· Sorting helps to
organize data and make it easier to find and retrieve specific information.
· Sorting can save
time and improve efficiency by allowing users to quickly access the data they
need.
Filtering
Filtering
is the process of viewing required record of a table that matches the specifies
criteria.
Primary key
A
primary key is a field or combination of fields in a table that uniquely
identifies each record, and is used to establish relationships between tables
and enforce data integrity.
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.
Importance of primary key
It
sets the relationship between tables.
It
reduces and controls duplication of record in a table
Foreign key
A
foreign key in MS Access is a field that establishes a relationship between two
tables by referencing the primary key of another table.
Composite Key
Composite
Key is the group of primary key that consists of two or more attributes.
Data Redundancy
· Data redundancy is
the duplication of data within a database.
· Data redundancy
can be controlled by normalizing the database, eliminating duplicate data and
storing it in separate tables, and using foreign keys to establish
relationships between the tables.
Relationship
A
relationship refers to the association or connection between two tables based
on a common field or fields. Its types are:
i) One to one relationship
ii) One to many relationship
iii) Many to many relationship
