Tables and Keys in DBMS – Primary, Foreign, and Other Keys Explained
Tables and Keys in DBMS
Introduction
Tables and keys form the backbone of relational databases, organizing data in structured formats. Understanding these concepts is essential for mastering Database Management Systems (DBMS).
Key Concepts:
- Relational Database: The most widely used database model, based on set theory.
- Relation: A synonym for a table in databases.
What is a Table?
A table organizes data into rows and columns. Each row represents a record, and each column represents an attribute or property.
Example: Customer Table
+-------------+--------------+---------------------+---------+ | Customer ID | Customer Name | Customer Address | Zip Code | +-------------+--------------+---------------------+---------+ | 1 | John Doe | New York | 10001 | | 2 | Jane Smith | Los Angeles | 90001 | | 3 | Alice Brown | Chicago | 60007 | +-------------+--------------+---------------------+---------+
Terminology in Tables
- Columns: Represent attributes or properties (e.g., Customer ID, Customer Name).
- Rows: Represent individual records or tuples (e.g., 1, John Doe, New York).
- Instance: A table with actual data, representing the current database state.
- Schema: Defines the table structure, including columns and constraints.
Schema Example:
Customer (CustomerID INT PRIMARY KEY,
CustomerName VARCHAR,
CustomerAddress VARCHAR,
ZipCode INT)
Keys in DBMS
A key is an attribute (or a set of attributes) that uniquely identifies tuples (rows) in a table.
1. Primary Key
Uniquely identifies each row in a table and cannot be null.
- Example: CustomerID in the Customer table.
- Rules: No duplicate values and no null values.
2. Candidate Key
A set of all possible keys that can uniquely identify rows. One candidate key is selected as the primary key.
Example: In the Customer table:
- CustomerID is a candidate key.
- CustomerName + CustomerAddress could also be a candidate key if it uniquely identifies rows.
3. Alternate Key
A candidate key not chosen as the primary key.
Example: If CustomerID is the primary key, CustomerName + CustomerAddress becomes the alternate key.
4. Super Key
Any attribute (or set of attributes) that can uniquely identify rows, including all candidate keys plus additional attributes.
Example: CustomerID + CustomerName is a super key (but not minimal).
5. Foreign Key
An attribute in one table that refers to the primary key of another table. Used to establish relationships between tables.
Example:
CustomerID in the Purchase table references CustomerID in the Customer table:
Customer Table: +-------------+--------------+---------------------+---------+ | Customer ID | Customer Name | Customer Address | Zip Code | +-------------+--------------+---------------------+---------+ | 1 | John Doe | New York | 10001 | +-------------+--------------+---------------------+---------+ Purchase Table: +--------------+-------------+------------+--------+ | Purchase ID | Customer ID | Product ID | Time | +--------------+-------------+------------+--------+ | 101 | 1 | 567 | 12:00 | +--------------+-------------+------------+--------+
Special Types of Keys
- Composite Key: A key with multiple attributes (e.g., CustomerName + CustomerAddress).
- Self-Referential Foreign Key: A foreign key referencing the primary key in the same table.
Example: Employee Table
+----------+--------------+------------+ | EmpID | EmpName | ManagerID | +----------+--------------+------------+ | 1 | Alice | NULL | | 2 | Bob | 1 | | 3 | Charlie | 1 | +----------+--------------+------------+
Integrity Constraints
- Entity Integrity: Ensures the primary key is unique and not null.
- Referential Integrity: Ensures foreign key values match primary key values in the referenced table.
Why Keys are Important
- Uniqueness: Prevents duplicate rows.
- Relationships: Connects tables meaningfully (e.g., foreign keys).
- Integrity: Ensures consistent and accurate data.
Conclusion
Tables and keys are fundamental to relational databases. Proper use of keys ensures data consistency, reduces redundancy, and establishes meaningful relationships, making them critical for efficient database design.
