Attribute types: simple/composite, single/multivalued, stored/derived, NULL-able, key attribute

0 · Recall Warm-up

Answer from memory first. Answers are in the Answer Vault at the bottom.

1. What is the difference between an entity type and an entity set? from Lesson 1.1.1
2. In Chen notation, how is an attribute drawn? from Lesson 1.1.1
3. What is the domain of an attribute? from Lesson 1.1.1

1 · The Hook

A student's name has a first part and a last part. A student can have several phone numbers. A student's age can be worked out from the date of birth, so maybe it should not be stored at all. And something must tell students apart: two students may share a name, but never a roll number. Each of these is a different kind of attribute, drawn differently and handled differently when we build tables.

2 · Predict First

A student has a date of birth and an age. If both are stored, what goes wrong after a year? Which of the two should be stored?

Make a guess in your head. The answer opens Section 3.

3 · Core Concept

Predict First — the answer

After a year the stored age is wrong unless someone updates every student's age. Store the date of birth (a stored attribute) and compute age when needed (a derived attribute).

Simple vs composite

• A simple (atomic) attribute cannot be split into meaningful parts: roll, cgpa.
• A composite attribute is made of smaller parts that are themselves attributes: name = (first_name, last_name); address = (street, city, pin). The parts are drawn as ellipses hanging from the composite's ellipse. Use a composite when users sometimes need the whole and sometimes the parts.

Single-valued vs multivalued

• A single-valued attribute has one value per entity: roll, dob.
• A multivalued attribute can have a set of values per entity: phone = {98250 11111, 98980 22222}; a course's textbooks. Drawn as a double ellipse.

Stored vs derived

• A stored attribute is kept in the database: dob.
• A derived attribute is computed from other data: age (from dob and today's date), number_of_courses (by counting enrolments). Drawn as a dashed ellipse.

NULL

An entity may have no value for an attribute. The special value NULL is used, with two main meanings:

• not applicable: a day scholar has no hostel_room;
• unknown: the value exists but is not known (a missing phone number). Unknown can be "exists but not known" or "not known whether it exists".

An attribute that may be NULL is NULL-able. Lesson 2.1.2 covers how NULL behaves in tables.

Complex attributes

Composite and multivalued can nest: a student's addresses might be a multivalued composite {(street, city, pin), …}. Written with braces for multivalued and parentheses for composite: {Address(street, city, pin)}.

Key attribute

A key attribute has a value that is different for every entity in the entity set, at all times. It is how entities are told apart: roll for STUDENT, cid for COURSE. Drawn underlined.

• A key can be composite: (building, room_no) identifies a hostel room when room numbers repeat across buildings. The whole combination is unique; no part alone is.
• An entity type can have more than one key: a STUDENT may have both roll and aadhaar_no.
• The key is a rule about every possible entity set, not just the current data: if names happen to be unique today, name is still not a key, because two students named Asha could join tomorrow.

4 · Visual

Key (underlined), composite (name with parts), multivalued (double), derived (dashed), NULL-able (hostel_room for day scholars).

5 · Worked Examples

Example 1 Easy

Classify: address(street, city, pin); email_ids (a student may have several); years_in_college (computed from admission year).

Solution

address: composite. email_ids: multivalued. years_in_college: derived.

Example 2 Medium

In the current data, every student has a different name. A student says: "Then name is a key." Is she right?

Solution

No. A key must be unique in every possible state of the entity set, not just today's. Two students with the same name could join later. Roll number is a key because the college guarantees it is never repeated.

Example 3 GATE

Hostel rooms are numbered 101, 102, … in every building. Which is a key of ROOM: room_no, building, or (building, room_no)? Is the key simple or composite?

Solution

(building, room_no), a composite key. room_no repeats across buildings and building repeats across rooms; only the combination is unique.

8 · Connect the Dots

Lesson 1.1.1 introduced entities and attributes; this lesson sorted attributes into kinds and named the key, the attribute that tells entities apart. Next: entities do not live alone. A student enrols in a course, a course is offered by a department: these links are relationships.

9 · Self-Test

GATE format. Try every question before opening the vault.

Q1 · MCQ · 1 mark(s) — Attribute kinds

A student may have several email addresses. The attribute email is:

1. (A) multivalued
2. (B) derived
3. (C) composite
4. (D) a key

Q2 · MCQ · 1 mark(s) — Notation

A dashed ellipse in an ER diagram denotes:

1. (A) a multivalued attribute
2. (B) a derived attribute
3. (C) a weak entity
4. (D) a key attribute

Q3 · MSQ · 2 mark(s) — Keys

Which are true about key attributes?

1. (A) A key may be made of more than one attribute.
2. (B) An entity type may have more than one key.
3. (C) A key value is different for every entity in the entity set.
4. (D) An attribute that happens to be unique in today's data is always a key.

Q4 · MCQ · 1 mark(s) — NULL

A day scholar has no hostel room. Storing NULL for hostel_room means:

1. (A) the value is an empty string
2. (B) the value is not applicable
3. (C) the room number is 0
4. (D) the student is in room NULL

Q5 · MCQ · 2 mark(s) — Composite vs multivalued

address(street, city, pin) where each student has exactly one address is:

1. (A) derived
2. (B) simple
3. (C) multivalued and composite
4. (D) composite and single-valued

11 · Concept Ledger

Unlocked in this lesson. From here on, these terms may be used.

simple attributecomposite attributesingle-valued attributemultivalued attribute (double ellipse)stored attributederived attribute (dashed ellipse)NULL (not applicable / unknown)NULL-ablecomplex attributekey attribute (underlined)composite key