DMA Model Answer

MAHARASHTRA STATE BOARD OF TECHNICAL EDUCATION
(Autonomous)
(ISO/IEC - 27001 - 2005 Certified)
SUMMER – 2023 EXAMINATION

-
Subject: Database Management Subject Code:
22416
Important Instructions to examiners:
1) The answers should be examined by key words and not as word-to-word as given in the
model answer scheme.
2) The model answer and the answer written by candidate may vary but the examiner may
try to assess the understanding level of the candidate.
3) The language errors such as grammatical, spelling errors should not be given more
Importance (Not applicable for subject English and Communication Skills.
4) While assessing figures, examiner may give credit for principal components indicated in
the figure. The figures drawn by candidate and model answer may vary. The examiner
may give credit for any equivalent figure drawn.
5) Credits may be given step wise for numerical problems. In some cases, the assumed
constant values may vary and there may be some difference in the candidate’s answers
and model answer.
6) In case of some questions credit may be given by judgement on part of examiner of
relevant answer based on candidate’s understanding.
7) For programming language papers, credit may be given to any other program based on
equivalent concept.
8) As per the policy decision of Maharashtra State Government, teaching in English/Marathi
and Bilingual (English + Marathi) medium is introduced at first year of AICTE diploma
Programme from academic year 2021-2022. Hence if the students in first year (first and
second semesters) write answers in Marathi or bilingual language (English +Marathi), the
Examiner shall consider the same and assess the answer based on matching of concepts
with model answer.
Q. Sub Answer Marking
No Q.N. Scheme
1. Attempt any FIVE of the following: 10M
a) a) Enlist any four DDL commands. 2M
Ans. i) CREATE Any four
commands
ii) DROP 1/2M each
iii) ALTER
iv) TRUNCATE
v) RENAME
b) State use of 2M
i) Commit and Use of
ii) Rollback commands each
command
Ans. i) Commit: It permanently saves all the changes made in the 1M
transaction of a database or table.
Page 1 / 15
(Autonomous)

-
22416
ii) Rollback : This command restores the database or table to last

committed state. It is also used with SAVEPOINT command to
jump to a savepoint in an ongoing transaction.
c) Define Composite Index 2M
Ans. Composite Index: An index created on more than one column is Correct
called composite index. definition
2M
d) State any two advantages of PL/SQL 2M
Ans. 1. It improves performance against running SQL queries multiple Any two
advantages
times.
1M each
2. It is secure, as the code is in the database and hides the internal
database details from the user.
3. It provides strong features, such as exception handling, procedure,
function, cursors, triggers which saves time on design and
debugging.
4. Applications written in PL/SQL are fully portable.
e) Enlist different types of Database Users 2M
Ans. 1. Database Administrators (DBA) Any four
types 1/2M
2. Database Designers.
each
3. System Analysts.
4. Application Programmers
5. Naive Users
6. Sophisticated Users
f) Enlist ACID properties of transactions 2M

Ans. i) Atomicity Each
property
ii) Consistency/Correctness
name 1/2M
iii) Isolation, each
iv) Durability
g) Define synonym and state its use 2M

Ans. Definition: Definition
Synonym is an alternative name for database object, referred to as the 1M
base object that can exist on a local or remote server. Use 1M
Page 2 / 15
(Autonomous)

-
22416
Use:
Synonyms are used mainly to make it easy for users to access
database objects owned by other users. They hide the underlying
object's identity and make it harder for a malicious program or user to
target the underlying object.
2. Attempt any THREE of the following: 12M

a) a) Define the following terms with respect to any relation: 4M
i) Cardinality Definition
of each
ii) Degree term 1M
iii) Domain
iv) Attribute
Ans. i) Cardinality: It is the number of rows from a table.
ii) Degree: It is the number of columns from a table.
iii) Domain: It is a set of all permissible values in an attribute.
iv) Attribute: Properties of an entity are called attributes.
The column headers in a table are referred as attributes.
b) Describe any two set operators with suitable example. 4M
Ans. Set operators combine the results of two component queries into a
Any two
single result. Queries containing set operators are called as compound operators
queries. Set operators in SQL are represented with following special with
keywords as: Union, Union all, intersection & minus. suitable
example
1) Union: The Union of two or more sets contains all elements, 2M each
which are present in either or both. Union works as or. The duplicates
of both the tables will appear only once.
E.g. select ename from emp1 union select ename from emp2;
2) Union all: The Union of 2 or more sets contains all elements,
which are present in both, including duplicates.
E.g. select ename from emp1 union all select ename from emp2;
3) Intersection: The intersection of two sets includes elements which
are present in both.
E.g. select ename from emp1 intersect select ename from emp2;
4) Minus: The minus of two sets includes elements from set1 minus
elements of set2.
E.g. select ename from emp1 minus select ename from emp2;
Page 3 / 15
(Autonomous)

-
22416
c) Write a command to create and drop synonym of any relation. 4M

Ans. Create synonym command Syntax: Correct
syntax of
CREATE SYNONYM synonym_name FORname_of_base_table; create 2M
Eg : Create Synonym Emp for Employee_Details;
Drop synonym Command Syntax: Correct
syntax of
DROP SYNONYM synonym_name; drop 2M
Eg : Drop Synonym Emp;
d) Consider the following schema student (RNO, Name, Course, 4M
Percentage)
Write SQL commands for the following :
i) Display the records having ‘A’ as first character in name of
students.
ii) Display the records having course as ‘cm’ and percentage
more than 70.
iii) Display all records in descending order of name.
Each SQL
iv) Display minimum and maximum percentage. command
1M
Ans. i) Select * from student where Name like „A%‟;
ii) Select * from student where Course=‟cm‟ and Percentage >70.
iii) Select * from student order by Name desc;
iv) Select min(Percentage), max(Percentage) from student;
3. Attempt any THREE of the following: 12M

a) Write a PL/SQL code to display 1 to 10 odd numbers
Ans. DECLARE 4M
Correct
I number; logic 2M
BEGIN
Correct
I: =1; syntax 2M
Loop
Dbms_output.put_line(I);
I: =I+2;
Exit when I>10;
End loop;
END;
Note: Any other relevant code shall be considered.
Page 4 / 15
(Autonomous)

MODEL ANSWER-Only for the Use of RAC Assessors
Subject: Database Management Subject Code: 22416
b) Describe the different types and causes of Database failure. 4M

Ans. 1. Hardware Failure/System crash
Any 4 types
There is a hardware malfunction that causes the loss of the content and causes
of volatile storage, and brings transaction processing to a halt. The 1M each
content of non-volatile storage remains intact, and is not corrupted
or changed.
2. Software Failure
The database software or the operating system may be corrupted or
failed to work correctly, that may cause the loss of the content of
volatile storage, and results into database failure.
3. Media Failure
A disk block loses its content as a result of either a head crash or
failure during a data transfer operation.
4. Network Failure
A problem with network interface card or network connection an
cause network failure.
5. Transaction Failure
i) Logical error: the transaction can no longer continue with its
normal execution because of some internal condition, such as wrong
input values, data not found, data overflow or resource limit
exceeded.
ii) System error: A system entered in state like deadlock
6. Application software Error:
The problem with software accessing the data from database. -This
may cause database failure as data cannot be updated using such
application to it. -Logical error in program cause one or more
transaction failure.
7. Physical disaster
The problem caused due to flood, fire, earthquake etc.
c) Describe Group by and Having clause with suitable example 4M
Ans. Group by: -
Descriptio
It is used to group rows that have the same values. The grouping can n with
happen after retrieval of rows based on a certain condition. example /
syntax of
Page 5 / 15
(Autonomous)

-
22416
Group By
2M
In Group By clause aggregate function can be performed with one
command
Syntax: -
SELECT statements... GROUP BY
column_name1[,column_name2,...]
Example: -
SELECT `gender` FROM `members`

GROUP BY `gender`;
Having clause: -
It is used to restrict the rows affected by the group by clause. This Descriptio
can be used in conjunction with the group by clause. Having imposes n with
example /
a condition on the group by clause which further filters the groups syntax of
created by group by clause. Having
clause
2M
Syntax:
SELECT statements... GROUP BY
column_name1[,column_name2,...] [HAVING
condition];
Example: -
SELECT * FROM `movies` GROUP BY
`category_id`,`year_released` HAVING `category_id` = 8;
d) Create a sequence for the following specification. 4M
Ans. Name ABC, Starting value: 10, Maximum value: 100,
Correct
Increment by 10. logic 2M
Create Sequence ABC
Start with 10 Correct
syntax 2M
Increment by 10
Minvalue 10
Maxvalue 100;
Note: Any other relevant code can be considered
Page 6 / 15
(Autonomous)

-
22416
4. Attempt any THREE of the following: 12

a) Describe Implicit and Explicit cursor 4M
Ans. Implicit cursors are automatically created by Oracle whenever an
Descriptio
SQL statement is executed when there is no explicit cursor for the n of
statement. Programmers cannot control the implicit cursors and Implicit
the information in it. cursor 2M
In PL/SQL, you can refer to the most recent implicit cursor as the
SQL cursor, which always has attributes such as %FOUND,
%ISOPEN, %NOTFOUND, and %ROWCOUNT. The SQL
cursor has additional attributes, %BULK_ROWCOUNT and
%BULK_EXCEPTIONS, designed for use with the FORALL
statement. Example: -
The following program will update the table and increase the salary
of each customer by 500 and use the SQL%ROWCOUNT attribute
to determine the number of rows affected −
DECLARE
total_rows number(2);
BEGIN
UPDATE customers
SET salary = salary + 500;
IF sql%notfound THEN
dbms_output.put_line('no customers selected');
ELSIF sql%found THEN total_rows:= sql
%rowcount;
dbms_output.put_line( total_rows|| ' customers selected ');
END IF;
END;
/
Explicit cursors are programmer-defined cursors for gaining more
Descriptio
control over the context area. An explicit cursor should be defined n of
in the declaration section of the PL/SQL Block. It is created on a Explicit
cursor 2M
SELECT Statement which returns more than one row.
Page 7 / 15
(Autonomous)

-
22416
The syntax for creating an explicit cursor is −

CURSOR cursor_name IS select_statement;
Working with an explicit cursor includes the following steps −
• Declaring the cursor for initializing the memory
• Opening the cursor for allocating the memory
• Fetching the cursor for retrieving the data
• Closing the cursor to release the allocated memory
Declaring the Cursor
Declaring the cursor defines the cursor with a name and
the associated SELECT statement. For example –
CURSOR c_customers IS
SELECT id, name, address FROM customers;
Opening the Cursor

Opening the cursor allocates the memory for the cursor and makes it
ready for fetching the rows returned by the SQL statement into it.
For example, we will open the above defined cursor as follows −
OPEN c_customers;
Fetching the Cursor

Fetching the cursor involves accessing one row at a time. For
example, we will fetch rows from the above-opened cursor as
follows −
FETCH c_customers INTO c_id, c_name, c_addr;
Closing the Cursor

Closing the cursor means releasing the allocated memory. For
example, we will close the above opened cursor as follows −
CLOSE c_customers;
Note: Any other relevant example shall be considered.
Page 8 / 15
(Autonomous)

-
22416
b) Describe the use of Index and write command to create an Index. 4M

Ans. Indexing is used to optimize the performance of a database by
Descriptio
minimizing the number of disk accesses required when a query is n of use of
processed. Index
command
• The index is a type of data structure. It is used to locate and access 2M
the data in a database table quickly.
• An index is a schema object.
• For example, if you want to reference all pages in a book that
discusses a certain topic, you first refer to the index, which lists all
the topics alphabetically and is then referred to one or more specific
page numbers.
Creating an Index:
Correct
Syntax: command
CREATE INDEX index to create
ON TABLE column; Index 2M
where the index is the name given to that index and TABLE is the
name of the table on which that index is created and column is the
name of that column for which it is applied.
Note: Any other relevant example shall be considered
c) Consider the Schema Emp (E.NO, E. Name, Department, Salary, 4M
Bonus). Write SQL command for the following.
i) Insert one record with suitable data.
ii) Display all records having a salary between 5000 and 10000.
iii) Get the total salary of all the employees.
iv) Display E.No., E.Name and total payment (i.e. Salary and
Bonus) of all employees.
Each
Ans. i) insert into Emp (E.NO, E.Name, Department, Salary, Bonus) correct
values (001,"Amit","Sales",20000,3000); command
1M
OR i) insert into Empvalues (001,"Amit","Sales",20000,3000);
ii) select * from Emp where Salary between 5000 and 10000;
iii) select sum(Salary) from Emp;
iv) select E.NO, E.Name, Salary, Bonus from Emp;
Page 9 / 15
(Autonomous)
- SUMMER – 2023 EXAMINATION

d) Describe different states of transaction with a neat diagram 4M

Ans.
Diagram
2M
These are different types of Transaction States :

1. Active State –
When the instructions of the transaction are running then the
transaction is in active state. If all the „read and write‟ operations Explanatio
are performed without any error then it goes to the “partially n 2M
committed state”; if any instruction fails, it goes to the “failed
state”.
2. Partially Committed –
After completion of all the read and write operation the changes are
made in main memory or local buffer. If the changes are made
permanent on the DataBase then the state will change to “committed
state” and in case of failure it will go to the “failed state”.
3. Failed State –
When any instruction of the transaction fails, it goes to the “failed
state” or if failure occurs in making a permanent change of data on
Data Base.
4. Aborted State –
After having any type of failure the transaction goes from “failed
state” to “aborted state” and since in previous states, the changes
are only made to local buffer or main memory and hence these
changes are deleted or rolled-back.
5. Committed State –
It is the state when the changes are made permanent on the
Data Base and the transaction is complete and therefore
terminated in the “terminated state”.
Page 10 / 15
(Autonomous)

-
22416
6. Terminated State –
If there isn‟t any roll-back or the transaction comes from the
“committed state”, then the system is consistent and ready for new
transaction and the old transaction is terminated.
e) Write a PL/SQL program to find the largest of three numbers. 4M
Ans. declare
Correct
a number; logic 2M
b number;
c number; Correct
syntax 2M
begin
dbms_output.put_line('Enter a:');
a:=&a;
dbms_output.put_line('Enter b:');
b:=&b;
dbms_output.put_line('Enter c:');
c:=&C;
if (a>b) and (a>c)
thendbms_output.put_line('A is GREATEST'||A);
elsif (b>a) and (b>c)
then
dbms_output.put_line('B is GREATEST'||B);
else
dbms_output.put_line('C is GREATEST'||C);
end if;
end;
Note: Any other relevant program shall be considered.
5. Attempt any TWO of the following: 12
a) Consider the schema 6M
Department (DNO, D Name, Location, Manager), Write SQL
commands for the following:
i) Create department table with suitable data type and size of
each attribute.
ii) Add one more attribute as Ph-no with suitable data type.
iii) Add the constraint primary key to D No.
Page 11 / 15
(Autonomous)

-
22416
Ans. i)
create table Department
(
DNO number(5),
DName varchar2(25), Each
Location varchar2(20), correct
Manager varchar2(30) command
2M
);
ii) alter table Department add (Ph-no number (12));
iii) alter table Department add constraint

Department_DNO_pk primary key( DNO );
(OR)
create table Department
(
DNO number(5) constraint Department_DNO_pk primary key,
DName varchar2(25),
Location varchar2(20),
Manager varchar2(30)
);
Page 12 / 15
(Autonomous)

MODEL ANSWER-Only for the Use of RAC Assessors
b) 6M
i) Update Supplier set location=‟Pune‟ where SNO=‟S2‟;

Ans. ii) select * from Product where PName like „%v%‟;
Each
(OR) correct
command
select PNO, PName,Price from Product where PName like „%v%‟ ; 1M
iii) select Supplier.SNo, Shipment.PNo, Shipment.Qty,

Supplier.SName from Supplier,Shipment where Supplier.SNo =
Shipment.SNo ;
(or)
select Supplier.SNo, Shipment.PNo, Shipment.Qty, Supplier.SName
from Supplier join Shipment on Supplier.SNo = Shipment.SNo ;
iv) selectShipment.SNo, Product.PNo. Product.PName from Product,
Shipment where Shipment.PNo = Product.PNo ;
(or)
selectShipment.SNo, Product.PNo. Product.PName from Product join
Shipment on Shipment.PNo = Product.PNo ;
v) select PNo, PName,Price from Product where Price>2000;
Page 13 / 15
(Autonomous)

-
22416
(OR)
select * from Product where Price>2000;
vi) select SName, Location from Supplier where Location

=‟Mumbai‟;
c) Create a trigger which invokes on updation of record in 6M

Department table.
Ans. create trigger trigger_update Correct
on department logic 3M
after update Correct
as syntax 3M
begin
select * from department;
end;
Note: Any example which can execute trigger before or after
updation shall be considered.
6. Attempt any TWO of the following: 12

a) Write SQL commands for the following: 6M
i) Create User ‘ABC’ with password as ‘IF4I’
ii) Grant privilege of select, delete, update on table EMP to user.
iii) Take back privilege of Delete from user ‘ABC’ on Relation
Emp. Each
Ans. i) create user ABC identified by IF4I ; correct
ii) grant select, delete, update on EMP to user ; command
2M
iii) revoke Delete on Emp from ABC ;
b) Consider a Schema EMP (E No, E Name, Dept, Salary) 6M
Write SQL Commands for the following:
i) Create a view EMP – V by fetching E No, Dept, Salary from
Emp
ii) Change Dept. to ‘Marketing’ of E No = 123 in view. Each
iii) Dropping view Emp – V correct
command
Ans. i) create view EMP-V as select ENO,Dept,Salary from Emp; 2M
ii)update Emp-V set Dept=‟Marketing‟ where ENO=123 ;
iii)drop view Emp-V ;
Page 14 / 15
(Autonomous)

-
22416
c) i) Write a function to find area of a circle and call the function. 6M

ii) Create a stored procedure to accept name and greet user with
name.
Ans. i)
SQL>create or replace function circle_area (radius in number)
return number aspi constant number := 3.14; Correct
area number; function
logic and
begin
syntax 2M
area := pi * radius *radius ; -- or area := pi * power(radius, 2);
return area; Function
end circle_area; call : 1M
/
Function Calling
begin
dbms_output.put_line(circle_area(3));
end;
/
(OR)
SQL>declare
r number;
ans number;
begin
r:=&r;
ans:=circle_area(r);
dbms_output.put_line(“radius=”||r);
dbms_output.put_line(“Area of circle ="||ans);
end;
/
ii)
create or replace procedure Greet_User (user_name in varchar2) Correct
is Procedure
begin logic and
syntax 3M
dbms_output.put_line(„Greet „ || user_name);
end;
/
Page 15 / 15

DMA Model Answer

Uploaded by

Copyright:

Available Formats

DMA Model Answer

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

DMA Model Answer

Uploaded by

Copyright:

Available Formats

MAHARASHTRA STATE BOARD OF TECHNICAL EDUCATION

SUMMER – 2023 EXAMINATION

SUMMER – 2023 EXAMINATION

ii) Rollback : This command restores the database or table to last

f) Enlist ACID properties of transactions 2M

g) Define synonym and state its use 2M

SUMMER – 2023 EXAMINATION

2. Attempt any THREE of the following: 12M

SUMMER – 2023 EXAMINATION

c) Write a command to create and drop synonym of any relation. 4M

3. Attempt any THREE of the following: 12M

SUMMER – 2023 EXAMINATION

b) Describe the different types and causes of Database failure. 4M

SUMMER – 2023 EXAMINATION

SELECT `gender` FROM `members`

SUMMER – 2023 EXAMINATION

4. Attempt any THREE of the following: 12

SUMMER – 2023 EXAMINATION

The syntax for creating an explicit cursor is −

SELECT id, name, address FROM customers;

Opening the Cursor

Fetching the Cursor

Closing the Cursor

Note: Any other relevant example shall be considered.

SUMMER – 2023 EXAMINATION

b) Describe the use of Index and write command to create an Index. 4M

- SUMMER – 2023 EXAMINATION

d) Describe different states of transaction with a neat diagram 4M

These are different types of Transaction States :

SUMMER – 2023 EXAMINATION

SUMMER – 2023 EXAMINATION

ii) alter table Department add (Ph-no number (12));

iii) alter table Department add constraint

SUMMER – 2023 EXAMINATION

i) Update Supplier set location=‟Pune‟ where SNO=‟S2‟;

iii) select Supplier.SNo, Shipment.PNo, Shipment.Qty,

SUMMER – 2023 EXAMINATION

vi) select SName, Location from Supplier where Location

c) Create a trigger which invokes on updation of record in 6M

6. Attempt any TWO of the following: 12

SUMMER – 2023 EXAMINATION

c) i) Write a function to find area of a circle and call the function. 6M

You might also like