IT Literacy

Database Management System

Database Management System (DBMS)
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 modified 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
Management System) ●​ MySQL
●​ dBase ●​ PostgreSQL
●​ Clipper ●​ Microsoft Access
●​ FoxPro ●​ SQL Server
●​ FileMaker ●​ Oracle

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 are 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 stores and organizes 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

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.
●​ The 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.
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.
●​ 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 it is difficult to implement many-to-many relationship.
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 Visual Foxpro, MySQL, Oracle, Sybase, Teradata, Microsoft
Access.

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.
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 a 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 the 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
●​ 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 databases on

computers. 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.
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 deal 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.
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

●​ 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].

●​ The Relational Model was proposed by Ted Codd in 1970.

●​ Entity - Relational Model defined by Peter Chen in 1976.

●​ Maturation of Relational Database and SQL took place in 1970.

●​ Object Oriented Database developed in 1985.

●​ Finally, the 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 store definitions of various data elements
and their relationships.

●​ Provides storage for not only 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.
●​ 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 a database?
A. A database is a logically coherent collection of data with some inherent
meaning, representing some aspect of the 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 the real world and which is
designed, built and populated with data for a specific purpose.
Q. What is a database system?
A. The database and DBMS software together is called a 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 disadvantages in File Processing System?

◊​ Data redundancy and inconsistency.
◊​ Difficult in accessing data.
◊​ Data isolation.
◊​ Data integrity.
◊​ Concurrent access is not possible.
◊​ Security Problems.
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 the database and what relationship among those data.
3.​ View level: The highest level of abstraction describes only part of the
entire database.
Q. What is extension and intention?
◊​ 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 the
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 systems. 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

Q. What is a 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 difficult to achieve

Q. What is a view? How is it 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 tables. In other words, there is no stored file that directly
represents the view, instead a definition of view is stored in a 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 the E-R model?

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

Q. What is an Object Oriented model?

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

Q. What is an Entity type?

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

Q. What is an Entity set?

A. It is a collection of all entities of a 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 the degree of a Relation?

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

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

Q. What is a relationship set?

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

Q. What is a relationship type?

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

Q. What is the degree of relationship type?

A. It is the number of entity types participating.
Q. What is DDL (Data Definition Language)?
A. A database 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 the database system
are specified by a set of definitions 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 is needed and how to get that data.
◊​ Non-Procedural DML or High level: DML requires a user to specify
what data is needed without specifying how to get that data.

Q. What is a 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 a Query evaluation engine?

A. It executes low-level instructions generated by the compiler.

Q. What is DDL Interpreter?

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

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 tuple variables i.e., variables 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.

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 the 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 the 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
the 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 the 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 joint dependency is implied by the set of FDs, over R in which the
left side is the key of R.
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 the same owner entity. It is
sometimes called a 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 identifies
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 it better known?

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 a data dictionary.

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 abortions.

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 a 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.

Q. What is "transparent DBMS"?

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

Q. What is a query?
A. A query with respect to DBMS relates to user commands that are used to
interact with a database. 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.
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 a 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.

Q. What is Storage Manager?

A. It is a program module that provides the interface between the low-level
 data stored in the 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 usable.
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 be defined to automatically
execute insert, update, and delete statements against a table. The trigger
can be 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 a 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 cached in
memory.

Q. What is a Transaction Manager?

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

Q. What is a 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 an Authorization and Integrity manager?

A. It is the program module, which tests for the satisfaction of integrity
constraints and checks the authority of the 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 are 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 different types of cursors?
A. PL/SQL uses cursors for all database information access 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 files (database files, 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.
◊​ 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 the database
before it becomes effective in the real world.

◊​ Retroactive Update: The updates that are applied to database after

it
becomes effective in the real world.

◊​ Simultaneous Update: The updates that are applied to the database

at the same time when it becomes effective in the real world.