Q #1) What do you understand by

‘Database’?

Ans: Database is an organized collection of

related data where the data is stored and

organized to serve some specific purpose.

For Example , A librarian maintains a

database of all the information related to

the books that are available in the library.

Q #2) Define DBMS.

Ans: DBMS stands for Database

Management system. It is a collection of

application programs which allow the user to

organize, restore and retrieve information

about data efficiently and as effectively as

possible.

Some of the popular DBMS's are MySql,

Oracle, Sybase, etc.

Q #3) Define RDBMS.

Ans: Relational Database Management

System(RDBMS) is based on a relational

model of data that is stored in databases in

separate tables and they are related to the

use of a common column. Data can be

accessed easily from the relational database

using Structured Query Language (SQL).

Q #4) Enlist the advantages of DBMS.

Ans: The Advantages of DBMS includes:

Data is stored in a structured way

and hence redundancy is controlled.

Validates the data entered and

provide restrictions on unauthorized

access to the database.

Provides backup and recovery of the

data when required.

Provides multiple user interfaces.

Q #5) What do you understand by Data

Redundancy?

Ans: Duplication of data in the database is

known as Data redundancy. As a result of

Data Redundancy, duplicated data is present

at various locations, hence it leads to

wastage of the storage space and the

integrity of the database is destroyed.

Q #6) What are the various types of

relationships in Database? Define them.

Ans: There are 3 types of relationships in

Database:

One-to-one: One table has the

relationship with another table

having the similar kind of column.

Each primary key relates to only one

or no record in the related table.

One-to-many: One table has a

relationship with another table that

has primary and foreign key relation.

The primary key table contains only

one record that relates to none, one

or many records in the related table.

Many-to-many: Each record in both

the tables can relate to many

numbers of record in another table.

Q #7) Explain Normalization and De-

Normalization.

Ans : Normalization is the process of

removing the redundant data from the

database by splitting the table in a well-

defined manner in order to maintain data

integrity. This process saves much of the

storage space.

De-normalization is the process of adding

up redundant data on the table in order to

speed up the complex queries and thus

achieve better performance.

Q #8) What are the different types of

Normalization?

Ans: Different Types of Normalization are:

First Normal Form (1NF): A relation

is said to be in 1NF only when all

the entities of the table contain

unique or atomic values.

Second Normal Form (2NF): A

relation is said to be in 2NF only if it

is in 1NF and all the non-key

attribute of the table is fully

dependent on the primary key.

Third Normal Form (3NF): A relation

is said to be in 3NF only if it is in

2NF and every non-key attribute of

the table is not transitively

dependent on the primary key.

Q #9) What is BCNF?

Ans: BCNF is Boyce Code Normal form. It is

the higher version of 3Nf which does not

have any multiple overlapping candidate

keys.

Q #10) What is SQL?

Ans: Structured Query language, SQL is an

ANSI(American National Standard Institute)

standard programming language which is

designed specifically for storing and

managing the data in the relational database

management system (RDBMS) using all

kinds of data operations.

Q #11) How many SQL statements are

used? Define them.

Ans: SQL statements are basically divided

into three categories, DDL, DML, and DCL.

They can be defined as:

Data Definition Language (DDL) commands

are used to define the structure that holds

the data. These commands are auto-

committed i.e. changes done by the DDL

commands on the database are saved

permanently.

Data Manipulation Language (DML)

commands are used to manipulate the data

of the database. These commands are not

auto-committed and can be rolled back.

Data Control Language (DCL) commands are

used to control the visibility of the data in

the database like revoke access permission

for using data in the database.

Q #12) Enlist some commands of DDL, DML,

and DCL.

Ans: Data Definition Language (DDL)

commands:

CREATE to create a new table or

database.

ALTER for alteration.

Truncate to delete data from the

table.

DROP to drop a table.

RENAME to rename a table.

Data Manipulation Language (DML)

commands:

INSERT to insert a new row.

UPDATE to update an existing row.

DELETE to delete a row.

MERGE for merging two rows or two

tables.

Data Control Language (DCL) commands:

COMMIT to permanently save.

ROLLBACK to undo the change.

SAVEPOINT to save temporarily.

Q #13) Define DML Compiler.

Ans: DML compiler translates DML

statements in a query language into a low-

level instruction and the generated

instruction can be understood by Query

Evaluation Engine.

Q #14) What is DDL interpreter?

Ans: DDL Interpreter interprets the DDL

statements and records the generated

statements in the table containing metadata.

Q #15) Enlist the advantages of SQL.

Ans: Advantages of SQL are:

Simple SQL queries can be used to

retrieve a large amount of data from

the database very quickly and

efficiently.

SQL is easy to learn and almost

every DBMS supports SQL.

It is easier to manage the database

using SQL as no large amount of

coding is required.

Q #16) Explain the terms ‘Record’, ‘Field’

and ‘Table’ in terms of database.

Ans: Record: Record is a collection of values

or fields of a specific entity. Eg. An

employee, Salary account, etc.

Field: A field refers to an area within a

record which is reserved for a specific piece

of data. Eg. Employee ID.

Table: Table is the collection of records of

specific types. E.g. Employee table is a

collection of record related to all the

employees.

Q #17) What do you understand by Data

Independence? What are its two types?

Ans: Data Independence refers to the ability

to modify the schema definition in one level

in such a way that it does not affect the

schema definition in the next higher level.

The 2 types of Data Independence are:

Physical Data Independence: It

modifies the schema at the physical

level without affecting the schema

at the conceptual level.

Logical Data Independence: It

modifies the schema at the

conceptual level without affecting or

causing changes in the schema at

the view level.

Q #18) Define the relationship between

‘View’ and ‘Data Independence’.

Ans: View is a virtual table which does not

have its data on its own rather the data is

defined from one or more underlying base

tables.

Views account for logical data independence

as the growth and restructuring of base

tables is not reflected in views.

Q #19) What are the advantages and

disadvantages of views in the database?

Ans: Advantages of Views:

As there is no physical location

where the data in views is stored, it

generates output without wasting

resources.

Data access is restricted as it does

not allow commands like insertion,

updation, and deletion.

Disadvantages of Views:

View becomes irrelevant if we drop a

table related to that view.

More memory is occupied when the

view is created for large tables.

Q #20) What do you understand by

Functional dependency?

Ans: A relation is said to be in Functional

dependency when one attribute uniquely

defines another attribute.

For Example, R is a Relation, X and Y are

two attributes. T1 and T2 are two tuples.

Then,

T1[X]=T2[X] and T1[Y]=T2[Y] means the

value of component X uniquely define the

value of component Y.

Also, X->Y means Y is functionally

dependent on X.

Q #21) When is functional dependency said

to be the fully functional dependency?

Ans: To fulfill the criteria of fully functional

dependency, the relation must meet the

requirement of functional dependency.

A functional dependency ‘A’ and ‘B’ is said

to be fully functional dependent when

removal of any attribute say ‘X’ from ‘A’

means the dependency does not hold

anymore.

Q #22) What do you understand by E-R

model?
Ans: E-R model is an Entity-Relationship

model which defines the conceptual view of

the database.

E-R model basically shows the real world

entities and their association/relations.

Entities here represent the set of attributes

in the database.

Q #23) Define Entity, Entity type, and Entity

set.

Ans: Entity can be anything, be it a place,

class or object which has an independent

existence in the real world.

Entity type represents a set of entities which

have similar attributes.

Entity set in the database represents a

collection of entities having a particular

entity type.

Q #24) Define Weak Entity set.

Ans: Weak entity set is the one whose

primary key comprises of its partial key as

well as the primary key of its parent entity.

This is the case because the entity set may

not have sufficient attributes to form a

primary key.

Q #25) Explain the terms ‘Attribute’ and

‘Relations’

Ans: Attribute describes the properties or

characteristics of an entity. For Example ,

Employee ID, Employee Name, Age, etc., can

be attributes of the entity Employee.

Relation is a two-dimensional table

containing a number of rows and columns

where every row represents a record of the

relation. Here, rows are also known as

‘Tuples’ and columns are known as

‘Attributes’.

Q #26) What are VDL and SDL?

Ans: VDL is View Definition language which

represents user views and their mapping to

the conceptual schema.

SDL is Storage Definition Language which

specifies the mapping between two

schemas.

Q #27) Define Cursor and its types.

Ans: Cursor is a temporary work area which

stores the data as well as the result set

occurred after manipulation of data

retrieved. A cursor can hold only one row at

a time.

The 2 types of Cursor are:

Implicit cursors are declared automatically

when DML statements like INSERT, UPDATE,

DELETE is executed.

Explicit cursors have to be declared when

SELECT statements which are returning

more than one row are executed.

Q #28) What is Database transaction?

Ans: Sequence of operation performed

which changes the consistent state of the

database to another is known as the

database transaction. After the completion

of the transaction, either the successful

completion is reflected in the system or the

transaction fails and no change is reflected.

Q #29) Define Database Lock and its types.

Ans: Database lock basically signifies the

transaction about the current status of the

data item i.e. whether that data is being

used by other transactions or not at the

present point of time.

There are two types of Database lock which

are Shared Lock and Exclusive Lock.

Q #30) What is Data Warehousing?

Ans: The storage as well as access to data,

that is being derived from the transactions

and other sources, from a central location in

order to perform the analysis is called Data

Warehousing.

Q #31) What do you understand by Join?

Ans: Join is the process of explaining the

relationship between different tables by

combining columns from one or more table

having common values in each. When a

table joins with itself, it is known as Self

Join.

Q #32) What do you understand by Index

hunting?

Ans: Index hunting is the process of

boosting the collection of indexes which

help in improving the query performance as

well as the speed of the database.

Q #33) How to improve query performance

using Index hunting?

Ans: Index hunting help in improving query

performance by:

Using query optimizer to coordinate

queries with the workload.

Observing the performance and

effect of index and query

distribution.

Q #34) Differentiate between ‘Cluster’ and

‘Non-cluster’ index.

Ans: Clustered Index alters the table and

reorders the way in which the records are

stored in the table. Data retrieval is made

faster by using the clustered index.

A Non-clustered index does alter the records

that are stored in the table but creates a

completely different object within the table.

Q #35) What are the disadvantages of a

Query?

Ans: Disadvantages of a Query are:

Indexes are not present.

Stored procedures are excessively

compiled.

Difficulty in interfacing.

Q #36) What do you understand by

Fragmentation?

Ans: Fragmentation is a feature which

controls the logical data units, also known

as fragments that are stored at different

sites of a distributed database system.

Q #37) Define Join types.

Ans: Given below are the types of Join,

which are explained with respect to the

tables as an Example:

employee table:

employee_info table:

1) Inner JOIN: Inner JOIN is also known as a

simple JOIN. This SQL query returns result

from both the tables having a common value

in rows.
SQL Query:

SELECT * from employee, employee_info

WHERE employee.EmpID =

employee_info.EmpID ;

Result:

2) Natural JOIN: This is a type of Inner JOIN

which returns results from both the tables

having same data values in the columns of

both the tables to be joined.

SQL Query:

SELECT * from employee NATURAL JOIN

employee_info;

Result:

3) Cross JOIN: Cross JOIN return results as

all the records where each row from the

first table is combined with each row of the

second table.

SQL Query:

SELECT * from employee CROSS JOIN

employee_info;

Result:

Let us do some modification in the above

tables to understand Right JOIN, Left JOIN,

and Full JOIN.

employee table:

employee_info table:

1) Right JOIN: Right JOIN is also known as

Right Outer JOIN. This returns all the rows

as a result from the right table even if the

JOIN condition does not match any records

in the left table.

SQL Query:

SELECT * from employee RIGHT OUTER

JOIN employee_info on (employee.EmpID =

employee_info.EmpID);

Result:

2) Left JOIN: Left JOIN is also known as

Left Outer JOIN. This returns all the rows as

a result of the left table even if JOIN

condition does not match any records in the

right table. This is exactly the opposite of

Right JOIN.

SQL Query:

SELECT * from employee LEFT OUTER JOIN

employee_info on (employee.EmpID =

employee_info.EmpID);

Result:

3) Outer/Full JOIN: Full JOIN return results

in combining the result of both the Left JOIN

and Right JOIN.

SQL Query:

SELECT * from employee FULL OUTER JOIN

employee_info on (employee.EmpID =

employee_info.EmpID);

Result:

Q #38) What do you understand by

‘Atomicity’ and ‘Aggregation’?

Ans: Atomicity is the condition where either

all the actions of the transaction are

performed or none. This means, when there

is an incomplete transaction, database

management system itself will undo the

effects done by the incomplete transaction.

Aggregation is the concept of expressing the

relationship with the collection of entities

and their relationships.

Q #39) Define Phantom deadlock.

Ans: Phantom deadlock detection is the

condition where the deadlock does not

actually exist but due to a delay in

propagating local information, deadlock

detection algorithms identify the deadlocks.

Q #40) Define checkpoint.

Ans: Checkpoint declares a point before

which all the logs are stored permanently in

the storage disk and is the inconsistent

state. In the case of crashes, the amount of

work and time is saved as the system can

restart from the checkpoint.

Q #41) What is Database partitioning?

Ans: Database partitioning is the process of

partitioning tables, indexes into smaller

pieces in order to manage and access the

data at a finer level.

This process of partitioning reduces the cost

of storing a large amount of data as well as

enhances the performance and

manageability.
Q #42) Explain the importance of Database

partitioning.

Ans: The importance of Database

partitioning are:

Improves query performance and

manageability.

Simplifies common administration

tasks.

Acts as a key tool for building

systems with extremely high

availability requirements.

Allows accessing a large part of a

single partition.

Q #43) Explain Data Dictionary.

Ans: Data dictionary is a set of information

describing the content and structure of the

tables and database objects. The job of the

information stored in the data dictionary is

to control, manipulate and access the

relationship between database elements.

Q #44) Explain Primary Key and Composite

Key.
Ans: Primary key is that column of the table

whose every row data is uniquely identified.

Every row in the table must have a primary

key and no two rows can have the same

primary key. Primary key value can never be

null nor can be modified or updated.

Composite Key is a form of the candidate

key where a set of columns will uniquely

identify every row in the table.

Q #45) What do you understand by Unique

key?

Ans: A Unique key is same as the primary

key whose every row data is uniquely

identified with a difference of null value i.e.

Unique key allows one value as NULL value.

Q #46) What do you understand by Database

Triggers?

Ans: A set of commands that automatically

get executed when an event like Before

Insert, After Insert, On Update, On Delete of

row occurs in a table is called as Database

trigger.
Q #47) Define Stored procedures.

Ans: A Stored procedure is a collection of

pre-compiled SQL Queries, which when

executed denotes a program taking input,

process and gives the output.

Q #48) What do you understand by B-Trees?

Ans: B-Tree represents the data structure in

the form of a tree for external memory that

reads and writes large blocks of data. It is

commonly used in databases and file

systems where all the insertions, deletions,

sorting, etc., are done in logarithmic time.

Q #49) Name the different data models that

are available for database systems.

Ans: Different data models are:

Relational model

Network model

Hierarchical model

Q #50) Differentiate between ‘DELETE’,

‘TRUNCATE’ and ‘DROP’ commands.

Ans: After the execution of ‘DELETE’

operation, COMMIT and ROLLBACK

statements can be performed to retrieve the

lost data.

After the execution of ‘TRUNCATE’

operation, COMMIT, and ROLLBACK

statements cannot be performed to retrieve

the lost data.

‘DROP’ command is used to drop the table

or key like the primary key/foreign key.

Q #51) Based on the given table, solve the

following queries.

Employee table

1) Write the SELECT command to display

the details of the employee with empid as

1004.

Ans:

SELECT empId, empName, Age, Address

from Employee WHERE empId = 1004;

Result:

2) Write the SELECT command to display all

the records of table Employee.

Ans:

SELECT * from Employee;

Result:

3) Write the SELECT command to display all

the records of the employee whose name

starts with the character ‘R’.

Ans:

SELECT * from Employee WHERE empName

LIKE ‘R%’;

Result:

4) Write a SELECT command to display id,

age and name of the employees with their

age in both ascending and descending order.

Ans:

SELECT empId, empName, Age from

Employee ORDER BY Age;

Result:

SELECT empId, empName, Age from

Employee ORDER BY Age Desc;

Result:

5) Write the SELECT command to calculate

the total amount of salary on each employee

from the below Emp table.

Emp table:
Ans:

SELECT empName, SUM(Salary) from Emp

GROUP BY empName;