AMERICAN COLLEGE OF TECHNOLOGY

Computer Science Department

May 2, 2025
Time allowed: 50 minutes
Total Weight: 25%

Name________________________________
ID. No: ______________________________
Section: _____________________________
Instructor’s Name ____________________
GENERAL DIRECTIONS:

This examination is designed to assess your understanding of key concepts, your ability to
apply knowledge, and Coding skills of Stored Procedures and Transactions in Advanced
Database Systems. Please read the instructions for each section carefully before answering.
Ensure your responses are well-organized and precise.

 Make sure that the exam paper contains 3 pages including the cover page and answer sheet.
 Read the instructions carefully for each part of the exam and attempt accordingly.
 Use of pencils and Red pens are not allowed
 Any action taken by exam invigilator during exam time will not be compromised
 Put aside any materials not allowed on the instructor’s desk.
 Write your answers on the separate answer sheet provided.
 Please write your name on both question paper and answer sheet

1|Page
Part I: Multiple Choice Questions ( 1 question with 1 point each and a total of 17 points)
____1. _____aims to bridge the gap between the object-oriented models used in application
development and the relational models used in database systems.
a) Transaction b) Class c) OORD d) Object
____2. In Object Oriented Relational database, tables can be designed to represent _______, and
rows in those tables represent ______ respectively.
a) Object , Classes b) Classes ,Objects c) Classes , inheritance
____3. Which OOP concept emphasizes on data and methods to bundled together?
a) Encapsulation b) Abstraction c) Polymorphism d) Inheritance
____4. Which of the following is the advantage of Object Oriented Relational Database?
a) Seamless Integration b)Reusability c)Maintainability d)All
____5. ______ are blueprints for defining a set of similar objects.
a) Classes b)Relation c) Abstraction d) a and b
____6. _______ is the process of identifying the essential aspects of an entity and ignoring the
unimportant properties.
a) Encapsulation b) Abstraction c) Polymorphism d) Inheritance
____7. Which Key Stages of Query Processing generates multiple execution plans?
a) Parsing and Translation b) Query Optimization c) Execution:
____8. Which Relational Algebra Operations Filters rows based on a condition?
a) Selection b) Projection c)Cartesian Product d) Union
____9. Which one is procedural (specifies how to retrieve data)?
a) Relational Algebra b) Structural Query Language
____10. Which of the following is incorrect about Relational Algebra?
a) Relational Algebra helps in query optimization.
b) Relational Algebra forms the foundation for database theory.
c) Relational Algebra used in database design and normalization.
d) All of the above
e) None of the above
___11. A _________ consists of a sequence of query and/or update statements.
a) Transaction b) Commit c) Rollback d) Flashback
___12. Which of the following makes the transaction permanent in the database?
a) View b) Commit c) Rollback d) Flashback
___13. In order to undo the work of transaction after last commit which one should be used?
a) View b) Commit c) Rollback d) Flashback
___14. Consider the following action:
TRANSACTION.....
Commit;
ROLLBACK;
What does Rollback do?
a) Undoes the transactions before commit
b) Clears all transactions
c) Redoes the transactions before commit
d) No action
____15. In case of any shut down during transaction before commit which of the following statement
is done automatically?
a) View b) Commit c) Rollback d) Flashback
____16. The format for compound stored Procedure statement is
a) Begin ……. end
b) Begin atomic……. end
c) Begin ……. repeat
d) Both Begin ……. end and Begin atomic……. End
____17. A stored procedure in SQL is a___________
2|Page
 a) is a collection of pre-compiled SQL statements.
b) It can be stored and run as a single unit.
c) Group of SQL statements compiled into a single execution plan.
d) All
Part II: Write the SQL Code for the following questions (4 questions with 2 points each and a
total of 8 points)
1. Write a Procedure which reads two Real Numbers and Print their average.
create procedure findAvergae(@num1 decimal(15,2),@num2 decimal(15,2))
as
declare @average as decimal(15,2);
set @average = (@num1 + @num2)/2;
print 'The average is '+cast(@average as varchar(50));
exec findAvergae 10.45,4.7;
2. Write SQL code to create the following Table with given Schema
MEMEBERS table
 CardNo - 5 characters, primary key,
 Surname - up to 15 characters,
 Name – as above,
 Address –up to 150 characters,
create procedure create_table
as
begin
create table MEMBERS(
CardNo varchar(5) primary key,
Surname varchar(15),
Name varchar(15),
Address varchar(150))
end;
exec create_table;

3. Write Stored Procedure which takes 4 values for each of the above attributes in members
table and insert the data in members table.
create procedure insert_data(@CardN0 varchar(5),@Surname varchar(15),@Name
varchar(15), @Address varchar(150))
as
begin
insert into MEMBERS (CardNo,Surname,Name,Address)
values(@CardN0,@Surname,@Name,@Address)
print ' Data Entered ';
end;
exec insert_data 101,'Alemu','Saba','Addis Ababa Bole Kefle Ketema Woreda 5';

4. Write transaction which changes the record of CardNo C001 Name to Alemu Kebede. Apply
try and catch to identify and display appropriate Message.
Begin by entering The Data:-
exec insert_data 'C001','Zergaw','Abebe','Addis Ababa Lemi Kura Kefle Ketema
Woreda 5';
Then To Change
create procedure Change_Data(@CardNo varchar(5),@SurName varchar(15),@Name varchar(15))
as

3|Page
 begin
begin transaction to_Change_Data
begin try
update MEMBERS set SurName=@SurName , Name=@Name where
CardNo=@CardNo;
commit transaction to_Change_Data;
print 'Data Changed';
end try
begin catch
rollback transaction to_Change_Data;
print 'Error.Data Not Change. Try Again.'
end catch

end;
exec Change_Data 'C001','Kebede','Alemu';

ANSWER SHEET
Name_______________________________ID. No, ___________Section, _____
True/False Multiple choice
1. 1. 6. 11. 16. Total marks S/he scored
2. 2. 7. 12. 17.
3. 3. 8. 13.
4. 4. 9. 14.
5 5. 10. 15

4|Page