MySQL

In the context of Relational Database Management Systems (RDBMS), cardinality refers to the uniqueness of data values contained in a particular column (or set of columns) in a table. It describes the nature of the relationship between two tables in terms of how many rows in one table are related to rows in another table. Cardinality is crucial for designing efficient databases and optimizing queries.

Here’s a detailed look at cardinality:

1. Types of Cardinality

   • One-to-One (1:1)

     • Each row in Table A is linked to exactly one row in Table B, and vice versa.
     • Example: Each employee has a unique company car.

     CREATE TABLE Employees (
         EmployeeID INT PRIMARY KEY,
         CarID INT UNIQUE,
         FOREIGN KEY (CarID) REFERENCES Cars(CarID)
     );
     
     CREATE TABLE Cars (
         CarID INT PRIMARY KEY,
         CarModel VARCHAR(50)
     );

   • One-to-Many (1:N)

     • A single row in Table A can be related to multiple rows in Table B, but each row in Table B is related to only one row in Table A.
     • Example: A department has many employees, but each employee belongs to only one department.

     CREATE TABLE Departments (
         DepartmentID INT PRIMARY KEY,
         DepartmentName VARCHAR(50)
     );
     
     CREATE TABLE Employees (
         EmployeeID INT PRIMARY KEY,
         FirstName VARCHAR(50),
         LastName VARCHAR(50),
         DepartmentID INT,
         FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
     );

   • Many-to-Many (N:N)

     • Rows in Table A can be related to multiple rows in Table B, and rows in Table B can be related to multiple rows in Table A.
     • Example: Employees can work on multiple projects, and each project can have multiple employees.

     CREATE TABLE Projects (
         ProjectID INT PRIMARY KEY,
         ProjectName VARCHAR(50)
     );
     
     CREATE TABLE Employees (
         EmployeeID INT PRIMARY KEY,
         FirstName VARCHAR(50),
         LastName VARCHAR(50)
     );
     
     CREATE TABLE EmployeeProjects (
         EmployeeID INT,
         ProjectID INT,
         PRIMARY KEY (EmployeeID, ProjectID),
         FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID),
         FOREIGN KEY (ProjectID) REFERENCES Projects(ProjectID)
     );

2. Cardinality in Database Design

   • Understanding cardinality helps in designing database schemas that reflect the real-world relationships between entities.
   • Properly defining cardinality can lead to more efficient queries and better data integrity.

3. Impact on Query Performance

   • The cardinality of relationships affects how efficiently queries can retrieve data.
   • For example, one-to-many relationships often involve joins that can impact performance if not indexed properly.

   -- Example of a join query in a one-to-many relationship
   SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName
   FROM Employees
   JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

4. Normalization

   • Cardinality plays a key role in database normalization, which is the process of organizing data to reduce redundancy and improve data integrity.
   • Normalizing tables helps in properly establishing relationships based on cardinality.

5. Indexing

   • Creating indexes on columns involved in cardinality relationships can enhance query performance.
   • For example, indexing foreign keys can speed up joins and searches.

   -- Creating an index on DepartmentID for faster joins
   CREATE INDEX idx_department ON Employees(DepartmentID);

Understanding cardinality helps in effectively designing and managing relational databases, ensuring that the relationships between data are accurately represented and that queries are optimized for performance.

1:1 Relationship

A1:1 (one-to-one) relationshipbetween two tables occurs when each record in the first table is related to only one record in the second table, and vice versa. This relationship is often used to extend a table or to store sensitive data separately.

Example: User and User Profile

In an application, each user has a single user profile, and each user profile is associated with a single user.

• Userstable:UserID,Username,Email
• UserProfilestable:ProfileID,UserID(foreign key),Address,PhoneNumber

SQL Example:

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Username VARCHAR(255) NOT NULL,
    Email VARCHAR(255)
);

CREATE TABLE UserProfiles (
    ProfileID INT PRIMARY KEY,
    UserID INT,
    Address VARCHAR(255),
    PhoneNumber VARCHAR(255),
    FOREIGN KEY (UserID) REFERENCES Users(UserID)
);

1:N Relationship

A1:N (one-to-many) relationshipbetween two tables occurs when a single record in the first table can be associated with one or more records in the second table. This is the most common relationship type.

Example: Author and Books

An author can write several books, but each book has only one author.

• Authorstable:AuthorID,Name,Country
• Bookstable:BookID,Title,PublicationYear,AuthorID(foreign key)

SQL Example:

CREATE TABLE Authors (
    AuthorID INT PRIMARY KEY,
    Name VARCHAR(255) NOT NULL,
    Country VARCHAR(255)
);

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(255) NOT NULL,
    PublicationYear INT,
    AuthorID INT,
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);

Many-to-One (N:1)

AN:1relationship is the inverse of 1:N, where many rows in Table A relate to a single row in Table B. It's another perspective of viewing a 1:N relationship, focusing on how multiple instances relate to a single instance on the other side.

Many-to-Many (N:N)

AN:Nrelationship occurs when rows in Table A can relate to many rows in Table B and vice versa. This type of relationship requires a third table, known as a junction or join table, to manage the associations between the entities.

Example Diagrams and Implementations:

Entity-Relationship Diagrams (ERDs) are often used to visually represent these relationships, with symbols indicating the type of cardinality between tables.

For example, in a library system, an Authors table and a Books table would have a 1:N relationship (one author can write multiple books), while a Users table and UserProfiles table might have a 1:1 relationship (each user has one profile).

SAMPLE DATA MODEL FOR 1:N (one-to-many)

As evident in the data model, the relationship line connecting the department and employee tables illustrates a one-to-many relationship—where one instance exists on the department side and multiple instances on the employee side. This signifies that within the department table, a given department may have zero, one, or multiple occurrences in the employee table. For instance, department ID 1 is utilized once in the employee table, department 2 is used multiple times, and department 5 is not utilized at all in the employee table.