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.


robot