DBMS Study Guide – Questions & Accurate Solutions
What is database Right Ans - 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 Right Ans - 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 Right Ans - 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 Right Ans - 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 Right Ans - 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 Right Ans - 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 Right Ans - 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. Right Ans - 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 Right Ans - 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 Right Ans - 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 Right Ans - 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 Right Ans - 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:
What is database Right Ans - 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 Right Ans - 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 Right Ans - 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 Right Ans - 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 Right Ans - 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 Right Ans - 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 Right Ans - 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. Right Ans - 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 Right Ans - 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 Right Ans - 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 Right Ans - 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 Right Ans - 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:
