2 marks question answers

Applications of DBMS 2m

•Banking

•Railway reservation system

•Telecom

•Universities

•Airways

Followingarethedatabaseusers: 2m

1.NaiveUsers:Unsophisticated users

2.ApplicationProgrammers:Users who write & develop

3.SophisticatedUsers:Interact with the system by making request in te form of query language

4. Query Evaluation Engine: It executes the instruction

Schema and Instance 2m

Schema: Structure of entire database is called the schema.

•Instance:

The data stored in database at a particular moment of time is called instance of database.

Data Independence 2m

•Definition:Data independence is ability to modify a schema definition in one level with out affecting aschema definition In the next higher level.

1.Physical data independence

2.Logical data independence

Advantages of DBMS over File Processing System 4m

1.Data Redundancy :

•Redundancy is the concept of repetition of data i.e. each data may have more than a single copy.

•The file system cannot control the redundancy of data as each user defines and maintains the needed files for a specific application to run.

2. Data Sharing:

•The file system does not allow sharing of data or sharing is too complex.

•Whereas in DBMS, data can be shared easily due to a centralized system.

Example : Google Drive, Google forms etc

3. Data integrity:

•There may be cases when some constraints need to be applied to the data before inserting it into the database.

•The file system does not provide any procedure to check these constraints automatically.

Example : Age, Mobile No etc

Explain Data Abstraction with diagram 4m

•Hides details of how data is stored and maintained.

•Data abstraction is hiding complexity of data structure from users.

Level 1: Physical level

•Lowest Level

•defines how data is stored in database

•It tells actual location of data that is being stored by the user

Level 2 : Logical level

•Defines what data is stored in database and what relationship exist among those data
•It decides structure of entire database

•DBA use the logical level for abstraction purpose

Level 3: View level

•Different views of same database is created for end users.

•Different views of same database can be created for user to interact with database for user friendly approch

descibe any four codd's rules 4m

E.F. Codd’s rules for RDBMS

Rule 1: Information Rule

• The data stored in a database, may it be user data or metadata, must be a value of some table cell.
• Everything in a database must be stored in a table format.

Rule 2: Guaranteed Access Rule

• Every single data element (value) is guaranteed to be accessible by

• Table-name + primary-key (row value) + attribute-name (column value).
• Ex. : SELECT SNAME FROM STUDENT WHERE SID= 01;

Rule 3: Powerful and Well-Structure Language

• One well Structured language must be there to provide all manners of access to the data stored in the database. Ex. : SQL etc. If the database access to the data without the use of this
language, then that is a violation

Rule 4: View Updating Rule

• All the views of a database, which can theoretically be updated, must also be updatable by the system.
Draw and explain overall structure of DBMS
• Overall structure of DBMS
• •Database Management system (DBMS) is a software that allows
access to data stored in a database and provides an easy and
effective method of

• 1.Definingthe information

• 2.Storingthe information.

• 3.Manipulationthe information.

• 4.Protectingthe information from system crashes or data theft.

• 5.Differentiatingaccess permissions for different users

• •Components of DBMS

• 1.Query processor

• 2.Storage manager

• 3.Disk storage
Q12. Enlist any four aggregate functions.

ANS:

An aggregate function is a function where the values of multiple rows are grouped together as input on
certain criteria to form a single value of more significant meaning. Aggregate functions are :

Count()

Sum()

Avg()

Min()

Max()

1. Count () : It returns number of rows from the given table if no attribute is mentioned. If some attribute is
mentioned, it gives total number of not null values for that attribute.

Eg : Select count(*) from emp;

Returns total number of records from emp table.

Select count(telephone) from emp;

Returns total number of employees having telephone numbers. 2. 2. Sum() : It give total of all values from a
numeric attribute of the given table.

Eg : Select sum(salary) from emp;

Returns total salary drawn of all employees from the emp table.

Avg () : It gives average of all the numeric values of the given attribute from the table.

Eg :Select Avg(salary) from emp;

Returns average salary of employees from emp table.

Min () : It gives minimum of all the values of the numeric given attribute from the table.

Eg :Select Min(salary) from emp;

Returns minimum salary value from emp table.

Max () : It gives maximum of all the values of the numeric given attribute from the table.

Eg :Select Max(salary) from emp; retunes maximum salary value from emp table,

Q13. Explain Cursor. List the two types of cursor.

ANS:

A cursor is a temporary work area created in system memory when a SQL statement is executed. A cursor is
a set of rows together with a pointer that identifies a current row. It is a database object to retrieve data
from a result set one row at a time. It is useful when we want to manipulate the record of a table in a
singleton method, in other words one row at a time. In other words, a cursor can hold more than one row,
but can process only one row at a time. The set of rows the cursor holds is called the active set.

Implicit cursor: these types of cursors are generated and used by the system during the manipulation of a
DML query. An implicit cursor is also generated by the system when a single row is selected by a SELECT
command. Programmers cannot control the implicit cursors.

Explicit cursor: this type of cursor is created by the user when the select command returns more than one
row, and only one row is to be processed at a time. An explicit cursor can move from one row to another in a
result set. An explicit cursor uses a pointer that holds the record of a row.

To create an explicit cursor the following steps are used:

1. Declare cursor: this is done in the declaration section of PL/SQL program.

Open: this step is done before the cursor is used to fetch the records.

Fetch: used to retrieve data row by row from the cursor.

Close: once the processing of the data is done, the cursor can be closed.

Example:

declare

name emp_details.ename%type;

cursor a is select ename from emp_details where empno=3;//cursor declaration

begin

open a;//opening the cursor

loop

fetch a into name;//fetching the rows from cursor

update emp_details set comm=3000 where empno=3;

exit when a%notfound;

dbms_output.put_line('record updated');

end loop;

close a;//closing the cursor

end;
Q14. Enlist arithmetic and logical SQL operators.

ANS:

SQL Arithmetic Operators: OR operator

Addition Operator(+) BETWEEN operator

Multiplication Operator (-) IN operator

Division Operator (/) NOT operator

Modulus Operator (%) ANY operator

SQL Logical Operators: LIKE operator

AND operator

This architecture has three levels:

1. External level
2. Conceptual level
3. Internal level
• External level

It is also called view level because several users can view their desired data from this level which is internally
fetched from database with the help of conceptual and internal level mapping. The user doesn’t need to know
the database schema details such as data structure; table definition etc. user is only concerned about data
which is what returned back to the view level after it has been fetched from database which is present at the
internal level. External level is the top level of the three level DBMS architecture.

• Conceptual level

It is also called logical level. The whole design of the database such as relationship among data, schema of data
etc. are described in this level. Database constraints and security are also implemented in this level of
architecture.

This level is maintained by DBA (database administrator)

• Internal level

This level is also known as physical level. This level describes how the data is stored in the storage devices. This
level is also responsible for allocating space to the data. This is the lowest level of the architecture.

Q16. Write SQL queries for following: i) Create table student with following attributes using suitable data types.
Roll no., as primary key, name, marks as not null and city. ii) Add column Date of Birth in above student table.
iii) Increase the size of attribute name by 10 in above student table. iv) Change name of Student table to stud.
ANS: i) CREATE TABLE Student

Rollno int PRIMARY KEY,

name varchar(30) NOT NULL,

marks int NOT NULL,

city varchar(20)

);

ii) ALTER TABLE student ADD DateofBirth varchar(20);

iii) ALTER TABLE student Modify name varchar(40);

iv) RENAME Student to Stud;

Q17.Write and Explain the syntax for creating and dropping indexes with an example.

ANS:

CREATE INDEX

The CREATE INDEX command is used to create indexes in tables. It allows duplicate values. Indexes are used to
retrieve data from the database very fast. The users cannot see the indexes; they are just used to speed up
searches/queries.

Syntax:

CREATE INDEX index_name

ON table_name (column1, column2, ...);

Example:

The following SQL creates an index named id_firstname on the FirstName column in the Student table:

CREATE INDEX id_firstname ON Student (FirstName);

DROP INDEX

The DROP INDEX statement is used to delete an index in a table

Syntax:

DROP INDEX index_name ON table_name;

Example:

DROP INDEX id_firstname ON Student;

Q18. Write a PL/SQL code to print reverse of a number.

ANS:

declare while n>0 loop

n number; r:=mod(n,10);

i number; rev:=(rev*10)+r;

rev number:=0; n:=trunc(n/10);

r number; end loop;

begin dbms_output.put_line('reverse is '||rev);

n:=&n; end;
Q19. Distinguish between network model and hierarchical model.

ANS :

Q20. Describe exception handling in brief.

ANS:

Exception Handling: Exception is nothing but an error. Exception can be raise when DBMS encounters errors or
it can be raised explicitly.

When the system throws a warning or has an error it can lead to an exception. Such exception needs to be
handled and can be defined internally or user defined.

Exception handling is nothing but a code block in memory that will attempt to resolve current error condition.

Syntax:

DECLARE ;

Declaration section

…executable statement;

EXCEPTION

WHEN ex_name1 THEN ;

Error handling statements/user defined action to be carried out;

END;

Types of Exception:

Predefined Exception/system defined exception/named exception:

Are always automatically raised whenever related error occurs. The most common errors that can occur during
the execution of PL/SQL. Not declared explicitly i.e. cursor already open, invalid cursor, no data found, zero
divide and too many rows etc. Programs are handled by system defined Exceptions.

User defined exception:

It must be declare by the user in the declaration part of the block where the exception is used. It is raised
explicitly in sequence of statements using: Raise_application_error(Exception_Number, Error_Message);
Q21. Explain joins in SQL with examples. ANS:

JOIN:

A SQL join is an instruction to combine data from two sets of data (i.e. two tables).A JOIN clause is used to
combine rows from two or more tables, based on a related column between them. SQL Join types are as
follows:

INNER JOIN or EQUI JOIN: A join which is based on equalities is called equi join. In equi join comparison
operator “=” is used to perform a Join.

Syntax:

SELECT tablename.column1_name,tablename.column1_name FROM table_name1 inner join table_name2 ON

table_name1.column_name=table_name2.column_name;

Example:

Select stud_info.stud_name, stud_info.branch_code, branch_details.location From stud_info inner join

branch_details ON Stud_info.branch_code=branch_details.branch_code;

2. LEFT OUTER JOIN: A left outer join retains all of the rows of the “left” table, regardless of whether there is a
row that matches on the “right” table.

Syntax:

Select column1name,column2name from table1name left outer join table2name on

table1name.columnname= table2name.columnname;

Example:

select last_name, department_name from employees left outer join departments on

employees.department_id = departments.department_id;

3) RIGHT OUTER JOIN: A right outer join retains all of the rows of the “right” table, regardless of whether there
is a row that matches on the “left” table.

Syntax:

Select column1name, column2name from table1name any_alias1 right outer join table2 name any_alias2 on
any_alias1.columnname =any_alias2.columnname;

Example:

Select last_name, department_name from employees e right outer join departments d on e.department_id =
d.department_id;

Q22. Explain function in PL/SQL with example.

ANS:

Function: Function is a logically grouped set of SQL and Pl/SQL statements that perform a specific task.

A function is same as a procedure except that it returns a value. A function is created using the CREATE
FUNCTION statement.

Syntax: CREATE [OR REPLACE] FUNCTION Example:

function_name [(parameter_name [IN | OUT | IN
CREATE OR REPLACE FUNCTION Success_cnt
OUT] type [, ...])]
RETURN number
RETURN return_datatype
IS cnt number(7) := 0;
{IS | AS}
BEGIN SELECT count(*) into cnt
BEGIN < function_body >
FROM candidate where result='Pass';
END [function_name];
RETURN cnt; END;
Q23. Explain ACID properties of transaction.

ANS: ACID properties of transaction

1. Atomicity: When one transaction takes place, many operations occur under one transaction. Atomicity
means either all operations will take place property and reflect in the database or none of them will be
reflected.

2. Consistency: Consistency keeps the database consistent. Execution of a transaction needs to take place in
isolation. It helps in reducing complications of executing multiple transactions at a time and preserves the
consistency of the database.

3. Isolation: It is necessary to maintain isolation for the transactions. This means one transaction should not be
aware of another transaction getting executed. Also their intermediate result should be kept hidden.

4. Durability: When a transaction gets completed successfully, it is important that the changes made by the
transaction should be preserved in database in spite of system failures.

Q24. Describe any four responsibilities of Database administrator.

ANS: Responsibilities of Database Administrator (DBA):

1. Schema Definition: Database or schema can be designed or defined by DBA.

2. Creating storage structure: DBA allocate or decide the space to store the database.

3. Create grant access methods: Different access methods to access the database can be granted by DBA to
the users.

4. Schema modification: The database or schema which is already defined can be modified by DBA as per the
requirements.

5. Granting authorization: To access the different databases, DBA can grant the authorization to authorized
users only.

6. Performance tuning: The problems/errors arise in database accessing; can be resolved by DBA to increase
the performance.

7. Regular maintenance: DBA can monitor the transactions in database and maintain the database error free
by doing the regular maintenance.

Q25. Write and Explain the syntax for creating database trigger.

ANS:

Database trigger:

Triggers can be referred as stored procedures that are fired or executed when an INSERT, UPDATE or DELETE
statement is given against the associated table.

Syntax: Here, as trigger will invoke before record is

inserted so, BEFORE Tag can be used.
create trigger [trigger_name]
create trigger stud_marks
[before | after]
before
{insert | update | delete}
INSERT
on [table_name]
on Student
[for each row]
for each row
[trigger_body]
set Student.total = Student.subj1 + Student.subj2 +
Example: Given Student Report Database, in which
Student.subj3, Student.per = Student.total * 60 /
student marks assessment is recorded. In such
100;
schema, create a trigger so that the total and
percentage of specified marks is automatically
inserted whenever a record is insert.
Q26. Explain Database Recovery techniques in detail.

ANS:

Database Recovery Techniques:

Database recovery techniques are used to restore the original data in system from backup. Backward and
forward recovery is two types of database recovery.

Recovery Techniques:

1. Log based recovery.

2. Shadow paging recovery

3. Checkpoints

1. Log based recovery: It records sequence of log records, which includes all activities done by database users.

It records the activities when user changes the database.

In case of database failure, by referring the log records users can easily recover the data.

Q26. Explain Database Recovery techniques in detail.

ANS:

Database Recovery Techniques:

Database recovery techniques are used to restore the original data in system from backup. Backward and
forward recovery is two types of database recovery.

Recovery Techniques:

1. Log based recovery.

2. Shadow paging recovery

3. Checkpoints

1. Log based recovery: It records sequence of log records, which includes all activities done by database users.

It records the activities when user changes the database.

In case of database failure, by referring the log records users can easily recover the data.

Q26. Explain Database Recovery techniques in detail.

ANS:

Database Recovery Techniques:

Database recovery techniques are used to restore the original data in system from backup. Backward and
forward recovery is two types of database recovery.

Recovery Techniques:

1. Log based recovery.

2. Shadow paging recovery

3. Checkpoints

1. Log based recovery: It records sequence of log records, which includes all activities done by database users.
It records the activities when user changes the database.

In case of database failure, by referring the log records users can easily recover the data.

Alter sequence:

Syntax:

alter sequence <sequence name> maxvalue <number>;

Alter sequence can change the maxvalue in the sequence created.

Dropping sequence:

Syntax:

drop sequence <sequenc name> ;

To drop the sequence the DROP command is used

Q29. Explain View with example.

ANS:

A view is like a window into the data stored in a database. It doesn’t actually store any data on its own.
Instead, it shows data from one or more tables based on a query (a question or request for information). You
can think of it like a saved search or a shortcut that you can use over and over again to get certain information.

When you use a view, it looks like you’re working with a regular table, but in reality, the database just runs the
query behind the scenes and gives you the result.

· Virtual Table: A view is not a real table; it only represents data from existing tables.

· Read-Only or Updatable: Depending on the complexity of the view, it can be read-only (if it involves complex
joins or aggregations) or updatable (if it's a simple query on a single table).

· Simplifies Queries: Views can encapsulate complex queries, making them easier to reuse.

· Security: Views can be used to restrict access to specific columns or rows of data.

Example :

CREATE VIEW EmployeeDetails AS

SELECT e.Name, d.DepartmentName, e.Salary

FROM Employees e

JOIN Departments d ON e.Department = d.DepartmentName;

Q30. Explain Index and its type with example?

ANS:

An index in a database is like a bookmark or table of contents that helps the database find and access data
faster. Instead of searching through all the rows in a table, the database can use the index to quickly locate the
data you need.

Types of Indexes:

1. Single-column Index: An index created on just one column of a table.

Syntax :

CREATE INDEX idx_student_id ON Students(student_id);

2. Multi-column Index (Composite Index): An index created on multiple columns. It helps when you often
query based on more than one column.

Syntax : 3. Unique Index: Ensures that all values in the

indexed column(s) are unique. It’s automatically
CREATE INDEX idx_name ON Students(first_name,
created when you define a primary key or unique
last_name);
constraint.
CREATE UNIQUE INDEX idx_unique_student_id ON
Students(student_id);
Syntax: