Topic 1:Introduction to Database

Administration

B Y: H A RU N K A M AU

By: Harun kamau-maseno university CCS 319:Database Administration

 Definitions

 Data Administration: A high-level function that is

responsible for the overall management of data
resources in an organization, including maintaining
corporate-wide definitions and standards
 Database Administration: A technical function that is
responsible for physical database design and for
dealing with technical issues such as security
enforcement, database performance, and backup and
recovery

By: Harun kamau-maseno university CCS 319:Database Administration

 Data Administration
 Data make a valuable asset.

 Data are used at many business

levels

 There are many databases and

database systems in an
organization.
 TPS=Transaction Processing Sys.
 MIS=Management Info. Sys.
 Responsibility for managing data -  DSS=Decisions Support Sys. &
Data Administrator (DA) or Data  EIS=Executive Info. Sys.
Analyst

By: Harun kamau-maseno university CCS 319:Database Administration

 Data Administration functions

 Data policies, procedures, standards

 Planning
 Data conflict (ownership) resolution
 Internal marketing of DA concepts
 Managing the data repository

By: Harun kamau-maseno university CCS 319:Database Administration

 Policies, procedures and standards

 Policies: General Statements of Direction or action that

communicate and support DBA goals
 Procedures: Written Instructions that describe a services
of steps to be followed during the performance of a
given activity
 Standards: More detailed and specific than policies,
and describe the minimum requirements of a DBA
activity
 Rules that are used to evaluate the quality of the activity

By: Harun kamau-maseno university CCS 319:Database Administration

 Areas of policies and procedures

 End-User database requirements gathering

 Database design and modeling
 Documentation and Naming conventions
 Design, coding, and testing of applications
 Database software selection
 Database security and integrity
 Database backup and recovery
 Database maintenance and operation
 End-user training

By: Harun kamau-maseno university CCS 319:Database Administration

 Database Administrator DBA

 DBA actively participates in DB system life cycle (plan, develop, install, manage,
upgrade…).
 . DBA manages DB system:
 2.1 Users: Creating user accounts, assigning use privileges
 2.2 System performance: Monitoring and tuning
 2.3 Backup & recovery: Supervising backups & system restoration after crashes
 2.4 Security: Monitoring

By: Harun kamau-maseno university CCS 319:Database Administration

 Database Administration Tasks

 Selection of hardware and software

 Installing/upgrading DBMS
 Tuning database performance
 Improving query processing performance
 Managing data security, privacy, and integrity
 Data backup and recovery
 User management
 Database planning.
 Design and implementation
 Data dictionary management

By: Harun kamau-maseno university CCS 319:Database Administration

 Database Planning & Design

 Estimation & Design (logical, physical)

 Data storage requirements, forms & reports needed (costs of development),
hardware needs, matching organizational needs with DBMS products
 Time, labor & cost to develop
 Data modeling – coordinates with Data Analyst in the domain of logical
design (e.g., class diagrams, user interface). Also DA and DBA cooperate on
schemas.
 In charge of technicalities of physical design (types of files, access structures,
DBMS product, hardware)

By: Harun kamau-maseno university CCS 319:Database Administration

 Database design and implementation

 Determination and Enforcement of Standards and Procedures

 Ensure the Design activities are performed within the Standards and Procedures

 Ensure Transactions are: Correct, Efficient, Compliant with Integrity and Standards

 Physical Design
 Defining technology standards:
 Programming standards.
 Layout and techniques.
 Variable & object definition.
 User interface.
 System testing techniques.
 Loading databases.
 Backup and recovery plans.
 User and operator training.

By: Harun kamau-maseno university CCS 319:Database Administration

 System upgrade

 Determines need for change

 Size and speed of the DB system
 Usage patterns
 System output:
 Additional reports & queries (coop. with DA and business analysts)
 Forecasting needs

By: Harun kamau-maseno university CCS 319:Database Administration

 Hardware and Software selection

 Important things to consider include:

 CPU- 8 cores, 2 . MHz and above

 RAM- 8 GB and above

 Hand drive- 1 TB and above, with RAID(Redundant Array of Inexpensive Disks)

 Network Adapter- 1 Gbit and above

 Keep up with current technological trends

 Predict future changes
 Emphasis on established off the shelf products

 Must be based on the Organization’s Needs

 So the first step is to determine companies NEEDS

By: Harun kamau-maseno university CCS 319:Database Administration

 Managing data security and privacy

 Protection of data against accidental or intentional loss,

destruction, or misuse
 Firewalls
 Establishment of user privileges
 Complicated by use of distributed systems such as internet
access and client/ server technology.

By: Harun kamau-maseno university CCS 319:Database Administration

 Managing Data Integrity

 Integrity controls protects data from unauthorized use

 Data consistency
 Maintaining data relationship
 Domains- sets allowable values
 Assertions- enforce database conditions

By: Harun kamau-maseno university CCS 319:Database Administration

 Data backup
Full
 We must assume that a database
will eventually fail Copies
Entire
 Establishment procedures Operational OpDB Backup
 how often should the data be back- Database
tend Database
up? Backup (OpDB)
Manager overwrites (Bkp DB)
 what data should be backed up more
frequently? Partial
 who is responsible for the back ups? (part of new data
 Back up facilities DBMS) Partial backup
 automatic dump- facility that produces t1
backup copy of the entire database Operational
grows
Database new data
 periodic backup- done on periodic
(OpDB)
basis such as nightly or weekly tend Partial backup
 cold backup- database is shut down
during backup
 hot backup- a selected portion of the
database is shut down and backed up
at a given time
 backups stored in a secure, off-site
Bkp DB
location

By: Harun kamau-maseno university CCS 319:Database Administration

 Database recovery

 Application of proven strategies for reinstallation of database after crash

 Recovery facilities include backup, journalizing, checkpoint, and recovery manager
 Journalizing facilities include:
 audit trail of transactions and database updates
 transaction log which records essential data for each transaction processed against the database
 database change log shows images of updated data. The log stores a copy of the image before and
after modification.
 Checkpoint facilities:
 when the DBMS refuses to accept a new transaction, the system is in a quiet state
 database and transactions are synchronized
 allows the recovery manager to resume processing from a short period instead of repeating the entire
day
 Recovery and Restart Procedures
 switch- mirrored databases
 restore/rerun- reprocess transactions against the backup
 transaction integrity- commit or abort all transaction changes
 backward recovery (rollback)- apply before images
 forward recovery (roll forward)- apply after images (preferable to restore/rerun)

By: Harun kamau-maseno university CCS 319:Database Administration

 Tuning database performance

 Set installation parameters/ upgrade DBMS

 Monitor memory and CPU usage
 Input/ output contention
 user striping

 distribution of heavily accessed files

 Application tuning by modifying SQL code in applications

By: Harun kamau-maseno university CCS 319:Database Administration

 Data dictionary management

 User Access Management

 Define each user to the database

 Assign Passwords

 Assign Access Privileges

 Read, Write, Delete

 Physical Access Control

 View Definitions: Protect and Control the Scope of the Data that is accessible to a user

 DBMS utilities access control: Limit the use of query and reporting tools

 DBMS usage Monitoring: Audit Logs

By: Harun kamau-maseno university CCS 319:Database Administration

 User management

 User Access Management

 Define each user to the database

 Assign Passwords
 Assign Access Privileges
 Read, Write, Delete

 Physical Access Control

 View Definitions: Protect and Control

the Scope of the Data that is
accessible to a user

 DBMS utilities access control: Limit

the use of query and reporting tools

 DBMS usage Monitoring: Audit Logs

By: Harun kamau-maseno university CCS 319:Database Administration
 Data warehouse Administration

 New role, coming with the growth in data warehouses

 Similar to DA/DBA roles
 Emphasis on integration and coordination of
metadata/data across many data sources
 Specific roles:
 Support decision –support applications
 Manage data warehouse growth
 Establish service level agreements regarding data warehouses and
data marts

By: Harun kamau-maseno university CCS 319:Database Administration