Entity Relationships, Keys and Relational Principles

1. Entity-Relationship Modeling

Entity-relationship modeling describes a domain before it is reduced to tables. It identifies the kinds of things the organization records, their properties and the rules connecting them.

An entity type is a category such as Student, Department or Course. An entity occurrence is one particular student or course. An entity must be distinguishable from other occurrences, usually through one or more identifying attributes.

ER modeling uses business concepts rather than interface screens or existing file layouts. A screen may combine several entities for convenience, while a sound model keeps their independently changing facts separate.

2. Attributes

An attribute describes an entity or relationship. Attributes can be:

• simple, such as salary;
• composite, such as an address divided into street, city and postal code;
• single-valued, such as date of birth;
• multivalued, such as several phone numbers;
• stored, such as quantity and unit price;
• derived, such as an order total calculated from lines.

Relational implementation normally decomposes composite attributes into useful columns and represents multivalued attributes in a related table. Repeating columns such as phone1, phone2 and phone3 impose an arbitrary limit and complicate searching.

Derived data may be calculated when requested or stored for performance and historical requirements. Stored derivations need controls that keep them synchronized with their source facts.

3. Relationships and Degree

A relationship expresses an association among entity types. Employee works for Department is binary because it connects two entity types. A recursive relationship connects occurrences of one type, such as Employee supervises Employee.

A ternary relationship connects three types at once. Suppose a Supplier supplies a Part to a Project. Splitting this fact into unrelated binary relationships may lose which supplier supplied which part to which project.

Relationship attributes describe the association itself. Enrollment.grade belongs to the link between Student and Course offering; it is not a permanent property of either one alone.

4. Cardinality

Cardinality states the maximum number of occurrences that may participate:

• one-to-one: each side associates with at most one occurrence on the other;
• one-to-many: one parent associates with many children, while each child associates with one parent;
• many-to-many: occurrences on both sides may associate with many on the other side.

Cardinality must come from business rules. A Department may employ many Employees, but each Employee may currently belong to one Department. A later requirement for simultaneous assignments would change the model to many-to-many.

5. Participation and Optionality

Participation states whether a relationship is mandatory. Minimum cardinality zero means optional participation; minimum one means mandatory participation.

For example, a new Department may temporarily have no employees, giving Department participation a minimum of zero. If every Employee must belong to a Department, Employee participation has a minimum of one.

Maximum cardinality and optionality answer different questions. “Zero or many” combines optional participation with a many maximum. “Exactly one” combines mandatory participation with a one maximum.

6. Strong and Weak Entities

A strong entity has an identifier independent of another entity. A weak entity depends on an owner for identification and existence.

An OrderLine might be identified by (order_id, line_number). line_number is unique only within one Order, so the Order's key participates in the weak entity's key.

Not every child with a foreign key is conceptually weak. A registered Employee has its own identity even when assigned to a Department. Dependence should reflect the domain, not merely the presence of a relationship.

7. Mapping a One-to-Many Relationship

A one-to-many relationship is normally implemented by placing the primary key of the one side as a foreign key in the many-side table:

DEPARTMENT(department_id, department_name)
EMPLOYEE(employee_id, employee_name, department_id)

EMPLOYEE.department_id references DEPARTMENT.department_id. Several employee rows may repeat the same department identifier, which correctly represents many employees belonging to one department.

If participation is mandatory, the foreign key should also be NOT NULL. A foreign key alone may permit NULL and therefore an employee with no referenced department.

8. Mapping a One-to-One Relationship

A one-to-one relationship uses a foreign key plus uniqueness. Suppose each Person has at most one Passport and each Passport belongs to exactly one Person:

PASSPORT(
    passport_number PRIMARY KEY,
    person_id UNIQUE NOT NULL,
    ...
)

The UNIQUE constraint prevents two passports from referencing the same person. The choice of which table receives the foreign key depends on optionality, lifecycle and access patterns. If two entities always share identity and lifecycle, merging them may be reasonable; if they are independently meaningful, separate tables preserve that distinction.

9. Mapping a Many-to-Many Relationship

A many-to-many relationship becomes an associative table:

STUDENT(student_id, ...)
COURSE(course_id, ...)
ENROLLMENT(student_id, course_id, term, grade)

ENROLLMENT contains foreign keys to both participants and stores attributes of the relationship. Its key must follow the business rule. If a student can repeat the same course in a later term, (student_id, course_id) is too restrictive; term or an offering identifier must participate.

An associative table is a full relation. It may have its own surrogate key, status, timestamps or relationships, but business uniqueness should still be constrained.

10. Superkeys

A superkey is any attribute set that uniquely identifies a tuple. If student_id is unique, then {student_id} is a superkey, but so are {student_id, name} and {student_id, date_of_birth}.

The extra attributes in larger superkeys are unnecessary for identification. Superkeys describe uniqueness, while candidate keys add the requirement of minimality.

Uniqueness must hold for all valid future states, not merely the current sample. A column whose values happen to differ today is not necessarily a key.

11. Candidate and Primary Keys

A candidate key is a minimal superkey. No proper subset remains unique. A table can have several candidate keys, such as employee_id and an officially unique tax identifier.

One candidate key is designated the primary key. The others are alternate keys and should normally receive UNIQUE constraints. The primary key is a design choice; candidate status comes from business semantics.

Primary-key columns cannot be NULL. A composite primary key requires every component to be present.

12. Composite Keys

A composite key contains more than one attribute. It is appropriate when identity naturally depends on a combination, such as (order_id, line_number).

Composite foreign keys must reference the complete candidate key with compatible column order and types. Using only one component does not identify a parent row.

Wide composite keys increase the size of referencing rows and indexes. A surrogate identifier can simplify references, but the original combination still needs a UNIQUE constraint if it represents business identity.

13. Natural and Surrogate Keys

A natural key has meaning in the domain, such as an ISBN edition identifier or an assigned employee number. A surrogate key is introduced primarily for database identity, such as an auto-increment integer.

A good natural key is unique, stable, compact and always known. Many apparent natural keys change, contain sensitive data or are assigned by another system whose rules are outside local control.

Surrogate keys offer compact stable references, but they do not eliminate business uniqueness. If duplicate email addresses are forbidden, UNIQUE(email) remains necessary even when customer_id is the primary key.

14. Foreign Keys

A foreign key is a set of child columns whose non-NULL values must match a candidate key in the referenced parent table. It establishes referential integrity.

Foreign-key values commonly repeat because many child rows can refer to one parent. A foreign key is not automatically unique and need not be the child's primary key.

Types and meanings should align. Referencing a product identifier from a column labeled customer_id may be technically possible with matching types but conceptually wrong.

15. NULL in Foreign Keys

If any permitted foreign-key representation is NULL, the relationship may be absent according to the DBMS rules and constraint definition. Use NOT NULL when every child must have a parent.

NULL should represent missing or inapplicable information, not a magic identifier. A special parent row such as “Unknown” has different semantics: it is an actual referenced row and can collect children deliberately.

Composite foreign keys require careful NULL rules. Prefer all components present for a relationship or all absent when optional, enforced with checks if needed.

16. Referential Actions

When a referenced parent key changes or a parent row is deleted, the foreign key defines an action:

• RESTRICT or NO ACTION prevents the change when matching children exist, subject to product timing;
• CASCADE propagates the update or deletion;
• SET NULL removes the reference while preserving nullable child rows;
• SET DEFAULT uses a declared default where supported.

Choose the action from lifecycle meaning. Deleting an Order can reasonably cascade to its OrderLines because lines have no independent purpose. Deleting a Department should rarely delete all Employees; reassignment or restriction is safer.

Cascades can cross several relationships, so their full effect must be understood before use.

17. Entity, Referential and Domain Integrity

Entity integrity requires every relation to have distinguishable rows and primary-key components to be non-NULL.

Referential integrity prevents child references to nonexistent parent keys. It preserves relationship validity during inserts, updates and deletes.

Domain integrity restricts individual values through types, nullability, defaults and checks. Business rules spanning several rows may require more advanced constraints, controlled transactions or carefully designed triggers.

18. Relational Principles

The relational model presents information through values in relations rather than navigation through physical addresses. Users express the required result declaratively and the DBMS chooses access paths.

Relations should be addressable through keys, missing information should receive systematic treatment and integrity rules should be expressed in the relational language rather than hidden in file-level procedures.

Physical organization remains necessary internally, but it should not become part of the logical meaning that every application must follow.

19. Codd's Relational Rules

Codd described a foundation rule and twelve rules for a fully relational system. They include:

• representing information as values in tables;
• guaranteed logical access using table, key and column;
• systematic treatment of NULL;
• an online relational catalog;
• a comprehensive relational data language;
• support for theoretically updatable views;
• set-level insert, update and delete;
• physical and logical data independence;
• independence of integrity constraints and distribution;
• protection against bypassing relational rules through lower-level access.

Commercial systems meet these ideals to different degrees. The rules are best understood as principles for relational completeness rather than a product checklist reduced to marketing labels.

20. Information and Guaranteed Access

The information principle says that facts should be represented logically as values in relations. Application-visible pointer chains should not be required to interpret the database.

Guaranteed access means a scalar value can be identified conceptually by relation name, a key identifying its row and a column name. This relies on proper keys; physical row position is not durable identity.

Queries may use indexes internally, but an index address is an access mechanism, not the logical identity of a business fact.

21. Set-Level Operations and Data Independence

A relational language operates on sets or multisets of rows. One UPDATE can modify every row matching a predicate without an application navigating record by record.

Physical data independence permits indexes and storage organization to change without changing relational queries. Logical data independence seeks to preserve external views when the conceptual schema evolves.

These properties reduce coupling between applications and storage, allowing database administrators to tune access without redefining what the facts mean.

22. Integrity Independence and Non-Subversion

Integrity independence means rules should be represented in the database language and catalog rather than existing only in application source code. This allows every access path to receive the same enforcement.

The non-subversion principle says that a lower-level interface must not bypass relational integrity and security rules. A system is not meaningfully relational if constraints apply through SQL but can be silently ignored through routine record-level access.

Administrative recovery facilities may operate below normal interfaces, but they require controlled privileges and procedures.

23. Reading an ER Diagram

For each entity, identify its key and attributes. For every relationship, read minimum and maximum participation in both directions. Ask whether the relationship has its own attributes and whether its identity depends on the participating entities.

Then test the diagram with concrete scenarios: zero children, several children, repeated events over time, changed identifiers, deleted parents and optional information. Many modeling errors appear only when time and lifecycle are considered.

An ER diagram is useful only when its symbols are backed by written business rules. “Customer places Order” is incomplete until optionality, maximums, identity and deletion behavior are known.

24. From ER Model to Reliable Relations

Map strong entities to relations with constrained candidate keys. Place foreign keys on the many side of one-to-many relationships. Add uniqueness for one-to-one mappings. Turn many-to-many relationships into associative relations, including attributes of the relationship.

Do not stop after drawing boxes and lines. Verify that each table represents one kind of fact, keys express stable identity, optionality matches nullability and referential actions match lifecycle.

This logical foundation prepares the schema for dependency analysis and normalization. Correct keys and relationships are essential because later SQL constraints, joins and transactions can enforce only the model that the design actually expresses.