 |
DBA712S - DATABASE ADMINISTRATION - 2ND OPP- JAN 2020 |
|
|
1 Page 1 |
▲back to top |
NAMIBIA UNIVERSITY
OF SCIENCE AND TECHNOLOGY
FACULTY OF COMPUTING AND INFORMATICS
DEPARTMENT OF COMPUTER SCIENCE
QUALIFICATION: BACHELOR OF COMPUTER SCIENCE, BACHELOR OF INFORMATION TECHNOLOGY
QUALIFICATION CODE: 07BACS, 80BSAN, 80BSSD LEVEL: 7
COURSE: DATABASE ADMINISTRATION
COURSE CODE: DBA712S
DATE: JANUARY 2020
SESSION: 2
DURATION: 3 HOURS
MARKS: 100
SUPPLEMENTARY/SECOND OPPORTUNITY EXAMINATION QUESTION PAPER
EXAMINER(S)
MIR. ISAAC NHAMU
MODERATOR:
DR. AMER DHEEDAN
THIS QUESTION PAPER CONSISTS OF 6 PAGES
(Excluding this front page)
INSTRUCTIONS
Answer ALL the questions.
Write clearly and neatly.
Number the answers clearly.
When answering questions you should be guided by the allocation
of marks in [ ]. Do not give too few or too many facts in your
answers.
PERMISSIBLE MATERIALS
1. Non programmable Scientific Calculator.
|
|
2 Page 2 |
▲back to top |
Section A: Multiple Choice, 15 marks one mark each.
1. Can an Integrity Constraint be enforced on a table if some existing table data does
not satisfy the constraint?
A. Yes
B. No
2. A View is
A. A more detailed look at a table
A description of column names
A diagram of table relationships
A pre-programmed SELECT statement against one or more tables
A stored set of data on the database
3. SQL*Pump is a utility for:
A. Loading data from an external file
Export and import data and metadata from a database
Reformat data during loading a database
Checking for incompatible data and discard them
Loading data using SQL INSERT commands
4. A User can
A. Be assigned multiple roles
Have multiple schemas
Have the privilege to access one or more table spaces
Have an authorization method that does not require a password
Have multiple concurrent sessions
5. What does the Listener NOT do??
A. Create a server process
Monitor network traffic on a particular port
Receive requests to connect to one or more databases
Keep a client session active
Provide network listening services for non-Oracle applications
6. Which of the following is not a reason for the fact that most of the processing is
done at the server?
A. To reduce network traffic.
B. For application sharing.
G To implement business rules centrally.
D. None of the above
1|Page
|
|
3 Page 3 |
▲back to top |
7. Multiple Tablespaces can share a single datafile
A. TRUE
B. FALSE
8. Net Manager can NOT be used to:
Associate a connect string with a global database service name
Shutdown a database remotely over a network connection
Identify the network protocol used to access a database server
Implement logging and tracing for network connections
Verify that a user can log on from a client to the database
9. What is NOT true using a Shared Server
Is good when the number of users is small
Is good in a Data Warehouse environment
Should be used for Database Administration activities
Facilitates effective access for large numbers of transactional sessions
Is preferable when using the SQL*Loader Utility
10. While loading data from a file, a record is rejected because of inconsistent data. Who
should solve the problem?
Application User
Project manager
Database Administrator
Data Administrator
Applications Programmer
11. Creating an Account does not require
A unique user-id
A list of tables to be accessed
A user profile
An authentication method
One or more tablespaces to be used
12. SQL DOES NOT contain
A. IF... THEN conditions
B. Data manipulation statements
C. The ROLLBACK command
D. Acommand to reset a user’s own password
2|Page
|
|
4 Page 4 |
▲back to top |
13. Which action is typical Reactive Monitoring?
A. Monitor decrease in free space
Monitor CPU usage after response time drops significantly
Monitor memory usage over time
Monitor average number of rows in a table
Monitor average number of user sessions
14. The Data Manipulation Language statements are
A. INSERT
B. UPDATE
C. SELECT
D. All of the above
15. After a server crash due to power failure, what is needed?
A. Manual database recovery
The STARTUP RECOVER command
Automatic rollback of previously unfinished transactions
Empty out buffers
Reset the log files
3|Page
|
|
5 Page 5 |
▲back to top |
Section B: Structured Questions
Question 1
a. Describe what each of the following schema objects does for Database
Administration:
[10]
i. SGA
ii, Index
iii. PMON
iv. Trigger
v. Sequence
b. Describe the three common database interfaces in cloud based Oracle databases. [6]
Question 2
a. Put the following database storage structures in a hierarchy in order of size and
indicate a typical size for each.
[4]
Extent, Segment, OS Block, Data Block
b. In Database Administration, what is a Data Dictionary?
[2]
c. What information does it store (give three examples of such information
including code on how to get that information).
[6]
Question 3
a. Differentiate between User privileges and Roles.
[2]
b. Give four benefits for the use of Roles in database administration and explain what
advantage they offer.
[8]
4|Page
|
|
6 Page 6 |
▲back to top |
Question 4
a. What is the content of the Redo Log files, and why are they so important for a DBMS?
[4]
b. A block in a database file (after formatting) contains a header, data, and free space.
a) Why is free space important?
[2]
b) Describe a situation where you would like very little free space in the database
and why?
[2]
c) Describe a situation where you would like a high percentage of free space and
why.
[4]
Question 5
a. What is the purpose of assigning a profile to a user; describe two?
[4]
b. Give at least three factors that DBAs consider when selecting an authentication
method for their users.
[6]
c. When creating users why is it important to assign them temporary tablespaces?
[2]
d. What happens if you do not assign them temporary tablespaces?
[2]
e. What is the relationship between a user and a schema?
[2]
f. Differentiate between image copies and backup sets types of backup. What is the
main advantage of backup sets over image copies?
[4]
5|Page
|
|
7 Page 7 |
▲back to top |
Section C: Database SQL commands
Question 1
a. Describe what each of the following SQL commands achieve?
[10]
i. CONNECT ruva/rashe @NUST.NA
ii. lsnrctl STATUS
ii.
SELECT name, con_id from v$pdbs
iv.
Tnsping sales
v. UTL_SMTP
b. You are given a text file Sal_info.dat with comma separated values Full_name,
Salary, Grade as shown in Figure 1 below.
Mweulwa Sheya, 3500, D
Emma Kahimise, 3500, D
Patritia Shilongo, 4100, C
Auther Niilungu, 5300, B
Letty Muyandulwa, 3500, D
David Simeon, 1800, F
Matilde Nehale, 6450, A
Eliaser Nekundi, 3500, D
Eunice Tomeka, 5300, B
|
Figure 1
Create table called Sal_Table in the SYSTEM Schema with suitable column names
and data types to accommodate the data in Figure 1.
[6]
Create a suitable control file call it Sal_Info.ctl in C:\\ExportLab. Be sure to name the
bad file and discard files appropriately.
[10]
Show the sqlldr command used to load the data in Figure 1 to the SYSTEM schema.
Assume the password for SYSTEM is nustuser!.
[4]
<<<<<<<< END >>>>>>>>
6|Page