Normalization, Transactions

Question 1

Unnormalized form

Answer 1

probably no relationships, just one large table (pro: no joins, easy to restructure, good for NoSQL; con: data duplication, empty attributes, non-atomic attributes)

Question 2

First normal form

Answer 2

each attribute must contain atomic values

Question 3

Second normal form

Answer 3

must be in 1NF and all non-key columns must describe the primary key

Question 4

Third normal form

Answer 4

must be in 2NF, cannot have attributes that provide facts about non-key attributes (“about the key, the whole key, and nothing but the key”)

Question 5

Transaction

Answer 5

sequence of statements/queries executed as a single unit (all-or-nothing)

Question 6

ACID properties

Answer 6

guarantee transactions maintain the integrity of the database, Atomicity, Consistency, Isolation, Durability

Question 7

Atomicity

Answer 7

require that each transaction be all-or-nothing, they are never “in progress”

Question 8

Consistency

Answer 8

a transaction must change the database from one valid state to another valid state, must maintain constraints

Question 9

Isolation

Answer 9

defines WHEN changes to the database are visible to others

Question 10

Durability

Answer 10

guarantees that successful transactions are committed to the database

Question 11

Isolation levels

Answer 11

Serializable, repeatable reads, read committed, read uncommitted

Question 12

Lock

Answer 12

mechanism for securing data for a transaction

Question 13

2PL

Answer 13

two-phase locking protocol, use a read lock and a write lock

Question 14

Expanding phase

Answer 14

first part of 2PL, locks are acquired before the transaction begins

Question 15

Shrinking phase

Answer 15

last part of 2PL, locks are released after the transaction commits/rolls back

Question 16

Serializable

Answer 16

strictest level of isolation, can only execute one transaction on a given unit of data at a time

Question 17

Dirty read

Answer 17

when a transaction retrieves a row that has been updated by another transaction that is not yet committed

Question 18

Non-repeatable read

Answer 18

when a transaction retrieves a row twice and that row is updated by another transaction that is committed in between

Question 19

Phantom read

Answer 19

when a transaction retrieves a set of rows twice and new rows are inserted into or removed from that set by another transaction that is committed in between

Question 20

Repeatable reads

Answer 20

2nd strictest, does not maintain range locks and results in phantom reads

Question 21

Read committed

Answer 21

3rd strictest, does not maintain range locks and releases read locks immediately after use (data can change after it is read). Results in non-repeatable reads

Question 22

Read uncommitted

Answer 22

least strict, allows users to read data that is not yet committed (dirty reads)