Chapter 08 of 41

Types of Attributes

In the ER Model chapter, we learned that attributes are the properties that describe an entity, like a student's name or roll number. But not all attributes behave the same way.

A student has one date of birth but may have several phone numbers. A name can be split into first and last name, but a roll number cannot be split into anything meaningful. Age does not need to be stored at all, because it can be calculated from the date of birth.

These differences matter a lot. Each kind of attribute is drawn differently in an ER diagram and is handled differently when the design becomes tables. In this chapter, we will go through every type of attribute you need for exams and interviews, with examples and the reasoning behind each one.


What Is an Attribute? (Quick Recap)

An attribute is a property or characteristic of an entity type or a relationship type. Each entity has a value for each of its attributes.

Every attribute has a domain (also called a value set): the set of values it is allowed to take.

Attribute

Possible Domain

semester

Integers from 1 to 8

cgpa

Decimal numbers from 0.00 to 10.00

gender

A small fixed set of codes

email

Strings in a valid email format

The domain is what later becomes a column's data type plus its CHECK constraints.

Here is a map of the attribute types we will cover. The first five come in pairs, and every attribute belongs to one side of each pair at the same time:

Attribute types
├── Simple        vs  Composite        (can it be divided into parts?)
├── Single-valued vs  Multivalued      (how many values per entity?)
├── Stored        vs  Derived          (stored, or computed from others?)
├── Key           vs  Non-key          (does it identify the entity?)
├── Required      vs  Optional (NULL)  (must every entity have a value?)
└── Complex attribute                  (composite + multivalued nested)

For example, phone of a student is usually simple, multivalued, stored, non-key and optional, all at once.


1. Simple vs Composite Attributes

Simple (Atomic) Attribute

A simple attribute cannot be divided into smaller meaningful parts.

Examples: roll_no, age, salary, gender.

You could split a roll number like 21CS045 into "21", "CS" and "045", but if the application never needs those parts separately, it is treated as simple.

Composite Attribute

A composite attribute is made up of smaller sub-parts, each of which is itself an attribute.

Classic examples:

name     → first_name, middle_name, last_name

address  → street, city, state, pin_code
              └── street → house_no, street_name   (composite inside composite)

In an ER diagram, a composite attribute is an ellipse connected to the ellipses of its components:

                    [ STUDENT ]
                         |
                     ( name )
                  /      |       \
       ( first_name ) ( middle_name ) ( last_name )

Why does this matter?

Whether to treat name as simple or composite depends on how the data is used:

  • If the college prints "Dear Priya" on letters, or sorts students by surname, you need the parts separately, so make it composite.

  • If you only ever display the full name, a simple name is fine.

When converting to tables, the relational model only stores atomic values in a column, so a composite attribute is usually flattened into its leaf components (columns first_name, last_name, ...). The composite itself (name) does not become a column. We will apply this in Converting ER Diagrams to Tables.


2. Single-Valued vs Multivalued Attributes

Single-Valued Attribute

A single-valued attribute has exactly one value for a given entity (at a given time).

Examples: a person's date_of_birth, a student's roll_no, an employee's salary.

Multivalued Attribute

A multivalued attribute can have a set of values for the same entity.

Examples:

  • A student's phone_numbers (personal, parent's, hostel)

  • An employee's skills (Java, SQL, Docker)

  • A person's degrees (B.Tech, M.Tech)

  • A car's colors (for two-tone cars)

It is drawn with a double ellipse:

   [ EMPLOYEE ]────(( skills ))

A multivalued attribute may also have lower and upper bounds, e.g., "a student may store between 1 and 3 phone numbers".

Why does this matter?

A table cell is supposed to hold one value. If you squeeze several values into one column like this:

+--------+-------+-----------------------+
| emp_id | name  | skills                |
+--------+-------+-----------------------+
| 2      | Priya | Java, SQL, Docker     |
| 3      | Rohan | SQL                   |
+--------+-------+-----------------------+

then simple questions become painful: "Who knows SQL?" now needs string searching, "SQL" might match "MySQL" or "NoSQL" by accident, and removing one skill means rewriting the whole string. This violates First Normal Form (see 1NF, 2NF & 3NF).

The standard fix, which you will see in Converting ER Diagrams to Tables, is to put a multivalued attribute in its own table, one row per value:

employee_skills
+--------+--------+
| emp_id | skill  |
+--------+--------+
| 2      | Java   |
| 2      | SQL    |
| 2      | Docker |
| 3      | SQL    |
+--------+--------+

So spotting multivalued attributes in the ER stage saves you from a design bug later.


3. Stored vs Derived Attributes

Stored Attribute

A stored attribute is one whose value is actually saved in the database.

Example: date_of_birth, hire_date, price.

Derived Attribute

A derived attribute is one whose value can be calculated from other stored attributes or related entities, so it does not need to be stored.

Examples:

Derived Attribute

Derived From

age

date_of_birth and today's date

years_of_service

hire_date and today's date

number_of_employees of a department

counting the employees related to that department

order_total

sum of (quantity × price) of the order's items

cgpa

grades and credits of all courses taken

Derived attributes are drawn with a dashed ellipse:

   [ STUDENT ]──( dob )         stored
        └────────( - age - )     derived (dashed)

Example: Deriving a Value with SQL

Our sample company database stores each employee's hire_date. We do not store years of service; we compute it when needed. Here it is computed as of 1 January 2026 (using a fixed date so the result does not change):

SELECT emp_id, name, hire_date,
       TIMESTAMPDIFF(YEAR, hire_date, '2026-01-01') AS years_of_service
FROM employees
WHERE emp_id IN (1, 4, 8);

Output:

+--------+--------------+------------+------------------+
| emp_id | name         | hire_date  | years_of_service |
+--------+--------------+------------+------------------+
|      1 | Aarav Sharma | 2018-04-12 |                7 |
|      4 | Sneha Kapoor | 2017-11-20 |                8 |
|      8 | Meera Nair   | 2023-02-14 |                2 |
+--------+--------------+------------+------------------+

In a real query you would use CURDATE() instead of the fixed date.

Why not just store derived values?

Because they go stale. If you store age = 20 today, it is wrong next year, unless some job updates every row on every birthday. If you store number_of_employees = 3 for a department, every insert, delete or transfer of an employee must also remember to update that number. Miss it once, and the database contradicts itself.

Computing the value when needed guarantees it is always correct.

That said, real systems sometimes store a derived value on purpose, for speed. An e-commerce site may store order_total because recomputing it from thousands of order items on every page view is slow, and because the total at the time of purchase must not change if prices change later. This is a conscious trade-off (called denormalization), not a default. MySQL also offers generated columns (GENERATED ALWAYS AS (expression)), which let the DBMS itself keep a deterministic derived value correct.


4. Key vs Non-Key Attributes

A key attribute is an attribute (or set of attributes) whose value is unique for every entity in the entity set, so it can be used to identify an entity.

Examples: roll_no for STUDENT, emp_id for EMPLOYEE, isbn for BOOK.

In Chen notation, the key attribute's name is underlined. (In the text diagrams on this site we write it as _roll_no_.)

( _roll_no_ )──[ STUDENT ]──( name )
   key                         non-key

Some points to note:

  • A key can be composite: e.g., for a CLASSROOM, (building, room_no) together are unique, but neither alone is.

  • An entity type can have more than one key, e.g., STUDENT may have both roll_no and email unique.

  • A weak entity has no key of its own, only a partial key (drawn with a dashed underline). See Weak Entities.

All other attributes are non-key attributes. The detailed theory of keys (super key, candidate key, primary key, alternate key, foreign key) comes in Keys in DBMS.


5. Required vs Optional Attributes (NULL Values)

Some attributes must have a value for every entity (a student must have a roll number). Others may be missing.

An optional attribute is one that may have no value for some entities. Such a missing value is represented by NULL.

NULL can mean different things, and it helps to know them:

Meaning of NULL

Example

Not applicable

apartment_no for someone who lives in an independent house

Unknown (exists but not known)

A person's height that we haven't measured yet

Unknown whether it exists

home_phone: maybe they have none, maybe we just don't know it

In our sample database, Meera Nair (emp 8) has a NULL email, and Ishita Gupta (emp 10) has a NULL dept_id and city. NULL is not zero and not an empty string; it means "no value". It also behaves specially in comparisons (NULL = NULL is not true), which the SQL tutorial covers in detail.

When the design becomes tables, required attributes become NOT NULL columns.


6. Complex Attributes

Composite and multivalued attributes can be nested inside each other. Such an attribute is called a complex attribute.

The Elmasri & Navathe textbook writes composite parts inside ( ) and multivalued parts inside { }. For example, a person who can have several addresses, each with several phone numbers:

{ address_phone ( { phone (area_code, number) },
                  address (street (house_no, street_name), city, state, pin) ) }

Read it from outside in:

  • address_phone is multivalued { }: a person can have many of them.

  • Each one is composite ( ): a set of phones plus one address.

  • phone is multivalued, and each phone is composite (area code + number).

  • address is composite, and its street is composite too.

Complex attributes are rare in exam diagrams, but they show up in theory questions. In practice, a structure this rich is often a sign that some part (like ADDRESS) deserves to be its own entity.


7. Attributes of Relationships (Descriptive Attributes)

Attributes are not only for entities. A relationship can have attributes too, sometimes called descriptive attributes.

[ STUDENT ]──M──< ENROLLS >──N──[ COURSE ]
                     |
                 ( grade )
                 ( enrolled_on )

grade is not a property of the student alone (they have many grades), nor of the course alone (it has many students). It describes one particular student–course pair. Relationships and their attributes are discussed further in Relationships, Cardinality & Participation.


Putting It All Together

Let's classify the attributes of an EMPLOYEE entity in one go:

Attribute

Simple / Composite

Single / Multi

Stored / Derived

Key?

Required?

emp_id

Simple

Single

Stored

Yes

Yes

name (first, last)

Composite

Single

Stored

No

Yes

email

Simple

Single

Stored

Unique, could be a key

Optional

phone_numbers

Simple

Multi

Stored

No

Optional

hire_date

Simple

Single

Stored

No

Yes

years_of_service

Simple

Single

Derived

No

(computed)

address (street, city, pin)

Composite

Single

Stored

No

Optional

And the ER diagram fragment:

                 ( first_name )  ( last_name )
                          \        /
       ( _emp_id_ )      ( name )      (( phone_numbers ))
               \             |             /
                \            |            /
                 +------[ EMPLOYEE ]-----+
                /            |            \
               /             |             \
     ( hire_date )   ( - years_of_service - )   ( address )
                                              /     |     \
                                       ( street ) ( city ) ( pin )

Common Mistakes

  • Treating "multiple values" as composite. Composite = one value made of parts (one address with city and pin). Multivalued = several values of the same kind (three phone numbers).

  • Storing derived values by default. Store dob, compute age. Only store a derived value deliberately, with a plan to keep it correct.

  • Comma-separated lists in one column for multivalued attributes. Use a separate table.

  • Making a composite attribute itself a column along with its parts. Only the leaf parts become columns.

  • Treating NULL as 0 or ''. NULL means "no value", and comparisons with it behave differently.

  • Assuming an attribute's type is fixed. Whether name is simple or composite, or city is an attribute or an entity, depends on the requirements.


Interview Questions

1. What is the difference between a composite and a multivalued attribute?

A composite attribute has one value made of several parts (name = first + last). A multivalued attribute can have several values of the same kind for one entity (several phone numbers). An attribute can be both, e.g., several addresses, each with parts.

2. What is a derived attribute? Give an example.

An attribute whose value can be computed from other data, so it need not be stored. Example: age derived from date of birth, or number of employees of a department derived by counting related employees. It is drawn as a dashed ellipse.

3. How is a multivalued attribute stored in a relational database?

In a separate table containing the owner's key and the value, one row per value, e.g., employee_skills(emp_id, skill) with primary key (emp_id, skill).

4. What symbols represent key, multivalued and derived attributes in Chen notation?

Key: ellipse with the name underlined. Multivalued: double ellipse. Derived: dashed ellipse. Partial key of a weak entity: dashed underline.

5. What is a complex attribute?

An attribute formed by nesting composite and multivalued attributes, e.g., a multivalued set of addresses where each address is composite.

6. Should "age" be stored in a database?

Generally no. Store date of birth and compute age when needed, because a stored age becomes wrong as time passes and would need constant updates.

7. What are the different meanings of a NULL value?

Value not applicable, value exists but is unknown, or it is unknown whether a value exists at all.


Summary

  • Simple attributes cannot be divided; composite attributes have meaningful sub-parts (name, address).

  • Single-valued attributes have one value per entity; multivalued attributes can have many (double ellipse). Multivalued attributes later become separate tables.

  • Stored attributes are saved; derived attributes are computed (dashed ellipse). Prefer storing the base data (dob) and deriving the rest (age).

  • Key attributes uniquely identify an entity (underlined); weak entities only have a partial key.

  • Optional attributes may be NULL; NULL means "no value", not zero.

  • Complex attributes nest composite and multivalued parts. Relationships can have descriptive attributes too.

Next, in Relationships, Cardinality & Participation, we will see how entities connect to each other, how many of them can connect, and whether the connection is compulsory.