 |
DBA721S - DATABASE ADMINISTRATION - 1ST OPP - JUNE 2025 |
|
|
1 Page 1 |
▲back to top |
n Am I BI A u n IVER s I TY
OF SCIEnCE Ano TECHnOLOGY
FACULTY OF COMPUTING AND INFORMATICS
DEPARTMENT OF COMPUTER SCIENCE
QUALIFICATION: Bachelor of Computer Science
QUALIFICATION CODE: 07BACS
COURSE NAME: Database Administration
DATE:JU!\\IE
DURATION: 3 HOURS
LEVEL: 7
COURSE CODE: DBA721S
PAPER:THEORY
MARKS: 70
EXAMINER
MODERATOR
FIRST OPPORTUNITY EXAMINATION QUESTION PAPER
MS. Albetina Shilongo
Mr. Ericky lipumbu
Mr. Paulus Kautwima
THIS QUESTION PAPERCONSISTSOF 6 PAGES{Including this front page)
INSTRUCTIONS
1) Answer ALL the questions on the answer scripts provided.
2) Write clearly and neatly.
3) Be guided by the number of marks allocated when answering the questions.
4) Show all your calculations.
5) Number your questions clearly.
pg. 1
|
|
2 Page 2 |
▲back to top |
Section A: Multiple Choice and true and false: Write the question number and correct letter next to it.
[10]
1. Which statement correctly defines a trigger?
a) A piece of logic written in PL/SQL.
b) Executed at the arrival of a SQL*FORMS event.
c) Both A & B
d) None of the above.
2. The size of a tablespace can be increased by performing which of the following?
a) Increasing the size of one of the Datafiles
b) Adding one or more Datafiles
c) Cannot be increased
d) None of the above
3. What actions are required following a database server crash caused by a power failure?
a) Manual database recovery
b) STARTUPRECOVERcommand
c) Automatic rollback of previously unfini~hed transactions
d) Empty out buffers
4. Which of the following statements correctly describe a View?
a) A more detailed look at a table
b) A description of column names
c) A diagram of table relationships
d) A pre-programmed SELECTstatement against one or more tables
5. Which of the following is not required when creating an account?
a) A unique user-id
b) A Schema
c) A user profiles
d) An authentication method
6. SQL* Pump is a utility designed for which of the following functions?
\\ a) Loading data from an external file
,·' b) Export and import data and meta data from a databasw-
, · c) Reformat data while loading a database
d) Checking for incompatible data and discarding them
7. Which of the following statements about a user is correct?
a) Have multiple schemas
pg.2
|
|
3 Page 3 |
▲back to top |
b) Have the privilege to access one or more table spaces
c) Have an authorization method that does not require a password
d) Have multiple concurrent sessions
8. Which of the following is not a function of the Oracle Listener?
a) Monitor network traffic on a particular port
b) Receive requests to connect to one or more databases
c) Keep a client se9ssion active
d) Provide network listening services for non-Oracle applications
9. All data files related to a tablespace are removed when the tablespace is dropped.
a) TRUE
b) FALSE
10. Can an Integrity Constraint be enforced on a table if some existing data does not satisfy the
constraint?
a) Yes
b) No
Section B: Structured Questions
[43]
1. Describe the role of each of the following database objects:
[10]
a) View
b) Index
c) Package
d) Trigger
e) Sequence
2. You have recently been appointed as a Junior Database Administrator in an organization. The finance
department requires a new application that will rely on the Oracle database to manage sensitive·
financial data. Currently there is an older version or Oracle running on the servers. Your task is to
recommend, install, and configuring the server to run the new version which is Oracle server Database
10g software to support this application. Discussthe steps you.will undertake to correctly accomplish
th~ tasks defined above.
'
[S]
3. As a Junior Database Administrator setting up Oracle Database 10g for a finance application, one key
task is ensuring that the right privileges and roles are granted to users. Explain the difference between
roles and privileges with examples.
[4]
4. Discuss any four benefits of assigning roles to users in database administration.
pg.3
|
|
4 Page 4 |
▲back to top |
(8)
5. When creating a user, the administrator may choose the appropriate authentication method based on
the environment. List and explain three authentication techniques commonly used in database
management.
[6]
6. When creating users, why is it important to assign them temporary tablespaces?
(2)
7. Explain the potential consequences of not assigning temporary tablespaces to users during their
creation.
(6)
8. Describe the relationship between a user and a schema?
(2)
Question 4
1. As ad database administrator for Tunga group of companies you manage a huge Sales table containing
multiple rows of their sales information. The sales table includes the following columns:
• Sale_lD (Primary Key)
• Customer _ID
4l Product ID
• Sale- Date
• Total Amount
• Region
On a weekly basis you run a query to generate weekly report using the following query:
SELECTRegion,
SUM(Total_Amount) AS Total_Sales
FROM Sales
WHERE Sale_Date BETWEEN TO_DATE ('2025-04-01', 'YYYY-MM-DD')
AND TO_DATE ('2025-04-07', 'YYYY-MM-DD')
GROUP BY Region
a) Identify the columns that you would index to improve the performance of the query and justify your
answer.
[4)
b) Describe the drawbacks of creating indexes in the database
(4)
c) Explain any 2 instances where Oracle or any other DBMS automatically create indexes?
(4)
Section C: Database SQL commands
[17)
1. Describe what each of the following SQLcommands achieve?
(10]
i.
CONNECT erasmus/kermit
as sysdba
ii. SELECT name from v$database
iii. LSNRCTL>set cur backup
iv. STARTUP NOMOUNT
pg.4
|
|
5 Page 5 |
▲back to top |
V.
CREATE PROFILE app user2 LIMIT PASSWORDGRACE TIME 10;
2. Describe the function of the Fine-Grained Audit Policy named audit_emps_salary below.
[7)
dbms_fga.add_yolicy
object_schema
object_name
policy_name
audit condition
audit column
handler schema
handler module
enable
statement_types
=> 'hr',
=> 'employees ' ,
=> 'audit_emps_salary',
=> 'dept_id=lO',
=> 'salary',
=> 'secure',
=> 'log emps_salary',
=> TRUE,
=> 's-:,lect') ;
<<<<<<<<END>>>>>>>>
pg.5
|
|
6 Page 6 |
▲back to top |
pg, 6