DBMS

COMPUTER

Copyright © 2014-2020 TestBook Edu Solutions Pvt. Ltd.: All rights reserved
 Download Testbook App

DBMS or Database
Management System

Meaning of Database (DB)

 Any piece of information can be referred to as a data and the place where this data
is stored in a way that it is easily accessible and can also be modifi ed
whenever required is known as a Database.

 DBMS is actually a tool or collection of programs that enables us to perform any kind
of operation on a data stored in a database. Database Management System allows
you to store, modify and extract information whenever required. It comprises of 4
essential components namely -

1. User: A user may be of any kind like a DB administrator, system

developer and an end user.

2. Database Applications: It may be departmental, personal computer or

an enterprise.

3. DBMS: software which allows us to manage the data stored in

database.

4. Database.

DBMS Examples
 RDBMS (Relational Database  MySQL
Management System)  PostgreSQL
 dBase  Microsoft Access
 Clipper  SQL Server
 FoxPro  Oracle
 FileMaker

COMPUTER | DBMS PAGE 2

 Download Testbook App

Types of DBMS
There are 4 major types of Database Management Systems:

1. Hierarchical Database - rarely used nowadays, uses one to many relations

for data elements.

2. Network Database - results in complex database structures as it uses many

to many relations.

3. Relational Database Management System (RDBMS) - most popular in the

DBMS market, does not support many to many relations. eg -MySQL, Oracle

4. Object Oriented Database Management System (OODBMS) - products

based on this type of DBMS is still in their infancy, it supports conversion of
data in the form of attributes. E.g.- PostgreSQL

DBMS - Architecture
 DBMS architecture helps in development, implementation, design, and maintenance
of a database that store and organize information for agencies, businesses, and
institutions.

 It is the base of any database management system, which allows it to perform the
functions effectively and efficiently.

 The whole concept of DBMS depends upon its architecture. It can be designed
as centralized, decentralized, or hierarchical.

Types of DBMS Architecture

 1-tier architecture

 2-tier architecture

 3-tier architecture

COMPUTER | DBMS PAGE 3

 Download Testbook App

In 1-tier architecture,

 In this architecture, the database is directly available to the user. This means that
the user can access the DBMS directly.

 The changes made here will be done directly on the database itself. It does not
provide handy tools for end users.

 1-tier architecture is used for local application development, where programmers

can directly communicate with the database for quick response.

2-tier Architecture

 The 2-tier architecture is similar to the basic client-server. In 2-tier architecture, the
applications on the client end can directly communicate with the database on the
server side. For this interaction, API's such as: ODBC, JDBC are used.

 User interface and application programs are run on the client-side.

 Server side is responsible for providing functionalities such as: query processing and
transaction management.

 To communicate with the DBMS, the client-side application establishes a connection

with the server side.

COMPUTER | DBMS PAGE 4

 Download Testbook App

3-tier Architecture

 In 3-Tier architecture, there is a layer between the client and the server. In this
architecture, the client cannot communicate with the server directly.

 At the client-end the application interacts with an application server which further
communicates with the database system.

 The end user has no idea about the existence of the database beyond the
application server. The database does not contain any information about any other
user beyond the application.

 3-tier architecture is used in case of large web applications.

Data Models
 A data model is a theory or specification describing how a database is structured
and used.

 A data model is not just a way of structuring data: it also defines a set of operations
that can be performed on the data.

COMPUTER | DBMS PAGE 5

 Download Testbook App

 The relational model, for example, defines operations such as select, and join.
Although these operations may not be explicit in a particular query language, they
provide the foundation on which a query language is built.

 Common Data Models:

 Hierarchical Model

 Relational Model

 Network Model

 Object Model (Object Oriented Database Management System)

Hierarchical Database Model

 Developed by IBM, is the Oldest database model.

 Represented using a tree-diagram. (Parent-child relationship)

 Each box is called a „Node‟

 The nodes represent a record type.

 A line connecting nodes represents the link.

 Parent-child type is suited for One-to-many relationship between two entities.

 But difficult to implement many-to-many relationship

COMPUTER | DBMS PAGE 6

 Download Testbook App

Relation Model
 The data is stored in two-dimensional tables (rows and columns). The data is
manipulated based on the relational theory of mathematics.

 Properties of Relational Tables:

 Values Are Atomic

 Each Row is Unique

 Column Values Are of the Same Kind

 The Sequence of Columns is Insignificant

 The Sequence of Rows is Insignificant

 Each Column Has a Unique Name

 A relational database management system (RDBMS) is a DBMS that is based on

the relational model.

 Some well known RDBMS: IBM DB2, Informix, Microsoft SQL Server, Microsoft
Visul Foxpro, MySQL, Oracle, Sybase, Teradata, Microsoft Access

COMPUTER | DBMS PAGE 7

 Download Testbook App

Network Database Model

A network model is a database model that is designed as a flexible approach to
representing objects and their relationships. A unique feature of the network model is its
schema, which is viewed as a graph where relationship types are arcs and object types
are nodes.

Advantages
The major advantage of network model are-

1.) Conceptual simplicity-Just like the hierarchical model,the network model is also
conceptually simple and easy to design.

2.) Capability to handle more relationship types-The network model can handle the
one to many and many to many relationships which is real help in modeling the real life
situations.

3.) Ease of data access-The data access is easier and flexible than the hierarchical
model.

4.) Data integrity- The network model does not allow a member to exist without an
owner.

5.) Data independence- The network model is better than the hierarchical model in
isolating the programs from the complex physical storage details.

COMPUTER | DBMS PAGE 8

 Download Testbook App

Disadvantages
1.) System complexity- All the records are maintained using pointers and hence the
whole database structure becomes very complex.

2.) Operational Anomalies- The insertion,deletion and updating operations of any

record require large number of pointers adjustments.

3.) Absence of structural independence-structural changes to the database is very

difficult.

Object Model
 The ODBMS which is an abbreviation for object-oriented database management
system is the data model in which data is stored in form of objects, which are
instances of classes. These classes and objects together make an object-
oriented data model.

 An object-oriented database (OOD) is a database system that can work with

complex data objects — that is, objects that mirror those used in object-
oriented programming languages. In object-oriented programming, everything is
an object, and many objects are quite complex, having different properties and
methods.

Advantages

 Add semantic content

 Visual presentation includes semantic content

 Database integrity

 Both structural and data independence

Disadvantages

 Lack of OODM standards

 Complex navigational data access

COMPUTER | DBMS PAGE 9

 Download Testbook App

 Steep learning curve

 High system overhead slows transactions

Database Languages
 Database languages are used to read, update and store data in a database.
Database Languages are the set of statements, that are used to define and
manipulate a database.

 Database Languages are used to create and maintain database on computer.

There are large numbers of database languages like Oracle, MySQL, MS Access,
dBase, FoxPro etc. SQL statements commonly used in Oracle and MS Access can
be categorized as data definition language (DDL), data control language (DCL) and
data manipulation language (DML).

 A Database language has Data Definition Language (DDL), which is used to

construct a database & it has Data Manipulation Language (DML), which is used to
access a database.

 Data Definition Language. DDL stands for Data Definition Language. ...

 Data Manipulation Language. DML stands for Data Manipulation Language.

 Data Control Language. DCL stands for Data Control Language. ...

 Transaction Control Language. TCL is used to run the changes made by the
DML statement.

COMPUTER | DBMS PAGE 10

 Download Testbook App

DDL (Data Definition Language)

 DDL or Data Definition Language actually consists of the SQL commands that can
be used to define the database schema.

 It simply deals with descriptions of the database schema and is used to create and
modify the structure of database objects in the database.

Examples of DDL commands:

 CREATE – is used to create the database or its objects (like table, index, function,
views, store procedure and triggers).

 DROP – is used to delete objects from the database.

 ALTER-is used to alter the structure of the database.

 TRUNCATE–is used to remove all records from a table, including all spaces
allocated for the records are removed.

 COMMENT –is used to add comments to the data dictionary.

 RENAME –is used to rename an object existing in the database.

DML(Data Manipulation Language):

 The SQL commands that deals with the manipulation of data present in the database
belong to DML or Data Manipulation Language and this includes most of the SQL
statements.

Examples of DML:

 INSERT – is used to insert data into a table.

 UPDATE – is used to update existing data within a table.

 DELETE – is used to delete records from a database table.

DCL(Data Control Language):

 DCL includes commands such as GRANT and REVOKE which mainly deal with the
rights, permissions and other controls of the database system.

COMPUTER | DBMS PAGE 11

 Download Testbook App

Examples of DCL commands:

 GRANT- gives users access privileges to the database.

 REVOKE- withdraw user’s access privileges given by using the GRANT command.

TCL(transaction Control Language)

 TCL commands deal with the transaction within the database.

Examples of TCL commands:

 COMMIT– commits a Transaction.

 ROLLBACK– rollbacks a transaction in case of any error occurs.

 SAVEPOINT–sets a savepoint within a transaction.

 SET TRANSACTION–specify characteristics for the transaction.

Database Schema
 A database schema is the skeleton structure that represents the logical view of the
entire database. It defines how the data is organized and how the relations among
them are associated. It formulates all the constraints that are to be applied on the
data.

 A database schema defines its entities and the relationship among them. It contains
a descriptive detail of the database, which can be depicted by means of schema
diagrams. It’s the database designers who design the schema to help programmers
understand the database and make it useful.

There are mainly three levels of data abstraction:

1. Internal Level: Actual PHYSICAL storage structure and access paths.

2. Conceptual or Logical Level: Structure and constraints for the entire database

3. External or View level: Describes various user views

COMPUTER | DBMS PAGE 12

 Download Testbook App

 Physical Database Schema: This schema pertains to the particular storage of

information and it’s kind of storage like files, indices, etc. It defines how the
information will be stored in an exceedingly auxiliary storage.

 Conceptual or Logical Level: This schema defines all the logical constraints that
require to be applied on the information stored. It defines tables, views, and integrity
constraints.

 External or View level: View schema is outlined because of the style of a database at
view level that usually describes end-user interaction with database systems.

Database Management System History

 DBMS has evolved a lot since its birth in 1960s

 Beginning with Networking & Hierarchical databases developed by Charles

[Link].

 Relational Model was proposed by Ted Codd in 1970.

 Entity - Relational Model defi ned by Peter Chen in 1976.

COMPUTER | DBMS PAGE 13

 Download Testbook App

 Maturation of Relational Database and SQL took place in 1970.

 Object Oriented Database developed in 1985.

 Finally first Internet database applications were created in 1995.

 In today's time we use Structured Query Language (SQL), Not only SQL (NoSQL)
and Cloud database.

Functions of DBMS
 Manages data dictionaries that stores definition of various data elements and their
relationships.

 Provides storage for not only a data but also all related data like procedural codes,
data validation and entry forms.

 Transforms and presents data according to the user’s expectations.

 DBMS provides data privacy by using security systems in a multi user database
interface.

 Allows multiple users to access any data at the same point of time without
any discrepancies.

 Ensures easy data recovery and back up in order to protect its integrity.

 It allows users to communicate and transact via emails and many other
communication modes.

Database Management System Purpose

 Database Management System is built to overcome the drawbacks of typical file
processing systems.

COMPUTER | DBMS PAGE 14

 Download Testbook App

 DBMS has reduced the data inconsistencies and difficulty in accessing data.

 It supports concurrent multiple users and ensures security of data.

 It also solves the integrity problem.

Important Questions
Q. What is database?
A. A database is a logically coherent collection of data with some inherent meaning,
representing some aspect of real world and which is designed, built and populated
with data for a specific purpose.

Q. What is DBMS?
A. A database is a logically coherent collection of data with some inherent meaning,
representing some aspect of real world and which is designed, built and populated
with data for a specific purpose.

Q. What is database system?

A. The database and DBMS software together is called as Database system.

Q. What are the advantages of DBMS?

 Redundancy is controlled.
 Unauthorized access is restricted.
 Providing multiple user interfaces.
 Enforcing integrity constraints.
 Providing backup and recovery.

Q. What are the disadvantage in File Processing System?

 Data redundancy and inconsistency.
 Difficult in accessing data.
 Data isolation.
 Data integrity.
 Concurrent access is not possible.
 Security Problems.

COMPUTER | DBMS PAGE 15

 Download Testbook App

Q. Define the "integrity rules"?

A. There are two Integrity rules.
 Entity Integrity: States that "Primary key cannot have NULL value"
 Referential Integrity: States that "Foreign Key can be either a NULL value or
should be Primary Key value of other relation.

Q. Describe the three levels of data abstraction?

A. There are three levels of abstraction:
1. Physical level: The lowest level of abstraction describes how data are stored.
2. Logical level: The next higher level of abstraction, describes what data are
stored in database and what relationship among those data.
3. View level: The highest level of abstraction describes only part of entire
database.

Q. What is extension and intension?

 Extension: It is the number of tuples present in a table at any instance. This is
time dependent.
 Intension: It is a constant value that gives the name, structure of table and the
constraints laid on it.

Q. What is System R? What are its two major subsystems?

A. System R was designed and developed over a period of 1974-79 at IBM San Jose
Research Center. It is a prototype and its purpose was to demonstrate that it is
possible to build a Relational System that can be used in a real life environment to
solve real life problems, with performance at least comparable to that of existing
system. Its two subsystems are
 Research Storage
 System Relational Data System.

Q. How is the data structure of System R different from the relational structure?
A. Unlike Relational systems in System R
 Domains are not supported
 Enforcement of candidate key uniqueness is optional
 Enforcement of entity integrity is optional
 Referential integrity is not enforced

COMPUTER | DBMS PAGE 16

 Download Testbook App

Q. What is Data Model?

A. A collection of conceptual tools for describing data, data relationships data
semantics and constraints.

Q. What is an Entity?
A. It is a 'thing' in the real world with an independent existence.

Q. What is Data Independence?

A. Data independence means that "the application is independent of the storage
structure and access strategy of data". In other words, the ability to modify the
schema definition in one level should not affect the schema definition in the next
higher level. Two types of Data Independence:
 Physical Data Independence: Modification in physical level should not affect
the logical level.
 Logical Data Independence: Modification in logical level should affect the
view level.
NOTE: Logical Data Independence is more diffi cult to achieve

Q. What is a view? How it is related to data independence?

A. A view may be thought of as a virtual table, that is, a table that does not really exist
in its own right but is instead derived from one or more underlying base table. In
other words, there is no stored file that direct represents the view instead a definition
of view is stored in data dictionary. Growth and restructuring of base tables is not
reflected in views. Thus the view can insulate users from the effects of restructuring
and growth in the database. Hence accounts for logical data independence.

Q. What is E-R model?

A. This data model is based on real world that consists of basic objects called entities
and of relationship among these objects. Entities are described in a database by a
set of attributes.

Q. What is Object Oriented model?

A. This model is based on collection of objects. An object contains values stored in
instance variables with in the object. An object also contains bodies of code that
operate on the object. These bodies of code are called methods. Objects that
contain same types of values and the same methods are grouped together into
classes.

COMPUTER | DBMS PAGE 17

 Download Testbook App

Q. What is an Entity type?

A. It is a collection (set) of entities that have same attributes.

Q. What is an Entity set?

A. It is a collection of all entities of particular entity type in the database.

Q. What is an Extension of entity type?

A. The collections of entities of a particular entity type are grouped together into an
entity set.

Q. What is Weak Entity set?

A. An entity set may not have sufficient attributes to form a primary key, and its primary
key compromises of its partial key and primary key of its parent entity, then it is said
to be Weak Entity set.

Q. What is an attribute?
A. It is a particular property, which describes the entity.

Q. What is a Relation Schema and a Relation?

A. A relation Schema denoted by R(A1, A2, ..., An) is made up of the relation name R
and the list of attributes Ai that it contains. A relation is defined as a set of tuples. Let
r be the relation which contains set tuples (t1, t2, t3, ..., tn). Each tuple is an ordered
list of n-values t=(v1,v2, ..., vn).

Q. What is degree of a Relation?

A. It is the number of attribute of its relation schema.

Q. What is Relationship?
A. It is an association among two or more entities.

Q. What is Relationship set?

A. The collection (or set) of similar relationships.

Q. What is Relationship type?

A. Relationship type defines a set of associations or a relationship set among a given
set of entity types.

COMPUTER | DBMS PAGE 18

 Download Testbook App

Q. What is degree of Relationship type?

A. It is the number of entity type participating.

Q. What is DDL (Data Definition Language)?

A. A data base schema is specified by a set of definitions expressed by a special
language called DDL.

Q. What is VDL (View Definition Language)?

A. It specifies user views and their mappings to the conceptual schema.

Q. What is SDL (Storage Definition Language)?

A. This language is to specify the internal schema. This language may specify the
mapping between two schemas.

Q. What is Data Storage - Definition Language?

A. The storage structures and access methods used by database system are specified
by a set of definition in a special type of DDL called data storage-definition
language.

Q. What is DML (Data Manipulation Language)?

A. This language that enable user to access or manipulate data as organized by
appropriate data model.
 Procedural DML or Low level: DML requires a user to specify what data are
needed and how to get those data.
 Non-Procedural DML or High level: DML requires a user to specify what data
are needed without specifying how to get those data.

Q. What is DML Compiler?

A. It translates DML statements in a query language into low-level instruction that the
query evaluation engine can understand.

Q. What is Query evaluation engine?

A. It executes low-level instruction generated by compiler.

Q. What is DDL Interpreter?

A. It interprets DDL statements and record them in tables containing metadata.

COMPUTER | DBMS PAGE 19

 Download Testbook App

Q. What is Record-at-a-time?
A. The Low level or Procedural DML can specify and retrieve each record from a set of
records. This retrieve of a record is said to be Record-at-a-time.

Q. What is Set-at-a-time or Set-oriented?

A. The High level or Non-procedural DML can specify and retrieve many records in a
single DML statement. This retrieve of a record is said to be Set-at-a-time or Set-
oriented.

Q. What is Relational Algebra?

A. It is procedural query language. It consists of a set of operations that take one or two
relations as input and produce a new relation.

Q. What is Relational Calculus?

A. It is an applied predicate calculus specifically tailored for relational databases
proposed by E.F. Codd. E.g. of languages based on it are DSL ALPHA, QUEL.

Q. How does Tuple-oriented relational calculus differ from domain-oriented

relational calculus?
 The tuple-oriented calculus uses a tuple variables i.e., variable whose only
permitted values are tuples of that relation. E.g. QUEL
 The domain-oriented calculus has domain variables i.e., variables that range
over the underlying domains instead of over relation. E.g. ILL, DEDUCE.

Q. What is normalization?
A. It is a process of analyzing the given relation schemas based on their Functional
Dependencies (FDs) and primary key to achieve the properties
1. Minimizing redundancy,
2. Minimizing insertion, deletion and update anomalies.

Q. What is Functional Dependency?

A. A Functional dependency is denoted by X Y between two sets of attributes X and Y
that are subsets of R specifies a constraint on the possible tuple that can form a
relation state r of R. The constraint is for any two tuples t1 and t2 in r if t1[X] = t2[X]
then they have t1[Y] = t2[Y]. This means the value of X component of a tuple
uniquely determines the value of component Y.

COMPUTER | DBMS PAGE 20

 Download Testbook App

Q. What is 1 NF (Normal Form)?

A. The domain of attribute must include only atomic (simple, indivisible) values.

Q. What is Fully Functional dependency?

A. It is based on concept of full functional dependency. A functional dependency X Y is
fully functional dependency if removal of any attribute A from X means that the
dependency does not hold any more.

Q. What is 2NF?
A. A relation schema R is in 2NF if it is in 1NF and every non-prime attribute A in R is
fully functionally dependent on primary key.

Q. What is 3NF?
A. A relation schema R is in 3NF if it is in 2NF and for every FD X A either of the
following is true
 X is a Super-key of R.
 A is a prime attribute of R.
In other words, if every non-prime attribute is non-transitively dependent on primary key.

Q. What is BCNF (Boyce-Codd Normal Form)?

A. A relation schema R is in BCNF if it is in 3NF and satisfies an additional constraint
that for every FD X A, X must be a candidate key.

Q. What is 4NF?
A. A relation schema R is said to be in 4NF if for every multivalued dependency X Y
that holds over R, one of following is true.
 X is subset or equal to (or) XY = R.
 X is a super key.

Q. What is 5NF?
A. A Relation schema R is said to be 5NF if for every join dependency {R1, R2, ..., Rn}
that holds R, one the following is true
 Ri = R for some i.
 The join dependency is implied by the set of FD, over R in which the left side
is key of R.

COMPUTER | DBMS PAGE 21

 Download Testbook App

Q. What is Lossless join property?

A. It guarantees that the spurious tuple generation does not occur with respect to
relation schemas after decomposition.

Q. What is Domain-Key Normal Form?

A. A relation is said to be in DKNF if all constraints and dependencies that should hold
on the constraint can be enforced by simply enforcing the domain constraint and key
constraint on the relation.

Q. What are partial, alternate, artificial, compound and natural key?

 Partial Key: It is a set of attributes that can uniquely identify weak
entities and that are related to same owner entity. It is sometime called as
Discriminator.
 Alternate Key: All Candidate Keys excluding the Primary Key are known
as Alternate Keys.
 Artificial Key: If no obvious key, either stand alone or compound is
available, then the last resort is to simply create a key, by assigning a unique
number to each record or occurrence. Then this is known as developing an
artificial key.
 Compound Key: If no single data element uniquely identifi es
occurrences within a construct, then combining multiple elements to create a
unique identifier for the construct is known as creating a compound key.
 Natural Key: When one of the data elements stored within a construct is
utilized as the primary key, then it is called the natural key.

Q. What is indexing and what are the different kinds of indexing?

A. Indexing is a technique for determining how quickly specific data can be found.
Types:
 Binary search style indexing
 B-Tree indexing
 Inverted list indexing
 Memory resident table
 Table indexing

Q. What is system catalog or catalog relation? How is better known as?

A. A RDBMS maintains a description of all the data that it contains, information about
every relation and index that it contains. This information is stored in a collection of
relations maintained by the system called metadata. It is also called data dictionary.

COMPUTER | DBMS PAGE 22

 Download Testbook App

Q. What is meant by query optimization?

A. The phase that identifies an efficient execution plan for evaluating a query that has
the least estimated cost is referred to as query optimization.

Q. What is durability in DBMS?

A. Once the DBMS informs the user that a transaction has successfully completed, its
effects should persist even if the system crashes before all its changes are reflected
on disk. This property is called durability.

Q. What do you mean by atomicity and aggregation?

 Atomicity: Either all actions are carried out or none are. Users should not
have to worry about the effect of incomplete transactions. DBMS ensures this
by undoing the actions of incomplete transactions.
 Aggregation: A concept which is used to model a relationship between a
collection of entities and relationships. It is used when we need to express a
relationship among relationships.

Q. What is a Phantom Deadlock?

A. In distributed deadlock detection, the delay in propagating local information might
cause the deadlock detection algorithms to identify deadlocks that do not really
exist. Such situations are called phantom deadlocks and they lead to unnecessary
aborts.

Q. What is a checkpoint and when does it occur?

A. A Checkpoint is like a snapshot of the DBMS state. By taking checkpoints, the
DBMS can reduce the amount of work to be done during restart in the event of
subsequent crashes.

Q. What are the different phases of transaction?

A. Different phases are
 Analysis phase
 Redo Phase
 Undo phase

Q. What do you mean by flat file database?

A. It is a database in which there are no programs or user access languages. It has no
cross-file capabilities but is user-friendly and provides user-interface management.

COMPUTER | DBMS PAGE 23

 Download Testbook App

Q. What is "transparent DBMS"?

A. It is one, which keeps its Physical Structure hidden from user.

Q. What is a query?
A. A query with respect to DBMS relates to user commands that are used to interact
with a data base. The query language can be classified into data definition language
and data manipulation language.

Q. What do you mean by Correlated sub query?

 Sub queries, or nested queries, are used to bring back a set of rows to be
used by the parent query. Depending on how the sub query is written, it can
be executed once for the parent query or it can be executed once for each
row returned by the parent query. If the sub query is executed for each row of
the parent, this is called a correlated sub query.
 A correlated sub query can be easily identified if it contains any references to
the parent sub query columns in its WHERE clause. Columns from the sub
query cannot be referenced anywhere else in the parent query. The following
example demonstrates a non-correlated sub query.
 Example: Select * From CUST Where '10/03/1990' IN (Select ODATE From
ORDER Where [Link] = [Link])

Q. What are the primitive operations common to all record management

systems?
A. Addition, deletion and modification.

Q. Name the buffer in which all the commands that are typed in are stored?
A. 'Edit' Buffer.

Q. What are the unary operations in Relational Algebra?

A. Projection and Selection.

Q. Are the resulting relations of PRODUCT and JOIN operation the same?
A. No.
 Product: Concatenation of every row in one relation with every row in
another.
 Join: Concatenation of rows from one relation and related rows from
another.

COMPUTER | DBMS PAGE 24

 Download Testbook App

Q. Which part of the RDBMS takes care of the data dictionary? How?
A. Data dictionary is a set of tables and database objects that is stored in a special
area of the database and maintained exclusively by the kernel.

Q. What is RDBMS KERNEL?

A. Two important pieces of RDBMS architecture are the kernel, which is the software,
and the data dictionary, which consists of the system-level data structures used by
the kernel to manage the database You might think of an RDBMS as an operating
system (or set of subsystems), designed specifically for controlling data access; its
primary functions are storing, retrieving, and securing data. An RDBMS maintains its
own list of authorized users and their associated privileges; manages memory
caches and paging; controls locking for concurrent resource usage; dispatches and
schedules user requests; and manages space usage within its table-space
structures.

Q. Name the sub-systems of a RDBMS.

A. I/O, Security, Language Processing, Process Control, Storage Management,
Logging and Recovery, Distribution Control, Transaction Control, Memory
Management, Lock Management.

Q. Which part of the RDBMS takes care of the data dictionary? How?
A. Data dictionary is a set of tables and database objects that is stored in a special
area of the database and maintained exclusively by the kernel.

Q. What is the job of the information stored in data-dictionary?

A. The information in the data dictionary validates the existence of the objects, provides
access to them, and maps the actual physical storage location.

Q. How do you communicate with an RDBMS?

A. You communicate with an RDBMS using Structured Query Language (SQL). Define
SQL and state the differences between SQL and other conventional programming
Languages. SQL is a nonprocedural language that is designed specifically for data
access operations on normalized relational database structures. The primary
difference between SQL and other conventional programming languages is that SQL
statements specify what data operations should be performed rather than how to
perform them.

COMPUTER | DBMS PAGE 25

 Download Testbook App

Q. What is Storage Manager?

A. It is a program module that provides the interface between the low-level data stored
in database, application programs and queries submitted to the system.

Q. Name the three major set of files on disk that compose a database in Oracle.
A. There are three major sets of files on disk that compose a database. All the files are
binary. These are
 Database files
 Control files
 Redo logs
The most important of these are the database files where the actual data resides.
The control files and the redo logs support the functioning of the architecture
itself. All three sets of files must be present, open, and available to Oracle for
any data on the database to be useable. Without these files, you cannot access
the database, and the database administrator might have to recover some or all
of the database using a backup, if there is one.

Q. What is database Trigger?

A. A database trigger is a PL/SQL block that can defined to automatically execute for
insert, update, and delete statements against a table. The trigger can e defined to
execute once for the entire statement or once for every row that is inserted, updated,
or deleted. For any one table, there are twelve events for which you can define
database triggers. A database trigger can call database procedures that are also
written in PL/SQL.

Q. What are stored-procedures? And what are the advantages of using them?
A. Stored procedures are database objects that perform a user defined operation. A
stored procedure can have a set of compound SQL statements. A stored procedure
executes the SQL commands and returns the result to the client. Stored procedures
are used to reduce network traffic.

Q. What is Buffer Manager?

A. It is a program module, which is responsible for fetching data from disk storage into
main memory and deciding what data to be cache in memory.

COMPUTER | DBMS PAGE 26

 Download Testbook App

Q. What is Transaction Manager?

A. It is a program module, which ensures that database, remains in a consistent state
despite system failures and concurrent transaction execution proceeds without
conflicting.

Q. What is File Manager?

A. It is a program module, which manages the allocation of space on disk storage and
data structure used to represent information stored on a disk.

Q. What is Authorization and Integrity manager?

A. It is the program module, which tests for the satisfaction of integrity constraint and
checks the authority of user to access data.

Q. What are stand-alone procedures?

 Procedures that are not part of a package are known as stand-alone because
they independently defined.
 A good example of a stand-alone procedure is one written in a SQL*Forms
application.
 These types of procedures are not available for reference from other Oracle
tools.
 Another limitation of stand-alone procedures is that they are compiled at run
time, which slows execution.

Q. What are cursors give different types of cursors?

A. PL/SQL uses cursors for all database information accesses statements. The
language supports the use two types of cursors

 Implicit

 Explicit

Q. What is cold backup and hot backup (in case of Oracle)?

 Cold Backup: It is copying the three sets of fi les (database fi les, redo
logs, and control file) when the instance is shut down. This is a straight file
copy, usually from the disk directly to tape. You must shut down the instance
to guarantee a consistent copy. If a cold backup is performed, the only option
available in the event of data file loss is restoring all the files from the latest
backup. All work performed on the database since the last backup is lost.

COMPUTER | DBMS PAGE 27

 Download Testbook App

 Hot Backup: Some sites (such as worldwide airline reservations

systems) cannot shut down the database while making a backup copy of the
files. The cold backup is not an available option.

Q. What is meant by Proactive, Retroactive and Simultaneous Update?

 Proactive Update: The updates that are applied to database before it
becomes effective in real world.

 Retroactive Update: The updates that are applied to database after it

becomes effective in real world.

 Simultaneous Update: The updates that are applied to database at the

same time when it becomes effective in real world.

COMPUTER | DBMS PAGE 28