Composite Attribute in DBMS

In DBMS (Database Management System), keys are fundamental to maintaining the integrity and efficiency of the data. Among various types of keys, composite keys play an important role mainly when a single attribute is not sufficient to answer or identify a record. In this article, we will discuss in detail about Composite key attributes in DBMS.

What are composite Key attributes?

Composite key attributes are combinations of two or more attributes that uniquely identify a record in a table together. Unlike simple key attributes which use a single key to identify a record. a composite key uses multiple attributes. These are useful in cases where no single attribute can uniquely identify a record.

Example: The different examples of composite key attributes are

• Student address will contain house number, street name, and pin code in it.
• Student name will contain first_name, middle_name, and last_name in it.
• Employee contact information can contain his email id, mobile number and other contact information it.

In this figure Composite attribute is address and name and its component attributes are state, street, zip and first name, middle name, last name respectively.

SQL Code Example

Example 1: Creating Employee table with Composite ContactInfo Attribute

Here we will create a employee table in which out contact info is the composite key which contains email id and contact number in it.

CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(50),
    -- Composite Attribute Contactnfo
    ContactInfo VARCHAR(100)
);

INSERT INTO Employee (EmployeeID, Name, ContactInfo)
VALUES 
    (1, 'John Doe', '[email protected], 123-456-7890'),
    (2, 'Jane Smith', '[email protected], 456-789-0123'),
    (3, 'Alice Johnson', '[email protected], 789-012-3456');

Employee Table

Query to select Email and Phone number of employee separately

 SELECT 
    SUBSTR(ContactInfo, 1, INSTR(ContactInfo, ',') - 1) AS Email,
    TRIM(SUBSTR(ContactInfo, INSTR(ContactInfo, ',') + 1)) AS PhoneNumber
FROM 
    Employee;

Output

Example 2: Creating Student Table where Address is composite attribute

Here we will create a Student table where student address is the composite key and it contains Street, City and Pin number in it.

CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Address VARCHAR(100) -- Composite Attribute
);

INSERT INTO Student (StudentID, Name, Address)
VALUES 
    (1, 'John Doe', '123 Main Street, Apt 2, 10001'),
    (2, 'Jane Smith', '456 Elm Street, Suite 3B, 20002'),
    (3, 'Alice Johnson', '789 Oak Avenue, 30003');

Student Table

Query to select Street City and Pin from Student's address Separately

SELECT 
    Name,
    substr(Address, 1, instr(Address, ',') - 1) AS Street,
    substr(Address, instr(Address, ',') + 2, instr(substr(Address, instr(Address, ',') + 2), ',') - 1) AS City,
    TRIM(substr(Address, LENGTH(Address) - instr(REVERSE(Address), ',') + 2)) AS PIN
FROM 
    Student;

Output

Conclusion

Composite attributes allows us to store and manage complex data in efficient manner. These are essential in cases where single attribute is not sufficient to uniquely identify the records. By combining various attributes, composite key attribute ensures the uniqueness and integrity of data in a table. Understanding and implementing composite key can enhance the efficiency and reliability of database systems.