MySQL Column and Row Data Types
MySQL provides various data types to store different kinds of values efficiently. These data types are categorized as:
Numeric Data Types: Used for storing numbers.
String (Character) Data Types: Used for storing text values.
Date and Time Data Types: Used for storing date and time values.
Row Data Types:
MySQL does not explicitly support row types, Table rows are structured based on column definitions, ensuring data consistency.
Database Management System (DBMS) and Its Functions
A DBMS (Database Management System) is software that manages and controls access to databases. It performs several functions:
Create the database and tables.
Modify data (insert, update, delete).
Read and retrieve data.
Enforce constraints to ensure data integrity.
Control concurrency (handling multiple users accessing data simultaneously).
Perform backup and recovery to prevent data loss.
Inner Join vs. Outer Join
Inner Join: Returns only rows with matching values in both tables.
SELECT Employees.EmployeeID, Employees.Name, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
Outer Join: Returns all rows from one table and matching rows from another table.
LEFT JOIN: Includes all rows from the left table and matches from the right.
RIGHT JOIN: Includes all rows from the right table and matches from the left.
FULL JOIN: Includes all rows from both tables (MySQL does not support FULL JOIN natively).
SELECT Employees.EmployeeID, Employees.Name, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
Normalization: 1NF, 2NF, 3NF, and Beyond
First Normal Form (1NF): Ensures each column contains atomic values and each row is unique.
Second Normal Form (2NF): Ensures no partial dependencies (i.e., non-key attributes must depend on the whole primary key).
Third Normal Form (3NF): Ensures no transitive dependencies (i.e., non-key attributes must not depend on another non-key attribute).
Boyce-Codd Normal Form (BCNF): Stronger than 3NF, ensuring every determinant is a candidate key.
Fourth Normal Form (4NF) & Fifth Normal Form (5NF): Address multivalued dependencies and further minimize redundancy.
Relational Database Concepts
Characteristics of a Relation (Table):
Each column has a unique name.
Each cell contains atomic values.
Rows are unique, with no duplicates.
The order of rows and columns does not matter.
Domain Integrity Constraint:
Defines valid values for a column (e.g., Age should be a positive integer).
Enforced using CHECK constraints and data types.
Referential Integrity Constraint
Ensures foreign key values reference valid primary key values.
Example:
FOREIGN KEY (Department) REFERENCES DEPARTMENT(DepartmentName);
NULL values in foreign keys: Allowed if relationships are optional.
Data Integrity Impact: Prevents orphan records and ensures relational consistency.