DATABASE ADMINISTRATOR (DBA)

A database administrator (DBA) directs or performs all activities related to maintaining a successful database environment. Responsibilities include designing, implementing, and maintaining the database system; establishing policies and procedures pertaining to the management, security, maintenance, and use of the database management system; and training employees in database management and use. A DBA is expected to stay abreast of emerging technologies and new design approaches. Typically, a DBA has either a degree in Computer Science and some on-the-job training with a particular database product or more extensive experience with a range of database products. A DBA is usually expected to have experience with one or more of the major database management products, such as Structured Query Language, SAP, and Oracle-based database management software.

Role of the DBA

1. Database Tuning: Tweaking any of several parameters to optimize performance, such as server memory allocation, file fragmentation and disk usage.

2. Database Security: Ensuring that only authorized users have access to the database and fortifying it against any external, unauthorized access.

3. Backup and Recovery: It is a DBA's role to ensure that the database has adequate backup and recovery procedures in place to recover from any accidental or deliberate loss of data.

4. Producing Reports from Queries: DBAs are frequently called upon to generate reports by writing queries, which are then run against the database.

TRANSACTION

A transaction is a unit of work that is performed against a database. Transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program.

A transaction is the propagation of one or more changes to the database. For example, if you are creating a record or updating a record or deleting a record from the table, then you are performing transaction on the table. It is important to control transactions to ensure data integrity and to handle database errors.

Properties of Transactions

Transactions have the following four standard properties, usually referred to by the acronym ACID:

1. Atomicity: ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure, and previous operations are rolled back to their former state.

2. Consistency: ensures that the database properly changes states upon a successfully committed transaction.

3. Isolation: enables transactions to operate independently of and transparent to each other.

4. Durability: ensures that the result or effect of a committed transaction persists in case of a system failure.

DATABASE LANGUAGES

Data Defunition Language (DDL)

Data Definition Language is used to define the database conceptual schema. In most DBMS, the DDL also defines user views and sometimes storage structures. The DBMS will have a DDL compiler whose function is to process DDL statements in order to identify descriptions of the schema constructs and to store the schema description in the DBMS catalog. In other DBMSs, separate languages -View Definition Language (VDL), Storage Definition Language (SDL) - may exist for specifying views and storage structures.

DDL statements are used to alter/modify a database or table structure and schema. These statements handle the design and storage of database objects;

CREATE – create a new Table, database, schema

ALTER   – alter existing table, column description

DROP    – delete existing objects from database

Data Manipulation Language (DML)

A data manipulation language (DML) is a family of computer languages including commands permitting users to manipulate data in a database. This manipulation involves inserting data into database tables, retrieving existing data, deleting data from existing tables and modifying existing data. DML is mostly incorporated in SQL databases.

DML statements affect records in a table. These are basic operations we perform on data such as selecting a few records from a table, inserting new records, deleting unnecessary records, and updating/modifying existing records.

DML statements include the following:

SELECT – select records from a table

INSERT – insert new records

UPDATE – update/Modify existing records

DELETE – delete existing records