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.

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.

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', 'john@example.com, 123-456-7890'),
    (2, 'Jane Smith', 'jane@example.com, 456-789-0123'),
    (3, 'Alice Johnson', 'alice@example.com, 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

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.

What are Composite key attributes?

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

Why Composite Key attributes are used in DBMS?

These are useful when a single attribute is not sufficient to uniquely identify a record in a table. They help to maintain the uniqueness and integrity of data by combining multiple attributes to form a single key attribute.

What are Advantages of Composite Key attributes?

Composite key attributes enhances efficiency and reliability of database by ensuring that each record is uniquely identified using multiple attributes. These are mainly useful in cases where a single attribute cannot uniquely identify a record in a table. They help to maintain the data integrity and support complex relationships.

Are there any Drawbacks of using composite key attributes?

The major drawback of using composite key attributes is increased complexity in querying and indexing as it involves multiple attributes. As we have seen in above example to fetch student address separately, we need to write very complex query. Moreover, maintaining and updating composite key attributes can be more complex as compared to single key attributes.