Skip to content

Lesson 6: Attributes and Keys

Simple, composite, multivalued, derived — and how entities are identified

About this lesson

Course: Database Management Systems

Unit 2: Entity Relationship Modelling · 7 Lecture Hours

Lesson duration: 1 lecture

Reading: Elmasri & Navathe, Ch. 3.3

Learning Outcomes

  1. Classify attributes as simple/composite, single-valued/multivalued, stored/derived, key, complex.
  2. Draw each kind in standard Chen notation.
  3. Explain NULL and its three distinct meanings.
  4. Define key attribute, candidate key, primary key and composite key in the ER context.
  5. Choose a sound primary key and justify the choice.

6.1 What an Attribute Is

Attribute

An attribute is a property that describes an entity. Each attribute has a domain — the set of values it may take (also called its value set).

For a particular entity, each attribute has a value. An entity is therefore, formally, a set of (attribute, value) pairs.

For the entity e1 of type STUDENT:

e1 = { (Rollno, 'X2019'), (Name, 'Ankit Sharma'), (Dateofbirth, '2001-03-14') }

In Chen notation an attribute is drawn as an ellipse (oval) joined by a plain line to the rectangle of its entity type.

        (Rollno)      (Name)      (Dateofbirth)
             \           |            /
              \          |           /
               +---------+----------+
               |      STUDENT       |
               +--------------------+

Figure 2.1 — Attributes attached to an entity type.

6.2 The Classification of Attributes

There are three independent axes of classification, plus two special cases. Independent means an attribute can be, say, composite and multivalued at the same time.

Simple (atomic) vs Composite

Simple / Atomic attribute

An attribute that is not divisible into meaningful smaller parts. Rollno, Age, TotalSeats.

Composite attribute

An attribute that can be divided into smaller sub-parts, each with an independent meaning. The sub-parts may themselves be composite, forming a hierarchy.

Notation: the component ovals hang off the composite oval.

                    (FirstName)  (MiddleName)  (LastName)
                          \           |           /
                           \          |          /
                          ( - - - N a m e - - - )
                                     |
                                     |
                          +----------+---------+
                          |      STUDENT       |
                          +----------+---------+
                                     |
                          ( - - - Address - - - )
                          /     /        \      \
                   (HouseNo) (Street)  (City) (PIN)

Figure 2.2 — Composite attributes Name and Address.

When to make an attribute composite? Only when the application refers to the parts individually. If the college ever sorts students by surname, or posts letters by city, then Name and Address must be composite. If addresses are only ever printed whole, a single simple attribute is correct and simpler.

Rule of thumb

Composite if the parts are referenced separately; simple otherwise. Over-decomposing is as much a design error as under-decomposing.

Single-valued vs Multivalued

Single-valued attribute

Has exactly one value for a given entity. Dateofbirth, Rollno.

Multivalued attribute

May have a set of values for the same entity — zero, one, or many. A student may have several phone numbers; a car may have several colours; a person may hold several degrees.

Notation: a double ellipse. Written {PhoneNo} in shorthand.

A multivalued attribute may have lower and upper bounds, e.g. a car has between one and three colours.

              ((PhoneNo))              ((Hobby))
                    \                     /
                     \                   /
                    +-+-----------------+-+
                    |       STUDENT        |
                    +----------------------+

Critical: a multivalued attribute is not representable as a single column in a relation, because relations must be in 1NF (atomic values). Unit 5 gives the mapping rule: every multivalued attribute becomes a separate table.

Stored vs Derived

Stored attribute

Its value is physically kept in the database. Dateofbirth.

Derived attribute

Its value is computed from other attributes (or from related entities) and need not be stored. Age is derived from Dateofbirth and today's date; NumberOfStudents for a course is derived by counting related admissions.

Notation: a dashed ellipse.

           (Dateofbirth)          , - - - - .
                  |               (   Age     )     <- dashed = derived
                  |                ` - - - - '
                  |                     |
              +---+---------------------+---+
              |         STUDENT             |
              +-----------------------------+
              Age = today's date - Dateofbirth

Why not store Age? Because it would be wrong within a year. Anything computable from stored data and time should generally be derived, to avoid the update anomalies of Unit 1. The exception is deliberate, controlled redundancy for performance — a stored count maintained by a trigger — which is a physical-design decision, not a conceptual one.

Key attributes

Key attribute

An attribute (or a set of attributes) whose value is distinct for each individual entity in the entity set. It provides the uniqueness constraint and is used to identify entities.

Notation: the attribute name is underlined.

An entity type may have more than one key attribute — for example STUDENT might have both Rollno and AadhaarNo, each unique. Each is underlined; each is a candidate key.

A key may be composite: the key is the combination, and no proper subset of it is unique. For a weak entity type there is no key at all, only a partial key — drawn with a dashed underline.

Complex attributes

Complex attribute

An attribute formed by arbitrarily nesting composite (grouped with parentheses) and multivalued (grouped with braces) constructions.

A person may have several addresses, and each address has a composite structure with several phones:

{ AddressPhone( {Phone(AreaCode, PhoneNumber)},
                Address(StreetAddress(Number, Street, AptNo),
                        City, State, Zip) ) }

Braces { } mean multivalued; parentheses ( ) mean composite.

Summary of notation

Attribute kind Chen notation Shorthand
Simple, single-valued Plain ellipse Age
Composite Ellipse with child ellipses Name(First, Last)
Multivalued Double ellipse {PhoneNo}
Derived Dashed ellipse /Age
Key Name underlined Rollno
Partial key (weak entity) Name dash-underlined Name (dashed)
Complex Nested combination {Addr(St, City)}
   THE COMPLETE PICTURE

        (Rollno)        ( - - Name - - )        ((PhoneNo))
       underlined       /      |      \          double
             |    (First)  (Middle)  (Last)         |
             |         \      |      /              |
             +----------+-----+-----+---------------+
                        |   STUDENT   |
             +----------+-----+-------+-------------+
             |                |                     |
       (Dateofbirth)    , - -Age- - .        ( - Address - )
        stored          ` - - - - - '        /    |    \
                          derived        (City)(Street)(PIN)

Figure 2.3 — All attribute kinds on one entity type.

6.3 NULL Values

NULL

A special marker meaning that a value is absent. It is not zero, and it is not the empty string.

NULL has three distinct meanings, and confusing them is a real source of bugs:

Meaning Sub-case Example
Not applicable The attribute does not apply to this entity ApartmentNo for someone living in an independent house
Unknown — missing A value exists but is not recorded The student has a date of birth; we simply have not been told it
Unknown — not known whether it exists We do not even know whether a value exists We do not know whether a student has a home phone at all

Consequences (developed fully in Unit 4):

  • Any arithmetic involving NULL yields NULL: 5 + NULL is NULL.
  • Any comparison with NULL yields UNKNOWN, not TRUE or FALSE: Age > 18 is UNKNOWN when Age is NULL, and so is NULL = NULL.
  • You must therefore test with IS NULL / IS NOT NULL, never with = NULL.
  • Aggregate functions ignore NULLs, except COUNT(*).
  • A primary key can never be NULL (entity integrity, Unit 3).

Design advice

An attribute that is NULL for most entities is a warning sign. It usually means the entity type should be specialised into sub-types (EER, Lesson 4), or that the attribute belongs to a different entity type altogether.

6.4 Keys in the ER Model

The four key terms

Superkey — any set of attributes whose values, taken together, uniquely identify an entity. A superkey may contain redundant attributes.

Candidate key — a minimal superkey: remove any attribute and it stops being unique. An entity type may have several.

Primary key — the candidate key the designer chooses to be the principal means of identification. Exactly one, per entity type. It is the one that becomes PRIMARY KEY on mapping, and it may never be NULL.

Alternate key — a candidate key that was not chosen as the primary key. It becomes a UNIQUE constraint on mapping.

Worked example

STUDENT (Rollno, AadhaarNo, Email, Name, Dateofbirth), where Rollno, AadhaarNo and Email are each unique.

Attribute set Superkey? Candidate key? Why
{Rollno} Yes Yes Unique, and minimal (it is a single attribute).
{AadhaarNo} Yes Yes Unique and minimal.
{Email} Yes Yes Unique and minimal.
{Rollno, Name} Yes No Unique, but not minimal — Rollno alone suffices.
{Name, Dateofbirth} No No Two students may share both.
{Rollno, AadhaarNo} Yes No Not minimal.

So the candidate keys are {Rollno}, {AadhaarNo} and {Email}. Choosing Rollno as primary key makes AadhaarNo and Email alternate keys.

How to choose a primary key

  1. Stable — its value should never need to change. (Email fails: people change email addresses.)
  2. Non-NULL, always available — known at the moment the entity is created. (AadhaarNo may fail: a new student may not have supplied it yet.)
  3. Minimal and short — fewer, smaller attributes make joins and indexes faster.
  4. Simple rather than composite where a good simple candidate exists.
  5. Not privacy-sensitive — using a national identity number as a primary key spreads it into every referencing table.

By these criteria Rollno is the right choice for STUDENT.

Natural vs surrogate keys

A natural key has meaning in the mini-world (Rollno, ISBN). A surrogate key is a system-generated meaningless number (StudentID INT AUTO_INCREMENT).

Surrogates are stable by construction and always available, and never need to change; but they are meaningless to users, and they do not by themselves prevent duplicate real-world entities being inserted twice. Good practice: use a surrogate as primary key and declare the natural key UNIQUE.

6.5 Solved Questions

Solved Question 1

Q. Classify the following attributes of an EMPLOYEE entity type, giving the notation for each: Ssn, Name(First, Last), {PhoneNo}, Age, Dateofbirth, Address(House, City, PIN), {Degree}.

A.

Attribute Classification Notation Justification
Ssn Simple, single-valued, key, stored Ellipse, name underlined Unique per employee, not divisible.
Name(First, Last) Composite, single-valued, stored Ellipse with two child ellipses Divisible into meaningful parts referenced separately (sorting by surname).
{PhoneNo} Simple, multivalued, stored Double ellipse An employee may have several phone numbers.
Age Simple, single-valued, derived Dashed ellipse Computed from Dateofbirth and the current date; storing it would make it wrong within a year.
Dateofbirth Simple, single-valued, stored Plain ellipse The base fact from which Age is derived.
Address(House, City, PIN) Composite, single-valued, stored Ellipse with three child ellipses Parts referenced separately (sorting by city, posting by PIN).
{Degree} Simple, multivalued, stored Double ellipse An employee may hold a bachelor's, a master's and a doctoral degree.

Note that the axes are independent: Name is composite and single-valued; had employees been allowed several names (maiden and married), it would be composite and multivalued, drawn as a double ellipse with children.

Solved Question 2

Q. Distinguish between composite and multivalued attributes. Can an attribute be both? Give an example.

A.

Composite Multivalued
Meaning One value, made of parts Several values of the same kind
Question it answers "What are the pieces of this value?" "How many values are there?"
Notation Ellipse with child ellipses Double ellipse
Example Address(House, City, PIN) {PhoneNo}
Mapping to relations (Unit 5) Becomes several columns in the same table Becomes a separate table

Yes, an attribute can be both. An employee may have several addresses (home, office, permanent), and each address has parts. This is {Address(House, Street, City, PIN)} — drawn as a double ellipse (multivalued) that has child ellipses (composite).

Such an attribute is a complex attribute. On mapping it produces a separate table EMP_ADDRESS(Ssn, House, Street, City, PIN) whose primary key is Ssn together with enough of the address to be unique.

Solved Question 3

Q. Define superkey, candidate key, primary key and alternate key. For BOOK(ISBN, AccNo, Title, Author, Publisher) — where ISBN identifies the title and AccNo is the library's unique accession number for a physical copy — list all candidate keys and choose a primary key with justification.

A. (Definitions as in Section 2.4.)

Careful reading matters here. ISBN identifies a title, but a library holds several physical copies of the same title, each with a different AccNo. So in a table of physical copies, ISBN is not unique.

  • Superkeys: {AccNo}, {AccNo, ISBN}, {AccNo, Title}, {AccNo, ISBN, Title, Author, Publisher}, and any set containing AccNo.
  • Candidate keys: {AccNo} only — it is the sole minimal superkey.
  • Not keys: {ISBN} (many copies share it), {Title}, {Title, Author} (a library may hold two editions).
  • Primary key: AccNo. It is the only candidate key; it is stable, always available at the moment a copy is accessioned, short and simple.
  • Alternate keys: none.

Design note. The fact that ISBN, Title, Author and Publisher repeat across every copy of the same title is redundancy, and signals that the design should be split into TITLE(ISBN, Title, Author, Publisher) and COPY(AccNo, ISBN). In the split design ISBN is the primary key of TITLE and a foreign key in COPY. This is exactly the reasoning that normalisation formalises in Unit 5.

Solved Question 4

Q. What is a NULL value? Explain its three meanings and state three consequences for query writing.

A. A NULL is a marker indicating the absence of a value. It is not zero, not a blank string, and not the value "unknown" — it is the absence of any value.

Three meanings:

  1. Not applicable. The attribute is meaningless for this entity — ApartmentNo for a student living in an independent house.
  2. Unknown, but exists (missing). The student certainly has a date of birth; the office has not recorded it.
  3. Unknown whether it exists. We do not know whether the student has a landline number at all.

Three consequences:

  1. Comparisons yield UNKNOWN. Age > 18 is neither true nor false when Age is NULL, so such rows are excluded by a WHERE clause. Even NULL = NULL is UNKNOWN, which is why WHERE Phone = NULL never matches; you must write WHERE Phone IS NULL.
  2. Arithmetic propagates NULL. Salary + Bonus is NULL whenever Bonus is NULL — so a row with no bonus silently disappears from a total unless handled with COALESCE(Bonus, 0).
  3. Aggregates ignore NULLs. AVG(Marks) averages only the non-NULL marks, so it differs from SUM(Marks)/COUNT(*). Only COUNT(*) counts every row.

Because of these subtleties SQL uses three-valued logic (TRUE, FALSE, UNKNOWN), covered in Unit 4.

Solved Question 5

Q. Why should Age be a derived attribute rather than a stored one? Under what circumstance might you store it anyway?

A. Age should be derived because it is a function of a stored fact and the current date: Age = today - Dateofbirth. Storing it creates two problems.

  1. It becomes wrong by the passage of time alone. Every stored age is incorrect on the day after the student's birthday, with no update having occurred. No integrity constraint can detect this, and no application error caused it.
  2. It is redundant. Storing both Dateofbirth and Age means the same information twice, so the two can disagree — the classic update anomaly of Unit 1.

Storing Dateofbirth alone and computing Age on demand (TIMESTAMPDIFF(YEAR, Dateofbirth, CURDATE())) is always correct and never inconsistent.

When you might store it anyway: as a deliberate, controlled redundancy for performance — for instance in a data warehouse holding a nightly snapshot, where ages are recomputed by the load job and queries scanning millions of rows must not evaluate a date function per row. Two conditions must hold: the staleness must be acceptable to the application, and the derived value must be maintained automatically (by a trigger, a scheduled job, or a materialised view) rather than by application code. This is a physical design decision; the conceptual schema still shows Age as derived.

Solved Question 6

Q. Draw the ER fragment for PATIENT with: a unique registration number; a name split into first and last; several phone numbers; date of birth; age; and several addresses, each having city and PIN.

A.

      (RegNo)      ( - - Name - - )       ((PhoneNo))
     underlined     /           \            double
         |     (First)        (Last)            |
         |         \           /                |
         +----------+---------+-----------------+
                    |    PATIENT    |
         +----------+-------+-------+------------+
         |                  |                    |
   (Dateofbirth)      , - -Age- - .      (( - Address - ))
      stored          ` - - - - - '       /            \
                        dashed        (City)          (PIN)
                       = derived      double ellipse WITH children
                                      = multivalued AND composite

Reading of the notation:

  • RegNo — plain ellipse, name underlined: simple, single-valued key attribute.
  • Name — ellipse with two child ellipses First and Last: composite, single-valued.
  • PhoneNo — double ellipse: simple, multivalued.
  • Dateofbirth — plain ellipse: simple, single-valued, stored.
  • Age — dashed ellipse: derived from Dateofbirth.
  • Address — double ellipse with child ellipses City and PIN: multivalued and composite, i.e. a complex attribute.

Shorthand: PATIENT( RegNo , Name(First, Last), {PhoneNo}, Dateofbirth, /Age, {Address(City, PIN)}).

6.6 Exercises

A. Short answer

  1. Define attribute and domain.
  2. Distinguish simple from composite attributes.
  3. How is a multivalued attribute drawn? Give two examples.
  4. What is a derived attribute? Why is it not stored?
  5. State the three meanings of NULL.
  6. Distinguish candidate key from primary key.
  7. What is an alternate key, and what does it become on mapping?

B. Long answer

  1. Explain all kinds of attributes in the ER model with notation and examples. Show that the classifications are independent by giving an attribute that is both composite and multivalued.
  2. Explain NULL values, their three meanings, and their consequences for comparison, arithmetic and aggregation.
  3. Define superkey, candidate key, primary key and alternate key, and state five criteria for choosing a primary key.
  4. Compare natural and surrogate keys. Recommend a policy and defend it.

C. Application and analytical

  1. For a VEHICLE entity type in an RTO database, classify each attribute and give its notation: registration number, chassis number, owner name (first, middle, last), colours (a vehicle may be two-toned), date of registration, age of the vehicle, engine capacity.
  2. For EMPLOYEE(Ssn, PAN, Email, Name, Dno, Salary) where Ssn, PAN and Email are each unique: a. List five superkeys. b. List all candidate keys. c. Choose a primary key and justify against all five criteria. d. Name the alternate keys and say what they become in SQL.
  3. Draw the ER fragment for a HOTEL with: a unique hotel code; name; address composed of street, city and PIN; several contact numbers; star rating; and the number of rooms currently vacant.
  4. A designer stores TotalMarks alongside Marks1, Marks2 and Marks3. Criticise this. Under what circumstances would it be defensible, and what mechanism would you require?
  5. In a table of employees, ManagerName is NULL for 1 employee and Bonus is NULL for 340 of 400. For each, decide which meaning of NULL applies and whether the design should change.
  6. For each, decide simple or composite, and justify from the application's needs: a. PhoneNumber in a college database that only ever dials the whole number. b. PhoneNumber in a telecom billing system that charges by area code. c. Date in a system that reports by month.

D. Objective — True or False with reason

  1. A composite attribute can be multivalued.
  2. NULL is the same as zero for numeric attributes.
  3. A derived attribute is never shown in the ER diagram.
  4. An entity type can have more than one candidate key but only one primary key.
  5. A multivalued attribute maps to a single column in the relational model.
  6. NULL = NULL evaluates to TRUE.
  7. Every superkey is a candidate key.

Answers to Section D

  1. True. The classifications are independent — {Address(City, PIN)} is both, drawn as a double ellipse with children.
  2. False. Zero is a known value; NULL is the absence of a value. 0 + 5 = 5, but NULL + 5 = NULL.
  3. False. It is shown, as a dashed ellipse. It is not stored, which is a different matter.
  4. True. All candidate keys are underlined in the diagram; the designer designates one as primary.
  5. False. It requires a separate table, because relations must be in 1NF with atomic values.
  6. False. It evaluates to UNKNOWN. Use IS NULL.
  7. False. Only minimal superkeys are candidate keys; {Rollno, Name} is a superkey but not a candidate key.

Lesson Summary

  • Attributes are classified on three independent axes — simple/composite, single-valued/multivalued, stored/derived — plus key attributes and complex (nested) attributes.
  • Notation: plain ellipse; children for composite; double ellipse for multivalued; dashed for derived; underline for key; dashed underline for partial key.
  • NULL means not applicable, unknown-missing, or unknown-whether-exists; it makes comparisons UNKNOWN and propagates through arithmetic.
  • Superkey ⊃ candidate key (minimal) → one chosen as primary key, the rest alternate keys.
  • Choose a primary key that is stable, always available, short, simple and not privacy-sensitive.