Diagramming Entities and Attributes (Crow’s Foot Notation)

Crow’s Foot (Information Engineering) notation is a compact way to show the entities in a system, their attributes, and the relationships between them. It is widely used because it maps cleanly to relational schemas and stays readable as models grow.

Core Conventions

1. Entities (rectangles): Real-world concepts or things you store data about (e.g., STUDENT, CLASS). Entity names are singular, often uppercase. A horizontal line separates the name from the attribute list.
2. Attributes (listed inside the entity box): Properties of the entity, written in a single column beneath the name. In Crow’s Foot, attributes are not drawn as ovals (that is Chen notation); instead they are listed inside the rectangle.
   • PK (Primary Key): Uniquely identifies a row (e.g., StudID).
   • FK (Foreign Key): References a parent entity’s PK (e.g., DormitoryNo referencing RESIDENCE).
   • CPK (Composite Primary Key): A PK composed of multiple attributes—common in associative entities.
3. Relationships (lines between entities): Use end symbols to express both maximum cardinality and minimum participation (optionality).
   • Max (cardinality): single bar | = one; crow’s foot < = many.
   • Min (optionality): bar | = at least one (mandatory); circle O = zero allowed (optional).
   Examples:
   1. 1:1 → |—|
   2. 1:N → |—<
   3. 0..1 → O—|
   4. 0..N → O—<
   5. M:N → model with an associative entity (junction table) instead of a direct line.
4. Associative (composite) entities: Used to resolve M:N relationships or to store attributes about a relationship (e.g., EnrollmentDate, Grade). Often shown with a visual cue (e.g., distinctive corners) and a CPK that includes FKs to each parent.

Below is a common Crow’s Foot format showing:

1. a standard entity (STUDENT) and
2. a composite/associative entity (STUDENT CLASS)

with keys identified and foreign-key origins noted.

Entity names are singular and typically uppercase. Keep the internal attribute list consistent across the diagram. Associative entities are clearly marked and usually carry a composite key formed from the parents’ PKs.

List attributes in a single column. Place the PK first, then FKs and other attributes. When an entity receives a PK from an associated entity, label it FK. For associative entities, label each key as CPK/FK to show it is both part of the composite primary key and a foreign key to a parent.

A typical implementation uses surrogate keys for simplicity, while preserving natural keys with UNIQUE constraints as needed:

-- Parent entities
CREATE TABLE student (
  studid        NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  student_no    VARCHAR2(20) UNIQUE NOT NULL,
  last_name     VARCHAR2(60) NOT NULL,
  first_name    VARCHAR2(60) NOT NULL,
  dormitoryno   NUMBER, -- FK to RESIDENCE(residence_id), not shown
  -- FOREIGN KEY (dormitoryno) REFERENCES residence(residence_id)
);

CREATE TABLE class (
  classid       NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  class_code    VARCHAR2(20) UNIQUE NOT NULL,
  title         VARCHAR2(120) NOT NULL,
  instrid       NUMBER, -- FK to INSTRUCTOR(instructor_id), not shown
  -- FOREIGN KEY (instrid) REFERENCES instructor(instructor_id)
);

-- Associative entity resolving M:N
CREATE TABLE student_class (
  studid        NUMBER NOT NULL,
  classid       NUMBER NOT NULL,
  enrollment_dt DATE DEFAULT SYSDATE,
  grade         VARCHAR2(2),
  CONSTRAINT pk_student_class PRIMARY KEY (studid, classid),
  CONSTRAINT fk_sc_student FOREIGN KEY (studid) REFERENCES student(studid),
  CONSTRAINT fk_sc_class   FOREIGN KEY (classid) REFERENCES class(classid)
);
  

Alternative (composite keys without surrogate IDs):

CREATE TABLE student_natural (
  student_no VARCHAR2(20) PRIMARY KEY,
  last_name  VARCHAR2(60) NOT NULL,
  first_name VARCHAR2(60) NOT NULL
);

CREATE TABLE class_natural (
  class_code VARCHAR2(20) PRIMARY KEY,
  title      VARCHAR2(120) NOT NULL
);

CREATE TABLE student_class_natural (
  student_no VARCHAR2(20) NOT NULL,
  class_code VARCHAR2(20) NOT NULL,
  CONSTRAINT pk_sc_nat PRIMARY KEY (student_no, class_code),
  CONSTRAINT fk_sc_nat_student FOREIGN KEY (student_no) REFERENCES student_natural(student_no),
  CONSTRAINT fk_sc_nat_class   FOREIGN KEY (class_code) REFERENCES class_natural(class_code)
);
  

• Drawing attributes as ovals in Crow’s Foot diagrams (that’s Chen, not Crow’s Foot).
• Modeling M:N with a direct line instead of an associative entity.
• Leaving optionality ambiguous (forgetting whether O or | applies).
• Neglecting UNIQUE constraints for natural identifiers when using surrogate keys.