1.3.2 - Databases

Question 1

What is SQL

Answer 1

Structured query language.

Declaritive language used for querying, creating and updating tables in relational databases.

Question 2

SELECT…FROM…WHERE…ORDER BY

Answer 2

SELECT - Select field(s) to be displayed
FROM - State the table(s) the data will come from
WHERE - State the search criteria/condition
ORDER BY - Sort the result EG. Ascending order

Question 3

List conditions unique to SQL

Answer 3

IN - Equal to a value within a set of values
BETWEEN…AND - Within a range defined by the two values
LIKE - Similar to
IS NULL - Field does not contain a value

Question 4

Transaction processing: Define ACID

Answer 4

Atomicity
Consistency
Isolation
Durability

Question 5

Explain Atomicity

Answer 5

Atomicity requires that a transaction must be processed in its entirety or not at all. Atomicity must gurantee that in any situation, including power cuts or hard dick crashes, it is not possible to process only part of a transaction.

Question 6

Explain Consistency in relation to ACID

Answer 6

Ensures that no transaction can violate any defined validation rules for maining the referential integrity of the database.

Question 7

Explain isolation in relation to ACID

Answer 7

Ensures that concurrent execution of transactions leads to the same results as if transactions were processed one after the other.

Question 8

Explain durability in relation to ACID

Answer 8

Ensures that once a transaction has been committed, it will remain so, even in the event of a power cut.

Question 9

What is record locking, and when is it used?

Answer 9

The technique of preventing simultaneous access to objects in a database in order to prevent updates being lost or inconsistencies in the data arising.

In simple terms, a record is locked whenever a user retrieves it for editing or updating.

Question 10

Problems with record locking.

Answer 10

If two users are attempting to update two records, a situation can occur in which neither proceed, known as deadlock. EG A bank clerk is trying to transfer money from account A to account B. Another bank clerk is trying to transfer money from account B to account A - both clerks beed access to the other persons account, which is locked.

Question 11

What is redundancy?

Answer 11

Maintaining multiple identical systems in different geographical locations, so that every transaction is written to multiple storage facilities. This built-in hardware redundancy protects against loss of data in the event of power failure or other disasters. Back up systems.