Database Administration: Key Concepts

Key Concepts in Chapter 1: What is a DBA?

• Database Administration is important to learn
• DBA roles provide a unique vantage point
• The role has a management discipline component
• Focus on data, databases and all systems
• The role has specific tasks and responsibilities
• Various types of DBAs can specialize in different areas
• Multiplatform issues need to be addressed by DBAs
• Production environments differ from test environments
• Newer technologies impact the role of the DBA
• There is DBA certification on top of all of this

What is Data?

• Data is central for all modern applications
• Data powers computerized applications
• Application programs focus on reading, writing, analyzing, moving, calculating, and modifying data
• Without data, programs would be useless

What is a Database?

• Databases are structured and persistent sets of data
• Databases are collections of related data
• Phone books are an example of a database
• Databases are associated with software
• Databases can be files containing records and fields of fixed types and lengths
• Databases are organized stores of data accessible by name (fields, records, files)
• Databases are created to store and organize data

What is a DBMS?

• A Database Management System (DBMS) is a software package
• Designed to create, store, and manage databases
• DBMS software allows end users or application programmers to share data
• DBMS provides systematic methods for creating, updating, retrieving, and storing information
• They ensure data integrity, control access, and handle rollback, restart, and recovery

Database Administrator

• Database administrators are responsible for ensuring the ongoing operational functionality and efficiency of an organization's databases and applications
• Every organization using a DBMS needs a database administration group
• Group ensures the company's databases are used and deployed effectively

Why Learn Database Administration?

• Data is central to modern applications
• Organizations cannot function without data
• Today's businesses are primarily driven by data
• DBAs are responsible for the design and maintenance of databases
• The role places the DBA at the center of business operations
• DBAs learn about many facts of business
• The DBA can discover how business utilise their data
• DBAs learn new technologies as adopted by the organization
• Good database design/utility improves an organization's competitive position

What Makes a Good DBA?

• Vast technical knowledge
• On call
• Communication skills
• Be a problem solver
• Enjoys challenges
• Exposure to new technologies
• Can work alone or as part of a group

Splitting Business and Technical Aspects

• Data administrators focus on the business aspects
• Database administrators focus on the technical aspects

Data administrator (DA)

• Focuses on business and is responsible for:
• Identifying and cataloging data requested by business users
• Creating conceptual and logical data models
• Production of an enterprise model for all business processes
• Sets data policies for the organization
• Identifying data owners + stewards
• Sets standards for control and data usage

What is Metadata?

• Data about the data
• Example: The number 12 could mean (day of the month, age, IQ, etc)

Data Administrator vs Chief Data Officer

• Not about technology, more about the meaning of data in the organization
• Responsible for rallying the organization to use data as a corporate asset
• Handles metadata
• Concerned with data quality, integrity, and reuse
• Will invariably implement and staff the DA function

Database Administrator (DBA)

• Focuses on technologies used to manage the data
• Works with Data and System Administrators
• Accepts logical models from the DA to implement physical databases
• Works with SA (system admin) to schedule/implement system software (e.g., DBMS)
• Tunes the environment appropriately
• Communicates data models to developers and other technicians
• Maps data models to create physical databases
• Works with application programming staff

System Administrator (SA)

• Focuses on DBMS (database management systems)
• Concerned with the systems implementation/operations
• Responsible for:
• Installation/ setup of resources for computing
• Pure technologist
• No responsibility for database design and support
• Infrastructure support
• Setting up the DBMS
• IT infrastructure is implemented to work with other system software
• Applying ongoing maintenance from the DBMS vendor

Ensuring Data Quality

• An organization's data must be useful, usable, available, and correct
• To make this a reality DBA must performs in a variety of areas

DBA General Tasks

• Database Design
• Performance monitoring and tuning
• Database availability
• Security
• Backup and recovery
• Data integrity
• Release migration

DBA Task: Database Design

• The DBA must transform a logical data model into a physical database implementation
• Must ensure the database can properly enable a useful database for its app and client

DBA Task: Performance Monitoring and Tuning

• Five factors influence database performance
• Workload describes intensity of resources use
• Throughput describes total capability
• Resources refers to the the hardware and software available
• Optimization refers to the analysis reqests
• Contention (conflict) describes components attempting to monopolize the resources
• Database performance optimized to minimise workload

Workload

• Combination of online transactions, batch jobs, ad hoc queries, data warehousing, analytical queries, and commands

Throughput

• Defines overall capability of the computer hardware and software to process data

Resources

• Hardware and software the system can use

Optimization

• Refers to analysis of database requests

Contention

• Condition in which two or more components of the workload are attempting to use a single resource in a conflicting way
• For example, dual updates of some data
• Contention increase > throughput decreases

3.Database Availability Tasks

• Databases must be available on-demand
• Automated alerts warn of DBMS outages and the need for corrective action
• DBA designs the database to maintain minimum disruption

4.Database Security and Authorization

• Prevents security breaches.
• Only authorize programmers and users to access the database.
• DBAs ensure data is available to authorized users.

Security Administration Actions

• Grant and monitor access to a variety of tasks to manage user accounts Creating database objects (databases, tables, views, etc.)
• Altering structure of database objects
• Reading and modifying the data
• Creating and accessing user-defined functions and data types
• Start/stop databases (and associated database objects)
• Setting and modifying DBMS parameters
• Running database utilities (LOAD, RECOVER, REORG)

5.Backup and Recovery

• DBAs prepared to recover data in the event of a problem
• Problems can come in all shapes and sizes

3 Types of Database Recovery

• Recover to current – Reverts to current version
• Point-in time recovery – Revert to prior version
• Transaction recovery – Rollback specific transactions

6.Data Integrity

• Database designed to store the correct data
• Database designed in the correct way
• Damaged data not stored
• Corrupted data not stored

3 Aspects of Database Integrity

• Physical
• Semantic
• Internal

Types of Database Constraints

• Referential integrity constraints
• Used to specify columns in tables to define relationships
• Unique constraints
• Ensure that the values or a column or set of columns only appear once in the table
• Check constraints
• Used to place more complex integrity rules inside tables

Data Types

• Physical constraints that enforce the type of data stored

Semantic Integrity

• Refers that data in the database has good data quality

Internal Integrity

• Refers to the integrity of the database internal structure

Index Consistency

• If index is out of sync access fails

Pointer Consistency

• Ptrovides reliable access to multimedia

Backup Consistency

• Checks old backups integrity

7.DBMS Release Migration

• Managing migration from release to release is key
• DBMS products change frequently
• New versions of DBMS released almost yearly
• Keeping DBMS running and up-to-date is ongoing
• Effort consumes many DBA cycles
• Approach must conform to organization’s needs

Common Type of DBA

• System DBA focuses on physical aspects
• Database Architect: Design + implementation of new databases
• Database Analyst
• Data Modeler
• Application DBA
• Focus on database design and ongoing support
• Able to perform data change management/performance
• Task Oriented DBA
• Specialized DBAs that focus on a specific task
• Performance Analyst
• Focuses on performance
• Data Warehouse Administrator - Online Analytical Processing (OLAP) for performing in-depth data analysis

System DBA Tasks

• Installing new DBMS versions
• Applying maintenance fixes
• Setting and tuning system parameters
• Tuning the operating system, network, and transaction processors
• Ensuring appropriate storage for the DBMS
• Enabling the DBMS to work with storage devices
• Interfacing with other technologies
• Installing third-party DBA tools.

Skills for A Database Architect

• Creating a logical data model
• Translating logical data models into physical database designs
• Implementing efficient databases (physical storage)

What is Data Warehousing?

• Data warehouses involve collecting data from many places
• Storing it
• Managing it to see business value

OLAP vs OLTP

• OLTP (Online Transactions Processing) efficiently manages of short transactions
• OLAP (Online Analytical Processing) organizes analytical queries without affecting transactional systems.

Multiplatform DBA Issues

• Managing multi-platform environments complicates database administration Issues to address:
• Features of each DBMS
• Features and characteristics of the operating system
• Networking
• DBMS Skills
• Support for programming languages
• Organizational requirements.

Production Versus Test Environments

• Environments created to qualify database implementation
• Test and Production
• Development happens in the TEST, apps runs in PRODUCTION
• Test + QA
• Programmer tests output and contents of database
• Program logic is correct.
• Quality assurance environment

The Impact of Newer Technologies on DBA

• DBAs need to be to be on top on new new technologies
• Understand how databases integrate with new technologies

Let's analyze three newer technologies that rely on databases:

• Database-coupled application logic
• Internet-enabled E-Business development
• Handheld computing

Procedural DBAs: Managing Database Logic

• Logic must stored in DBMS.
• Database provides triggers, and functions
• Provide the ability to have functions and database talk to each other. Or have multiple DBs talk to each other

Stored Procedures

• A stored procedure is a pre-prepared SQL code that you can save, for reuse.
• Stored procedures can be programs that can run within a database.
• The movement of the application from client side to database server
• Stored procedures reduce overhead + increase performance
• Freestanding database object that is not"physically" associated with another object.
• Stored procedures can access 1 or many tables

Triggers

• Triggers - event-driven specialized procedures attached to database tables.
• Trigger code is executed by the RDBMS as data changes in the database.
• Each trigger is attached to single, specified table.
• Triggers advanced rules that can be activated by an event
• INSERT, UPDATE, or DELETE

e-DBAs

• Can manage Web-based applications + Internet issues
• Internet skills enable those applications

Factors Impacting Database Administration

• 24/7 Data Availability, New Technologies + Web connectivity
• Integration of “legacy” data with Web-based apps
• App architecture and integration
• Web-based administration
• Internet performance engineering
• Unpredictable workload

DBA in the Cloud

• DBAs are needed to administer could databases
• They will face similar availability issue

DBA Certification

• Exams exist for IBM, Microsoft + Oracle platforms
• Testing shows capability to do tasks + duties
• Show that you have certified in professionals skills
• Helpful, but jobs require on site work experience