Database Systems: Normalization and Transactions

Process of organizing data in a database to reduce redundancy and improve data integrity.
Involves dividing large tables into smaller ones and defining relationships between them.
Key forms of normalization:
1. First Normal Form (1NF): Ensures all columns contain atomic values; no repeating groups.
2. Second Normal Form (2NF): Achieves 1NF and removes partial dependencies; every non-key attribute is fully functionally dependent on the primary key.
3. Third Normal Form (3NF): Achieves 2NF and removes transitive dependencies; non-key attributes should not depend on other non-key attributes.
4. Boyce-Codd Normal Form (BCNF): A stronger version of 3NF; every determinant must be a candidate key.

Refers to the management of data transactions in a database to ensure data integrity and consistency.
Key properties defined by ACID:
1. Atomicity: Ensures all operations in a transaction are completed; if one fails, the entire transaction fails.
2. Consistency: Guarantees that a transaction takes the database from one valid state to another, maintaining all predefined rules.
3. Isolation: Ensures transactions occur independently; the results of a transaction are not visible to others until committed.
4. Durability: Ensures that once a transaction has been committed, it remains so, even in the event of a system failure.
Techniques used include locking mechanisms and concurrency control.

The process of designing the structure of a database, including the schemas and relationships among data.
Key steps include:
1. Requirements analysis: Identify data needs and user requirements.
2. Conceptual design: Create an Entity-Relationship (ER) model to represent data entities and relationships.
3. Logical design: Translate the ER model into a logical schema using tables and relationships.
4. Physical design: Decide on storage structures and access methods for the database management system (DBMS).

Structured Query Language (SQL) is the standard language for managing and manipulating relational databases.
Key components include:
- Data Definition Language (DDL): Commands like CREATE, ALTER, DROP used to define database structures.
- Data Manipulation Language (DML): Commands like SELECT, INSERT, UPDATE, DELETE used to manage data.
- Data Control Language (DCL): Commands like GRANT, REVOKE used to control access to data.
SQL queries can be simple or complex, with the ability to join tables, filter results, and aggregate data.

Non-relational databases designed to handle large volumes of unstructured or semi-structured data.
Types of NoSQL databases include:
1. Document Stores: Store data in JSON-like documents (e.g., MongoDB).
2. Key-Value Stores: Store data as key-value pairs (e.g., Redis).
3. Column-Family Stores: Store data in columns rather than rows (e.g., Cassandra).
4. Graph Databases: Focus on relationships between data points (e.g., Neo4j).
Advantages include scalability, flexibility in data models, and ability to handle diverse data types.

Organizes data within a database to minimize redundancy and enhance data integrity.
Involves segmentation of large tables into smaller tables while establishing defined relationships.
First Normal Form (1NF): Requires all columns to contain atomic values with no repeating groups.
Second Normal Form (2NF): Builds upon 1NF by eliminating partial dependencies; all non-key attributes must fully rely on the primary key.
Third Normal Form (3NF): Further refines 2NF by removing transitive dependencies, ensuring non-key attributes are not dependent on other non-key attributes.
Boyce-Codd Normal Form (BCNF): Stricter than 3NF, mandating that every determinant in the table is a candidate key.

Manages database transactions to maintain data integrity and consistency.
Governed by ACID properties:
- Atomicity: Ensures completion of all operations within a transaction, failing the entire transaction if any part fails.
- Consistency: Guarantees that each transaction transitions the database from one valid state to another, adhering to predefined rules.
- Isolation: Allows transactions to operate independently, preventing the visibility of uncommitted results to other transactions.
- Durability: Ensures committed transactions remain permanent, even in the face of system failures.
Common techniques include locking mechanisms and concurrency control measures.

Involves creating the database structure, including schemas and data relationships.
Key steps in the design process:
- Requirements Analysis: Identifying user data needs and requirements.
- Conceptual Design: Developing an Entity-Relationship (ER) model to visualize data entities and their interconnections.
- Logical Design: Converting the ER model into a logical schema represented in tables and relationships.
- Physical Design: Determining storage structures and access methods for the Database Management System (DBMS).

SQL is the standardized language for database management and manipulation.
Comprises several key components:
- Data Definition Language (DDL): Commands such as CREATE, ALTER, and DROP used for defining database structures.
- Data Manipulation Language (DML): Commands including SELECT, INSERT, UPDATE, and DELETE utilized for data management.
- Data Control Language (DCL): Commands like GRANT and REVOKE to manage data access permissions.
SQL queries can range from simple to complex, enabling table joins, result filtering, and data aggregation.

Non-relational databases designed to manage extensive volumes of unstructured or semi-structured data.
Types of NoSQL databases include:
- Document Stores: Store data as JSON-like documents (e.g., MongoDB).
- Key-Value Stores: Organize data as key-value pairs (e.g., Redis).
- Column-Family Stores: Structure data through columns instead of rows (e.g., Cassandra).
- Graph Databases: Center on the relationships between data points (e.g., Neo4j).
Advantages are characterized by their scalability, flexibility in data models, and capability to manage diverse data types.