Category: DBMS

Necessary Privileges for Creating Database Links

A database link is a pointer in the local database that lets you access objects on a remote database. To create a private database link, you must have been granted the proper privileges. The following table illustrates which privileges are required on which database for which type of link:

Privilege Database Required For
CREATE DATABASE LINK Local Creation of a private database link.
CREATE PUBLIC DATABASE LINK Local Creation of a public database link.
CREATE SESSION Remote Creation of any type of database link.

To see which privileges you currently have available, query ROLE_SYS_PRIVS. For example, you could create and execute the following privs.sql script (sample output included):

SELECT DISTINCT PRIVILEGE AS "Database Link Privileges"
FROM ROLE_SYS_PRIVS
WHERE PRIVILEGE IN ( 'CREATE SESSION','CREATE DATABASE LINK',
                     'CREATE PUBLIC DATABASE LINK')

or just execute following query to see all the permissions for current user:
SELECT DISTINCT PRIVILEGE AS "Database Link Privileges" FROM ROLE_SYS_PRIVS

Source: Oracle Docs

What is Lost Update Problem in DBMS?

In a schedule, if update performed by transaction T1 on data item ‘X’ gets overwritten by the update performed by transaction T2 on same data item ‘X’, then we say that update of T1 is lost to the update of T2.

This problem is known as Lost-Update-Problem in concurrent schedules.

Example:

with X = 50 and Y =50 (initial values)

T1T2
read(x) (T1I1)
x=x+10 (T1I2)
read(x) (T2I1)
x=x+20 (T2I2)
write(x) (T1I3)
read(y) (T1I4)
write(x) (T2I2)
commit (T2I2)
y=y+10 (T1I5)
write(y) (T1I6)
commit (T1I7)

Explanation:

Continue reading “What is Lost Update Problem in DBMS?”

Serial Schedules, Concurrent Schedules and Conflict Operations

A schedule is the representation of execution sequence for all the instructions of the transactions. Schedules are categorized in two types:

  • Serial Schedules
  • Concurrent Schedules

Serial Schedules:

A schedule is said to be serial if and only if all the instructions of all the transactions get executed non-preemptively as an unit. OR

Each serial schedule consists of a sequence of instructions from various transactions, where the instructions belonging to one single transaction appear together in that schedule.

Continue reading “Serial Schedules, Concurrent Schedules and Conflict Operations”

Concurrent Execution in Transaction | DBMS

Transaction-processing systems usually allow multiple transactions to run concurrently. Allowing multiple transactions to update data concurrently causes several complications with consistency of the data.

Ensuring consistency in spite of concurrent execution of transactions requires extra work; it is far easier to insist that transactions run serially—that is, one at a time, each starting only after the previous one has completed.

Continue reading “Concurrent Execution in Transaction | DBMS”

Difference between Normalization and Normal Forms

Normalization:

Normalization is the systematic process applied on the relations to reduce the degree of redundancy.

Normalization is defined as systematic because it always gives guarantee for following properties –

  • Lossless decomposition.
  • Dependency preservation.
Continue reading “Difference between Normalization and Normal Forms”

Implementation of Atomicity and Durability using Shadow Copy

The recovery-management component of a database system can support atomicity and durability by a variety of schemes.

Here we are going to learn about one of the simplest scheme called Shadow copy.

Shadow copy:

Continue reading “Implementation of Atomicity and Durability using Shadow Copy”

DBMS Notes for GATE 2020

Types of Functional Dependencies in Normalization

FD

Functional Dependency:

In Relational database, Functional dependency is denoted as X -> Y where X:Determinant and Y: Dependent. So, as per the concept the value of Y gets determined by the value of X.

If value of X gets duplicated, then in those rows value of Y shall also gets duplicated correspondingly.

If the determinant X value is unique (different) then the dependent Y could have any value meaning:

  • For same X , value of Y should be same.
  • For different X, value of Y could be same or different.

Continue reading……..

Difference between DBMS and RDBMS

DBMSVsRDBMS.png

Precedence Graph to check Conflict Serializable Schedule

Precedence graph algorithm can be used to find out whether the given concurrent schedule is conflict serializable or not.

Algorithm:

  1. Create the number of node in the graph equal to the number of transactions in the given schedule.
  2. Starting with each and every transaction identify all the existing conflicting operations and represent them in the graph in the form of edges following the direction of the conflicting operation.
  3. Check if the precedence graph has either a cycle or a loop.
  4. If the cycle or loop does exist, then the given schedule is not conflict serializable.
  5. Else the schedule is conflict serializable.
  6. In case the schedule is conflict serializable then apply the Topological ordering in the graph to find out the equivalent serial schedule.
Continue reading “Precedence Graph to check Conflict Serializable Schedule”
%d bloggers like this: