L6 Updates

Question 1

When defining a foreign key constraint in SQL, we can specify three possible actions the database system can take (the default and two others) when a value referenced by the foreign key value is to be deleted or updated. Explain what the three possible actions do.

Answer 1

1. Disallow such updates (default)
2. ON DELETE CASCADE / ON UPDATE CASCADE
   
   • for actions on the referenced relation to cascade to referencing relation
3. ON DELETE SET NULL / ON UPDATE SET NULL
   
   • the referencing relation’s values will be set to null

Question 2

Give two reasons why SQL views are considered useful.

Answer 2

• Views provide an abstracting layer preventing direct access to the tables
• encourage In One Place Only (IOPO) policy

Question 3

What is the acid test?

Answer 3

Transactions are designed to meet the “ACID” test:

• “A” stands for “atomicity”
  
  • transactions should execute completely or appear not to have run at all
• “C” stands for “consistency”
  
  • the constraints specified in the database must be preserved
• “I” stands for “isolation”
  
  • each transaction should appear to execute as if there are no others
• “D” stands for “durability”
  
  • once a transaction completes, it effect should never be lost

Question 4

Views are sometimes described as virtual. Describe what is meant by this.

Answer 4

• views are not precomputed and their results stored.
• I Instead they can be queried and the system “combines” the query and view definition.

Question 5

What is Dirty data?

Answer 5

Dirty data means data written by a transaction that
has not yet committed.

Question 6

What is a dirty read operation and what is the risk with them?

Answer 6

• A dirty read is a read of dirty data written by anothertransaction.
• The risk is that the transaction that wrote the datamay abort.
• This results in non-serializable behaviour.
• This may or may not be a problem for the reading transaction.

Question 7

Which Transaction Isolation Levels exist in SQL?

Answer 7

• READ UNCOMMITTED: allowed to read dirty data.
• READ COMMITTED: not allowed to read dirty data (but can still issue the same query a number of times and get different answers).
• REPEATABLE READ: ensures that tuples read by a query don’t “disappear” if the query is repeated, but can still retrieve phantom tuples if insertions have been made.
• SERIALIZABLE: the default (and strongest).

Question 8

Which of the SQL transaction isolation levels will disallow dirty read operations?

Answer 8

SERIALIZABLE

REPEATABLE READ

READ COMMITTED

Question 9

Under which conditions is a view said to be updatable in SQL?

Answer 9

• The from clause has only one database relation.
• The select clause contains only attribute names of the relation (no expressions, aggregates, or distinct).
• Any attribute not listed in the select clause can be set to null.
• There is no group by or having clause.

Question 10

What does updatable mean in the context of a sql view?

Answer 10

inserts, updates or deletes can be applied

Question 11

Give the names for the two operations which end a transaction and explain how the database is affected by each.

Answer 11

• COMMIT causes the transaction to end successfully.
  
  • All changes to the database are made permanent.
• ROLLBACK cause the transaction to abort. All
  
  • changes to the database are undone.

Question 12

What is a transaction commit?

Answer 12

• COMMIT causes the transaction to end successfully.
• All changes to the database are made permanent.

Question 13

What is a transaction abort?

Answer 13

• ROLLBACK cause the transaction to abort.
• All changes to the database are undone.

Question 14

Is a dirty read operation one that should always be avoided?