Transcript Database Administration

DATABASE ADMINISTRATION
ITEC 450 Fall 2012
Instructor: Dr. Rama Gudhe
INTRODUCTION OF WEEK 1
Fall 2012
Course overview
 Database technology
 DBA’s role and classifications
 DBA’s tasks
 DBA career path

ITEC 450
2
COURSE MODULES
Fall 2012
DBA techniques and practices
 Database installation and configuration:
 Database management
 Database tuning
 Metadata, tools, and data warehousing

ITEC 450
3
COURSE OUTCOMES
Fall 2012
Database administration roles
 DBMS installation and physical database design
 Database connectivity (network), availability,
and security
 Database backup and recovery
 Database performance monitoring and
optimization
 Database administration hands-on experiences
and best practices

ITEC 450
4
MODULE 1
DATABASE ADMINISTRATION
TECHNIQUES AND PRACTICES
Section I Database Administrator
DATABASE TECHNOLOGY
Fall 2012
Database –organized store of data
 DBMS –database management system designed
to create, store, and manage databases, normally
including a Structured Query Language (SQL)
application programming interface
 SQL –Standard query language to access
database

ITEC 450
DDL -Commands to define or modify a database
structure, including creating, altering, and dropping
tables, indexes, constraints
 DML -Commands to modify and query database data

6
ADVANTAGES OF DBMS
Fall 2012

Advantages of DBMS

ITEC 450
- Control of data redundancy - Economy of scale
- Data consistency
- Balance of conflicting requirements
- More information from the same amount of data
- Sharing of data
- Improved data accessibility and
responsiveness
- Improved data integrity
- Increased productivity
- Improved security
- Improved maintenance through
data independence
- Enforcement of standards
- Increased concurrency
- Improved backup and recovery
services
Disadvantages of DBMS
- Complexity, Size, Cost of DBMSs, Additional H/W costs
- Cost of conversion, Performance, Higher impact of a failure
7
RDBMS TYPES AND VENDORS
Winter 2012
RDBMS – DBMS that incorporates the relational
data model.
 Major DBMS and dominated platforms
 Oracle –Unix and Linux
 DB2 (IBM) – zSeries and LUW
 SQL Server (Microsoft) – Windows
 Other DBMS: Teradata, Sybase, MySQL
(Oracle), Access

ITEC 450
8
DATABASE ADMINISTRATOR (DBA)


The DBA is responsible for designing and
maintaining an enterprise’s database
A DBA ensures the ongoing operational functionality
and efficiency of an organization's databases and
applications.
A good DBA needs to enjoy challenges and be a good
problem solver
System DBA (Production DBA) –DBMS focus, rather
than business



ITEC 450

Fall 2012

Focus on physical installation and performance monitoring
Remote DBA
Application DBA (Development DBA) –database
design and ongoing support for a specific application


An expert at writing and debugging complex SQL
Understand the best ways to incorporate database requests
into application programs
9
DATABASE ADMINISTRATION
ITEC 450
logical design and physical
implementation
 Performance monitoring and tuning
 Database management and availability
 Database security and authorization
 Database backup and recovery
 Data integrity and loading
Fall 2012
 Database
10
DATABASE ADMINISTRATION FUNCTIONS
ITEC 450
of hardware and software
 Installing/upgrading DBMS
 Tuning database performance
 Improving query processing performance
 Managing data security, privacy, and
integrity
 Data backup and recovery
Fall 2012
 Selection
11
DATA ADMINISTRATION FUNCTIONS
ITEC 450
policies, procedures, standards
 Planning
 Data conflict (ownership) resolution
 Internal marketing of DA concepts
 Managing the data repository
Fall 2012
 Data
12
DBA CAREER PATH
DBA Certification

ITEC 450
Oracle Certified Professional (OCP)
 Microsoft Certified Database Administrator
(MCDBA)

Fall 2012

On-job development:
Database developer -> DBA
 Small company to perform all roles

13
ADDITIONAL LEARNING ON ORACLE

Types of Oracle Database Users


DBA, Data Administrators, Application Developers,
Database users
Common Oracle DBA Tasks


ITEC 450
Oracle Database Administrators Guide;
 Oracle Database 2 Day DBA

Fall 2012
Oracle – relational database
 Learning materials:

Install, create db and users, backup, implement, tune
Database Administrator Security and Privileges

Operating system, database (sys, system)
14