Tribhuvan University
Institute of Science and Technology
B.Sc. CSIT Seventh Semester Detailed-syllabus
Course Title: Database Administration
Course no: CSC-406 Full Marks: 60+20+20
Credit hours: 3 Pass Marks: 24+8+8
Nature of course: Theory (3 Hrs.) + Lab (3 Hrs.)
Course Synopsis: DBA Roles, DB backup, restoration and recovery, Tuning of database
Goal: The course covers about: principles of DBA Roles, DB backup, restoration and
recovery, Tuning of database and overall DB administration which could be useful for
administrator in the future.
Course contents:
Unit Course Content-breakdown Lecture Hours
Introduction: 5 Hrs
DBMS architecture and data independence
DBA roles and responsibilities
SQL *PLUS Overview
SQL *Plus Fundamentals
Producing more readable outputs
Accepting values at runtime
Using iSQL *Plus.
2. Log File Management: 5 Hrs
Introduction to Control and Redo Log Files
Managing the control files
Maintaining and monitoring redo log files
Multiplexing redo log files
Archiving log files
3. Managing users and security: 10 Hrs
Profiling and Managing users
managing user privileges and roles
managing and querying role information
Database Security and Auditing
Creating and managing DB objects
Tables, indexes, triggers, views, stored procedures, etc.
Transaction concurrency management
4. DB Backup and Recovery: 10 Hrs
Backup and Recovery Overview
Database backup, restoration and recovery
defining of backup and recovery procedure
Testing the backup and recovery plan
parallel instance recovery
recovering from non-critical loses
5. Oracle Recovery Manager (RMAN): 5 Hrs
Collection by: www.csitprogram.blogspot.com
Database corruption
automatic storage management
RMAN configuration
Database Archival
6. Unit 6: DB Performance Tuning: 10 Hrs
Introduction
Tuning methodology
Tuning concepts
AADM (Automatic Database Diagnostic Monitor)
SQL Tuning Advisor
AWR Report
Virtual Private Database
Policy types, selective columns, column masking
Laboratory works:
1. Installation of Oracle Database
2. Database Creation
3. User Creation
4. Role, Privileges and group management
5. Database object creation
a. Tables
b. Indexes
c. Views
d. Triggers
e. Stored Procedures
f. Function
g. Package, etc.
6. Database Backup
a. Online backup
b. Offline backup
7. DB Recovery technique
a. Export and Import utility
b. Data Pump
c. Data guard
8. RMAN
9. Database Archiving
10. Performance Tuning
a. ADDM Report
b. AWR Report
c. Spot Light
d. OEM
Text Books:
1. Introduction to Database Administration, by O’reilly
2. ORACLE DBA handbooks
Collection by: www.csitprogram.blogspot.com
3. C.J. Date, Database Systems, Addison Wesley, 2000
Homework Assignment:
Assignment should be given throughout the semester and must be done individually.
Computer Usage: No Specific
Prerequisite: Database Management System
Category Content: Science Aspect: 40%
Design Aspect: 60%
Committee:
Prof. Dr. Subarna Shakya (Expert) -Tribhuvan University
Bishnu Nath Gautam -New Summit College
Ram Krishna Dahal -Kathford College
Laxman Adhikari -Amrit Science Campus
May 29-30-2012 (Jestha 16th and 17th 2069)
Central Department of Computer Science and Information Technology, TU
Collection by: www.csitprogram.blogspot.com
Model Question (Database Administration)
Tribhuvan University
Institute of Science and Technology
Bachelor Level/Fourth Year/Seventh Semester/Science Pass Marks: 24
Database Administration (CSC-406) Full Marks: 60
Time: 3 Hours.
Candidates are required to give their answers in their own words as far as practicable. The
figures in the margin indicate full marks.
Group A
Long Answer Questions (Attempt any TWO questions) [2×10=20]
1. What do you mean by database management system? Mention the clear architecture of
database management system including its different files.
2. You are working as DBA in Nepal Clearing House Limited which handle 24/7 online
system. You have to design security and backup procedure for the organization.
3. What are the various kinds of performance report available in the oracle database
management system mention the name and describe them one by one.
Group B
Short Answer Questions (Attempt any Eight questions) [8×5=40]
4. What is DML? Explain all the SML command with suitable example.
5. What is a view? Write an importance of view.
6. Explain the role of database administrator?
7. What is ASM? Mention the capabilities of ASM.
8. Write the command to add new redo group and drop group in the database.
9. Write DDL command for following.
a. Change table product to product_org
b. Add new check constraints in the status field of product_org table value of status
field should always (00,01 and 09)
c. Create backup table of sales
d. Disable check constraint in the table.
10. What is AWR report? Write all the component of AWR report.
11. What do you mean by parallel instance recovery?
12. Write a trigger to create log of every update and delete statement of product table.
13. Write short note of followings:
a. Step to create backup device
b. Internet database connector
Collection by: www.csitprogram.blogspot.com
