2.
Database Management System
(2.1)
Introduction
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.
|
Data |
Information |
|
Raw form of any facts, figures or entities are known as data. |
The processed form of data is known as information. |
|
Data alone does not give any meaning. |
When the data becomes information it gives meaningful result. |
|
Data does not directly helps in decision making. |
Information directly helps in decision making. |
|
For example, Aaradhya, 1000, account, balance etc. are raw data
individually does not give any meaning. |
For example, Aaradhya has 1000 balance in his bank account. Here
Aaradhya, 1000, account, balance all have their significant meaning. |
Database
A
collection of systematically organized inter-related data which store,
organize and retrieve data is called a database. 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).
A
computerized database is an organized collection of structured data stored
electronically on a computer system.
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.
|
Database |
DBMS |
|
A collection of systematically organized inter-related
data which store, organize and retrieve data is called a database. |
Database management system (DBMS) is a computerized system that stores
data, processes them and provides information in an organized form |
|
Besides computers, databases can even be maintained in physical
ledgers, books, or papers. |
In a database management system (DBMS), all the records are maintained
only on a computer. |
|
In the case of the databases, very less information can be modified at
a time. |
In the database management system (DBMS), a lot of information can be
changed at one time (as it can have many users using it at the same time). |
|
Eg. Dictionary, Marks Ledger, Telephone Directory, Attendance Register
etc. |
E.g. MS-Access, Oracle, MS-SQL Server, MySQL, PostgreSQL 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.
Define Field Name. Write any two rules for creating the
Field Name
A
field name refers to the name given to a specific data element within a
database table.
Any two
rules for creating the Field Names are:
Field
names should clearly describe the data they represent.
Field
names should consist of alphanumeric characters and underscores (_) only.
List the field name from the following table structure.
|
Symbol No. |
Name |
Marks |
|
00100200Q |
Surya Gurung |
85 |
|
00100201R |
Birendra Sharma |
91 |
Ans: The
field names are Symbol No., Name and Marks.
List the field name and records used in the following
table structure.
|
Symbol_No |
Name |
Marks |
|
001009010 |
Hari Thapa |
85 |
|
00100901Q |
Binu Sharma |
91 |
Ans:Field Names = Symbol_No, Name and Marks
Record = 00100900P Hari
Thapa 85 and 00100901Q Binu
Sharma 91
|
Record |
Field |
|
A record is a row in a table which contains information about single
items in a database. |
A field is a column in a table which contains information about a
certain type for all records. |
|
Record is complete set of information. |
Field is a smallest unit of information. |
|
It is also called tuple. |
It is also known as attributes |
|
For example, in Table – Book Records, all the information about a book
is record. Here, the combination of Code, Book Name, Level, Author, Year of
Publication values forms a record. |
For example, in Table – Book Records, Code, Book Name, Level, Author,
Year of Publication are the fields of book records table. |
Data type
Data
type is an attribute for a field that determines the type of data that can be
stored in that field.
What happens while we enter text in number data type?
If we enter text in a numeric field then it display an error message
indicating that the data type is incorrect
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 MSWord 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 |
|
Write a similarity and a difference between Number field
type and Currency filed type.
Ans: similarity and a
difference between Number field type and Currency field type
Similarity:
Both
store numerical values: Both field types are designed to store numerical
data.
Difference:
Currency
field type is specialized for handling monetary values by including specific
currency formats and precision, whereas the Number field type is more generic
and can store any numerical data without currency-specific formatting.
Which
data type is used to store alpha numeric characters or special symbols in
MS-Access?
Text data
type is used to store alpha numeric characters or special symbols in MS-Access.
Which
data type is suitable to store photographs of students in MS-Access?
The
suitable data type to store photographs of students in MS-Access is OLE object.
Which data types are used to store graphics and numeric
characters in MS-Access?
OLE
object is used for graphic and Numeric data type is used for numeric character
Which
data type is used to store your school fee in MS-Access?
Currency
is the data type used to store school fee in MS-Access.
Which
data type is suitable to store salary of an employee in MS-Access?
Ans:
Currency data type is suitable to store salary of an employee in MS-Access.
Which
data type is used in MS-Access to store address of a person?
Memo
data type is used in MS-Access to store address of a person.
Which
data type is used to store date of birth of an employee in MS-Access?
Date/Time
data type is used to store date of birth of an employee in MS-Access
Which in
the logical data type of MS-Access?
The
logical data type of MS-Access is Yes/No.
Which data type of MS Access is used for data that can
store only one of two possible values?
The data
type that can store only one of two possible values is Yes/No.
What
data type is commonly represented as Boolean in MS - Access?
-Yes/No
What is
the field size of yes/no field?
The
field size of YES/NO field is 1 bit.
Which
view is used to modify a table structure in MS-Access?
Design
View is used to modify a table structure in MS-Access.
What is
the default extension of MS-Access?
The
default extension of Ms-Access is : .mdb (2003 version) and .accdb (2007
version).
What is
the default size of text field in MS-Access?
The
default size of text field is : 50 (2003 version) and 255(2007 version).
What is
the default size of number field in MS-Access?
The
default size of number field in MS-Access is 4 bytes (Long Integer)
What is
the maximum length of field name in MS-Access?
The
maximum length of field name in MS-Access 64 characters.
Which
query is used to modify and calculate data in DBMS?
Update
query is used to modify and calculate data in DBMS.
Which view is used to modify a record in MS-Access?
Datasheet
view
Which object of MS-Access is used to retrieve data from the
table?
Select
Query
Which query is used to make changes in data stored in
database?
Update
Query
Which
object of MS-Access is used to retrieve data from the table?
Query is
used to retrieve data from a table.
Which
object of DBMS is used to print information?
The
object of DBMS which is used to print information is report.
What is the primary component of a database that organizes
data into rows and columns?
-Table
What is the primary building block of a database?
-Table
Which object is used to store the data in database?
-Table
Which object of MS Access is used to provide graphical
interface to enter data into a table?
-Form
Which query is used to view the data stored in database?
Select
query is used to view the data stored in database
What is
the purpose of a query in a database?
To ask
question about the data stored in the database.
Which
field property speeds up searching and sorting of records based on a field?
Indexing
What
happens while we enter text in number data type?
It does
not accept data and displays error message.
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
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.
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
It is important because entire data is managed and
kept in a table for the future retrieval process. It makes sure that the
information stays accurate and consistent.
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.
Is table a database?
No, Table is not a database. A table is an object inside a database.
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.
· Form displays complete record one at a time, so
we can view and modify records using the form.
· Forms provides GUI (Graphical Users Interface)
using which users interact with MS-Access database.
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.
It’s types are :
- Append
Query: Adds new records to an existing table.
- Update
Query: Modifies existing records in a table.
- Delete
Query: Deletes records from a table.
- Make
Table Query: Creates a new table based on data from one or more tables.
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.
Why is query object used in MS-Access? Give two reasons.
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.
Why update query is necessary in Database system? Give
reasons
Update Query is used to add, change, delete the information
in an existing record. It allows user to update data of a field at once. So, it
is necessary in MS Access database system.
Methods
to create query
Design
view
Using
wizard
|
Select Query |
Update Query |
|
- A select query is the most common category which is used to extract
specific information from one or more tables in a database. - The Select query is used to select data from a database. |
- Update query is a type of action query which makes global changes to
a group of records in one or more tables. - The Update query is used to update existing records in a table. |
|
Select Query |
Action Query |
|
Select query is
simply used to select and display the relevant data from the database. |
An action query is
a query that makes changes to or removes many records in just one operation. |
|
It does not make
change to database. |
It makes change to
database. |
|
A query does not
save data; rather, it displays information from tables. |
Action query
allows to preview the query results before performing it, which is a nice
feature. |
|
A select query is
a database item that presents information in Datasheet view. |
Multiple records
can be added, updated, or deleted simultaneously using action queries. |
|
Form |
Report |
|
Form is primarily
used for entering data |
Report is used for
presenting the data. |
|
Form is also used
for displaying records but one record at a |
Report is used for
displaying whole |
|
time. |
records. |
|
Data can be
modified through the form. |
Data can not be
modified through report. |
|
Form is designed
to be used on screen. |
Report are
designed to be printed. |
|
Query |
Report |
|
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 |
Report is one of the MS-Access database objects used to present
information in an effective and organized format that is ready for printing |
|
Queries help filter large amounts of
data into more manageable quantities. |
Reports present data in meaningful
formats so that conclusions and information about the data can be more easily
seen. |
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.
Any two examples of data sorting are:
Alphabetical sorting: Sorting a list of names in
alphabetical order, such as arranging a phonebook by last name.
Numerical sorting: Sorting a list of numbers in ascending
or descending order, such as arranging a list of exam scores from highest to
lowest.
How many ways to sort data in MS-Access?
We can sort data in MS-Access by ascending or descending
order on numbers or text.
Filtering
Filtering
is the process of viewing required record of a table that matches the specified
criteria.
|
Sorting |
Filtering |
|
The process of arranging all the records in a table either ascending or
descending order based on field or fields is known as sorting. |
Filtering is the process of viewing required record of a table that
matches the specifies criteria. |
|
Sorting affects the entire dataset. |
Filtering only affects the data that is displayed. |
|
Sorting is often used to make data easier to read. |
Filtering is used to find specific information. |
|
Sorting can be done in ascending or descending order. |
Filtering has various criteria that can be applied. |
What is
key in DBMS? Write any two names of keys available in
DBMS.
A key is a field or combination of
fields that uniquely identifies each record in a table.
Any two names of keys available in
DBMS are primary key and foreign key.
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
Why is primary key necessary in record?
A primary key is necessary in a database record because
it reduce and control duplication of the record in a table, sets the
relationship between tables and identifies each record of a table uniquely.
Write two examples of primary key fields.
Any two examples of primary key fields are SEE Registration
number, Username
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.
|
Primary key |
Foreign 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 |
A foreign key in MS Access is a field that establishes a relationship
between two tables by referencing the primary key of another table |
|
The primary key doesn’t allow null values. |
Foreign key accepts multiple null values. |
|
For example, in a table of employees, each employee might have an ID
number as their primary key. |
For example, in a table of orders, the foreign key might be the
customer ID number, linking the orders table to a table of customer
information. |
Data Redundancy
· Data redundancy refers to the repetition of
data in a database, where the same data is stored in multiple places.
· 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
What is data processing?
Data processing is the method of collecting raw data and
translating it into usable information.
What do you mean by Indexing? Write its importance.
Indexing
is a field property which speeds up searching and sorting of records based on a
field.
Its importance is it helps databases quickly find and
retrieve data, making queries run faster.
No comments:
Post a Comment