Role of Database Administrator ( DBA )

Oct 18 • Engineering Sample Papers • 3910 Views • No Comments on Role of Database Administrator ( DBA )

A database administrator is one who has all the authorities to control and manage the whole database. These includes the installation of the database, updating the database, configure the database, monitoring and maintenance of the database in an organization. An administrator has all the authorities an power to control the whole system, likewise the database administrator has all the authorities, all the powers to control, manage the whole database system. The duties of database administrator is to access all the information of the database. Database administrator can grant the user’s authority to access the database. It stores the profile of each user in database.

Database Administrator

Required Skills 
The following skills are required to become a database administrator.

• A person should have good communication skill.
• A person should have well knowledge about database theory.
• A person should have well knowledge about database design.
•  The person should have well knowledge about Relational Database Management System.
• A person should have goo knowledge in Structured Query Language (SQL).

Qualification

The person required Bachelor’s degree or higher in Computer Science Engineering or MCA. He/she should have well required knowledge in the concept of database engineering. The person may be required a certification from the organisation, as database administrator for the particular Relational Database Management System being used.

Responsibility 
The responsibilities of a database administrator are as follows:

1. The database administrator should install the database server.
2. The database administrator should upgrade the database server.
3. The database administrator should install and upgrade the application tools.
4. The database administrator should allocate the system storage required for the database system.
5. Database administrator should plan for the future storage.
6. Database administrator should modify the database structure.
7. Database administrator maintains the system security.
8. Database administrator controls user access to the database.
9. Database administrator monitors use access to the database.
10. Database administrator monitors the performance of the database.
11. Database administrator optimize the performance of the database.
12. Planning for backup and recovery system.
13. Should maintain the achieved data.

Schema

Schema is a logical structure which contains the objects like segments, views, procedures, functions, package, triggers, user-defined objects, etc. A segment is a data structure that can be a table, index or temporary files.

Table

A table is where the data is kept in rows and columns manner. The data which are to be stored in table should be interrelated and conceptual. Table is the heart of the database and a table is implemented in one schema in one table space.

Hierarchical level of Database Administrator

The hierarchical level of database administrator in organisation are as follows:

1. Data analyst/query designer
2. Junior DBAs
3. Midlevel DBAs
4. Senior DBA
5. DBA consultant
6. Manager or director of database administrator

Modes of operation in Oracle
Oracle has several modes of operation. When we want to start oracle, we just simple issue the command
 startup;
The modes of operation is divided into three phases.
(a) Nomount phase:- This is the first phase. In this phase, the database reads the initial parameter file and starts up the oracle memory structures as well as background processes.
startup nomount;
(b) Mount phase:- This is the second phase in modes of operation. In this phase, the previously read parameter file is used to find those control files which contains the name of the data files.
startup mount;
(c) Open phase:- This is the third phase. In this phase, the data files and redo logs are opened and making the available for normal operations.
startup open;

Procedure for shutdown the database
For shutting down the database, perform the following basic steps.
(a) Close the database including data files and redo logs.
(b) Unmount the database from the instance
(c) Shut down the instance to close the control files

Distributed management in Oracle
The distributed management in oracle are handled through distributed options. This includes

• Manage the transaction that can result from two-phase commit
• Create, edit and drop database lines
• Use streams to implement messaging
• Use advanced queues to pass messages.

Which areas you can manage the database objects?
In the following areas, we can manage the database objects.

• Control files
• Redo logs
• Undo management
• Schema objects
• Segments

Important SQL Queries

1. Query Emp table.
2. Select the employees in department 30.
3. List the names, numbers and departments of all clerks.
4. Find the department numbers and names of employees of all departments with deptno greater than 20.
5. Find employees whose commission is greater than their salaries.
6. Find employees whose commission is greater than 60 % of their salaries.
7. List name, job and salary of all employees in department 20 who earn more than 2000/-.
8. Find all salesmen in department 30 whose salary is greater than 1500/-.
9. Find all employees whose designation is either manager or president.
10. Find all managers who are not in department 30.
11. Find all the details of managers and clerks in dept 10.
12. Find the details of all the managers (in any dept) and clerks in dept 20.
13. Find the details of all the managers in dept. 10 and all clerks in dept 20 and all employees who are neither managers nor clerks but whose salary is more than or equal to 2000/-.
14. Find the names of anyone in dept. 20 who is neither manager nor clerk.
15. Find the names of employees who earn between 1200/- and 1400/-.
16. Find the employees who are clerks, analysts or salesmen.
17. Find the employees who are not clerks, analysts or salesmen.
18. Find the employees who do not receive commission.
19. Find the different jobs of employees receiving commission.
20. Find the employees who do not receive commission or whose commission is less than 100/-.
21. If all the employees not receiving commission is entitles to a bonus of Rs. 250/- show the net earnings of all the employees.
22. Find all the employees whose total earning is greater than 2000/- .
23. Find all the employees whose name begins or ends with ‘M’
24. Find all the employees whose names contain the letter ‘M’ in any case.
25. Find all the employees whose names are upto 15 character long and have letter ‘R’ as 3rd character of their names.
26. Find all the employees who were hired in the month of February (of any year).
27. Find all the employees who were hired on last day of the month.
28. Find all the employees who were hired more than 2 years ago.
29. Find the managers hired in the year 2003.
30. Display the names and jobs of all the employees separated by a space.
31. Display the names of all the employees right aligning them to 15 characters.
32. Display the names of all the employees padding them to the right up to 15 characters with ‘*’.
33. Display the names of all the employees without any leading ‘A’.
34. Display the names of all the employees without any trailing ‘R’.
35. Show the first 3 and last 3 characters of the names of all the employees.
36. Display the names of all the employees replacing ‘A’ with ‘a’.
37. Display the names of all the employees and position where the string ‘AR’ occurs in the name.
38. Show the salary of all the employees , rounding it to the nearest Rs. 1000/-.
39. Show the salary of all the employees , ignoring the fraction less than Rs. 1000/-.
40. Show the names of all the employees and date on which they completed 3 years of service.
41. For each employee, display the no. of days passed since the employee joined the company.
42. For each employee, display the no. of months passed since the employee joined the company.
43. Display the details of all the employees sorted on the names.
44. Display the names of the employees, based on the tenure with the oldest employee coming first.
45. Display the names, jobs and salaries of employees, sorting on job and salary.
46. Display the names, jobs and salaries of employees, sorting on descending order of job and within job sorted on salary.
47. List the employee names, department names and salary for those employees who have completed 1 year of service.
48. List the employee names, department names and salary for those employees who are earning 0 commission or commission is null. Sort your output in the order of department name.
49. List the employee names, department names and hiredate for those employees who have joined in 2003 . Sort your output in the order of joining date.
50. List all the department names along with the names of employees in them , irrespective of the fact whether any employee is there or not.

Related Links
Interview questions of Database Management System

be freshers interview question and answersEngineeringinterview question and answers for fresherssample paper

« Oracle in Database Engineering and Grid Computing Major functions of IT in Supply chain Management »