Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
A materialized view is a view whose contents are computed and stored. Materialized view is also a logical virtual table, but in this case the result of the query is stored in the table or the disk. The performance of the materialized view is better than normal view since the data is stored in the disk.It's also called indexed views since the table created after the query is indexed and can be accessed faster and efficiently.ExampleConsider the view given below −Create view branchloan(branch-name, total-loan) as select branch-name , sum(amount) from loan groupby branch-name;Materializing the above view would be especially useful ... Read More
A schedule has view-serializability if it is viewed as equivalent to a serial schedule. A schedule is view serializable if the following three rules are satisfied −Rule 1 − If Ti reads data initially, after this Tj writes the same data, in the given schedule. This sequence must be followed in the transaction combination (read write operation).Rule 2 − If Ti writes data initially, after this Tj reads the same data, in the given schedule. This sequence must be followed in the transaction combination (write read operation).Rule 3 − If Ti writes data, after this Tj writes the data finally. ... Read More
In a transaction, a data item which we want to read or write should first be locked before any read or write operation. After the operation is over, the transaction unlocks the data item so that other transactions can lock that same data item for their use.ExampleLet us see how locking mechanisms help us to create error free schedules.An erroneous schedule is as follows −Here t2 reads A, before A is modified in T1. This will result in inconsistency.Now we use locking mechanism in the above schedule which is shown below −Until T1 performs Unlock(A) T2 cannot access A. So, ... Read More
Two schedules are said to be conflict equivalent if the order of any two conflicting operations are the same in both the schedules.Also, a concurrent schedule S is conflict equivalent to a serial schedule S’, if we can obtain S’ out of S by swapping the order of execution of non-conflicting instructions.Example 1Even if the schedule S1 keeps the database in the consistent state, we cannot convert it into a serial schedule and hence we conclude that the schedule is not conflict equivalent to any of the serial schedules.So, instead of considering only the read and write operation, we will ... Read More
Conflict serializability orders any conflicting operations in the same way as some serial execution. A pair of operations is said to conflict if they operate on the same data item and one of them is a write operation.That meansReadi(x) readj(x) - non conflict read-read operationReadi(x) writej(x) - conflict read-write operation.Writei(x) readj(x) - conflic t write-read operation.Writei(x) writej(x) - conflict write-write operation.Where I and j denote two different transactions Ti and Tj.Precedence graphIt is used to check conflict serializability.The steps to check conflict serializability are as follows −For each transaction T, put ... Read More
A transaction must satisfy the ACID properties of DBMS, namelyAtomicity − Execute all or none transaction.Consistency − The DB should be consistent before and after transaction execution.Isolation − Transaction must be executed in isolated form.Durability − Roll backing should always be possible in Case of any failure.A schedule is defined as an execution sequence of transactions. A schedule maintains the order of the operation in each individual transaction. A schedule is the arrangement of transaction operations. A schedule may contain a set of transactions.We already know that a transaction is a set of operations. To run transactions concurrently, we arrange ... Read More
A transaction is a unit of database processing which contains a set of operations. For example, deposit of money, balance enquiry, reservation of tickets etc.Every transaction starts with delimiters begin transaction and terminates with end transaction delimiters. The set of operations within these two delimiters constitute one transaction.main() { begin transaction } end transactionA transaction is divided into states to handle various situations such as failure. It passes through various states during its lifetime. The state of a transaction is defined by the current activity it is performing.At a particular instant of time, a transaction can be in one ... Read More
Cost-based optimization is expensive. Heuristics are used to reduce the number of choices that must be made in a cost-based approach.RulesHeuristic optimization transforms the expression-tree by using a set of rules which improve the performance. These rules are as follows −Perform the SELECTION process foremost in the query. This should be the first action for any SQL table. By doing so, we can decrease the number of records required in the query, rather than using all the tables during the query.Perform all the projection as soon as achievable in the query. Somewhat like a selection but this method helps in ... Read More
Expression tree is also called a query tree. It demonstrates an evaluation plan.ExampleConsider an evaluation plan for the given query −Select name from customer, account where customer.name=account.name and account.balance>2000;There are two evaluation plans −Πcustomer.name(σcustomer.name=account.name^ account.balance>2000(customerXaccount)Πcustomer.name(σcustomer.name=account.name(customerXσ account.balance>2000(account)The expression trees for the above evaluation plans are as follows −Cost evaluator evaluates the cost of different evaluation plans and chooses the evaluation plan with lowest cost. Disk access time, CPU time, number of operations, number of tuples, size of tuples are considered for cost calculations.Read More
The overall design of the database is called database schema. Schema will not be changed frequently. It is the logical structure of a database. It does not show the data in the database.The schema is pictorially represented as follows −Types of SchemaThe different types of schemas are as follows −Physical schema − It is a database design at the physical level.It is hidden below the logical schema and can be changed easily without affecting the application programs.Logical schema − It is a database design at the logical level. Programmers construct applications using logical schema.External − It is schema at view ... Read More