RDBMS Theory

TOC

Basic

• Features
• E-R & OO model
• levels of data abstraction
• Types

Keys

• Keys-function
• Different Types of keys

Transactions

• Transactions
• Operations
• ACID properties
• Schedule
• Isolation Levels

Others

• 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/

Features

• Name
  • name of table
• attribute
  • column
• tuple
  • row in the table

E-R & OO Model

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

reference

Levels of Data Abstraction

• Physical Level
  • how data is stored
• Logical Level
  • what type of data stored &
  • relationship between data
• View Level
  • entire DB

Types

• 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

Keys Function

• help uniquely rows of data in table
• establish relationship between tables

Types of Keys

• 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

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

Transactions

logical unit of processing
entails one or more database access operation.

• 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 reference

Transaction Operations

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

reference

Transaction Operations

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

Inconsistency Example

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

Transaction Schedule

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

Transaction Isolation Levels

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

DB recovery

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.