- Structured Data: Stores data in tables with fixed schemas.
- ACID Compliance: Ensures data reliability.
- Scalability: Vertical scaling (adding resources to a single server).
- Examples: MySQL, PostgreSQL, Oracle DB.
- Use Case: Banking systems, e-commerce platforms, and applications requiring complex queries.
- Flexible Schema: Stores unstructured or semi-structured data.
- Eventual Consistency: Prioritizes scalability over strict consistency.
- Scalability: Horizontal scaling (adding more servers).
- Examples: MongoDB (document), Cassandra (column), Redis (key-value).
- Use Case: Real-time analytics, IoT applications, social networks.
{
"name": "ramesh",
"age": 61
}
{
"email": "kapoor@gmail.com",
"phone": "45454545"
}Normalization is a database design process that organizes data to reduce redundancy and improve data integrity.
- 1NF (First Normal Form): Removes duplicate columns and ensures atomic values.
- 2NF (Second Normal Form): Removes subsets of data that depend on part of a primary key.
- 3NF (Third Normal Form): Ensures no transitive dependency (non-primary key depends on another non-primary key).
- BCNF (Boyce-Codd Normal Form): Stronger version of 3NF, ensuring every determinant is a candidate key.
An index is a data structure that improves query performance by enabling faster data retrieval.
Indexes create a lookup table that points to the location of data.
CREATE INDEX idx_name ON employees(name);
- Faster SELECT queries.
- Improved search performance.
- Slower INSERT/UPDATE/DELETE operations.
- Increased storage requirements.
ACID stands for:
- Atomicity: Ensures a transaction is fully completed or not executed at all.
- Consistency: Ensures the database remains in a valid state after a transaction.
- Isolation: Transactions are executed independently.
- Durability: Changes from a committed transaction are permanently saved.
Banking systems require ACID compliance to ensure accurate transfers between accounts.
-
INNER JOIN: Returns rows that have matching values in both tables.
SELECT * FROM employees e INNER JOIN departments d ON e.dept_id = d.id;
-
LEFT JOIN: Returns all rows from the left table and matching rows from the right table. Fills unmatched rows with NULL.
SELECT * FROM employees e LEFT JOIN departments d ON e.dept_id = d.id;
-
RIGHT JOIN: Returns all rows from the right table and matching rows from the left table. Fills unmatched rows with NULL.
SELECT * FROM employees e RIGHT JOIN departments d ON e.dept_id = d.id;
-
OUTER JOIN: Returns all rows from both tables, filling unmatched rows with NULL.
SELECT * FROM employees e FULL OUTER JOIN departments d ON e.dept_id = d.id;
- INNER JOIN: When you need only matching records (e.g., finding employees in specific departments).
- LEFT/RIGHT JOIN: When you want all records from one table and matching records from another (e.g., finding all employees regardless of department assignment).
- OUTER JOIN: When you need all records from both tables (e.g., finding employees without departments and departments without employees).
- Structure: Relational, table-based schema with predefined structure.
- Query Language: Use SQL for data manipulation.
- Scalability: Vertically scalable (add resources to a single server).
- Examples: MySQL, PostgreSQL, Oracle.
- Use Case: Banking systems, e-commerce, applications with complex queries.
- Structure: Non-relational, supports document, key-value, graph, or columnar models.
- Query Language: Varies by database (e.g., JSON-based for document databases).
- Scalability: Horizontally scalable (add more servers).
- Examples: MongoDB (document), Cassandra (column), Redis (key-value).
- Use Case: Real-time analytics, IoT applications, content management systems.
Normalization is the process of organizing data to reduce redundancy and improve data integrity.
-
1NF (First Normal Form):
- Ensure all attributes contain atomic values.
- Remove duplicate columns.
-
2NF (Second Normal Form):
- Remove partial dependency (non-prime attributes depend on part of a composite key).
-
3NF (Third Normal Form):
- Remove transitive dependency (non-prime attributes depend on other non-prime attributes).
-
BCNF (Boyce-Codd Normal Form):
- A stricter version of 3NF; every determinant must be a candidate key.
An index is a data structure that improves the speed of data retrieval operations on a database table.
- Creates a lookup table to quickly find the row corresponding to a key value.
- Reduces the time complexity for search operations from O(n) to O(log n).
CREATE INDEX idx_name ON employees(name);
- Slower write operations due to the need to update the index.
- Increased storage requirements.
ACID stands for:
- Atomicity: Transactions are all-or-nothing.
- Consistency: Database remains in a valid state after a transaction.
- Isolation: Transactions do not interfere with each other.
- Durability: Committed transactions are permanently saved.
In a banking system, transferring funds between accounts ensures the amount is deducted and added atomically and consistently.
-
INNER JOIN:
- Returns rows with matching values in both tables.
SELECT * FROM employees e INNER JOIN departments d ON e.dept_id = d.id;
-
LEFT JOIN:
- Returns all rows from the left table and matching rows from the right table; unmatched rows are filled with NULL.
SELECT * FROM employees e LEFT JOIN departments d ON e.dept_id = d.id;
-
RIGHT JOIN:
- Returns all rows from the right table and matching rows from the left table; unmatched rows are filled with NULL.
SELECT * FROM employees e RIGHT JOIN departments d ON e.dept_id = d.id;
-
FULL OUTER JOIN:
- Returns all rows from both tables; unmatched rows are filled with NULL.
SELECT * FROM employees e FULL OUTER JOIN departments d ON e.dept_id = d.id;
- INNER JOIN: For retrieving only matching records (e.g., finding employees in specific departments).
- LEFT JOIN: For including all records from one table and matching ones from another (e.g., employees with or without departments).
- FULL OUTER JOIN: To find all rows, including unmatched records (e.g., employees without departments and vice versa).