What are the 4 transaction control problems?
Lost Update
Dirty Read
Non-repeatable read
Phantom Read
Explain read uncommitted (isolation level)
can read not yet committed changes made by other transactions
No isolation
Explain Read committed (isolation level)
Read or write lock on the current row
Explain repeatable read (isolation level)
holds read locks on all rows it references and write locks on all rows it inserts, updates, or deletes
data is locked for the complete rest of a transaction
Explain serialisable (isolation level)
no transaction to data that is the subject of any other transaction
PMON (Process Monitor)
performs process recovery when a user process fails
- cleans the database buffer cache
- frees resources
SMON (System Monitor)
performs recovery
cleans up temporary segments
DBWR (Database Writer)
writes modified buffers in the database buffer cache to disk
LGWR (Log Writer)
writes the redo log buffer to a redo log file on disk
CKPT (Checkpoint Process)
updates the headers of all data files to record the details of the checkpoint
ARCH (Archiver)
copies redo log files to a designated storage device after a log switch has occurred
What are the types of Database languages? (DDL, DML, QL)
- Data Definition Language (DDL): creating, modifying, and deleting data structures;
- Data Manipulation Language (DML): recording, reading, modifying and deleting data objects;
- Query Language (QL)
specially developed for ad hoc queries;
often offer natural language commands;
form a subset of DML from a functional point of view.
Explain the first normal form
No repeating groups
Atomic (indivisible) values
Each field contains only one value
Explain the second normal form
Must be in 1NF
No partial dependency (composite key split up)
All non-key attributes must depend on the entire primary key
Explain the third normal form
Must be in 2NF
No transitive dependency
Non-key attributes should depend only on the primary key
