RDBMS Theory
Published: 2021-05-12 18:48:19

## TOC

<u> Basic </u>
* [Features](#features)
* [E-R & OO model](#er-oo-model)
* [levels of data abstraction](#levels-of-data-abstraction)
* [Types](#types)

<u> Keys </u>
* [Keys-function](#keys-function)
* [Different Types of keys](#types-of-keys)

<u> Transactions </u>
* [Transactions](#transactions)
* [Operations](#transaction-operations)
* [ACID properties](#acid-properties)
* [Schedule](#transaction-schedule)
* [Isolation Levels](#isolation-levels)

<u> Others </u>
* [DB Recovery](#db-recovery)

References:
https://www.educba.com/rdbms-interview-questions/
https://www.educba.com/dbms-keys/?source=leftnav
https://www.guru99.com/dbms-keys.html
https://www.studytonight.com/dbms/database-key.php
https://www.geeksforgeeks.org/types-of-keys-in-relational-model-candidate-super-primary-alternate-and-foreign/

### <a name="features">Features</a>
- Name
  - name of table
- attribute
  - column
- tuple
  - row in the table

### <a name="er-oo-model">E-R & OO  Model</a>
> Entity-Relationship model & Object oriented model

- E-R
  - top-down approach
  - real life scenarios are entityes
  - properties & attributes in ER diagram & connections 
- Object oriented
  - scenarios are objects
  - objects with similar functionalities are grouped together & linked to different other objects
![alt text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/o3sthyn5m4iie6ibduy7.png)
![alt text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/h1hhlex1ts00l3216vm2.png)
 
 <small>[reference](https://www.tutorialspoint.com/Comparison-between-E-R-Model-and-Object-Oriented-Model)</small>

### <a name="levels-of-data-abstraction">Levels of Data Abstraction</a>
- Physical Level
  - how data is stored
- Logical Level
  - what type of data stored &
  - relationship between data
- View Level
  - entire DB


### <a name="types">Types</a>
- In memory
  - redis
  - memcache

in-memory DB can be made persistent with transaction log
https://medium.com/@denisanikin/what-an-in-memory-database-is-and-how-it-persists-data-efficiently-f43868cff4c1

### <a name="keys-function">Keys Function</a>
- help uniquely rows of data in table
- establish relationship between tables


### <a name="types-of-keys">Types of Keys</a>
- Super key
  - group of single OR multiple keys which identifies rows in table
- Candidate key
  - super key but without attributes not important for uniquely identifying tuples/  rows.
    - subset of super key
    - can be >1 candidate key for a table
    - cannot be NULL or empty. 
    - can be a combination of >1 attributes
- Primary key
  - uniquely identify each record in a table
    - cannot be NULL
    - cannot have duplicate values
- Composite Key
  - consists of >=2 attributes that uniquely identify any record in a table 
  - attributes forming composite key **are not a key individually**
    - individual key cannot be foreign key
- Foreign key
  - column creating relationship between 2 tables
- Alternate key
  - candidate keys other than primary key
- Compound key
  - >=2 attributes that can uniquely identiy records. 
  - **any part can be foreign key**
- Surrogate key
  - artificial key which aims to uniquely identify each record
  - its only purpose is to be unique identifier (eg. incremental key, 
    - it has no actual meaning
    - eg. incremental key, time stamp
    - used when: 
    - no property has the parameter of primary key
    - primary key is too big or complicate

![alt text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7fw35dgnpn1ff6bo749z.png)
 

Further reading
https://www.tutorialspoint.com/Surrogate-Key-in-RDBMS
https://www.hackertrail.com/talent/database/sql-interview-questions-answers/


### <a name="transactions">Transactions</a>
> logical unit of processing 
entails one or more database access operation.

![alt text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rih2vb1kxcog3tpxn1mi.png)
 
- successful transaction can change database from one consistent state of anther
- if DB is in inconsistent state before transaction it will be in inconsistent state after
<small>[reference](https://www.guru99.com/dbms-transaction-management.html)</small>

### <a name="transaction-operations">Transaction Operations</a>
> Transactions access data in 2 ways
**READ**: read value from DB store in a buffer in main memory
**WRITE**: write value back to database from buffer

However transaction may fail before all operations in it are finished (due to hardware / software / power failure).

So there are two operators
> **Commit**: save work done permanently
**Rollback**: undo work done

<small>[reference](https://www.javatpoint.com/dbms-transaction-processing-concept)</small>

### <a name="acid-properties">Transaction Operations</a>
To maintain consistency in database before & after transaction ACID properties are followed

- Atomicity
  - transaction takes place at once or not at all
- Consistency
  - DB must be consistent before and after transaction
    - eg. if operations of add & subtract are executed concurrently, DB may be inconsistent (transactions may read different values)
- Isolation
  - result of transaction should not be visible to others before transaction is committed
- Durable
  - once database committed transaction, changes made by transaction should be permanent 
    
 
<u> Inconsistency Example </u>
![alt text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/o888r6vg29tfge6jtkx2.png)
 

References:
https://www.geeksforgeeks.org/concurrency-control-in-dbms/?ref=lbp
https://www.geeksforgeeks.org/acid-
properties-in-dbms/

### <a name="transaction-schedule">Transaction Schedule</a>
> series of operations from 1 or more transactions

- Serial
  - 1 transaction completely executes before starting another
- Concurrent
  - operations of transaction are interleaved with operations of other transactions of a schedule


### <a name="isolation-levels">Transaction Isolation Levels</a>
> part of ACID properties
Isolation Level define degree to which transaction must be isolated from data modification made by any other transaction in database system

Isolation levels determine by following phenomena
- Dirty Read
  - transaction reads data not yet commited
- Non Repeatable read
  - transaction reads same row twice gets different value each time
- Phantom Read
  - 2 same queries executed but rows retrieved by two are different. 


There are 4 isolation levles
- Read Uncommitted
  - lowest isolation level
  - allow dirty reads (1 transaction can read uncommitted changes made by other transactions) 
- Read Committed
  - no dirty read
  - any data read is committed the moment it is read
- Repeatable Read
  - most restrictive
  - holds read locks on all rows it references and write locks on al rows it inserts / updates / deletes
  - other transactions cannot read update or delete rows -> avoid non-repeatable read. 
- Serializable
  - highest isolation level
  - transaction will be serially executed 

![alt text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fedbsvei0wng2j1d1gac.png)

![alt text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hczac77vguej2f7a1yc1.png)
 

### <a name="db-recovery">DB recovery</a>
> recovery techniques are heavily dependent on existence of **system log**
System log keeps track of all transaction operations that affect DB value
information is needed to recover from transaction failure.