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', '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

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.