Attributes in DBMS

An attribute in DBMS refers to a property or characteristic of an entity. It is essentially a column in a table, which holds data related to an entity. For example, in a Student database, the attributes could be Student_ID, Name, Age, and Grade. Each of these attributes defines a piece of information about the student entity.

There are two major types of Attributes in DBMS

• Key Attributes in DBMS
• Non-Key Attributes in DBMS

Here is the descriptive diagram, which tells all attributes in the DBMS Let’s explain all types

1. Non-Key Attributes in DBMS

These attributes describe properties of an entity but do not uniquely identify it.

i. Simple Attribute

A simple attribute is indivisible and cannot be broken down into smaller components. It represents a single data value.

• It is also called atomic attributes.
• It can be allowed NULL if defined as optional.
• Simple attributes serve as the foundational elements for creating all other types of attributes.

Notation: Represented by an oval connected to the entity. Example: 

• In the example below, each student has attributes such as roll number, class, and name, which cannot be subdivided into more sub-attributes.
• These attributes are referred to as simple attributes.

ii. Composite Attribute

A composite attribute can be broken down into smaller sub-parts, each of which can hold meaningful data.

• Example: Full_Name (First_Name, Last_Name), Address (Street, City, Zip)
• Notation: Represented by an oval connected to sub-ovals showing its components.

iii. Derived Attribute

A derived attribute is one whose value is derived from other attributes in the database.

• Example: Age (from DOB), Total_Salary (Base + Bonus)
• Notation: Represented by a dashed oval in ER diagrams.

iv. Single-Valued Attribute

A single-valued attribute holds only one value for a particular entity.

• Example: Employee_ID, Date_of_Birth
• Notation: Represented by a single oval (just like simple attribute).

v. Multi-Valued Attribute

A multi-valued attribute can hold multiple values for a single entity.

• Example: Phone Numbers, Skills
• Notation: Represented by a double oval.

vi. Complex Attributes

If a composite attribute is further split into its components, then it will be a complex attribute.  In the following diagram, the street is a complex attribute.

vii. NULL Attribute

A NULL attribute represents a value that is unknown or missing.

• Example: Email Address (NULL if missing), Phone Number (NULL if not provided)
• Notation: There is no special ER notation for NULL, but it is usually indicated in the schema definition with NULL allowed.

2. Key Attributes in BDMS

In DBMS (Database Management System), a key attribute is an attribute (or set of attributes) that is used to uniquely identify a tuple in a table. It plays a crucial role in maintaining data integrity, uniqueness, and efficient data retrieval.

There are different types of key attributes in DBMS, Let’s explain each type with the following example Consider the following Employee table with six attributes Where

• ID → UNIQUE , NOT NULL,
• Name → NOT UNIQUE, NOT NULL,
• ID_Card → UNIQUE, NOT NULL
• Salary → NOT UNIQUE,  NOT NULL
• Phone → UNIQUE, Can NULL
• Email → UNIQUE, can NULL

1. Super Key Attribute

A Super Key Attribute is any attribute (or a combination of attributes) in a table that can uniquely identify each record in that table.

• Every Primary Key and Candidate Key is a Super Key, but not every Super Key is a Candidate Key or Primary Key
• A Super Key may contain extra/unnecessary attributes.
• Minimal Super Key (with no extra attributes) is called a Candidate Key.
• There can be many Super Keys in one table.

Example:

In the given example, each Candidate Key generates 2^4= 16  superkeys (since 4 non-unique attributes can be added in any combination).

• From {ID} → 16 superkeys
• From {ID_Card} → 16 superkeys
• From {ID, ID_Card} → 16 superkeys (since it’s also a valid unique set, though not minimal)

Total Superkeys = 48

The list of  Superkeys is given below

When Contain “ID” Atrribute {ID} {ID, Name} {ID, Salary} {ID, Phone} {ID, Email} {ID, Name, Salary} {ID, Name, Phone} {ID, Name, Email} {ID, Salary, Phone} {ID, Salary, Email} {ID, Phone, Email} {ID, Name, Salary, Phone} {ID, Name, Salary, Email} {ID, Name, Phone, Email} {ID, Salary, Phone, Email} {ID, Name, Salary, Phone, Email}

When Contain “ID Card” Atrribute 17. {ID_Card} 18. {ID_Card, Name} 19. {ID_Card, Salary} 20. {ID_Card, Phone} 21. {ID_Card, Email} 22. {ID_Card, Name, Salary} 23. {ID_Card, Name, Phone} 24. {ID_Card, Name, Email} 25. {ID_Card, Salary, Phone} 26. {ID_Card, Salary, Email} 27. {ID_Card, Phone, Email} 28. {ID_Card, Name, Salary, Phone} 29. {ID_Card, Name, Salary, Email} 30. {ID_Card, Name, Phone, Email} 31. {ID_Card, Salary, Phone, Email} 32. {ID_Card, Name, Salary, Phone, Email}

When Contain  “ID” and”ID Card” Atrribute 33. {ID, ID_Card} 34. {ID, ID_Card, Name} 35. {ID, ID_Card, Salary} 36. {ID, ID_Card, Phone} 37. {ID, ID_Card, Email} 38. {ID, ID_Card, Name, Salary} 39. {ID, ID_Card, Name, Phone} 40. {ID, ID_Card, Name, Email} 41. {ID, ID_Card, Salary, Phone} 42. {ID, ID_Card, Salary, Email} 43. {ID, ID_Card, Phone, Email} 44. {ID, ID_Card, Name, Salary, Phone} 45. {ID, ID_Card, Name, Salary, Email} 46. {ID, ID_Card, Name, Phone, Email} 47. {ID, ID_Card, Salary, Phone, Email} 48. {ID, ID_Card, Name, Salary, Phone, Email}

2. Candidate Key Attribute

A Candidate Key is a minimal set of attributes that can uniquely identify each tuple (row) in a relation. Candidate key is always unique and can never be null.

•  It is called a candidate because it is a candidate for the Primary Key.
• Candidate Keys are derived from Super Keys (they are minimal Super Keys).
•  Out of all candidate keys, one is chosen as the Primary Key, while the rest are called Alternate Keys.

Example:

In the given example, the Candidate Keys are 2

• {ID}
• {ID_Card}

3. Primary Key Attribute

A Primary Key Attribute in DBMS is an attribute (or a set of attributes) that uniquely identifies each record in a table and cannot contain NULL values.

Key Points

• Chosen from the candidate keys
• Only one Primary Key is allowed per table.
• It can consist of a single attribute or a combination of attributes (Composite Key).
• Automatically creates an index for fast searching.

Problem: When you have more than one Candidate Key, which will be the Primary Key? Suppose you have two Candidate Keys: ID ID_Card DBMS does not automatically decide between multiple candidate keys. You, as the designer, must choose one as the Primary Key. How to choose? Usually, the best Primary Key is: Stable (won’t change over time) Compact (shorter and simple) Always present (NOT NULL) Used often in queries and relationships Between ID and ID Card: ID → Usually system-generated (auto-increment, surrogate key). Best for PK. ID_Card → Real-world identifier (like CNIC, SSN, Passport No). Might change if a new ID_Card is issued or errors exist. Most practical Primary Key = ID ID Card can be kept as a Unique Key / Alternate Key.

Example:

In the given example, the Primary Key is only 1. 

• {ID}

4. Alternate Key Attribute

An Alternate Key Attribute in a DBMS is any Candidate Key that is not chosen as the Primary Key. It still has the properties of being unique and not null, but it serves as an alternative way to uniquely identify tuples (rows) in a relation.

Key Points:

• It is exactly similar to a Primary Key, but not chosen as the Primary Key.
• Derived from Candidate Keys.
• Unique and Not Null like a Primary Key.
• Used as an alternative identifier for records.
• There can be multiple Alternate Keys in one table.

Example: In the given example, the Alternate Key is only 1, which is 

• {ID_Card}

5. Unique Key

A Unique Key Attribute in DBMS is an attribute (or set of attributes) that ensures all values in the column(s) are unique across rows, but NULL values are allowed (depending on the DBMS). Example: In the given example, the Unique keys are 

• ID
• ID_Card
• Email
• Phone

Some Additional Keys Attributes in DBMS

1. Surrogate Key

A surrogate key is an artificial or system-generated key used when no natural primary key exists or when composite keys are inconvenient.

• Usually, auto-increment numbers are assigned by the system.

Example: Employee Table

Key Explanation

• Emp_ID = Surrogate Key (auto-generated, has no business meaning).
• Full_Name, Email, Phone = Natural attributes, but may change or allow duplicates.
• Instead of using Email or Phone as a primary key (since they may change), we rely on Emp_ID.

2. Composite Key Attribute

A Composite Key Attribute (or simply Composite Key) is a key that consists of two or more attributes combined together to uniquely identify each tuple (row) in a relation. Composite Attributes cannot always uniquely identify a tupleIt can still give duplicate values in rare cases. Therefore, we need to find the optimal set of attributes that can uniquely identify the rows in a table.

Key Points

• Made up of two or more attributes.
• Each attribute alone is not sufficient for uniqueness.
• Used when no single attribute can uniquely identify records.
• Can act as a Candidate Key or Primary Key if chosen.
• Common in many-to-many relationship tables (junction tables).

Example: Enrollment Table

 Key Explanation:

• Student_ID alone  → not unique (same student can enroll in multiple courses).
• Course_ID alone  → not unique (same course can have many students).
• Student_ID + Course_ID  → together uniquely identify a row.

So, {Student_ID, Course_ID} = Composite Key.

3. Foreign Key Attribute

A Foreign Key Attribute is an attribute (or set of attributes) in one table that refers to the Primary Key of another table, establishing a relationship between the two tables.

Explanation: In Orders Table:

• Customer_ID → Foreign Key (references Customers.Customer_ID).
• Product_ID → Foreign Key (references Products.Product_ID).

So the Orders Table has two foreign keys.

Important Key Points:

• A Reference Key is another name for a Foreign Key.
• Without a Primary Key (or Unique Key) in the parent table, a foreign/reference key cannot exist.
• Since a table can be related to multiple other tables, it may contain multiple foreign keys.

Difference Between Primary Key and Foreign Key Aspect Primary Key Foreign Key Definition A column (or set of columns) that uniquely identifies each record within its own table. A column (or set of columns) in one table that refers to the Primary Key of another table. Purpose Ensures that every row in the table is unique and identifiable. Creates a relationship between two tables and maintains referential integrity. Uniqueness Must always be unique and NOT NULL for every record. Can contain duplicate values and may allow NULL (if the relationship is optional).

4. Partial Key 

• It is also called the Discriminator Key in Weak Entities
• A partial key is an attribute that uniquely identifies a tuple only within a particular context (usually inside a weak entity).
• It cannot uniquely identify a record by itself, but when combined with a foreign key, it can.

Example

Course Table (Strong Entity)

Student Table (Weak Entity)

Key Explanation

As we know, each course contains unique roll numbers, but different courses may contain similar roll numbers.

• Roll_No alone  → not unique (e.g., Roll_No 01 appears in both courses).
• Course_ID alone → not unique (a course has many students).
• Course_ID + Roll_No → together uniquely identify a student.

Here:

• Roll_No = Partial Key.
• Course_ID = Foreign Key (from Course table).
• Course_ID + Roll_No = Composite Primary Key of Student table.

5. Secondary Key (Not for Uniqueness)

• A secondary key is an attribute (or set of attributes) used only for searching or retrieval, not for uniquely identifying tuples.

• It may not be unique.

Example

Student Table

Key Explanation

• Student_ID = Primary Key (unique for each student).

• Name = Secondary Key (not unique, since “Ali” appears more than once).

• Even though Name is not unique, we can use it for searching:

  • Example query → Find all students where Name = ‘Ali’ → returns Student_ID 101 and 103.

Importance of Key Attributes in DBMS

• Ensures Uniqueness: Prevents duplicate records in a table.
• Maintains Data Integrity: Every entity can be uniquely identified.
• Helps in Relationships: Used in foreign keys to establish links between tables.
• Supports Normalization: Removes redundancy and improves database design.
• Efficient Searching: Queries become faster with proper keys.

Attributes Comparison in DBMS

Let’s start the comparison of attributes in DBMS by choosing a student entity

1. Simple Vs. Composite Attributes 

Simple Attribute	Composite Attribute
The attribute that cannot be further split into its components is a simple attribute.	An attribute that can split into components is known as a composite attribute.
Example: The roll number of a student, the ID number of an employee, etc.	Example: The name of the student can be split into first, middle, and last names.

2. Single-Valued Vs. Multi-Valued Attributes

Single-Valued Attribute	Multi-Valued Attribute
The attribute which has a single value for each entity instance is known as a single-valued attribute. There is no alternative to this value.	The attribute which takes up more than one value for each entity instance is known as a multi-valued attribute.
Example: The Registration No, RollNo, DOB, and Gender of a student will always be unique.	Example: The phone number of any person can be a mobile or Landline number.

  Note: Multi-valued attributes are represented through double circles.

3. Stored Vs. Derived Attributes

Stored Attribute	Derived Attribute
Those attributes cannot be derived from other attributes.	An attribute that is derived from other attributes is a derived attribute.
For example, DOB cannot be derived in the same way as CNIC, etc.	Example: The age of the student can be derived from the DOB.

4. Key Vs. Non-Key Attributes

Key Attribute	Non-Key Attribute
It represents the primary key that is used to uniquely identify the instance (entire row) of an entity.	Those attributes that cannot be used as primary keys are known as non-key attributes.
Example: Roll_No, Student_ID or Student_RegNo etc.	Example: Student name, address, etc.

Note: This attribute is represented with a circle containing an underline as well.

5. Required Vs. Optional Attributes

 It is mostly used in filling out application forms. Sometimes, a Phone number is optional, and a Name is mandatory.

Required Attribute	Optional Attribute
Those attributes that are mandatory are called required attributes	Those attributes that are not mandatory are called Optional attributes

Example: Sometimes, the phone number is optional, and the name is mandatory while filling out forms.

Conclusion

In conclusion, attributes are the building blocks of a DBMS. They define the properties of entities and play a significant role in structuring and organizing data. By understanding the types of attributes and their applications, you can better design databases that are efficient, scalable, and maintainable. Whether you’re working with simple or complex data, attributes ensure that your database is organized, consistent, and capable of handling various data management needs.