topic: transaction processing

Question 1

what is transaction processing

Answer 1

any information that’s divided into individual, indivisible operations called transactions
each transaction must succeed or fail as a complete unit, no partial completions
a sequence of one or more SQL statements run as a single unit of work

Question 2

what are the 4 base functionalities
CRUD

Answer 2

Create - insert/create
Read - select
Update - update
Delete - delete

Question 3

for data integrity transaction processing in database management systems (DBMS) must conform to

Answer 3

ACID
atomicity
consistency
isolation
durability

Question 4

describe what ATOMICITY is in ACID

Answer 4

when a change to a database is fully or not at all performed
without it a bank transfer from one account to another could deduct funds from the source but not add funds to the destination

Question 5

describe what CONSISTENCY is in ACID

Answer 5

any change in a database must retain the overall state of the database e.g. transferring funds from one bank account to another
so referential integrity is preserved

Question 6

describe what ISOLATION is in ACID

Answer 6

transaction must not be interrupted by another transaction, it must occur in isolation so other user cannot access the used data, by using record locking

Question 7

describe what DURABILITY is in ACID

Answer 7

once a change in a database is successfully made it must not be lost due to a system failure. e.g. achieved by making DBMS write affects/ outcomes of transaction immediately backed to a permanent secondary storage, unlike a temp volatile storage like main memory (RAM)

Question 8

when does data redundancy occur

Answer 8

when the same piece of data is stored in 2 or more separate places

Question 9

describe data integrity

Answer 9

ensuring that data in a database remans accurate, consistent, and reliable

Question 10

how to ensure data integrity (4)

Answer 10

-implementing validation rules
-strict access controls
-robust backups
-automated monitoring to prevent corruption

Question 11

how to ensure data redundancy doesn’t happen

Answer 11

-normalization techniques
-use a centralized database (DBMS) that serves as a definitive source for all departments

Question 12

what does record locking prevent … so …

Answer 12

allowing multiple users to simultaneously update a database table may cause one of the updates to be lost so…
when a record is updated, the entire record is copied into the user’s own local memory. When the record is saved, the entire record is rewritten to the database

Question 13

why is data redundancy (having data in two or more places in a database is generally NOT a good thing)

Answer 13

1. Increases the size of the database unnecessarily
2. Causes data inconsistency eg a user’s credit rating in one table is 63, while in another table or database it’s 75
3. More effort to maintain ie you have to update all instances of that data item
4. Decreases efficiency of database
5. May cause data corruption eg when two orders are stored when there should only be one, the data becomes inaccurate