Skip to main content
Q- List and explain each of the ACID properties that collectively guarantee that database transactions are processed reliably.
Ans- ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably. They are defined as follows:
  • Atomicity. Atomicity requires that each transaction be “all or nothing”: if one part of the transaction fails, the entire transaction fails, and the database state is left unchanged. An atomic system must guarantee atomicity in each and every situation, including power failures, errors, and crashes.
  • Consistency. The consistency property ensures that any transaction will bring the database from one valid state to another. Any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof.
  • Isolation. The isolation property ensures that the concurrent execution of transactions results in a system state that would be obtained if transactions were executed serially, i.e., one after the other. Providing isolation is the main goal of concurrency control. Depending on concurrency control method (i.e. if it uses strict - as opposed to relaxed - serializability), the effects of an incomplete transaction might not even be visible to another transaction.
  • Durability. Durability means that once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors. In a relational database, for instance, once a group of SQL statements execute, the results need to be stored permanently (even if the database crashes immediately thereafter). To defend against power loss, transactions (or their effects) must be recorded in a non-volatile memory.

Comments

Post a Comment

Popular posts from this blog

Methods of Rank Rows in SQL Server: ROW_NUMBER(), RANK(), DENSE_RANK() and NTILE()

SQL Server provides us with a number of window functions that help us to perform calculations across a set of rows, without the need to repeat the calls to the database. Unlike the standard aggregate functions, the window functions will not group the rows into a single output row, they will return a single aggregated value for each row, keeping the separate identities for those rows. The Window term here is not related to the Microsoft Windows operating system, it describes the set of rows that the function will process. One of the most useful types of window functions is Ranking Window Functions that are used to rank specific field values and categorize them according to the rank of each row, resulting in a single aggregated value for each participated row. There are four ranking window functions supported in SQL Server;  ROW_NUMBER(),   RANK() ,  DENSE_RANK()  and  NTILE() . All these functions are used to calculate ROWID for the provided rows window in ...
Post a Comment
Read more
Q- What is DBCC command in SQL sever. A - DBCC  (Database consistency checker ) are used to check the consistency of the databases. The DBCC commands are most useful for performance and trouble shooting exercises. I have listed down and explained all the DBCC commands available in SQL Server 2005, with examples. These are four types: Maintenance Informational Validation Miscellaneous                Maintenance Commands Performs maintenance tasks on a database, index, or file group. Syntax: DBCC  CLEANTABLE ( ‘DatabaseName’,’TableName.ColumnName’ ,0) 2.  DBREINDEX  – Builds one or more indexes for the table in the specified database. (Will be  removed  in the future version, use  ALTER INDEX  instead) Syntax: USE DatabaseName DBCC  DBREINDEX ( ‘TableName.ColumnName’,’PK_ID’ ,80) 3.  DROPCLEANBUFFERS  – Removes all clean buffers from buffer pool. Synta...
1 comment
Read more

Write a query that prints a list of employee names for employees in Employee having a salary greater than 2000

  Write a query that prints a list of employee names (i.e.: the  name  attribute) for employees in  Employee  having a salary greater than  2000  per month who have been employees for less than  10  months. Sort your result by ascending  employee_id . Input Format The  Employee  table containing employee data for a company is described as follows: Column Type employee_id Integer name String months Integer salary Integer where  employee_id  is an employee’s ID number,  name  is their name,  months  is the total number of months they’ve been working for the company, and  salary  is the their monthly salary. Sample Input employee_id name months salary 12228 Rose 15 1968 33645 Angela 1 3443 45692 Frank 17 1608 56118 Patrick 7 1345 59725 Lisa 11 2330 74197 Kimberly 16 4372 78454 Bonnie 8 1771 83565 Michael 6 2017 98607 Todd 5 3396 99989 Joe 9 3573 Sample Output Angela Michael Todd Joe Explanat...
1 comment
Read more