Get the Answers to Common Questions

Normalization in MySQL is a process of organizing data in a database to reduce redundancy and improve data integrity. The goal is to design tables in such a way that data is logically stored, dependencies are minimized, and database performance is optimized.

Normalization involves decomposing larger tables into smaller, more manageable tables while establishing relationships between them. This process is guided by normal forms, which are rules to structure the data effectively.

1. First Normal Form (1NF)

• Ensures that each column contains only atomic (indivisible) values.
• Each row is unique and identified by a primary key.

2. Second Normal Form (2NF)

• Achieves 1NF.
• Ensures that all non-key attributes are fully functionally dependent on the entire primary key (removes partial dependency).
• Example:
• Not 2NF:
  
  | StudentID | CourseID | CourseName   | Grade | 
  |-----------|----------|--------------|-------| 
  | 1         | 101      | Math         | A     | 
  | 2         | 102      | Science      | B     | 
  
  2NF: Split into two tables:
  StudentCourses 
  | StudentID | CourseID | Grade | 
  |-----------|----------|-------| 
  | 1         | 101      | A     | 
  | 2         | 102      | B     | 
  
  Courses 
  | CourseID | CourseName | 
  |----------|------------| 
  | 101      | Math       | 
  | 102      | Science    | 
  
  
  
  3. Third Normal Form (3NF)
• Achieves 2NF.
• Ensures that no transitive dependency exists (a non-key column depends on another non-key column).
  3NF: Split into two tables:
  Employees 
  | EmployeeID | DepartmentID | 
  |------------|--------------| 
  | 1          | D1           | 
  | 2          | D2           | 
  
  Departments 
  | DepartmentID | DepartmentName | 
  |--------------|----------------| 
  | D1           | HR             | 
  | D2           | IT             | 
  
  

1. INNER JOIN - Combines rows from two tables where there is a match in the specified columns and returns matching rows.

SELECT columns FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
 

2. LEFT JOIN (or LEFT OUTER JOIN) - Retrieves all rows from the left table and the matching rows from the right table

SELECT columns FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;
 

3. RIGHT JOIN (or RIGHT OUTER JOIN) - Retrieves all rows from the right table and the matching rows from the left table.

SELECT columns FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;
 

4. FULL JOIN (or FULL OUTER JOIN) - Combines results of both LEFT JOIN and RIGHT JOIN

SELECT columns FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name UNION SELECT columns FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;
 

5. CROSS JOIN - returns every combination of rows

SELECT columns FROM table1 CROSS JOIN table2;
 

6. SELF JOIN - Joins a table to itself

SELECT E1.name AS Employee, E2.name AS Manager FROM employees E1 LEFT JOIN employees E2 ON E1.manager_id = E2.id;

1. Primary Key - A column that uniquely identifies each row in a table.

2. Foreign Key - A column in one table that refers to the primary key in another table

3. Unique Key - Ensures that all values in a column (or set of columns) are unique, except NULL values.

4. Composite Key - A key made up of two or more columns to uniquely identify a row.

5. Index Key

• A key used to improve the speed of query execution.
• It creates an index for faster lookups on specified columns.

Types of Indexes:

• Single-column index: Index on one column.
• Multi-column index: Index on multiple columns.
• Unique index: Prevents duplicate values in the indexed column(s).

6. Candidate Key - A set of one or more columns that can uniquely identify a row.

7. Alternate Key - A candidate key that is not chosen as the primary key.

1. Myisam
2. Isam
3. Innodb
4. Heap
5. Merge

1. Transactions Support

MyISAM: Does not support transactions.

InnoDB: Supports ACID-compliant transactions (Atomicity, Consistency, Isolation, Durability).
 

2. Locking Mechanism

MyISAM: Uses table-level locking (locks the entire table on insert/update/delete).

InnoDB: Uses row-level locking (locks only the affected rows, improving performance in concurrent operations).
 

3. Foreign Keys

MyISAM: Does not support foreign key constraints.

InnoDB: Supports foreign key constraints and referential integrity.
 

4. Performance

MyISAM: Faster for read-heavy operations (SELECT queries) but slower for write-heavy workloads.

InnoDB: Performs better for write-intensive operations and high-concurrency applications.
 

5. Crash Recovery

MyISAM: No crash recovery, can lead to data corruption if the server crashes.

InnoDB: Has automatic crash recovery using its transaction log files.
 

6. Storage Structure

MyISAM: Stores data in separate files (.MYD for data, .MYI for indexes).

InnoDB: Stores data in a tablespace (ibdata1 file or per-table storage).
 

7. Full-Text Search

MyISAM: Supports full-text search indexing (before MySQL 5.6).

InnoDB: Supports full-text search from MySQL 5.6+.
 

8. Use Cases

MyISAM: Best for read-heavy applications like blogs, reporting, and analytics.

InnoDB: Best for transactional applications like e-commerce, banking, and CMS.

A transaction in a database is a sequence of one or more SQL operations executed as a single unit of work. A transaction ensures that either all the operations succeed together or none take effect.

Example of a Transaction (Bank Transfer):

START TRANSACTION; 

UPDATE accounts SET balance = balance - 500 WHERE account_id = 1; 
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2; 

COMMIT;  -- Ensures both updates succeed together 
 

Triggers can be perform before and after query.

Types:

1. Before Insert
2. After Insert
3.  Before Update
4.After Update
5.Before Delete
6.After Delete

A Stored Procedure in MySQL is a set of SQL statements that are stored in the database and can be executed as a single unit. Stored procedures help improve performance, encapsulate business logic, and enhance security.
 

Why Use Stored Procedures?

1. Performance Improvement

   • Reduces network traffic by sending only procedure calls instead of multiple queries.

   • Speeds up execution because stored procedures are precompiled.

2. Reusability & Maintainability

   • Write once and reuse multiple times across different applications.

   • Reduces code duplication and makes maintenance easier.

3. Security & Access Control

   • Users can execute stored procedures without needing direct access to underlying tables.

   • Permissions can be restricted to prevent unauthorized data manipulation.

4. Encapsulation of Business Logic

   • Business rules can be implemented within the database layer, ensuring consistency across applications.

5. Transaction Management

   • Helps in executing multiple SQL statements within a single transaction.

   • Ensures atomicity (all or nothing execution).