Gist from Drafts

Databases-keys.md

Understanding Keys in RDBMS

Great Youtube Explainer

https://youtu.be/8wUUMOKAK-c?si=sOVs-sh681RqmVfO

Detailed Breakdown of Understanding Keys in RDBMS Using Your Personal Contact Database

In a scenario, where you manage contacts across multiple platforms (iMessage, LinkedIn, email accounts, WhatsApp, GroupMe), implementing relational database keys is essential to organize, maintain, and retrieve your contact information effectively. Here’s how different types of keys can be applied using your contact database as an example:

---

1. What is a Key in RDBMS?

In your personal contact database, a key is an attribute (like a phone number or email) or a set of attributes that uniquely identifies a contact. These keys help in ensuring that your contact data remains unique and related, even though your contacts span across multiple platforms.

2. Types of Keys in RDBMS Applied to Your Contact Database

a. Primary Key

• Definition: In your contact database, the primary key is a unique identifier for each contact. It could be something like contact_id, which ensures that each contact is uniquely identified, even if they exist on multiple platforms.
• Purpose: Helps uniquely identify a contact, ensuring that your contacts are distinct across iMessage, LinkedIn, WhatsApp, etc.
• Constraints:
  • No two contacts can have the same contact_id.
  • The primary key cannot be NULL.

Example:

CREATE TABLE contacts (
   contact_id INT PRIMARY KEY,
   first_name VARCHAR(50),
   last_name VARCHAR(50),
   phone_number VARCHAR(15),
   email_address VARCHAR(100)
);

Here, contact_id is the primary key that uniquely identifies each contact across platforms.

b. Foreign Key

• Definition: In your database, a foreign key might link your contacts table to a separate table containing platform-specific information. For instance, a contact could be linked to their communication channels via a platform_id.
• Purpose: Ensures that the platform data (iMessage, LinkedIn, WhatsApp, etc.) is linked to the correct contact.
• Constraints: Foreign keys can be NULL and do not need to be unique.

Example:

CREATE TABLE contact_platforms (
   platform_id INT PRIMARY KEY,
   platform_name VARCHAR(50)
);

CREATE TABLE contact_details (
   contact_id INT,
   platform_id INT,
   username VARCHAR(100),
   FOREIGN KEY (contact_id) REFERENCES contacts(contact_id),
   FOREIGN KEY (platform_id) REFERENCES contact_platforms(platform_id)
);

Here, platform_id in the contact_details table is a foreign key that references the platform_id in the contact_platforms table, linking a contact to their platform-specific information.

c. Candidate Key

• Definition: In your contact database, a candidate key could be an attribute like email_address or phone_number, which could potentially be used as a primary key. However, you’ll need to choose one as the actual primary key.
• Purpose: Helps uniquely identify contacts by their phone number or email address, ensuring no duplicates exist across platforms.
• Constraints: Must contain unique, non-null values.

Example:

CREATE TABLE contacts (
   contact_id INT,
   phone_number VARCHAR(15) UNIQUE,
   email_address VARCHAR(100) UNIQUE,
   PRIMARY KEY (contact_id)
);

Here, both phone_number and email_address are candidate keys because they uniquely identify a contact, though they are not the primary key.

d. Composite Key

• Definition: A composite key might consist of multiple columns that together uniquely identify a record. In your case, this could be a combination of platform_id and contact_id to uniquely identify how each contact interacts across platforms.
• Purpose: Ensures unique identification of a contact across multiple platforms.
• Constraints: Must be unique across the combination of columns.

Example:

CREATE TABLE contact_details (
   contact_id INT,
   platform_id INT,
   PRIMARY KEY (contact_id, platform_id)
);

Here, the combination of contact_id and platform_id uniquely identifies a contact’s interaction on a specific platform, ensuring no duplicate entries.

e. Alternate Key

• Definition: An alternate key is a candidate key that is not chosen as the primary key but still enforces uniqueness. For instance, you might choose phone_number as the alternate key in case you need to enforce unique phone numbers without using them as the primary key.
• Purpose: Ensures that certain fields (like email_address) remain unique, even if they aren’t the primary key.
• Constraints: Must contain unique and non-null values.

Example:

CREATE TABLE contacts (
   contact_id INT PRIMARY KEY,
   email_address VARCHAR(100) UNIQUE
);

In this example, email_address is an alternate key, ensuring that no two contacts share the same email.

f. Super Key

• Definition: A super key is any combination of columns that can uniquely identify a record. It can contain extra columns beyond what's needed for uniqueness. In your case, contact_id + phone_number could form a super key, even though just one would suffice.
• Purpose: While super keys provide unique identification, they may not be as efficient as simpler keys like primary or candidate keys.
• Constraints: The combination of columns must uniquely identify a contact.

Example:

CREATE TABLE contacts (
   contact_id INT,
   phone_number VARCHAR(15),
   email_address VARCHAR(100),
   PRIMARY KEY (contact_id),
   UNIQUE (contact_id, email_address)
);

Here, contact_id and email_address together form a super key, even though just contact_id is sufficient.

---

3. Importance of Keys in Your Contact Database

• Data Integrity: Keys ensure that each contact is unique, preventing duplicates even if the same person appears on multiple platforms.
• Referential Integrity: Foreign keys ensure that platform-specific information is correctly associated with each contact.
• Efficient Data Retrieval: Primary and foreign keys improve the performance of queries, making it easier to find and link your contacts across different platforms.
• Database Normalization: Keys play a critical role in structuring your contact database efficiently, reducing redundancy and improving scalability as you add more contacts.

---

4. Practical Activities for Your Contact Database

a. Hands-On Activity: Creating a Contact Table with Keys

Set up your contact database, ensuring that each contact is uniquely identifiable across platforms.

Activity:

1. Create a contacts table with a contact_id as the primary key.
2. Create a contact_platforms table with platform details (iMessage, LinkedIn, WhatsApp, etc.).
3. Create a contact_details table linking contacts to their specific platforms using a composite key.

b. Working with Candidate and Alternate Keys

Practice enforcing uniqueness for phone numbers and email addresses, which could serve as alternate keys.

Activity:

1. Add unique constraints to your phone_number and email_address columns.
2. Ensure that no two contacts can have the same phone number or email.

c. Exploring Composite Keys

Design a system where the same contact can appear across multiple platforms without duplication.

Activity:

1. Create a composite key in the contact_details table that ensures each contact is linked uniquely to a platform.

---

5. Best Practices for Using Keys in Your Contact Database

• Always Define Primary Keys: Use contact_id as a unique identifier across all your contacts, regardless of platform.
• Use Foreign Keys to Link Platforms: Ensure you use foreign keys to link platform-specific details to contacts, maintaining referential integrity.
• Use Composite Keys for Complex Relationships: Apply composite keys when a contact exists on multiple platforms.
• Enforce Uniqueness for Critical Fields: Ensure that key fields like phone numbers and emails are unique to prevent duplicates.