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.

RECORD-BASED LOGICAL MODELS

Hierarchical model

In Hierarchical database data is organized into a tree-like structure, implying a single upward link in each record to describe the nesting, and a sort field to keep the records in a particular order in each same-level list. In the hierarchical model two main data structures concepts: record and parent child relationship.

Record: A collection of field or data items values that provide information on an entity. Each field has a certain data type such as integer, real or string. Record of the same type are grouped into record types.

Parent child relationship type (PCR): It is the 1:N relation between two record types. The record type 1side is parent record type and one on the N side is called child record type of the PCR type. An occurrence (or instance) of the PCR type consists on one record of the parent record type and a number of records (zero or more) of the child record types.

A hierarchical database schema consist number of hierarchical schemas and each hierarchical schema consists numbers of record type and PCR types.

                                                         Above  figure shows A Hierarchical Schema

A hierarchical schema is displayed as a hierarchical diagram.

•  Record type names are displayed in rectangular boxes and 
•  PCR types are displayed as lines connecting the parent record type to the child record type.

Properties of Hierarchical Schema

1. One record type, called the root of the hierarchical schema, does not participate as a child           record type in any  PCR type.

2. Every record type except the root participates as a child record type in exactly one PCR type.

3. A record type can participate as parent record type in any number (zero or more) of PCR           type.

4. A record type that does not participate as parent record type in any PCR type is called a leaf     of the hierarchical schema.

5. If a record type participates as parent record type in any PCR type, then it is child.

Advantages of Hierarchical Database

1. Simplicity: Data naturally have hierarchical relationship in most of the practical situations.       Therefore, it is easier to view data arranged in this manner. This makes, this type of database      more suitable for the purpose. The design process, consequently, is simple.

2. Security: These database systems can enforce varying degree of security features unlike flat-       file systems.

3. Database integrity: Because of its inherent parent-child structure, database integrity is highly     promoted in these systems.

4. Efficiency: For 1:M type relationships, these types of database are very efficient.

Disadvantages of Hierarchical Database

1. Complexity of implementation: The actual implementation of a hierarchical database                 depends on the physical storage of data. This makes the implementation complicated.

2. Difficulty in management: The movement of a data segment from one location to another           causes all the accessing programs to be modified making database management a complex         affair.

3. Structural Dependence: The database has a rigidly defined relationship and hence any                 change in any part of the structure of the database would require change in the programs           accessing it. This makes maintenance very difficult.

4. Complexity of programming: Programming a hierarchical database is relatively complex           because the programmers must know the physical path of the data items.

5. Poor portability: The database are not easily portable mainly because there is little or no             standard existing for these types of databases.