Fundamentals of Databases Flashcards
Describe 3NF
All attributes rely on the key, the whole key, and nothing but the key.
What is a primary key?
A unique identifier for each record.
Why is a database normalised?
To remove redundant data.
What steps should be taken to normalise a database from 0NF to 1NF?
Eliminate duplicate columns.
Identify a primary key.
Remove any repeating groups of data.
Separate any non-atomic attributes.
What is a composite key?
Two or more columns in a table that can be used to uniquely identify each row.
What steps should be taken to normalise a database from 1NF to 2NF?
Remove any partial dependencies by splitting the table.
Fix any many-to-many relationships by creating a linking table.
Describe transaction processing
Any information processing which is divided into individual, indivisible operations. Each operation must succeed or fail as a complete unit.
What does CRUD stand for?
Create
Read
Update
Delete
In order to ensure data integrity, transaction processing in all database management systems must conform to a set of rules which can recalled using the acronym ACID. What are the ACID rules?
Atomicity,
A change to the database must be completely performed or not at all.
Consistency,
Any change to the database must retain the overall state of the database.
Isolation,
A transaction must not be interrupted by another transaction.
Durability,
Once a change is made to the database it must not be lost due to a system failure.
How, in practice, is it ensured transactions occur in isolation?
Record-locking.
The record(s) being used by the transaction are locked by being placed in a read-only state. The lock is only removed once the transaction is completed.
How is it ensured databases don’t lose data due to system failure?
Databases write transactions immediately back to permanent secondary storage.
