DBMS EXAM QUESTIONS AND ANSWERS
what is database - Correct Answers -a collection of inter-related data
which helps in efficient retrieval, insertion and deletion of data and
organizes the data in the form of tables, views, schemas, reports etc.
provides proper security measures for protecting the data from unauthorized access.
In Database Management System the data can be fetched by SQL queries and
relational algebra
For Example, university database organizes the data about students, faculty, and admin
staff etc. which helps in efficient retrieval, insertion and deletion of data from it.
Data vs information - Correct Answers -Data is nothing but facts and statistics stored or
free flowing over a network, generally it's raw and unprocessed. For example: When
you visit any website, they might store you IP address, that is data, in return they might
add a cookie in your browser, marking you that you visited the website, that is data,
your name, it's data, your age, it's data.
Data becomes information when it is processed, turning it into something meaningful.
Like, based on the cookie data saved on user's browser, if a website can analyse that
generally men of age 20-25 visit us more, that is information, derived from the data
collected.
DDL - Correct Answers -is short name of Data Definition Language, which deals with
database schemas and descriptions, of how the data should reside in the database.
CREATE: to create a database and its objects like (table, index, views, store procedure,
function, and triggers)
ALTER: alters the structure of the existing database
DROP: delete objects from the database
TRUNCATE: remove all records from a table, including all spaces allocated for the
records are removed
COMMENT: add comments to the data dictionary
RENAME: rename an object
DML - Correct Answers -DML is short name of Data Manipulation Language which
deals with data manipulation and includes most common SQL statements such
SELECT, INSERT, UPDATE, DELETE, etc., and it is used to store, modify, retrieve,
delete and update data in a database.
SELECT: retrieve data from a database
INSERT: insert data into a table
UPDATE: updates existing data within a table
DELETE: Delete all records from a database table
,MERGE: UPSERT operation (insert or update)
CALL: call a PL/SQL or Java subprogram
EXPLAIN PLAN: interpretation of the data access path
LOCK TABLE: concurrency Control
Database Management System - Correct Answers -The software which is used to
manage database is called Database Management System (DBMS).
A DBMS is a software that allows creation, definition and manipulation of database,
allowing users to store, process and analyse data easily.
For Example, MySQL, Oracle etc. are popular commercial DBMS used in different
applications. DBMS allows users the following tasks:
advantages and disadvantages of DBMS - Correct Answers -Advantages of DBMS
Segregation of applicaion program.
Minimal data duplicacy or data redundancy.
Easy retrieval of data using the Query Language.
Reduced development time and maintainance need.
With Cloud Datacenters, we now have Database Management Systems capable of
storing almost infinite data.
Seamless integration into the application programming languages which makes it very
easier to add a database to almost any application or website.
Disadvantages of DBMS
It's Complexity
Except MySQL, which is open source, licensed DBMSs are generally costly.
They are large in size.
File System - Correct Answers -File System manages data using files in hard disk.
Users are allowed to create, delete, and update the files according to their requirement
example of file based University Management System. - Correct Answers -Data of
students is available to their respective Departments, Academics Section, Result
Section, Accounts Section, Hostel Office etc.
Some of the data is common for all sections like Roll No, Name, Father Name, Address
and Phone number of students but some data is available to a particular section only
like Hostel allotment number which is a part of hostel office.
Issues in a database management system - Correct Answers -Redundancy of data:
Data is said to be redundant if same data is copied at many places.
If a student wants to change Phone number, he has to get it updated at various
sections. Similarly, old records must be deleted from all sections representing that
student.
DBMS - gives primary key, foreign key constraint.
,Inconsistency of Data: Data is said to be inconsistent if multiple copies of same data
does not match with each other.
If Phone number is different in Accounts Section and Academics Section, it will be
inconsistent. Inconsistency may be because of typing errors or not updating all copies of
same data.
Difficult Data Access: A user should know the exact location of file to access data, so
the process is very cumbersome and tedious.
If user wants to search student hostel allotment number of a student from 10000
unsorted students' records, how difficult it can be.
Unauthorized Access: File System may lead to unauthorized access to data.
SECURITY ISSUES
If a student gets access to file having his marks, he can change it in unauthorized way.
DBMS - role based access control allowed
No Concurrent Access: The access of same data by multiple users at same time is
known as concurrency.
File system does not allow concurrency as data can be accessed by only one user at a
time. IN DBMS - RR allowed, RW, WR, WW not allowed
No Backup and Recovery: File system does not incorporate any backup and recovery of
data if a file is lost or corrupted.
Memory Efficient - not. a lot of data to be searched to see a small amount of data. 25
GB - 1 KB
Attributes - metadata is required
These are the main reasons which made a shift from file system to DBMS.
DBMS architecture - Correct Answers -A Database Management system is not always
directly available for users and applications to access and store data in it. A Database
Management system can be centralised(all the data stored at one location),
decentralised(multiple copies of database at different locations) or hierarchical,
depending upon its architecture.
1-tier DBMS architecture also exist, this is when the database is directly available to the
user for using it to store data.
Generally such a setup is used for local application development, where programmers
communicate directly with the database for quick response.
Database Architecture is logically of two types:
2-tier DBMS architecture
3-tier DBMS architecture
2-tier DBMS Architecture - Correct Answers -2-tier DBMS Architecture
2-tier DBMS architecture includes an Application layer between the user and the DBMS,
which is responsible to communicate the user's request to the database management
system and then send the response from the DBMS to the user.
An application interface known as ODBC(Open Database Connectivity) provides an API
that allow client side program to call the DBMS. Most DBMS vendors provide ODBC
drivers for their DBMS.
, Such an architecture provides the DBMS extra security as it is not exposed to the End
User directly. Also, security can be improved by adding security and authentication
checks in the Application layer too.
DBMS 3 tier structure - Correct Answers -Three Tier architecture:In this type, there is
another layer between the client and the server. The client does not directly
communicate with the server. Instead, it interacts with an application server which
further communicates with the database system and then the query processing and
transaction management takes place. This intermediate layer acts as a medium for
exchange of partially processed data between server and client. This type of
architecture is used in case of large web applications.
DBMS 3-tier architecture divides the complete system into three inter-related but
independent modules
1. Physical Level:
includes the query processing languages, all relations and their corresponding
constraints.
information about the location of database objects in the data store is kept.
Various users of DBMS are unaware of the locations of these objects.
describes how the data is being stored in secondary storage devices like disks and
tapes and also gives insights on additional storage details.
2. Conceptual Level:
At conceptual level, data is represented in the form of various database tables.
describes the structure of the db
also called Application Tier
For Example, STUDENT database may contain STUDENT and COURSE tables which
will be visible to users but users are unaware of their storage.Also referred as logical
schema,it describes what kind of data is to be stored in the database.
3. External Level:
An external level specifies a view of the data in terms of conceptual level tables.
Each external level view is used to cater to the needs of a particular category of users.
For Example, FACULTY of a university is interested in looking course details of
students, STUDENTS are interested in looking at all details related to academics,
accounts, courses and hostel details as well. So, different views can be gener
DBMS 3 tier - Correct Answers -3-tier DBMS architecture is the most commonly used
architecture for web applications.
It is an extension of the 2-tier architecture. In the 2-tier architecture, we have an
application layer which can be accessed programatically to perform various operations
on the DBMS. The application generally understands the Database Access Language
and processes end users requests to the DBMS.
what is database - Correct Answers -a collection of inter-related data
which helps in efficient retrieval, insertion and deletion of data and
organizes the data in the form of tables, views, schemas, reports etc.
provides proper security measures for protecting the data from unauthorized access.
In Database Management System the data can be fetched by SQL queries and
relational algebra
For Example, university database organizes the data about students, faculty, and admin
staff etc. which helps in efficient retrieval, insertion and deletion of data from it.
Data vs information - Correct Answers -Data is nothing but facts and statistics stored or
free flowing over a network, generally it's raw and unprocessed. For example: When
you visit any website, they might store you IP address, that is data, in return they might
add a cookie in your browser, marking you that you visited the website, that is data,
your name, it's data, your age, it's data.
Data becomes information when it is processed, turning it into something meaningful.
Like, based on the cookie data saved on user's browser, if a website can analyse that
generally men of age 20-25 visit us more, that is information, derived from the data
collected.
DDL - Correct Answers -is short name of Data Definition Language, which deals with
database schemas and descriptions, of how the data should reside in the database.
CREATE: to create a database and its objects like (table, index, views, store procedure,
function, and triggers)
ALTER: alters the structure of the existing database
DROP: delete objects from the database
TRUNCATE: remove all records from a table, including all spaces allocated for the
records are removed
COMMENT: add comments to the data dictionary
RENAME: rename an object
DML - Correct Answers -DML is short name of Data Manipulation Language which
deals with data manipulation and includes most common SQL statements such
SELECT, INSERT, UPDATE, DELETE, etc., and it is used to store, modify, retrieve,
delete and update data in a database.
SELECT: retrieve data from a database
INSERT: insert data into a table
UPDATE: updates existing data within a table
DELETE: Delete all records from a database table
,MERGE: UPSERT operation (insert or update)
CALL: call a PL/SQL or Java subprogram
EXPLAIN PLAN: interpretation of the data access path
LOCK TABLE: concurrency Control
Database Management System - Correct Answers -The software which is used to
manage database is called Database Management System (DBMS).
A DBMS is a software that allows creation, definition and manipulation of database,
allowing users to store, process and analyse data easily.
For Example, MySQL, Oracle etc. are popular commercial DBMS used in different
applications. DBMS allows users the following tasks:
advantages and disadvantages of DBMS - Correct Answers -Advantages of DBMS
Segregation of applicaion program.
Minimal data duplicacy or data redundancy.
Easy retrieval of data using the Query Language.
Reduced development time and maintainance need.
With Cloud Datacenters, we now have Database Management Systems capable of
storing almost infinite data.
Seamless integration into the application programming languages which makes it very
easier to add a database to almost any application or website.
Disadvantages of DBMS
It's Complexity
Except MySQL, which is open source, licensed DBMSs are generally costly.
They are large in size.
File System - Correct Answers -File System manages data using files in hard disk.
Users are allowed to create, delete, and update the files according to their requirement
example of file based University Management System. - Correct Answers -Data of
students is available to their respective Departments, Academics Section, Result
Section, Accounts Section, Hostel Office etc.
Some of the data is common for all sections like Roll No, Name, Father Name, Address
and Phone number of students but some data is available to a particular section only
like Hostel allotment number which is a part of hostel office.
Issues in a database management system - Correct Answers -Redundancy of data:
Data is said to be redundant if same data is copied at many places.
If a student wants to change Phone number, he has to get it updated at various
sections. Similarly, old records must be deleted from all sections representing that
student.
DBMS - gives primary key, foreign key constraint.
,Inconsistency of Data: Data is said to be inconsistent if multiple copies of same data
does not match with each other.
If Phone number is different in Accounts Section and Academics Section, it will be
inconsistent. Inconsistency may be because of typing errors or not updating all copies of
same data.
Difficult Data Access: A user should know the exact location of file to access data, so
the process is very cumbersome and tedious.
If user wants to search student hostel allotment number of a student from 10000
unsorted students' records, how difficult it can be.
Unauthorized Access: File System may lead to unauthorized access to data.
SECURITY ISSUES
If a student gets access to file having his marks, he can change it in unauthorized way.
DBMS - role based access control allowed
No Concurrent Access: The access of same data by multiple users at same time is
known as concurrency.
File system does not allow concurrency as data can be accessed by only one user at a
time. IN DBMS - RR allowed, RW, WR, WW not allowed
No Backup and Recovery: File system does not incorporate any backup and recovery of
data if a file is lost or corrupted.
Memory Efficient - not. a lot of data to be searched to see a small amount of data. 25
GB - 1 KB
Attributes - metadata is required
These are the main reasons which made a shift from file system to DBMS.
DBMS architecture - Correct Answers -A Database Management system is not always
directly available for users and applications to access and store data in it. A Database
Management system can be centralised(all the data stored at one location),
decentralised(multiple copies of database at different locations) or hierarchical,
depending upon its architecture.
1-tier DBMS architecture also exist, this is when the database is directly available to the
user for using it to store data.
Generally such a setup is used for local application development, where programmers
communicate directly with the database for quick response.
Database Architecture is logically of two types:
2-tier DBMS architecture
3-tier DBMS architecture
2-tier DBMS Architecture - Correct Answers -2-tier DBMS Architecture
2-tier DBMS architecture includes an Application layer between the user and the DBMS,
which is responsible to communicate the user's request to the database management
system and then send the response from the DBMS to the user.
An application interface known as ODBC(Open Database Connectivity) provides an API
that allow client side program to call the DBMS. Most DBMS vendors provide ODBC
drivers for their DBMS.
, Such an architecture provides the DBMS extra security as it is not exposed to the End
User directly. Also, security can be improved by adding security and authentication
checks in the Application layer too.
DBMS 3 tier structure - Correct Answers -Three Tier architecture:In this type, there is
another layer between the client and the server. The client does not directly
communicate with the server. Instead, it interacts with an application server which
further communicates with the database system and then the query processing and
transaction management takes place. This intermediate layer acts as a medium for
exchange of partially processed data between server and client. This type of
architecture is used in case of large web applications.
DBMS 3-tier architecture divides the complete system into three inter-related but
independent modules
1. Physical Level:
includes the query processing languages, all relations and their corresponding
constraints.
information about the location of database objects in the data store is kept.
Various users of DBMS are unaware of the locations of these objects.
describes how the data is being stored in secondary storage devices like disks and
tapes and also gives insights on additional storage details.
2. Conceptual Level:
At conceptual level, data is represented in the form of various database tables.
describes the structure of the db
also called Application Tier
For Example, STUDENT database may contain STUDENT and COURSE tables which
will be visible to users but users are unaware of their storage.Also referred as logical
schema,it describes what kind of data is to be stored in the database.
3. External Level:
An external level specifies a view of the data in terms of conceptual level tables.
Each external level view is used to cater to the needs of a particular category of users.
For Example, FACULTY of a university is interested in looking course details of
students, STUDENTS are interested in looking at all details related to academics,
accounts, courses and hostel details as well. So, different views can be gener
DBMS 3 tier - Correct Answers -3-tier DBMS architecture is the most commonly used
architecture for web applications.
It is an extension of the 2-tier architecture. In the 2-tier architecture, we have an
application layer which can be accessed programatically to perform various operations
on the DBMS. The application generally understands the Database Access Language
and processes end users requests to the DBMS.
