Attribute Types in ER Diagrams
Fast recall
Quick revision
Core answer
Understanding of different attribute types in ER modeling and their impact on database schema design decisions
Storing multi-valued attributes as comma-separated strings in one column instead of normalizing them into a separate related table
Explain the translation of each attribute type to SQL—simple attribute to column, derived attribute to view or computed column, multi-valued attribute to 1:N relationship
Interview-ready guide
Interview answer
A simple attribute is treated as one value in the model, so a username commonly becomes one column.
A derived attribute is calculated from other facts; age can be calculated from birth_date in a query or view. It may also be materialized when the design has a reliable refresh rule. A multivalued attribute has several values for one entity, such as an employee's phone numbers.
When each value needs independent lookup, uniqueness, or relationships, map it to employee_phone(employee_id, phone_number) with one row per phone. A comma-separated value makes normal per-phone constraints and equality searches difficult. JSON or array storage is another possible database-specific design, not an automatic error, but its query and constraint boundaries must be deliberate.
ER notation records the meaning; the physical mapping follows the required operations and guarantees.
Attribute Types in ER Diagrams
Direct Answer
A simple attribute is not divided further in the model. A derived attribute is calculated from other facts and is commonly drawn with a dashed oval. A multivalued attribute can have several values for one entity and is commonly drawn with a double oval. Its SQL mapping depends on whether those values need independent keys, constraints, and queries.
Code Example
-- Simple attributes: direct columns
CREATE TABLE employee (
employee_id BIGSERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL, -- Simple attribute
birth_date DATE NOT NULL, -- Simple attribute
salary DECIMAL(10,2) -- Simple attribute
);
-- Derived attribute: not stored, computed in query or view
CREATE VIEW employee_summary AS
SELECT
employee_id,
name,
birth_date,
EXTRACT(YEAR FROM AGE(birth_date)) AS age, -- Derived: age from birth_date
salary
FROM employee;
-- Multi-valued attribute: separate table with 1:N relationship
CREATE TABLE employee_skill ( -- Normalized multi-valued attribute
employee_id BIGINT NOT NULL,
skill_name VARCHAR(50) NOT NULL,
proficiency_level VARCHAR(20),
PRIMARY KEY (employee_id, skill_name),
CONSTRAINT fk_employee FOREIGN KEY (employee_id)
REFERENCES employee(employee_id) ON DELETE CASCADE
);
-- Query: Find all skills for an employee
SELECT skill_name, proficiency_level
FROM employee_skill
WHERE employee_id = 42;
Common Mistakes & Pitfalls
- Storing multi-valued data as CSV: A delimited string prevents normal per-value foreign keys and makes exact matching harder.
- Materializing without a refresh rule: A stored age, total, or count can drift when its inputs change.
- Assuming every derivation fits a generated column: Database restrictions differ, and values based on current time or other rows often require a query, view, trigger, or application-maintained projection.
Check your understanding
Answer these without looking back at the article.
- How would you store a person's multiple email addresses in a normalized schema?
- What is a composite attribute and how is it different from a multi-valued attribute?
Looking for a structured roadmap?
Every module on this page is also part of the full Java backend interview track.