DBMS Languages

• DDL(Data Definition Language):  CREATE, ALTER, DROP, TRUNCATE, RENAME, etc.
• DML(Data Manipulation Language): SELECT, UPDATE, INSERT, DELETE, etc.
• DCL(Data Control Language):  GRANT and REVOKE.
• TCL(Transaction Control Language):  COMMIT, ROLLBACK, and SAVEPOINT.

ACID

Atomicity: Either True or false transactions Consistency: Data remains consistent before and after a transaction Isolation: State of an ongoing transaction doesn’t affect the state of another ongoing transaction. Durability: Ensures that the data is not lost in cases of a system failure or restart and is present in the same state as it was before the system failure or restart.

ER Model

Normalisation & Denormalisation

Normalisation: Reducing redundancy by organising data into multiple tables. Denormalisation:  

• Reverse process of normalization as it combines the tables which have been normalized into a single table
• Makes data retrieval becomes faster, used in JOINS

Locking

• Shared Lock: Multiple transactions are allowed to read the data. (Used for reads)
• Exclusive lock: Doesn’t allow more than one transaction (Used for writes) for preventing inconsistency

DELETE & TRUNCATE

DELETE command:  Delete rows by condition

• It deletes only the rows which are specified by the WHERE clause.

• Rollbacks

• It maintains a log to lock the row of the table before deleting it and hence it’s slow.

TRUNCATE command:  Delete table's all data, not table itself

• It removes complete data from a table in a database.

• No Rollbacks

• It doesn’t maintain a log and deletes the whole table at once and hence it’s fast.

Levels of data abstraction

### Data Warehousing

 > Collecting, extracting, transforming, and loading data from multiple sources and storing them in one DB

DB Keys

Candidate Key: Multiple cols that are unique (each table can have >=1 ) Primary Key:

• Unique identifier to a tuple (at most 1 per table)
• Any Candidate keys (unique col) can be made Primary Key

Super Key: Candidate Key + Primary Key (All Unique Cols) Alternate Key: Candidate Keys - Primary Keys Foreign Key*: Attribute that has common value in another table Composite Key: Two or more columns for identification when combined

Normalisation Forms

Example of the schema here:

1NF (1st Normal Form)

• Column has 1 val
• Duplicates cols removal
• Cross Product columns where date changes Example in 1NF:

2NF (2nd Normal Form)

• Should be 1NF
• Every non-prime attribute of the table should be fully dependent on the primary key

DB in production

Scaling Sharding Databases

Indexing

Functional Dependency

Concurrency Control Protocols

JOINS

Here are the different types of the JOINS in SQL:

• (INNER) JOIN:  Intersection
• LEFT (OUTER) JOIN: All from left + matches from right
• RIGHT (OUTER) JOIN: All from right + matches from left
• FULL (OUTER) JOIN: Union