DBMS (Database Management System) [10 Marks]
.png)
1.8
DBMS (Database Management System)
Data
Data are raw form of any facts, figures or entities
which does not give any meaning.
Data does
not directly help in decision making.
For example, Aaradhya, 1000, account, balance etc. are
raw data individually does not give any meaning.
Information
Information is the processed, organized collection of
inter-related data which has significant meaning.
Information
directly helps in decision making.
For example, Aaradhya has 1000 balance in her bank
account gives 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.
Purpose of Database
Database is used to store, organize and retrieve data.
Database can be computerized and non-computerized
(manual).
Components of Database
v
Field
v Record
v
Table
Difference between computerized and
non-computerized database
|
Computerized Database |
Non-Computerized Database (Manual) |
|
A
computerized database is an organized collection of structured data stored
electronically on a computer system. |
The database which is managed in file or ledger
without using computer system is called manual database (non-computerized). |
|
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. |
|
Eg. Library Management System |
Eg. Telephone Directory |
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 / Computerized Database
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 which allows
users to store, manage, and analyze large volumes of data in a structured
format using tables.
MS Access provides tools for creating databases,
designing forms and reports, and running queries to retrieve and manipulate
data.
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 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 |
v
Fields
are : Symbol_No, Name, Address and Class
v Records are :
25612, Ram Thapa, Kathmandu, 10 ; 25617, Shyam Shrestha,
Dhading, 8 ; 25635, Rita Dhungel, Pokhara, 10
v
Values
are: 25612, 25617, 25635, Ram Thapa, Shyam Shrestha, Rita Dhungel, Kathmandu,
Dhading, Pokhara, 10, 8, 10.
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
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
v Field names should clearly describe the data they represent.
v Field names should consist of alphanumeric characters and
underscores (_) only.
Difference
between record and field
|
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. |
|
Eg. A
student's record containing name, age, and marks. |
E.g. "Name,"
"Age," and "Marks" are fields in the student's record. |
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
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.
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 |
|
Which data type is used to store
alpha numeric characters or special symbols in MS-Access? - Text
Which data type is suitable to store
photographs of students in MS-Access? - OLE
object
While designing table structure
which data types are suitable to store information about student's name and
date of birth?
Student’s Name uses text data type
and date of birth uses Date/Time data type
Which data types are used to store
graphics and numeric characters in MS-Access?
OLE
object is used for graphic and Number data type is used for numeric character
Which data type is used to store
your school fee in MS-Access? - Currency
Which data type is suitable to store
salary of an employee in MS-Access? - Currency
Which data type is used in MS-Access to store address of a
person? - Memo
Which data type is used to store
date of birth of an employee in MS-Access? - Date/Time
Which in the logical data type of
MS-Access? – Yes/No
Which data type of MS Access is used for
data that can store only one of two possible values? - 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 insert the data in table of MS-Access? - Datasheet
Write
any two elements of database. - Field and Record
Which
object of DBMS is used to print information? - Report
Which view is used to modify a table
structure in MS-Access? - Design View
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 field size of “AutoNumber” data type? - 4 Bytes
What
is the storage size of memo and text data type in MS- Access?
Text can store up to 255 characters
and Memo can store up to 65,535 characters
What is the storage size of text and
currency data type?
Ans:
The storage size of text data type is 255 and currency data type is 8 bytes.
What is the default size of number
field in MS-Access? - 4 bytes (Long Integer)
What is the maximum length of field
name in MS-Access? - 64 characters
Which
query is used to modify and calculate data in DBMS? – Action query
Which
type of query do you use to calculate the total marks of students from table in
MS Access? - Select Query
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? - Query
Which query is used to make changes in
data stored in database? - Action Query
Which object of DBMS is used to
print information? - 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
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. |
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.
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
What are the
different parts of a report?
The different
parts of report are: Report Header, Report Footer, Page Header, Page Footer etc
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.
What
type of work is done in MS-Access using form and query object?
Form provides graphical interface to view, modify and
add data in a table or multiple linked tables.
Query is used to
view, retrieve, change and analyze records from a table or multiple linked
tables based on specified condition
Methods to create
query
Design view Using wizard
Difference between
select query and action update query
|
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. |
Difference between
select query and action action query
|
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. |
Difference between
form and report
|
Form |
Report |
|
|
|
|
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 |
Report
is one of the MS-Access database objects used to present information in an
effective and organized format that is ready for printing. |
|
Data can be modified through the
form. |
Data can not be modified through report. |
Difference between
query and report
|
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.
Difference between
sorting and filtering
|
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. |
Differences between filtering and
querying.
|
Filtering |
Querying |
|
A filtering can be used to display all the data of a
single table based on a specified condition |
A querying can be used to display
selected data from one or more tables based on a specified condition or no
condition at all. |
|
Altering the data displayed
changes the original data of the table. |
Manipulating the data of a saved
query does not affect the original data. |
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 unique field in a database table
that identifies each record uniquely.
It ensures no duplicate records and helps in
establishing relationships between tables.
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.
Data
processing
Data
processing is the method of collecting raw data and translating it into usable
information.
Difference between primary key and
foreign key
|
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 is a connection between
two or more tables that defines how data in those tables is related to each
other. Relationships help ensure data integrity and enable to organize and
retrieve data efficiently.
Its
types are: i) One to one
relationship ii) One to many
relationship
iii) Many to many relationship
No comments:
Post a Comment