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

OBJECT-BASED LOGICAL MODELS

Object-Relational Database(ORD)

An Object-Relational Database (ORD) or Object-Relational Database Management System (ORDBMS) provides a relational database management system that allows developers to integrate a database with their own custom data-types and methods. The term object-relational database sometimes also refers to external software products running over traditional DBMS to provide similar features - systems more correctly described as object-relational mapping systems.

Whereas traditional RDBMS or SQL-DBMS products focused on the efficient management of data drawn from a limited set of data-types (defined by the relevant language standards), an object-relational DBMS allows software-developers to integrate their own types and the methods that apply to them into the DBMS.

ORDBMS-technology aims to allow developers to raise the level of abstraction at which they view the problem domain.

OBJECT-BASED LOGICAL MODELS

Entity-Relationship Model

The (E-R) data model is based on a perception of a real worker that consists of a collection of basic objects, called entities, and of relationships among these objects.

The entity-relationship model for data uses three features to describe data. They are;

1. Entities, which specify distinct real-world items in an application.

2. Relationships, which connect entities and represent meaningful dependencies between them.

3. Attributes, which specify properties of entities and relationships.

Record-Based Logical Models

Relational Model

The relational model was introduced in an academic paper by E.F.Codd in 1970 as a way to make database management systems more independent of any particular application.

The relational database model is based upon the conception of implementing the database with the mathematical set theory. In this model, data is collection of tables called Relation in the set theory. The tabular representation of data contains rows and columns in which rows represent set of attributes of individual entity and columns represent the attribute of entity.

Three key terms are used extensively in relational database models; relations, attributes, and domains. A relation is a table with columns and rows. The named columns of the relation are called attributes, and the domain is the set of values the attributes are allowed to take.

All relations in a relational database have to adhere to some basic rules to qualify as relations. First, the ordering of columns is immaterial in a table. Second, there can't be identical tuples or rows in a table. And third, each tuple will contain a single value for each of its attributes.

Components of the Relational Model

1. Collections of objects or relations that store the data.

2. A set of operators that can act on the relations to produce other relations.

3. Data integrity for accuracy and consistency.

RECORD-BASED LOGICAL MODELS

 Network Model

Network model was developed with the view to eliminate the problems faced by hierarchical database during 1980s. The network model (defined by the CODASYL specification) organises data using two fundamental constructs, called records and sets. Records contain fields (which may be organised hierarchically, as in the programming language COBOL). Sets define one-to-many relationships between records: one owner, many members. A record may be an owner in any number of sets, and a member in any number of sets.

A network database model is a database model that allows multiple records to be linked to the same owner file. The model can be seen as an upside down tree where the branches are the member information linked to the owner, which is the bottom of the tree. The multiple linkages which this information allows the network database model to be very flexible. In addition, the relationship that the information has in the network database model is defined as many-to-many relationship because one owner file can be linked to many member files and vice versa.

                                                                                                                                                                           Network Model

Advantages of Network Database

Network database offer many advantages other than ones offered by hierarchical database. Some of them are mentioned below:

1. Simplicity: Similar to the hierarchical model, this model is simple and the implementation is effortless.

2. Ability to Manage More Relationship Types: The network model has the ability to manage one-to-one (1:1) as well as many-to-many (N: N) relationships.

3. Flexibility in data access: Data items can be navigated in more than one way providing the much desired flexibility of the data access.

4. Data Integrity: In a network model, there's always a connection between the parent and the child segments because it depends on the parent-child relationship.

5. Data Independence: Data independence is better in network models as opposed to the hierarchical models.

6. Standards: Universal standards have been developed and enforced in these types of databases.

Disadvantages of Network Database

Although network databases displayed significant improvement over hierarchical databases yet they suffered from disadvantages as mentioned:

1. Functional Flaws: Because a great number of pointers is essential, insertion, updates, and deletion become more complex.

2. Lack of Structural Independence: A change in structure demands a change in the application as well, which leads to lack of structural independence.

3. System Complexity: Each and every record has to be maintained with the help of pointers, which makes the database structure more complex.