2.1 Database Management System
Data
[SEE 2073] [SLC 2064] [SLC 2070] [[SLC
2065 S] [SLC 2069 S]
Data
can be numbers, letters or symbols representing facts and figures which may or
may not give any sense. E.g.: Ram, student, 20, etc.
Information
[SLC 2070]
Information is an organized
collection of related data, which gives a complete sense.
“Ram is a student. He is 20 years
old.”, is information that gives a complete sense.
Database
[SEE 2074] [SEE 2073] [SEE 2073 U] [SLC 2072]
[SEE 2071 S] [SLC 2064] [SLC
2065]
A database is an organized
collection of related data and information that can be used for different
purpose so that it can easily be accessed, managed, and updated.
E.g. Dictionary, Marks Ledger,
Telephone Directory etc.
DBMS
(Database Management System) [SEE 2075 U]
[SEE 2074 U] [SLC 2066] [SLC 2067]
Database
management system (DBMS) is a computerized system that stores data process them
and provide information in an organized form. E.g.: MS-Access, Oracle, Fox pro, Dbase etc.
Features of DBMS [SEE 2075] [SLC 2071]
a)
It
reduces data redundancy which means duplication of data.
b)
It
makes data searching easy and fast.
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) [SQE 2074]
RDBMS
is a database management system that is based on the relation model in which
data is stored in the form of tables and the relationship among the data is
also stored in the form of tables.
E.g.: SQL, MS-Access, Oracle, etc.
MS-Access
MS-Access
is RDBMS software developed by Microsoft Corporation which is used to collect, store
and manage information in multiple tables.
Features
of MS-Access
a)
It
provides the flexible ways to add, edit, delete and display the related data.
b)
Graphical user
interface made it easy to use
Objects
of MS-Access [SEE 2074 U] [SLC 2071]
a) Table
b) Form
c) Query
d) Report
Table
Table is an object of Ms-Access that stores large volume of
data in the form of rows and columns.
The different ways to create table are:
i) Design view
ii) Using wizard
Importance of table
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.
Record [SEE
2075 S2]
Record
is a collection of multiple related fields which contains information about
single items in a database. Record is complete set of information. E.g.: Ram Chabahil 4468790
Field [SEE
2075 S2]
A column in a table
is called field which contains information about a certain type for all records.
Field is a small unit of information. E.g.: Name, Address, Telephone
Primary
key [SEE 2075] [SEE 2073] [SEE 2071 S] [SLC 2067] [MM 2076] [PMT 2075K]
A primary key is the field that
uniquely identifies each record in a
database so that
there will not be repetition of data. It does not allow duplicate data and null
value.
Importance
of Primary Key [SEE 2073] [SLC
2069 S] [MM 2076]
a) To reduce and control duplication of record
in a table.
b) To set the relationship between
tables.
Composite
key
The group of primary key that
consists of two or more attributes is called composite key.
Foreign
Key
Foreign key is a unique field or
combination of fields in linked table whose value matches with a primary key in
another table.
Data
Redundancy [SEE 2074]
Data redundancy
means repetition of same piece of data in a database in more than one location.
Data redundancy can be controlled in MS-Access by normalization of database.
Data type [SEE 2075 S2] [SEE 2074] [SEE
2073] [SLC 2072] [SLC
2064] [SLC 2066] [SLC 2070]
Data type is an attribute for a
field that determines what type of data it can contain.
Any four data types of MS-Access
are:
i) Text
ii) Number
iii) Memo
iv) Currency
While
designing table structure which data types are suitable to store information
about teacher’s name, address, salary and date of birth. [SLC 2065] [SLC 2068 S]
Teacher’s
name - Text
Address – Text
Salary – Currency
Date of birth – Date/Time
Identify a record, field and value from
the following table structure. [SQE 2074]
Roll
Name
Marks
1
Kopila
87
2
Rabin Rana
56
Ans:
Record = 1 Kopila 87, 2 Prabin Rana
56
Field
= Roll, Name, Marks
Value
= 87, 56
What
happens when we enter text in a numeric field?
Ans: If we
enter text in a numeric field then it displays the errors.
Text
It is used for
storing text or combination of text and numbers. It can
store up to 255 characters.
Memo
It is used for storing lengthy text and numbers. It can
store up to 65,535 characters.
Field properties
The four types of field properties
of MS-Access are:
i) Caption ii) Format iii) Validation rule iv) Input mask
Caption
Caption is a
field property which displays an alternate name for the field to make the field
name more explanatory. It can contain up to 2048 characters.
Validation
rule:
A field property which enables to
limit values that can be accepted into a field is known as validation rule.
Validation
text
A field property which allows
type text to be displayed if the user input value is prohibited by the
validation rule is known as validation text.
Indexing
Indexing is one of
the important properties of database that speed up searching and storing of
records using on the field.
Relationship
Relationship is an
association among several entities (table). Its types are:
i)One to one relationship
ii)One to many relationship
iii)Many to many relationship
Sorting [SEE 2073 U] [SLC 2068]
Sorting is
the process of arranging the record in ascending or descending order according
to a given field or fields. Sorted data is easier to handle than unsorted data.
Sorting helps to find specific information quickly.
Filtering
Filtering is an
option that selects the required records and displays the result.
Query [SEE 2075 U]
[SEE 2075 S2] [SLC 2072] [SLC 2065] [SLC
2069] [SEE 2066 S] [SLC 2068 S]
Query is an object of Ms-Access that
is used to view, retrieve, change and analyze records from a table or multiple
linked tables based on specified condition. It is the question asked to
database.
The
different ways to create query are:
i) Design view
ii) Using wizard
The
different types of query are:
i) Select query
ii) Action query
In action query, we have update query, append query, delete query and
make-table query.
Select query
A select query is a query which is used
to select and display the relevant data from the database.
Action
query
Action query is a query which makes changes to many records
in just one operation.
Update query
Update query is a type of action query which is used to
change one or more field values in many records at once.
Append query
Append query is a type of action query which adds a group of
records in one or more tables to the end od records.
Delete Query
A delete query is a type of action query that removes a
record or group of records from one or more tables according to the specific
condition or criteria.
Make Table Query
A
make-table query is a type of action query which creates a new table from all
or part of the data in one or more tables.
Advantages of query [SLC 2065]
a)
We can filter, perform calculations
and summarize data.
b)
Large volume of records can be
updated or deleted at a same time.
Form [SEE 2073 U] [SLC 2064] [SLC
2066] [SLC 2068] [SLC 2065 S] [SLC
2069 S] [SQE 2075K]
Form is an
object of Ms-Access which provides graphical interface to
view, modify and add records.
The different ways to create form are:
i) Design view
ii) Using wizard
Importance of form [SEE 2075] [SLC
2064, 2066, 2065 S, 2070 S
i. We can view a complete record in specific format
ii. We can edit
record easily at a time.
Report [SEE 2074 U] [SEE 2071 S] [SLC
2070] [SLC 2067 S]
Report is
one of the MS-Access database objects used to present information in an effective
and organized format that is ready for printing. It is generated
from tables or queries.
The methods of creating report are:
i) Using design view
ii) Using report wizard
Why is
Report created? / Importance of report [SEE
2071 S] [SLC 2070] [SLC 2067 S]
[SEE 2075 U] [SLC 2067]
-to print documents according to
user specifications of the summarize information through queries or tables.
