Transaction Management

A Transaction is a compilation of operations that carry out a single logical function in a database application.

Transaction management part ensures that the database remains in a consistent (correct) state even with system failure (e.g., power failures and operating system crashes) and transaction failures.

Concurrency control manager controls the interface between the concurrent transactions, to make certain the consistency of the database.

Database Design

The process of designing the general structure of the database:-

Logical Design – decide on the database schema. Database design necessitates that we find a “good” assortment of relation schemas.

Business decision – What attributes should we evidence in the database?

Computer Science decision – What relation schemas should we have and how should the attributes be disseminated between the various relation schemas?

Physical Design – Deciding on the physical layout of the database

Structured Query Language (SQL)

SQL: Extensively used non­procedural language

Example: Find the name of the customer with customer­id 192­83­7465

Select customer.customer_name

from customer

where customer.customer_id = ‘192­83­7465’

Example: Find the balances of all accounts detained by the customer with

customer­id 192­83­7465

Select account.balance

From depositor, account

where depositor.customer_id = ‘192­83­7465’ and

depositor.account_number = account.account_number

Application programs commonly access databases through one of:-

• Language extension to consent to embedded SQL
• Application program interface (e.g., ODBC/JDBC) which permit SQL
• Queries to be sent to a database

Data Definition Language (DDL)

• Specification notation for significant the database schema

Example: create table account (

Account number char (10),

Balance integer)

DDL compiler produces a set of tables stored in a data dictionary.

1. Data dictionary contains metadata (i.e., data about data)
2. Database schema
3. Data storage and definition language
4. Specifies the storage structure and admittance methods used

Integrity constraints

Domain constraints

Referential integrity (references constraint in SQL)

Assertions

Authorization

Data Manipulation Language (DML)

• Data Manipulation Language is a language for accessing and manipulate the data organized by the apposite data model.
• DML also known as query language.

Two classes of languages:-

1. Procedural – user specify what data is required and how to get those data
2. Declarative (nonprocedural) – user specifies what data is required without specify how to get those data

SQL is the most broadly used query languages

Data Models?

Data Model  is a collection of tools for describing:-

1. Data
2. Data relationships
3. Data semantics
4. Data constraints 

• Relational model
• Entity Relationship data model (generally for database design)
• Object based data models (Object-oriented and Object relational)
• Semi structured data model (XML) 

Other older models:-

• Network model
• Hierarchical model

Instance and Schema:-

Similar to types and variables in programming languages:-

Schema:– The logical structure of the database

Example: The database consists of information about a set of clientele and accounts and the connection between them)

Analogous to type information of a variable in a program.

Physical schema: Database design at the physical level

Logical schema: Database design at the logical level

Instance:- The actual content of the database at a particular point in time

Analogous to the value of a variable.

Physical Data Independence:– the ability to modify the physical schema without Changing the logical schema

Applications depend on the logical schema

In general, the interfaces between the various levels and components should be well defined so that changes in some part do not seriously influence others.