Types of Attributes in the ER Model

Concept

An attribute is a property of an entity type. Every attribute has a domain — the set of values it may take. The classification below is examined directly and, more usefully, decides how each attribute becomes a column later.

Simple and composite

          ( address )
             /  |  
            /   |   
    (street) (city) (pincode)

  Store the LEAVES as columns. Keeping the whole thing in one
  column makes "everyone in Chennai" impossible to answer
  reliably.

Single valued and multivalued

  WRONG                        RIGHT

  students                     students
   roll_no                      roll_no
   phone1                      student_phones
   phone2                       roll_no
   phone3                       phone
                                primary key (roll_no, phone)

  The wrong version breaks the moment somebody has a fourth
  number, and wastes space on everyone who has one. This is
  also exactly what 1NF forbids, so the two topics agree.

Stored and derived

• Stored — held in the database. Date of birth.
• Derived — computed when needed from other data. Age, computed from date of birth and today.

A derived attribute is drawn with a dashed ellipse. Store the stored one and derive the other; storing age means it is wrong the next morning.

Complex attributes

A complex attribute is composite and multivalued at the same time — for example, several addresses, each having street, city and pincode. Notation nests the two symbols. In tables this always becomes a separate table.

Key attributes and null values

• A key attribute uniquely identifies each entity of the type, and is drawn underlined.
• A null value means not applicable, unknown, or not yet supplied. The three meanings are different and are treated fully in the relational phase, but the design decision belongs here: an attribute that is null for most entities is often a sign that a separate entity type is hiding.

The complete summary

Example

   STUDENT with every attribute type present

     roll_no      key, underlined
     name         composite -> first_name, last_name
     dob          simple, stored
     age          derived, dashed - never stored
     phone        multivalued, double ellipse
     address      composite -> street, city, pincode

   Resulting tables:

     students        ( roll_no, first_name, last_name, dob,
                       street, city, pincode )
     student_phones  ( roll_no, phone )

   Two tables, because ONE multivalued attribute was present.

Common mistakes

• Storing a multivalued attribute as phone1, phone2, phone3. It breaks on the next value, wastes space, and makes searching all numbers awkward.
• Storing a derived attribute. Age stored today is wrong tomorrow. Store the basis, derive the result.
• Keeping a composite attribute in one column. It defeats searching and sorting by its parts.
• Confusing composite with multivalued. Composite means one value with parts; multivalued means several values.
• Forgetting to underline the key attribute. Marks are given for the notation.

Exam and interview questions

1. List all attribute types with their notation and one example each.
2. Differentiate composite and multivalued attributes.
3. How is a multivalued attribute converted into tables, and why not as repeated columns?
4. Why should a derived attribute not be stored, and when might you store one anyway?
5. What is a complex attribute?

Practice

1. Classify each for an EMPLOYEE: employee id, full name, skills, salary, years of service, home address, dependants.
2. Convert an EMPLOYEE with a multivalued skills attribute into tables and state the primary key of each.
3. Give one case where storing a derived attribute is justified, and name the risk it creates.

Conclusion

Simple or composite, single or multivalued, stored or derived, plus complex and key attributes. Learn the notation for the exam and the conversion rule for real work: multivalued and complex attributes always become their own table.