Concept of View in DBMS and its advantages

A view is a virtual table. A view has no independent physical existence. The view’s definition exists only in the database’s metadata, but the data comes from the table or tables from which you derive the view.

A view is the result set of a stored query on the data in which the database users can query just as they would in a persistent database collection object.

This pre-established query command is kept in the database dictionary. Unlike ordinary base tables in a relational database, a view does not form part of the physical schema: as a result set, it is a virtual table computed or collated dynamically from data in the database when access to that view is requested. Changes applied to the data in a relevant underlying table are reflected in the data shown in subsequent invocations of the view.

Advantages of View:-

·         Views can represent a subset of the data contained in a table. Consequently, a view can limit the degree of exposure of the underlying tables to the outer world: a given user may have permission to query the view, while denied access to the rest of the base table.

·         Views can join and simplify multiple tables into a single virtual table.

·         Views can act as aggregated tables, where the database engine aggregates data (Sum, Average etc.) and presents the calculated results as part of the data.

·         Views can hide the complexity of data.

·         Views take very little space to store; the database contains only the definition of a view, not a copy of all the data that it presents.

·         Depending on the SQL engine used, views can provide extra security.

Basically there are  two Views regarding to DBMS.

1.     Updatable View

2.     Non-updatable views

Difference between updatable and non-updatable views:

Database practitioners can define views as non-updatable or updatable. If the database system can determine the reverse mapping from the view schema to the schema of the underlying base tables, then the view is updatable.

INSERT, UPDATE, and DELETE operations can be performed on updatable views.

Read-only views do not support such operations because the DBMS cannot map the changes to the underlying base tables. A view update is done by key preservation.

Two Phase Locking ( 2 PL) Protocol

Two phase locking: in basic two phase locking, a transaction must own a read lock on data item x before reading x, and must own a write lock on x before writing x. Read locks conflict with write locks on the same data item, and write locks conflict with other write locks on the same data item. 

Read locks are implicitly requested by the TM by sending reads, and write locks are implicitly requested by the TM by sending writes. Write locks are implicitly released by commits, but in order to release read locks, special lock release operations are required. 

Every transaction obtains locks in a two phase manner. 

During the growing phase, the transaction obtains locks without releasing any locks. During the shrinking phase, the transaction releases locks without obtaining any locks. 

A basic 2PL scheduler follows the following three rules:

1When the 2PL scheduler receives a lock request, it tests whether the requested lock conflicts with another lock that is already set. If so, it queues the lock request. If not, it responds to the lock request by setting the lock.

2Once the 2PL scheduler has a set a lock on a data item, it cannot release the lock until the DM has completed processing of the lock’s corresponding operation.

3Once the 2PL scheduler has released a lock for a transaction, it may not subsequently obtain any locks for the same transaction.

A basic 2PL scheduler requires a strategy to prevent, avoid or detect-and-break deadlocks. 

Various strategies are waits-for-graphs, pre-ordering and pre- declaration of locks, timestamp-priority-based restarts and many others. 

Variations on the basic 2PL method include primary copy 2PL, voting 2PL, multi-version 2PL, centralized 2PL, asymmetric running priority, symmetric running priority, wait-depth limited locking (WDL), dynamic locking with no waiting, asymmetric cautious waiting, wound-wait, wait-die, local wait –depth control (LWDC) and adaptive callback locking. Other variations that make restrictive assumptions about transaction- specification and correctness are weaker consistency semantics, decomposition into subtasks, ordered sharing altruistic locking, proclamations, increment/ decrement locks, sagas and compensations, commutative operations, and other semantic methods.

Dynamic 2PL and static 2PL are two variants of basic 2PL. in dynamic 2PL, a transaction obtains a lock only when it needs to access a corresponding data item. 

In static 2PL, a transaction pre declares and obtains all the locks it may need before it begins any computation. 

Current databases use dynamic 2PL and its variants almost exclusively. Almost all implementations of 2PL enforce strict execution, which requires the scheduler to release all of a transaction’s read locks after the transaction terminates, and all of the transaction’s write locks after the DM has processed the transaction’s commit or abort, 2PL involves the overhead of extra messages needed to acknowledge lock sets and to release locks, and a mechanism to prevent or detect and break deadlocks.   

ACID PROPERTY OF TRANSACTION

A transaction can be defined as a sequence of operations performed together as a single logical unit of work. A single logical unit of work which evolves one or more database access operations must possess the four properties called ACID (Atomicity, Consistency, isolation, durability). 

Atomicity states that either all the data modifications are performed or none of them are performed. 

 Consistency is a state in which all the data in a consistent state after a transaction is completed successfully. 

 Isolation states that any data modification made by a transaction must be isolated from the modifications made by any other concurrent transactions. In other words, a transaction either accesses data in the state it was on, before another concurrent transaction modified it or it access the data after the second transaction has been completed. There is no scope for the transaction to see an intermediate state. 

 Durability states that any changes in data by a completed transaction remain permanently in effect in the system. Hence any change in data due to a completed transaction persists even in the event of a system failure.

.

INTEGRITY RULES IN DBMS

Integrity  Rules:

Database Management System  provides  high level of data integrity, by using the  Integrity Rules:

1. Entity Integrity: Entity integrity says that  a prime attribute in a relation cannot accept null values or duplicate value.

2. Referential Integrity: The referential integrity rule is related along with the foreign key concept. Let R1 and R2 are two relations where R1 is having an attribute(s) with primary key. Let R2 be having a foreign key, that refers to relation R1 via the same set of attributes. Then the value of the foreign key in a tuple in R2 relation must either be equal to primary key of a tuple in a relation R1 relation or be entirely NULL.

R1

Department_ID	Department_Name	Department_Head	Department_Addres
D1	HR	Jacob	London
D2	IT	Rohan	Newyork
D3	FINANCE	Michel	Delhi

R2

EMP_ID	EMP_Name	EMP_City	Department_ID
E1	Rohan		D2
E2	Mukesh		D1
E3	Joseph		D1
E4	Ron		D1
E5	Michel		D3
E6	Jacob		D1

In above example  , in relation R1  Department_ID  is primary key  where as in R2 Department_ID is foreign key.

Value of the foreign key in R2 is same as Value exist in R1 of primary key.

Relational algebra

Relational algebra is a procedural language which specifies the operations to be performed on existing relations to get the desired relations. Relational algebra constitutes a basic set of operations for manipulating relational data. These operations enable the user to perform some basic retrieval operations. The result of the retrieval operation on a relation is another relation. The relational operations can be divided into two categories:

1)    Set oriented operations which include mathematical set operations like UNION, INTERSECTION, SET DIFFERENCE and CARTESIAN PRODUCT first these operations require union compatible operand relations. Two relations are union compatible when they have same arty and one to one corresponding attributes defined over the same domain.

2)    These operations are developed and specifically for relational database which include SELECT, PRODUCT, RENAME, JOIN and DIVION.

a.     Selection (σ): The select operation is used to select a subset of the tuples from a relation that satisfies a selection condition or search criteria. The resulting relation and the main (original) relation have the same attributes. It discards the words that do not match the selection condition. For example: to select that employee from EMPLOYEE relation who is having empno 1007, we will write

σ(empno =1007) (EMP)

b.     Projection (σ): whereas the select operations is used to choose a sunset a tuples of relation the PROJECT operation is used to choose only a subset of columns discarding others. More simply, project operations on relations are defined as the projection of all tuples over same set of attributes. The output of this operation is a new relation which will have the attributes only listed in attribute list from relation R. For example, if we have to take only the employee names and their salaries from EMPLOYEE relation then this can be represented as

σ(ename,sal)(EMP)

c.      Natural join: This operation takes only two source relations as inputs and produces a relation whose tuples are formed by concatenating tuples from each input source. It is basically a Cartesian product of the extension of each input source. However, not all possible combinations of tuples necessarily end up in the result. This is because it implicitly selects from among all possible tuple combinations only those that have identical values in attributes shared by both relations. If the degree of the input sources were m and n, and the number of shared attributes was s, then the degree of the resultant relation is m+n-s.

d.     Division: It involves dividing one relation by another. Division is in principle a partitioning operation. Thus, 6/2 can be paraphrased as partitioning a single group of 6 into a number of groups of 2, which in this case, 3 groups of 2. Relational division too can leave remainders but we ignore remainders and focus only on constructing whole groups of the divisor.

The manner in which a relational dividend is partitioned is a little more complex. A division of a relation R is performed over some attribute of R. The divisor is a subset of values from that attribute domain and the result is a relation comprising the remaining attributes of R. In relational algebra expressions, the divisor is in fact specified by another relation D. For this to be meaningful at all, D must have at least one attribute in common with the R. The division is over the common attribute and the set of values used as the actual divisor are the values found in D.  

e.     Set operations: relations are basically sets. A set operation on two or more sets is meaningful if the sets comprise values of the same type. This is so that comparison of values from different sets is meaningful. As a relation is a set of tuples, the values we are interested in are the tuples themselves. The structure of the tuples must be identical, the tuples must be of equal length and their corresponding elements must be of the same type. Only then can two tuples be equal. Thus, meaningful set operations on relations require that the source relations have identical schemas.

The following are the operations:

1.     Union: Union operator gives the information available in both the relations used union. For example, give the complete list of employees who are either working as full time or part time. Consider full_time and part_time two seperates tables which store the employees separately.

Ʌ(ename) (Full_time) U  Ʌ(ename)(Part_time)

2.     Intersection: it returns the common information from both the relations used in the intersection. The intersection operation takes the form

For example: display the name of the employees who are working as full time as well as part time.

Ʌ(ename) (Full_time)  Ʌ(ename) (part_time)

3.     Difference: it returns the information, which is available in first relation but not in second. The difference operation takes the form

Relation1-Relation2

Example: to display only those employee names who are working as full time, not as part time.

Ʌ(ename) (full_time)- Ʌ(ename)(part_time) 

Few Basic Key of DBMS

a. Primary key – It is a set of one or more fields/columns of a table that uniquely identify a record in database table. It cannot accept null, duplicate values. Only one Candidate Key can be Primary Key.

b. Candidate key – It is a set of one or more fields/columns that can identify a record uniquely in a table. There can be multiple Candidate Keys in one table. Each Candidate Key can work as Primary Key.

Eg: In below diagram ID, Roll No. and Enroll No. are Candidate Keys since all these three fields can be work as Primary Key.

c. Alternate key – It is a key that can be work as a primary key. Basically, it is a candidate key that currently is not primary key.

Eg: In below diagram Roll No. and Enroll No. becomes Alternate Keys when we define ID as Primary Key.

 

ID	Roll No.	Name	Enroll No.	Address	Dept ID
1	6068741	Harsh	ADM1	Delhi	1
2	6068742	Neha	ADM2	Noida	1
3	6068743	Aditi	ADM3	Gurgaon	2

Primary Key

                             Alternate Keys

Possible Candidate Keys