All data items are updated at the end of the transaction, at the end, if any data item is found inconsistent with respect to the value in, then the transaction is rolled back.Check for conflicts at the end of the transaction. No checking while the transaction is executing. Checks are all made at once, so low transaction execution overhead. Updates are not applied until end-transaction. They are applied to local copies in a transaction space.PhasesThe optimistic concurrency control has three phases, which are explained below −Read PhaseVarious data items are read and stored in temporary variables (local copies). All operations ... Read More

The timestamp-ordering protocol ensures serializability among transactions in their conflicting read and write operations. This is the responsibility of the protocol system that the conflicting pair of tasks should be executed according to the timestamp values of the transactions.A conflict occurs when an older transaction tries to read/write a value already read or written by a younger transaction. Read or write proceeds only if the last update on that data item was carried out by an older transaction.Otherwise, the transaction requesting read/write is restarted and gives a new timestamp. Here no locks are used so no deadlock.The timestamp of transaction ... Read More

There are three possible ways in which a transaction can be executed. These are as follows −Serial execution − In serial execution, the second transaction can begin its execution only after the first transaction has completed. This is possible on a uniprocessor system.Parallel execution − In parallel execution, two transactions can start their execution at exactly the same instant of time. For this, we require more than one processor.Concurrent execution − In concurrent execution, execution of the second process can begin even before the process has completed its execution.Let us consider two transactions T1 and T2. In concurrent execution, the ... Read More

Use the create command to create a table. Insert the values that are book information into the created database table using insert command.If we want to view or retrieve the inserted data use the select command.Step 1Create a book table in database as follows −The create command is used to create table, view, indexSyntaxThe syntax for the create command is as follows −Create table tablename(col1 datatype(size), col2 datatype(size), ……….colN datatype(size));ExampleUse the below mentioned command −create table book (bookname varchar(30), authorname varchar(30), noofcopies number(20));The output is the table created as shown below −BooknameAuthornamenoofcopiesStep 2Describe − The command used to describe the ... Read More

A schedule has view-serializability if it is view 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. This ... Read More

Locking and unlocking of the database should be done in such a way that there is no inconsistency, deadlock and no starvation.2PL locking protocolEvery transaction will lock and unlock the data item in two different phases.Growing Phase − All the locks are issued in this phase. No locks are released, after all changes to data-items are committed and then the second phase (shrinking phase) starts.Shrinking phase − No locks are issued in this phase, all the changes to data-items are noted (stored) and then locks are released.Consider a tree structure database as follows −A -> B -> CLet us now ... Read More

Query is a question or requesting information. Query language is a language which is used to retrieve information from a database.Let’s see some of the structure query language (sql) commands used to perform operations on databases.Create CommandThe create command is used to create table, view, index.The syntax for the create command is as follows −Create table tablename(col1 datatype(size), col2 datatype(size), ……….colN datatype(size));For Example, create table student(name char(30), regno number(10), branch char(20));OutputThe output is the table created below −NameRegnoBranchExampleUse the command given below −create table employee(ename char(30), department varchar(20), age number(10), address varchar(20), Phone number(10));OutputThe output is as follows −EnameDepartmentAgeAddressPhoneDescribe CommandThe ... Read More

Locking and unlocking of the database should be done in such a way that there is no inconsistency, deadlock and no starvation.2PL locking protocolEvery transaction will lock and unlock the data item in two different phases.Growing Phase − All the locks are issued in this phase. No locks are released, after all changes to data-items are committed and then the second phase (shrinking phase) starts.Shrinking phase − No locks are issued in this phase, all the changes to data-items are noted (stored) and then locks are released.The 2PL locking protocol is represented diagrammatically as follows −In the growing phase transaction ... Read More

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

Let’s see the stages of database system life cycle (DDLC) and their facts using tabular form −Stage of Database system development LifecycleExample of FactsExample of Documentation ProducedDatabase PlanningAims and objectives of database projects.Mission statements and objectives.System IdentificationDescription of major user views (Job roles, business application areas).Definition of scope and boundary of database system, definition of user views to be supported.Requirements Collection and AnalysisRequirements of user views, system specifications, including performance and security requirements.User requirement specification, system specification.Database designUser’s responses to checking the logical database design, functionality provided by target DBMS.Logical database design, data dictionary, physical database design.Application DesignUsers’ response to ... Read More