Week 8

Question 1

What SQL command allows you to enter data into a table? What is the syntax for this command?

Answer 1

INSERT INTO VALUES (value1, value2, …, valuen)

Each value corresponds to a column for that table.

Question 2

What SQL command allows you to modify data in a table? What is the syntax for this command?

Answer 2

UPDATE
SET
WHERE

Question 3

What happens if WHERE is not specific for UPDATE?

Answer 3

All values for columns indicated in SET would be changes to the expression entered

Question 4

How can you restore table contents before COMMIT has been executed?

Answer 4

Enter the ROLLBACK; command

Question 5

Which data manipulation commands can ROLLBACK revert?

Answer 5

• Commands that add table rows
• Commands that modify table rows
• Commands that delete table rows

Question 6

What SQL command allows you to delete data in a table? What is the syntax for this command?

Answer 6

DELETE FROM

WHERE

Question 7

What happens if WHERE is not specific for DELETE?

Answer 7

All rows in specified table will be deleted

Question 8

What SQL commands allows you to copy data in a table into a new table? What is the syntax for this command?

Answer 8

1. Create new table with attributes to be copied using
   CREATE

NOTE: Attributes in copied table DO NOT need to be the same as original table. BUT tuple of attributes need to be specified in next step if not same.

2. Extract and insert data from original table into new table using INSERT INTO command.

INSERT INTO or
SELECT
FROM

Question 9

What is the oracle method of copying data into a new table?

Answer 9

CREATE TABLE AS
SELECT
FROM

Question 10

What SQL command allows you to add designations to attributes in an existing table? What is the syntax for this command? What designations can you allocate?

Answer 10

ALTER TABLE
ADD (column name);

Designations include:
PRIMARY KEY
FOREIGN KEY
UNIQUE

Question 11

What is a transaction?

Answer 11

It is a logical unit of work that must be ENTIRELY completed OR aborted.

If only one portion of the transaction is successful and the remainder is unsucessful, the WHOLE transaction is unsucessful

Question 12

If you query data from a table in a database, is this considered a transaction?

Answer 12

Yes, since it accesses the database.

Question 13

What are properties of each individual transaction?

What do they mean?

Answer 13

• Atomicity: Transaction is a single logical unit of work
• Consistency: Database must maintain a consistent state
• Isolation: Data in use by one transaction cannot be accessed until the first is complete.
• Durability: Transactions cannot be undone or lost

Question 14

What do DBMS use to keep track of all transactions that update the database?

Answer 14

A transaction log

Question 15

What data does a transaction log contain?

Answer 15

• A record for the beginning of the transaction.
• For each transaction component (SQL statement):
– The type of operation being performed (INSERT, UPDATE, DELETE).
– The names of the objects affected by the transaction (the name of the table).
– The “before” and “after” values for the fields being updated.
– Pointers to the previous and next transaction log entries for the same transaction.
• The ending (COMMIT) of the transaction.

Question 16

What does concurrency control mean?

Answer 16

This refers to the coordiation of the simultaneous execution of transactions in a multiuser database system.

Question 17

What is a lost update?

Answer 17

This occurs when two concurrent transactions T1 and T2 are updating the SAME DATA ELEMENT and one of theses updates is lost (overwritten by other transaction).

Question 18

What is the phenomenon of uncommited data?

Answer 18

Occurs when two transactions T1 and T2 are executed concurrently and the first is rolled back after the second accesed the uncommited data.

Question 19

What is inconsistent retrievals?

Answer 19

Occurs when a transaction accesses data before and after one or more other transactions finish wokring with such data

Question 20

What is a scheduler?

Answer 20

It is a DBMS component that ensures that all transactions to be completed are executed concurrently. It interleaves execution of database operations to ensure serializability.

Question 21

What is a lock in terms of concurrency control?

Answer 21

It is a ‘device’ that transactions can be given to prevent the data item that they are working on from being utilised by other transactions.

Lock is released and passed to next transaction after first is complete. This assumption that multiple transactions require a lock or else conflicts occur is called pessimistic locking.

Question 22

What are the levels of lock granularity?

Answer 22

• Database level (EXTREMELY SLOW)
• Table level
• Page level (most common for multiuser DBMS)
• Row level (high overhead since lock assigned to each row, can have lots of rows)
• Attribute level

Question 23

What are the different types of locks?

Answer 23

Binary: Two states either LOCKED (1) or UNLOCKED (0)
Shared: Concurrent transactions granted read access
Exclusive: Access reserved for transaction that locked objects

Question 24

What is a deadlock?

Answer 24

When two transactions are stuch in a loop waiting for each other to unlock data.

Question 25

What is Two-phase locking (2PL)?

Answer 25

A locking protocol with two phases:
1. A growing phase, in which a transaction acquires all required locks without unlocking any data. Once all locks have been acquired, the transaction is in its locked point.

2. A shrinking phase, in which a transaction releases all locks and cannot obtain a new lock.

Question 26

What rules does Two-phase locking (2PL) follow?

Answer 26

• Two transactions cannot have conflicting locks.
• No unlock operation can precede a lock operation in the same transaction.
• No data is affected until all locks are obtained—that is, until the transaction is in its
locked point.

Question 27

What are the three basic techniques to control deadlocks?

Answer 27

Deadlock prevention: A transaction requesting a new lock is aborted when there is
the possibility that a deadlock can occur

Deadlock detection: If a deadlock is found, the “victim” transaction is aborted (rolled back and restarted) and the other transaction continues.

Deadlock avoidance: The transaction must obtain all of the locks it needs before it
can be executed. This technique avoids the rolling back of conflicting transactions by
requiring that locks be obtained in succession.

Question 28

What are the three types of database critical events that can trigger the database recovery process? Give some examples for each one.

Answer 28

• Hardware/software failures. hard disk media failure, a bad capacitor on a motherboard, or a failing memory bank. Other causes of errors under this category include application program or operating system errors that cause data to be overwritten, deleted, or lost.
• Human-caused incidents. This type of event can be categorized as unintentional or intentional.
o An unintentional failure is caused by carelessness by end-users. Such errors include deleting the wrong rows from a table, pressing the wrong key on the keyboard, or shutting down the main database server by accident.
o Intentional events are of a more severe nature and normally indicate that the company data are at serious risk. Under this category are security threats caused by hackers trying to gain unauthorized access to data resources and virus attacks caused by disgruntled employees trying to compromise the database operation and damage the company.
• Natural disasters. This category includes fires, earthquakes, floods, and power failures.