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.
- Disallow such updates (default)
- ON DELETE CASCADE / ON UPDATE CASCADE
- for actions on the referenced relation to cascade to referencing relation
- ON DELETE SET NULL / ON UPDATE SET NULL
- the referencing relation’s values will be set to null
Give two reasons why SQL views are considered useful.
- Views provide an abstracting layer preventing direct access to the tables
- encourage In One Place Only (IOPO) policy
What is the acid test?
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
Views are sometimes described as virtual. Describe what is meant by this.
- views are not precomputed and their results stored.
- I Instead they can be queried and the system “combines” the query and view definition.
What is Dirty data?
Dirty data means data written by a transaction that
has not yet committed.
What is a dirty read operation and what is the risk with them?
- 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.
Which Transaction Isolation Levels exist in SQL?
- 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).
Which of the SQL transaction isolation levels will disallow dirty read operations?
SERIALIZABLE
REPEATABLE READ
READ COMMITTED
Under which conditions is a view said to be updatable in SQL?
- 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.
What does updatable mean in the context of a sql view?
inserts, updates or deletes can be applied
Give the names for the two operations which end a transaction and explain how the database is affected by each.
- 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.
What is a transaction commit?
- COMMIT causes the transaction to end successfully.
- All changes to the database are made permanent.
What is a transaction abort?
- ROLLBACK cause the transaction to abort.
- All changes to the database are undone.
Is a dirty read operation one that should always be avoided?
