Oracle

In the context of relational databases, cardinality refers to the uniqueness of data values in a particular column (attribute) of a database table. Understanding cardinality is crucial for database design and optimization. It helps in defining relationships between tables, optimizing queries, and ensuring data integrity. Cardinality can be categorized into different types based on the relationship between tables or the uniqueness of values within a column.

Here’s a breakdown of cardinality concepts:

1. Types of Cardinality

   • One-to-One (1:1)

     • Each row in Table A corresponds to exactly one row in Table B, and vice versa.
     • Example: Each Membership might correspond to exactly one Library Card.

   • One-to-Many (1:N)

     • A single row in Table A can relate to multiple rows in Table B.
     • Example: Each Author can write multiple Books, but each Book is written by one Author.

   • Many-to-Many (N:M)

     • Rows in Table A can relate to multiple rows in Table B, and vice versa.
     • Example: An Author can write multiple Books, and each Book can have multiple Authors.

2. Cardinality in SQL

   • Unique Constraints

     • Ensure that all values in a column are unique.
     • Example:

       ALTER TABLE Authors
       ADD CONSTRAINT unique_author_email UNIQUE (email);
       

   • Primary Keys

     • A column or a set of columns uniquely identifies each row in a table.
     • Example:

       CREATE TABLE Books (
           book_id INT PRIMARY KEY,
           title VARCHAR(255),
           author_id INT
       );
       

   • Foreign Keys

     • Establish relationships between tables, ensuring referential integrity.
     • Example:

       CREATE TABLE Memberships (
           membership_id INT PRIMARY KEY,
           library_id INT,
           FOREIGN KEY (library_id) REFERENCES Library(library_id)
       );
       

3. Cardinality in Queries

   • Filtering Results Based on Cardinality
     • Use cardinality to filter and retrieve data based on relationships.
     • Example:

       SELECT b.title
       FROM Books b
       JOIN Authors a ON b.author_id = a.author_id
       WHERE a.name = 'John Doe';
       

Understanding and correctly applying cardinality concepts helps in designing efficient databases and writing optimized queries.

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.