Database (RDBMS) - AsrafulSamad/PoraLikha GitHub Wiki

DBMS/RDBMS:

RDBMS refers to Relational Database Management System.

DBMS Architecture:

Single Tier Architecture:

Local database system. Doesn’t require a network connection to perform.
Two Tier Architecture:

Client(User & DB App) -> Network Connection -> Server(Database System)
Three Tier Architecture:

Client(User & DB App) -> Network Connection -> Server(Server App --> Database System)

DBMS - Three Level Architecture:

External or View level

Records can be described as blocks of storage (bytes, gigabytes, terabytes etc.) in memory.
Conceptual or Logical level

Records can be described as fields and attributes along with their data types, their relationship among each other can be logically implemented.
Internal or Physical level

User just interact with system with the help of GUI.

RDBMS Concepts:

Table
Record or Tuple
Field or Column name or Attribute or Tuple or vertical entity
Domain

Set of permitted values for an attribute.
Instance

The data stored in database at a particular moment of time is called instance of database.
Schema

Schema (Physical, logical and view schema) is a structural view(design) of a database that shows the relationship between tables.
Keys
1. Primary Key
  
  A primary is a column or set of columns in a table that uniquely identifies tuples (rows) in that table.
2. Super Key
  
  A super key is a set of one of more columns (attributes) to uniquely identify rows in a table.
3. Candidate Key
  
  A super key with no redundant attribute is known as candidate key
4. Alternate Key
  
  Out of all candidate keys, only one gets selected as primary key, remaining keys are known as alternate or secondary keys.
5. Composite Key
  
  A key that consists of more than one attribute to uniquely identify rows (also known as records & tuples) in a table is called composite key.
6. Foreign Key
  
  Foreign keys are the columns of a table that points to the primary key of another table. They act as a cross-reference between tables.
```
  **Note:**
    Super Key (Super Set)  -> Candidate Key (Sub Set) -> Primary Key (One is Chosen from Candidate Key) 
    Super Key Set - Candidate Key Set => Alternate Key
```
SQL

Structured Query Language (DDL, DML, DCL, TCL)
DDL

Data Definition Language (CREATE, ALTER, DROP, TRUNCATE, RENAME, DROP, Comment)
DML

Data Manipulation Language (SELECT, INSERT, UPDATE, DELETE)
DCL

Data Control language (GRANT, REVOKE)
TCL

Transaction Control Language (COMMIT, ROLLBACK)
ER Diagram:
1. Entity
2. Attribute
  1. Key attribute
  2. Composite attribute
  3. Multivalued attribute
  4. Derived attribute
3. Relationship
  1. One to One
  2. One to Many
  3. Many to One
  4. Many to Many
Constraints:
1. NOT NULL
2. UNIQUE
3. DEFAULT
4. CHECK
5. Key Constraints – PRIMARY KEY, FOREIGN KEY
6. Domain constraints
7. Mapping constraints
Cardinality:
1. In Context of Data Models
  
  Relationship(One to One, One to Many, Many to One, Many to Many)
2. In Context of Query Optimization
  
  In terms of query, the cardinality refers to the uniqueness of a column in a table. The column with all unique values would be having the high cardinality and the column with all duplicate values would be having the low cardinality. These cardinality scores helps in query optimization.
Normalization: To eliminate Update, Insert and Delete anomaly Normalization has come into play.
1. First normal form (1NF):
  
  As per the rule of first normal form, an attribute (column) of a table cannot hold multiple values. It should hold only atomic values.
2. Second normal form (2NF):
  1. Table is in 1NF (First normal form)
  2. No non-prime attribute is dependent on the proper subset of any candidate key of table. An attribute that is not part of any candidate key is known as non-prime attribute. To make the table complies with 2NF we can break it in two tables.
3. Third Normal form (3NF):
  1. Table must be in 2NF
  2. Transitive functional dependency of non-prime attribute on any super key should be removed. A -> B && B -> C
4. Boyce Codd normal form (BCNF):
  
  A table complies with BCNF if it is in 3NF and for every functional dependency X->Y, X should be the super key of the table.
De-Normalization: Denormalization is a database optimization technique in which we add redundant data to one or more tables. This can help us avoid costly joins in a relational database. Note that denormalization does not mean not doing normalization. It is an optimization technique that is applied after doing normalization.
ACID:
1. Atomicity
  
  Transaction must be complete not partial.
2. Consistency
  
  While modifying one row by one transaction another transaction can not modify it.
3. Isolation
  
  For every pair of transactions, one transaction should start execution only when the other finished execution.
4. Durability
  
  Once a transaction completes successfully, the changes it has made into the database should be permanent even if there is a system failure.
Delete/Truncate/Drop:
1. Delete -> It is used to delete the one or more tuples of a table.
2. Drop -> It is used to drop the whole table.
3. Truncate -> It is used to delete all the rows of a relation (table) in one go.
View:

A View is a virtual relation that acts as an actual relation. It is not a part of logical relational model of the database system. Tuples of the view are not stored in the database system and tuples of the view are generated every time the view is accessed. Query expression of the view is stored in the databases system.
Materialized Views:

When the results of a view expression are stored in a database system, they are called materialized views. SQL does not provides any standard way of defining materialized view, however some database management system provides custom extensions to use materialized views.

Indexing

When index is created on a column of a table, it actually creates a lookup table with column and a pointer to the memory address where a row with this column is actually stored.

Ref: https://www.geeksforgeeks.org/indexing-in-databases-set-1/

B-Tree Index:
1. Balanced Tree
2. Nodes store value and RowId
3. Logarithmic Scalability
Execution Plan:

Steps database need to perform to execute a query. Sample: EXPLAIN SELECT * FROM users where id = 1;
Notes:
1. Using function in where clause does not use index.
2. Remove function.
3. Force index Can be used but not good.
4. Column order matters.
5. Inequality Operators.
Access Types:
1. CONST/EQ_REF -> Super fast
2. REF/RANGE -> index range scan
3. INDEX -> Full Index Scan
4. ALL -> Full table Scan
Other Terms:
1. Access Time: It refers to the time needed to find particular data element or set of elements.
2. Insertion Time: It refers to the time taken to find the appropriate space and insert a new data.
3. Deletion Time: Time taken to find an item and delete it as well as update the index structure.
4. Space Overhead: It refers to the additional space required by the index.

Index & Hard Disk:

Track & Sector => Block (512 Bytes In General)

Employee Table Example:

   5 Columns
   100 Rows
   128 Bytes per row.
   Number of blocks = 512/128 = 4 rows per block
   For 100 records = 100/4 = 25 Blocks
   Number of blocks for index = 4 (512/10 = 32, 100/32 =3.2) -> Dense Index
   Multilevel Index -> Sparse Index

Organization mechanisms of Indexing
1. Sequential File Organization or Ordered Index File
  1. Dense Index
  2. Sparse Index
2. Hash File organization
Methods of indexing
1. Clustered Indexing
2. Non-Clustered or Secondary Indexing
3. Multilevel Indexing
Tips
1. Use index to improve performance of slow query
2. Use B-Treee index when there is less or no duplicate values in column (high cardinality).
3. Use bitmap index when there are lots of duplicate value in column (low cardinality)
4. Must index foreign key if there can be concurrent DML operation (if not indexed can cause deadlock!!!)
5. Never use bitmap index in a table where concurrent DML is possible (if index exist can cause deadlock!!!)
6. Bitmap index main used in data warehouse/reporting DB
7. Good practices to add index in own tablespace rather global tablespace

Procedure:

Stored Procedures are created to perform one or more DML operations on Database. It is nothing but the group of SQL statements that accepts some input in the form of parameters and performs some task and may or may not returns a value.

Cursor:

A cursor can be viewed as a pointer to one row in a set of rows. The cursor can only reference one row at a time, but can move to other rows of the result set as needed. In SQL procedures, a cursor makes it possible to define a result set (a set of data rows) and perform complex logic on a row by row basis.

Trigger:

A trigger is a stored procedure in database which automatically invokes whenever a special event in the database occurs. For example, a trigger can be invoked when a row is inserted into a specified table or when certain table columns are being updated.

Dead Lock:

In a database, a deadlock is an unwanted situation in which two or more transactions are waiting indefinitely for one another to give up locks.

Checkpoint:

The checkpoint is used to declare a point before which the DBMS was in the consistent state, and all transactions were committed. During transaction execution, such checkpoints are traced. After execution, transaction log files will be created. Upon reaching the savepoint/checkpoint, the log file is destroyed by saving its update to the database. Then a new log is created with upcoming execution operations of the transaction and it will be updated until the next checkpoint and the process continues. A checkpoint is a feature that adds a value of C in ACID-compliant to RDBMS. A checkpoint is used for recovery if there is an unexpected shutdown in the database.

RAID(Redundant Arrays of Independent Disks):

RAID or Redundant Array of Independent Disks, is a technology to connect multiple secondary storage devices and use them as a single storage media.

RAID 0: In this level, a striped array of disks is implemented. The data is broken down into blocks and the blocks are distributed among disks. Each disk receives a block of data to write/read in parallel. It enhances the speed and performance of the storage device. There is no parity and backup in Level 0.
RAID 1: uses mirroring techniques. When data is sent to a RAID controller, it sends a copy of data to all the disks in the array. RAID level 1 is also called mirroring and provides 100% redundancy in case of a failure.
RAID 2: records Error Correction Code using Hamming distance for its data, striped on different disks. Like level 0, each data bit in a word is recorded on a separate disk and ECC codes of the data words are stored on a different set disks. Due to its complex structure and high cost, RAID 2 is not commercially available.
RAID 3: stripes the data onto multiple disks. The parity bit generated for data word is stored on a different disk. This technique makes it to overcome single disk failures.
RAID 4: In this level, an entire block of data is written onto data disks and then the parity is generated and stored on a different disk. Note that level 3 uses byte-level striping, whereas level 4 uses block-level striping. Both level 3 and level 4 require at least three disks to implement RAID.
RAID 5: RAID 5 writes whole data blocks onto different disks, but the parity bits generated for data block stripe are distributed among all the data disks rather than storing them on a different dedicated disk.
RAID 6: RAID 6 is an extension of level 5. In this level, two independent parities are generated and stored in distributed fashion among multiple disks. Two parities provide additional fault tolerance. This level requires at least four disk drives to implement RAID.

OLAP(Online Analytical Processing):

Any type of Data warehouse system is an OLAP system. Uses of OLAP are as follows:

Spotify analyzed songs by users to come up with the personalized homepage of their songs and playlist.
Netflix movie recommendation system.

OLTP(Online transaction processing ):

Online transaction processing provides transaction-oriented applications in a 3-tier architecture. OLTP administers day to day transaction of an organization.

DB Partitioning:

Kinds of Partitioning :

Horizontal Partitioning -> By Row
Vertical Partitioning -> By Column

Other kinds of Partitioning:

Single-level Partitioning
Composite Partitioning

Three approaches:

Hash
Range
List

Sharding

Before sharding one should consider followings.

Indexing
Partitioning
Vertical scaling (faster processor, more RAM, faster disk etc.)
Replication (CQRS)
Caching

When all of the above fail, one should use sharding as the last resort. Sharding enables horizontal scaling.

A single logical dataset is split into multiple databases that are then distributed across multiple machines.
Sharding adopts a shared-nothing architecture.
Different kinds of sharding:
- Key-Based
- Range-Based
- Dictionary-Based
- Hierarchical
- Entity Groups

Ref: https://devopedia.org/database-sharding

Database Compression:

Database compression is a set of techniques that reorganizes database content to save on physical storage space and improve performance speeds. Compression can be achieved in two primary ways:

Lossless
Lossy Among lossless techniques, all compressed data is stored in binary.
Run-Length Encoding
Prefix encoding
Dictionary compression

Ref: https://devopedia.org/database-compression

Databases, tablespaces, and datafiles:

In Oracle, Databases, tablespaces, and datafiles are closely related, but they have important differences:

An Oracle database consists of one or more logical storage units called tablespaces, which collectively store all of the database's data.
Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that conform to the operating system in which Oracle is running.
A database's data is collectively stored in the datafiles that constitute each tablespace of the database. For example, the simplest Oracle database would have one tablespace and one datafile. Another database can have three tablespaces, each consisting of two datafiles (for a total of six datafiles).

How to choose the right database

These are the most important questions to ask when you are picking a database:

* How much data do you expect to store when the application is mature?
* How many users do you expect to handle simultaneously at peak load?
* What availability, scalability, latency, throughput, and data consistency does your application need?
* How often will your database schemas change?
* What is the geographic distribution of your user population?
* What is the natural “shape” of your data?
* Does your application need online transaction processing (OLTP), analytic queries (OLAP), or both?
* What ratio of reads to writes do you expect in production?
* Do you need geographic queries and/or full-text queries?
* What are your preferred programming languages?
* Do you have a budget? If so, will it cover licenses and support contracts?
* Are there legal restrictions on your data storage?

Ref:

 https://www.infoworld.com/article/3452894/how-to-choose-the-right-database-for-your-application.html
 https://github.com/dwyl/how-to-choose-a-database

Database Migration:

 Database migration is the process of migrating data from one or more source databases to one or more target databases by using a database migration service. 
 When a migration is finished, the dataset in the source databases resides fully, though possibly restructured, in the target databases. 
 Clients that accessed the source databases are then switched over to the target databases, and the source databases are turned down.

Ref: