Part 1 of CS437

MySQL Column and Row Data Types

MySQL provides various data types to store different kinds of values efficiently. These data types are categorized as:

1. Numeric Data Types: Used for storing numbers.
   

2. String (Character) Data Types: Used for storing text values.
   

3. Date and Time Data Types: Used for storing date and time values.
   
   

4. 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

1. 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.

2. 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.