Local Attributes: Defining… — Specialization in… | OneNoughtOne

0/278

Database Management SystemsSpecialization

Specialization in Enhanced ER Modeling

LevelIntermediate

Duration55 mins

TopicSpecialization

4 / 5

Local Attributes: Defining Subtype-Specific Characteristics

The Essence of Subtype Distinction

If inheritance gives subtypes everything from their supertypes, what makes subtypes different from each other? The answer lies in local attributes —the attributes defined specifically for a subtype that don't exist in the supertype or in sibling subtypes. Local attributes are the raison d'être of specialization. Without them, there would be no structural reason to create subtypes; a simple discriminator attribute would suffice. Local attributes capture the unique characteristics that make each subtype semantically distinct and practically useful.

What You Will Learn

By the end of this page, you will master local attributes in EER specialization: how to identify them, design them effectively, handle their constraints, and understand their implications for schema design and data management. You'll develop judgment about when attributes should be local versus inherited.

Understanding Local Attributes

Definition: A local attribute (also called a specific attribute or subtype attribute) is an attribute that: Is defined on a subtype, not on the supertype Is meaningful only for entities of that subtype Does not apply to sibling subtypes or to the supertype in general Formal Notation: For subtype S with supertype E: local_attrs(S) = attrs(S) - attrs(E) These are the attributes that S has which E does not have Example: EMPLOYEE (supertype) ├── emp_id (PK) ├── name ├── salary └── hire_date MANAGER (subtype) - Local Attributes: ├── department \leftarrow Local: only managers have departments they lead ├── budget \leftarrow Local: only managers have budgets to manage └── direct_reports \leftarrow Local: only managers have people reporting to them ENGINEER (subtype) - Local Attributes: ├── tech_stack[] \leftarrow Local: only engineers have technical expertise ├── github_handle \leftarrow Local: only engineers have code repositories └── certifications[] \leftarrow Local: only engineers have technical certifications Note that these local attributes would be meaningless or inappropriate for other subtypes—a SALESPERSON doesn't have a tech_stack, and an ENGINEER doesn't have direct_reports (in their engineer capacity).

The Local Attribute Litmus Test

Ask:'Does this attribute make sense for ALL subtypes of the supertype?' If YES \to it belongs in the supertype (and will be inherited). If NO \to it's a local attribute of the specific subtype where it makes sense.

Categories of Local Attributes

Local attributes come in various forms, each serving different modeling purposes. Understanding these categories helps in attribute design.

Local Attribute Categories
Category	Description	Example
Role-Specific Attributes	Attributes relevant to the role the subtype plays	Manager.team_size, Doctor.specialty, Teacher.subjects[]
Capability Attributes	Attributes describing what the subtype can do	PremiumMember.download_limit, AdminUser.access_scope, TruckDriver.cdl_class
Measurement Attributes	Type-specific metrics or quantities	SavingsAccount.interest_rate, ElectricVehicle.battery_capacity, ServerNode.cpu_cores
Relationship Attributes	Foreign keys to type-specific related entities	Student.advisor_id, Patient.primary_physician_id, ProjectManager.portfolio_id
Configuration Attributes	Type-specific settings or preferences	BusinessAccount.tax_id, InternationalShipping.customs_code, EnterpriseUser.sso_config
Temporal Attributes	Type-specific dates or timestamps	TenuredProfessor.tenure_date, RetiredEmployee.retirement_date, CompletedOrder.shipped_date
Status Attributes	Type-specific state information	PendingClaim.review_stage, ActiveSubscription.renewal_date, GraduatedStudent.graduation_honors

Composite Local Attributes: Local attributes can be composite, just like any other attribute: BUSINESS_CUSTOMER (subtype of CUSTOMER) └── billing_address (composite local attribute) ├── company_name ├── attention_line ├── street ├── city ├── state ├── postal_code └── country While CUSTOMER might have an address attribute (inherited), BUSINESS_CUSTOMER has a specialized billing_address with business-specific components like company_name and attention_line. Multivalued Local Attributes: Local attributes can also be multivalued: PHYSICIAN (subtype of MEDICAL_STAFF) ├── board_certifications[] \leftarrow Multivalued: can hold multiple certification records ├── hospital_privileges[] \leftarrow Multivalued: privileges at multiple hospitals └── specialty_areas[] \leftarrow Multivalued: multiple areas of specialty

Designing Effective Local Attributes

Well-designed local attributes enhance model clarity, support efficient queries, and enable proper constraint enforcement. Here are principles and practices for effective local attribute design. Principle 1: Semantic Appropriateness Local attributes must genuinely belong to that specific subtype. Ask: Does this attribute conceptually make sense for this subtype? Would subject matter experts agree this attribute belongs here? Is the attribute applicable to ALL instances of the subtype?

Good Local Attribute Design

•Manager.budget — All managers manage budgets
•SavingsAccount.interest_rate — All savings accounts earn interest
•DoctoralStudent.dissertation_topic — All doctoral students have dissertations
•ElectricVehicle.charging_port_type — All EVs need charging

Poor Local Attribute Design

•Manager.favorite_color — Not role-relevant
•SavingsAccount.last_login — Applies to all accounts
•DoctoralStudent.gpa — All students have GPA (should be inherited)
•ElectricVehicle.color — All vehicles have color (should be inherited)

Principle 2: Constraint-Aware Design Consider what constraints the local attribute requires: -- Local attribute with constraints CREATE TABLE SAVINGS_ACCOUNT ( account_id INT PRIMARY KEY REFERENCES ACCOUNT(account_id), -- Local attributes with constraints interest_rate DECIMAL(5,4) NOT NULL CHECK (interest_rate >= 0 AND interest_rate <= 0.10), min_balance DECIMAL(10,2) DEFAULT 100.00, compound_frequency ENUM('DAILY', 'MONTHLY', 'QUARTERLY', 'ANNUALLY') NOT NULL, withdrawal_limit INT CHECK (withdrawal_limit >= 0) ); Principle 3: Nullability Decisions For each local attribute, explicitly decide: NOT NULL : Every instance of the subtype must have this value NULLABLE : Some instances may not have a value MANAGER ├── department (NOT NULL) — Every manager heads a department ├── budget (NOT NULL) — Every manager has a budget ├── secretary_id (NULL) — Not all managers have secretaries └── company_car_id (NULL) — Only some managers get company cars

Avoid Attribute Creep

A common error is adding attributes to subtypes that only apply to SOME instances of that subtype. If only SOME managers have company cars, either make it nullable, or consider whether there's a further subtype (EXECUTIVE_MANAGER) that always has the attribute. Don't create attributes that are mostly NULL even within the subtype.

Local Attributes That Define Relationships

A particularly important category of local attributes are foreign keys that establish subtype-specific relationships. These are relationships that only the subtype participates in—not the supertype or siblings. Example: University System

Converting Mermaid diagram...

In this model: advisor_id is a local FK in STUDENT \to only students have advisors department_id is a local FK in FACULTY \to only faculty belong to academic departments office_id is a local FK in STAFF \to only staff have office assignments These relationships cannot be modeled on PERSON because they're semantically inappropriate for other subtypes. A faculty member doesn't have an 'advisor' in the student sense; a student doesn't have an 'office assignment' in the staff sense. Subtype-Specific Relationship vs. Inherited Relationship:

Relationship Placement Decision
Question	If YES...	If NO...
Does this relationship apply to ALL subtypes?	Place on supertype (inherited)	Place on specific subtype (local)
Would sibling subtypes make sense with this relationship?	Consider supertype placement	Definitely local to this subtype
Does the relationship target change by subtype?	Use local relationships per subtype	Can use supertype relationship
Are relationship constraints different by subtype?	May need local with specific constraints	Supertype with inherited constraints

Relationship Naming Convention

Name local relationship attributes to reflect the subtype's perspective: Student.advisor_id (not Student.person_id), Faculty.department_id (not Faculty.unit_id). The name should convey the semantic meaning in the context of that subtype.

Constraints on Local Attributes

Local attributes can have their own constraints, independent of any inherited constraints. These constraints are enforced only for the subtype where the local attribute is defined. Types of Local Attribute Constraints:

local-constraints.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- Local Attribute Constraints Examples
 
-- SAVINGS_ACCOUNT local constraints
CREATE TABLE SAVINGS_ACCOUNT (
    account_id INT PRIMARY KEY REFERENCES ACCOUNT(account_id),
    
    -- Domain constraint: interest rate between 0 and 10%
    interest_rate DECIMAL(5,4) NOT NULL 
        CHECK (interest_rate >= 0 AND interest_rate <= 0.10),
    
    -- NOT NULL constraint: compound frequency is required
    compound_frequency VARCHAR(20) NOT NULL
        CHECK (compound_frequency IN ('DAILY', 'MONTHLY', 'QUARTERLY', 'ANNUALLY')),
    
    -- Range constraint: withdrawal limit must be positive
    monthly_withdrawal_limit INT CHECK (monthly_withdrawal_limit > 0),
    
    -- Default value: minimum balance defaults to 100
    minimum_balance DECIMAL(10,2) NOT NULL DEFAULT 100.00
);
 
-- INVESTMENT_ACCOUNT local constraints
CREATE TABLE INVESTMENT_ACCOUNT (
    account_id INT PRIMARY KEY REFERENCES ACCOUNT(account_id),
    
    -- Enum constraint: risk levels
    risk_level VARCHAR(20) NOT NULL 
        CHECK (risk_level IN ('CONSERVATIVE', 'MODERATE', 'AGGRESSIVE')),
    
    -- Referential constraint: portfolio must exist
    portfolio_id INT NOT NULL REFERENCES PORTFOLIO(portfolio_id),
    
    -- Business rule constraint: contribution limits
    annual_contribution_limit DECIMAL(12,2),
    ytd_contributions DECIMAL(12,2) DEFAULT 0,
    
    -- Cross-attribute constraint: can't exceed limit
    CONSTRAINT check_contribution_limit 
        CHECK (ytd_contributions <= annual_contribution_limit OR annual_contribution_limit IS NULL)
);

Cross-Attribute Constraints: Local attributes can participate in constraints involving inherited attributes: -- PREMIUM_ACCOUNT: local attribute constrained by inherited attribute CREATE TABLE PREMIUM_ACCOUNT ( account_id INT PRIMARY KEY REFERENCES ACCOUNT(account_id), -- Local: premium tier based on balance premium_tier VARCHAR(20) NOT NULL, -- Constraint involving inherited 'balance' and local 'premium_tier' -- (This would be enforced via trigger or application logic -- since balance is in the ACCOUNT table) CONSTRAINT tier_balance_consistency CHECK ( (premium_tier = 'GOLD' AND balance >= 50000) OR (premium_tier = 'PLATINUM' AND balance >= 250000) OR (premium_tier = 'BLACK' AND balance >= 1000000) ) -- Note: Actual implementation depends on physical schema );

Cross-Table Constraints

When local attributes need to be constrained against inherited attributes that live in a different physical table (in class table inheritance), standard CHECK constraints may not work. You'll need triggers, application logic, or materialized views to enforce such constraints.

Querying Local Attributes

Local attributes are only accessible when querying the specific subtype where they're defined. This has important implications for query construction. Access Patterns:

local-attribute-queries.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
-- Query 1: Accessing local attributes directly from subtype
SELECT 
    account_id,
    balance,          -- inherited from ACCOUNT
    interest_rate,    -- local to SAVINGS_ACCOUNT
    compound_frequency -- local to SAVINGS_ACCOUNT
FROM SAVINGS_ACCOUNT
WHERE interest_rate > 0.03;
 
-- Query 2: Cannot access local attributes from supertype
SELECT 
    account_id,
    balance,
    interest_rate  -- ERROR! interest_rate not in ACCOUNT
FROM ACCOUNT;
 
-- Query 3: Joining subtype to get local + inherited
-- (In class table inheritance schema)
SELECT 
    a.account_id,
    a.balance,           -- from supertype table
    a.holder_id,         -- from supertype table
    s.interest_rate,     -- from subtype table
    s.minimum_balance    -- from subtype table
FROM ACCOUNT a
JOIN SAVINGS_ACCOUNT s ON a.account_id = s.account_id
WHERE s.interest_rate > 0.03;
 
-- Query 4: Polymorphic query with optional local data
-- (Getting all accounts, with savings-specific data when applicable)
SELECT 
    a.account_id,
    a.balance,
    a.account_type,
    s.interest_rate  -- NULL for non-savings accounts
FROM ACCOUNT a
LEFT JOIN SAVINGS_ACCOUNT s ON a.account_id = s.account_id;

Query Design Patterns for Local Attributes:

•Direct subtype query: When you know you want only that subtype and need local attributes, query the subtype directly.
•Joined query: When you need both inherited and local attributes, join supertype and subtype tables (in class table inheritance).
•Left join for optional data: When querying supertypes but wanting local attributes when available, use LEFT JOINs.
•Union for multiple subtypes: When querying multiple subtypes with different local attributes, use UNION with explicit column selection.
•Discriminator filtering: In single table inheritance, filter by discriminator to simulate subtype-specific queries.

Schema Mapping Affects Queries

The actual SQL for accessing local attributes depends entirely on how the EER model was mapped to relational tables. Single table inheritance gives simpler queries but with NULLs. Class table inheritance gives cleaner data but requires JOINs. Always understand your physical schema before writing queries.

Complete Example: E-Commerce Product Hierarchy

Let's examine a comprehensive example that demonstrates inherited and local attributes working together in a realistic domain.

Converting Mermaid diagram...

product-hierarchy-attributes.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
-- Complete Attribute Analysis
 
-- PRODUCT (Supertype - Level 0)
-- All attributes here are INHERITED by all subtypes
CREATE TABLE PRODUCT (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL CHECK (price > 0),
    category_id INT REFERENCES CATEGORY(id),
    stock_qty INT DEFAULT 0 CHECK (stock_qty >= 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- PHYSICAL_PRODUCT (Subtype - Level 1)
-- Inherits: product_id, name, description, price, category_id, stock_qty, created_at
-- Local: weight_kg, dimensions, shipping_class, warehouse_id
CREATE TABLE PHYSICAL_PRODUCT (
    product_id INT PRIMARY KEY REFERENCES PRODUCT(product_id),
    weight_kg DECIMAL(8,3) NOT NULL,
    dimensions JSONB,  -- {length, width, height}
    shipping_class VARCHAR(50) NOT NULL,
    warehouse_id INT REFERENCES WAREHOUSE(id)
);
 
-- DIGITAL_PRODUCT (Subtype - Level 1)
-- Inherits: product_id, name, description, price, category_id, stock_qty, created_at
-- Local: file_size_mb, download_url, format, license_type
CREATE TABLE DIGITAL_PRODUCT (
    product_id INT PRIMARY KEY REFERENCES PRODUCT(product_id),
    file_size_mb DECIMAL(10,2),
    download_url VARCHAR(500) NOT NULL,
    format VARCHAR(50),
    license_type VARCHAR(50) NOT NULL CHECK (license_type IN ('PERSONAL', 'COMMERCIAL', 'ENTERPRISE'))
);
 
-- SUBSCRIPTION_PRODUCT (Subtype - Level 1)
-- Inherits: product_id, name, description, price, category_id, stock_qty, created_at
-- Local: billing_cycle, trial_days, auto_renew, tier_level
CREATE TABLE SUBSCRIPTION_PRODUCT (
    product_id INT PRIMARY KEY REFERENCES PRODUCT(product_id),
    billing_cycle VARCHAR(20) NOT NULL CHECK (billing_cycle IN ('MONTHLY', 'QUARTERLY', 'ANNUAL')),
    trial_days INT DEFAULT 0,
    auto_renew BOOLEAN DEFAULT true,
    tier_level VARCHAR(50)
);
 
-- BOOK (Subtype of PHYSICAL_PRODUCT - Level 2)
-- Inherits: product_id, name, description, price, category_id, stock_qty, created_at (from PRODUCT)
--           weight_kg, dimensions, shipping_class, warehouse_id (from PHYSICAL_PRODUCT)  
-- Local: isbn, author, publisher, page_count
CREATE TABLE BOOK (
    product_id INT PRIMARY KEY REFERENCES PHYSICAL_PRODUCT(product_id),
    isbn VARCHAR(17) UNIQUE NOT NULL,
    author VARCHAR(255) NOT NULL,
    publisher VARCHAR(255),
    page_count INT CHECK (page_count > 0)
);
 
-- Query: Find all books with their complete attribute set
SELECT 
    -- From PRODUCT (inherited through chain)
    p.product_id, p.name, p.description, p.price, p.category_id, p.stock_qty,
    -- From PHYSICAL_PRODUCT (inherited)
    pp.weight_kg, pp.shipping_class, pp.warehouse_id,
    -- From BOOK (local)
    b.isbn, b.author, b.publisher, b.page_count
FROM PRODUCT p
JOIN PHYSICAL_PRODUCT pp ON p.product_id = pp.product_id
JOIN BOOK b ON pp.product_id = b.product_id
WHERE b.author LIKE '%Knuth%';

Database Management SystemsSpecialization

Specialization in Enhanced ER Modeling

LevelIntermediate

Duration55 mins

TopicSpecialization

4 / 5

Local Attributes: Defining Subtype-Specific Characteristics

The Essence of Subtype Distinction

If inheritance gives subtypes everything from their supertypes, what makes subtypes different from each other? The answer lies in local attributes —the attributes defined specifically for a subtype that don't exist in the supertype or in sibling subtypes. Local attributes are the raison d'être of specialization. Without them, there would be no structural reason to create subtypes; a simple discriminator attribute would suffice. Local attributes capture the unique characteristics that make each subtype semantically distinct and practically useful.

What You Will Learn

By the end of this page, you will master local attributes in EER specialization: how to identify them, design them effectively, handle their constraints, and understand their implications for schema design and data management. You'll develop judgment about when attributes should be local versus inherited.

Understanding Local Attributes

Definition: A local attribute (also called a specific attribute or subtype attribute) is an attribute that: Is defined on a subtype, not on the supertype Is meaningful only for entities of that subtype Does not apply to sibling subtypes or to the supertype in general Formal Notation: For subtype S with supertype E: local_attrs(S) = attrs(S) - attrs(E) These are the attributes that S has which E does not have Example: EMPLOYEE (supertype) ├── emp_id (PK) ├── name ├── salary └── hire_date MANAGER (subtype) - Local Attributes: ├── department \leftarrow Local: only managers have departments they lead ├── budget \leftarrow Local: only managers have budgets to manage └── direct_reports \leftarrow Local: only managers have people reporting to them ENGINEER (subtype) - Local Attributes: ├── tech_stack[] \leftarrow Local: only engineers have technical expertise ├── github_handle \leftarrow Local: only engineers have code repositories └── certifications[] \leftarrow Local: only engineers have technical certifications Note that these local attributes would be meaningless or inappropriate for other subtypes—a SALESPERSON doesn't have a tech_stack, and an ENGINEER doesn't have direct_reports (in their engineer capacity).

The Local Attribute Litmus Test

Ask:'Does this attribute make sense for ALL subtypes of the supertype?' If YES \to it belongs in the supertype (and will be inherited). If NO \to it's a local attribute of the specific subtype where it makes sense.

Categories of Local Attributes

Local attributes come in various forms, each serving different modeling purposes. Understanding these categories helps in attribute design.

Local Attribute Categories
Category	Description	Example
Role-Specific Attributes	Attributes relevant to the role the subtype plays	Manager.team_size, Doctor.specialty, Teacher.subjects[]
Capability Attributes	Attributes describing what the subtype can do	PremiumMember.download_limit, AdminUser.access_scope, TruckDriver.cdl_class
Measurement Attributes	Type-specific metrics or quantities	SavingsAccount.interest_rate, ElectricVehicle.battery_capacity, ServerNode.cpu_cores
Relationship Attributes	Foreign keys to type-specific related entities	Student.advisor_id, Patient.primary_physician_id, ProjectManager.portfolio_id
Configuration Attributes	Type-specific settings or preferences	BusinessAccount.tax_id, InternationalShipping.customs_code, EnterpriseUser.sso_config
Temporal Attributes	Type-specific dates or timestamps	TenuredProfessor.tenure_date, RetiredEmployee.retirement_date, CompletedOrder.shipped_date
Status Attributes	Type-specific state information	PendingClaim.review_stage, ActiveSubscription.renewal_date, GraduatedStudent.graduation_honors

Composite Local Attributes: Local attributes can be composite, just like any other attribute: BUSINESS_CUSTOMER (subtype of CUSTOMER) └── billing_address (composite local attribute) ├── company_name ├── attention_line ├── street ├── city ├── state ├── postal_code └── country While CUSTOMER might have an address attribute (inherited), BUSINESS_CUSTOMER has a specialized billing_address with business-specific components like company_name and attention_line. Multivalued Local Attributes: Local attributes can also be multivalued: PHYSICIAN (subtype of MEDICAL_STAFF) ├── board_certifications[] \leftarrow Multivalued: can hold multiple certification records ├── hospital_privileges[] \leftarrow Multivalued: privileges at multiple hospitals └── specialty_areas[] \leftarrow Multivalued: multiple areas of specialty

Designing Effective Local Attributes

Well-designed local attributes enhance model clarity, support efficient queries, and enable proper constraint enforcement. Here are principles and practices for effective local attribute design. Principle 1: Semantic Appropriateness Local attributes must genuinely belong to that specific subtype. Ask: Does this attribute conceptually make sense for this subtype? Would subject matter experts agree this attribute belongs here? Is the attribute applicable to ALL instances of the subtype?

Good Local Attribute Design

•Manager.budget — All managers manage budgets
•SavingsAccount.interest_rate — All savings accounts earn interest
•DoctoralStudent.dissertation_topic — All doctoral students have dissertations
•ElectricVehicle.charging_port_type — All EVs need charging

Poor Local Attribute Design

•Manager.favorite_color — Not role-relevant
•SavingsAccount.last_login — Applies to all accounts
•DoctoralStudent.gpa — All students have GPA (should be inherited)
•ElectricVehicle.color — All vehicles have color (should be inherited)

Principle 2: Constraint-Aware Design Consider what constraints the local attribute requires: -- Local attribute with constraints CREATE TABLE SAVINGS_ACCOUNT ( account_id INT PRIMARY KEY REFERENCES ACCOUNT(account_id), -- Local attributes with constraints interest_rate DECIMAL(5,4) NOT NULL CHECK (interest_rate >= 0 AND interest_rate <= 0.10), min_balance DECIMAL(10,2) DEFAULT 100.00, compound_frequency ENUM('DAILY', 'MONTHLY', 'QUARTERLY', 'ANNUALLY') NOT NULL, withdrawal_limit INT CHECK (withdrawal_limit >= 0) ); Principle 3: Nullability Decisions For each local attribute, explicitly decide: NOT NULL : Every instance of the subtype must have this value NULLABLE : Some instances may not have a value MANAGER ├── department (NOT NULL) — Every manager heads a department ├── budget (NOT NULL) — Every manager has a budget ├── secretary_id (NULL) — Not all managers have secretaries └── company_car_id (NULL) — Only some managers get company cars

Avoid Attribute Creep

A common error is adding attributes to subtypes that only apply to SOME instances of that subtype. If only SOME managers have company cars, either make it nullable, or consider whether there's a further subtype (EXECUTIVE_MANAGER) that always has the attribute. Don't create attributes that are mostly NULL even within the subtype.

Local Attributes That Define Relationships

A particularly important category of local attributes are foreign keys that establish subtype-specific relationships. These are relationships that only the subtype participates in—not the supertype or siblings. Example: University System

Converting Mermaid diagram...

In this model: advisor_id is a local FK in STUDENT \to only students have advisors department_id is a local FK in FACULTY \to only faculty belong to academic departments office_id is a local FK in STAFF \to only staff have office assignments These relationships cannot be modeled on PERSON because they're semantically inappropriate for other subtypes. A faculty member doesn't have an 'advisor' in the student sense; a student doesn't have an 'office assignment' in the staff sense. Subtype-Specific Relationship vs. Inherited Relationship:

Relationship Placement Decision
Question	If YES...	If NO...
Does this relationship apply to ALL subtypes?	Place on supertype (inherited)	Place on specific subtype (local)
Would sibling subtypes make sense with this relationship?	Consider supertype placement	Definitely local to this subtype
Does the relationship target change by subtype?	Use local relationships per subtype	Can use supertype relationship
Are relationship constraints different by subtype?	May need local with specific constraints	Supertype with inherited constraints

Relationship Naming Convention

Name local relationship attributes to reflect the subtype's perspective: Student.advisor_id (not Student.person_id), Faculty.department_id (not Faculty.unit_id). The name should convey the semantic meaning in the context of that subtype.

Constraints on Local Attributes

Local attributes can have their own constraints, independent of any inherited constraints. These constraints are enforced only for the subtype where the local attribute is defined. Types of Local Attribute Constraints:

local-constraints.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- Local Attribute Constraints Examples
 
-- SAVINGS_ACCOUNT local constraints
CREATE TABLE SAVINGS_ACCOUNT (
    account_id INT PRIMARY KEY REFERENCES ACCOUNT(account_id),
    
    -- Domain constraint: interest rate between 0 and 10%
    interest_rate DECIMAL(5,4) NOT NULL 
        CHECK (interest_rate >= 0 AND interest_rate <= 0.10),
    
    -- NOT NULL constraint: compound frequency is required
    compound_frequency VARCHAR(20) NOT NULL
        CHECK (compound_frequency IN ('DAILY', 'MONTHLY', 'QUARTERLY', 'ANNUALLY')),
    
    -- Range constraint: withdrawal limit must be positive
    monthly_withdrawal_limit INT CHECK (monthly_withdrawal_limit > 0),
    
    -- Default value: minimum balance defaults to 100
    minimum_balance DECIMAL(10,2) NOT NULL DEFAULT 100.00
);
 
-- INVESTMENT_ACCOUNT local constraints
CREATE TABLE INVESTMENT_ACCOUNT (
    account_id INT PRIMARY KEY REFERENCES ACCOUNT(account_id),
    
    -- Enum constraint: risk levels
    risk_level VARCHAR(20) NOT NULL 
        CHECK (risk_level IN ('CONSERVATIVE', 'MODERATE', 'AGGRESSIVE')),
    
    -- Referential constraint: portfolio must exist
    portfolio_id INT NOT NULL REFERENCES PORTFOLIO(portfolio_id),
    
    -- Business rule constraint: contribution limits
    annual_contribution_limit DECIMAL(12,2),
    ytd_contributions DECIMAL(12,2) DEFAULT 0,
    
    -- Cross-attribute constraint: can't exceed limit
    CONSTRAINT check_contribution_limit 
        CHECK (ytd_contributions <= annual_contribution_limit OR annual_contribution_limit IS NULL)
);

Cross-Attribute Constraints: Local attributes can participate in constraints involving inherited attributes: -- PREMIUM_ACCOUNT: local attribute constrained by inherited attribute CREATE TABLE PREMIUM_ACCOUNT ( account_id INT PRIMARY KEY REFERENCES ACCOUNT(account_id), -- Local: premium tier based on balance premium_tier VARCHAR(20) NOT NULL, -- Constraint involving inherited 'balance' and local 'premium_tier' -- (This would be enforced via trigger or application logic -- since balance is in the ACCOUNT table) CONSTRAINT tier_balance_consistency CHECK ( (premium_tier = 'GOLD' AND balance >= 50000) OR (premium_tier = 'PLATINUM' AND balance >= 250000) OR (premium_tier = 'BLACK' AND balance >= 1000000) ) -- Note: Actual implementation depends on physical schema );

Cross-Table Constraints

When local attributes need to be constrained against inherited attributes that live in a different physical table (in class table inheritance), standard CHECK constraints may not work. You'll need triggers, application logic, or materialized views to enforce such constraints.

Querying Local Attributes

Local attributes are only accessible when querying the specific subtype where they're defined. This has important implications for query construction. Access Patterns:

local-attribute-queries.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
-- Query 1: Accessing local attributes directly from subtype
SELECT 
    account_id,
    balance,          -- inherited from ACCOUNT
    interest_rate,    -- local to SAVINGS_ACCOUNT
    compound_frequency -- local to SAVINGS_ACCOUNT
FROM SAVINGS_ACCOUNT
WHERE interest_rate > 0.03;
 
-- Query 2: Cannot access local attributes from supertype
SELECT 
    account_id,
    balance,
    interest_rate  -- ERROR! interest_rate not in ACCOUNT
FROM ACCOUNT;
 
-- Query 3: Joining subtype to get local + inherited
-- (In class table inheritance schema)
SELECT 
    a.account_id,
    a.balance,           -- from supertype table
    a.holder_id,         -- from supertype table
    s.interest_rate,     -- from subtype table
    s.minimum_balance    -- from subtype table
FROM ACCOUNT a
JOIN SAVINGS_ACCOUNT s ON a.account_id = s.account_id
WHERE s.interest_rate > 0.03;
 
-- Query 4: Polymorphic query with optional local data
-- (Getting all accounts, with savings-specific data when applicable)
SELECT 
    a.account_id,
    a.balance,
    a.account_type,
    s.interest_rate  -- NULL for non-savings accounts
FROM ACCOUNT a
LEFT JOIN SAVINGS_ACCOUNT s ON a.account_id = s.account_id;

Query Design Patterns for Local Attributes:

•Direct subtype query: When you know you want only that subtype and need local attributes, query the subtype directly.
•Joined query: When you need both inherited and local attributes, join supertype and subtype tables (in class table inheritance).
•Left join for optional data: When querying supertypes but wanting local attributes when available, use LEFT JOINs.
•Union for multiple subtypes: When querying multiple subtypes with different local attributes, use UNION with explicit column selection.
•Discriminator filtering: In single table inheritance, filter by discriminator to simulate subtype-specific queries.

Schema Mapping Affects Queries

The actual SQL for accessing local attributes depends entirely on how the EER model was mapped to relational tables. Single table inheritance gives simpler queries but with NULLs. Class table inheritance gives cleaner data but requires JOINs. Always understand your physical schema before writing queries.

Complete Example: E-Commerce Product Hierarchy

Let's examine a comprehensive example that demonstrates inherited and local attributes working together in a realistic domain.

Converting Mermaid diagram...

product-hierarchy-attributes.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
-- Complete Attribute Analysis
 
-- PRODUCT (Supertype - Level 0)
-- All attributes here are INHERITED by all subtypes
CREATE TABLE PRODUCT (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL CHECK (price > 0),
    category_id INT REFERENCES CATEGORY(id),
    stock_qty INT DEFAULT 0 CHECK (stock_qty >= 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- PHYSICAL_PRODUCT (Subtype - Level 1)
-- Inherits: product_id, name, description, price, category_id, stock_qty, created_at
-- Local: weight_kg, dimensions, shipping_class, warehouse_id
CREATE TABLE PHYSICAL_PRODUCT (
    product_id INT PRIMARY KEY REFERENCES PRODUCT(product_id),
    weight_kg DECIMAL(8,3) NOT NULL,
    dimensions JSONB,  -- {length, width, height}
    shipping_class VARCHAR(50) NOT NULL,
    warehouse_id INT REFERENCES WAREHOUSE(id)
);
 
-- DIGITAL_PRODUCT (Subtype - Level 1)
-- Inherits: product_id, name, description, price, category_id, stock_qty, created_at
-- Local: file_size_mb, download_url, format, license_type
CREATE TABLE DIGITAL_PRODUCT (
    product_id INT PRIMARY KEY REFERENCES PRODUCT(product_id),
    file_size_mb DECIMAL(10,2),
    download_url VARCHAR(500) NOT NULL,
    format VARCHAR(50),
    license_type VARCHAR(50) NOT NULL CHECK (license_type IN ('PERSONAL', 'COMMERCIAL', 'ENTERPRISE'))
);
 
-- SUBSCRIPTION_PRODUCT (Subtype - Level 1)
-- Inherits: product_id, name, description, price, category_id, stock_qty, created_at
-- Local: billing_cycle, trial_days, auto_renew, tier_level
CREATE TABLE SUBSCRIPTION_PRODUCT (
    product_id INT PRIMARY KEY REFERENCES PRODUCT(product_id),
    billing_cycle VARCHAR(20) NOT NULL CHECK (billing_cycle IN ('MONTHLY', 'QUARTERLY', 'ANNUAL')),
    trial_days INT DEFAULT 0,
    auto_renew BOOLEAN DEFAULT true,
    tier_level VARCHAR(50)
);
 
-- BOOK (Subtype of PHYSICAL_PRODUCT - Level 2)
-- Inherits: product_id, name, description, price, category_id, stock_qty, created_at (from PRODUCT)
--           weight_kg, dimensions, shipping_class, warehouse_id (from PHYSICAL_PRODUCT)  
-- Local: isbn, author, publisher, page_count
CREATE TABLE BOOK (
    product_id INT PRIMARY KEY REFERENCES PHYSICAL_PRODUCT(product_id),
    isbn VARCHAR(17) UNIQUE NOT NULL,
    author VARCHAR(255) NOT NULL,
    publisher VARCHAR(255),
    page_count INT CHECK (page_count > 0)
);
 
-- Query: Find all books with their complete attribute set
SELECT 
    -- From PRODUCT (inherited through chain)
    p.product_id, p.name, p.description, p.price, p.category_id, p.stock_qty,
    -- From PHYSICAL_PRODUCT (inherited)
    pp.weight_kg, pp.shipping_class, pp.warehouse_id,
    -- From BOOK (local)
    b.isbn, b.author, b.publisher, b.page_count
FROM PRODUCT p
JOIN PHYSICAL_PRODUCT pp ON p.product_id = pp.product_id
JOIN BOOK b ON pp.product_id = b.product_id
WHERE b.author LIKE '%Knuth%';

Local Attributes: Defining Subtype-Specific Characteristics

The Essence of Subtype Distinction

Understanding Local Attributes

Categories of Local Attributes

Designing Effective Local Attributes

Local Attributes That Define Relationships

Constraints on Local Attributes

Querying Local Attributes

Complete Example: E-Commerce Product Hierarchy

There’s more to understand.

Local Attributes: Defining Subtype-Specific Characteristics

The Essence of Subtype Distinction

Understanding Local Attributes

Categories of Local Attributes

Designing Effective Local Attributes

Local Attributes That Define Relationships

Constraints on Local Attributes

Querying Local Attributes

Complete Example: E-Commerce Product Hierarchy

There’s more to understand.