Part II — Database Modeling and Design

Chapter 5. Database Requirements and ER Modeling

Everything in this book so far has consumed a schema that already existed. This chapter and the next two create one. Databases are not designed by staring at SQL syntax: they are designed by first understanding the enterprise — what facts exist, what things the business tracks, what rules connect them — and modeling that understanding before writing a single CREATE TABLE. The Entity-Relationship (ER) model, introduced by Peter Chen in 1976, is the industry's standard conceptual language for that stage, and ER fluency is a hiring-interview staple.

The method has three movements: collect and analyze requirements (what the users need), model the data conceptually with entities, attributes, and relationships (what the enterprise is), and only then translate to a relational schema (Chapter 6), which normalization (Chapter 7) then hardens. We build the university database this way and discover that the ER model, done honestly, predicts the six-table schema of Appendix H.

After studying this chapter you will be able to:

• Gather and organize database requirements using several fact-finding techniques.
• Identify entities, attributes, and relationships from a requirements narrative.
• Draw and read ER diagrams in both Chen notation and crow's-foot notation.
• Distinguish entity types from entity instances and simple, composite, multivalued, and derived attributes.
• Specify relationship cardinality (1:1, 1:N, M:N) and participation (total, partial).
• Model weak entities with identifying relationships and discriminators.
• Use specialization, generalization, and their constraints, plus aggregation and categories.
• Develop a complete ER model from a written requirements document.

5.1 Requirements collection and analysis

A database outlives the applications that first used it, so the requirements stage asks broader questions than "what should the screen show?" Good requirement gathering uses several channels, because each surfaces different facts:

• Interviews and observation. Ask the registrar's staff how enrollment actually works — including the exceptions ("students may audit," "a section may be canceled the first week"). Watch a clerk process a late enrollment; the workaround reveals a missing entity.
• Document analysis. Forms, spreadsheets, report layouts, and catalogs are frozen requirements: every box on the registration form is a candidate attribute; every total on the dean's report is a candidate derived fact.
• Use cases and user stories. "Student enrolls in a section" names the actors (student), the action (enroll), and the objects (section) — the nouns are entity candidates, and the story's rules are constraint candidates.
• Data sources and volumes. Existing files, expected number of students and sections, growth rate — these shape physical design (Chapter 19) more than the conceptual model, but record them now.

Analysis turns the raw material into three lists — the things tracked (entity candidates), the facts recorded about each (attribute candidates), and the business rules connecting things (relationship and constraint candidates) — plus a data dictionary draft: for every candidate, a name, meaning, source, and owner. Two disciplines keep this honest. First, separate the current from the desired: "the spreadsheet also stores the advisor's phone number" is a current artifact, not necessarily a requirement. Second, state the rules as rules: "every section belongs to exactly one course" is a constraint that will become a foreign key; "a student may enroll in at most five sections per semester" is a constraint that SQL will express as a trigger or application check (Chapter 22).

Requirements are finished when the three lists are stable, every candidate has an owner, and the users recognize their world in your summary. The deliverable of this stage is a written requirements specification; the deliverable of the rest of the chapter is the ER model that satisfies it.

5.2 Entities, attributes, and relationships

An entity is a thing in the enterprise that exists independently and about which data are recorded — a student, a course, a section. An attribute is a recorded property of an entity — a student's full_name, gpa. A relationship connects two or more entities — a student enrolls in a section.

Attributes are classified by structure:

• Simple (atomic) attributes hold one indivisible value: student_id, gpa. Relational tables can store only simple attributes — Chapter 2's atomicity, already visible here.
• Composite attributes group sub-attributes with meaning of their own: full_name could be modeled as composite (first, last) if the university needed them separately (sorting by surname). Mapping flattens composites into their parts.
• Multivalued attributes hold a set of values for one entity: a department's phones. The relational model cannot store them in place — each becomes its own table (Chapter 6) — which is why the canonical schema has none.
• Derived attributes are computable from stored ones: age from admission_year (approximately), a student's earned credits by summing passed enrollments. The default policy is do not store what you can compute — storing invites the classic update anomaly (Chapter 7).

Each entity needs a key attribute (or set) that identifies its instances: student_id identifies students; course_id identifies courses. Key discipline from Chapter 2 (candidate keys, uniqueness) applies directly, and the ER stage is where natural-versus-surrogate key decisions get made deliberately (Chapter 6).

Relationships may themselves carry attributes: enrolls connects a student and a section, but the fact that belongs to the pair — the grade — is neither the student's nor the section's alone. Relationship attributes are the ER model's way of pointing at a future bridge table.

5.3 Entity-Relationship (ER) diagrams

An ER diagram (ERD) is the model drawn. Chen notation — rectangles for entities, diamonds for relationships, ovals for attributes, underlined key attributes — shows structure explicitly and is the notation of textbooks and exams. Crow's-foot notation — rectangles joined by lines whose ends encode cardinality — is the notation of industry tools (and Appendix F's quick reference):

 Chen notation                          crow's-foot notation

 ┌────────┐   enrolls   ┌─────────┐      STUDENT ────< ENROLLS >──── SECTION
 │ STUDENT├──────< >────┤ SECTION │              (many-to-many, grade
 └────────┘             └─────────┘               on the relationship)
   attributes in ovals, key underlined; "many" ends drawn as crow's feet

Both diagrams say the same thing: students and sections are related many-to-many through enrolls, and the relationship carries a grade. Diagram conventions worth fixing now: relationship names are verb phrases read aloud ("student enrolls in section"); attribute ovals are drawn only on small diagrams — real diagrams live in data dictionaries; and a diagram is a communication artifact, so the reader's convention beats the author's preference. The full university ERD, in crow's foot:

 DEPARTMENT ──< COURSE ──< COURSE_SECTION >── INSTRUCTOR
      │  (offers)   (has)         (1 per section, N per instructor)
      │
      ├──< STUDENT >──── ENROLLMENT ────< COURSE_SECTION
      │(1 dept : N major students; enrollment resolves STUDENT─SECTION M:N)

Reading it aloud is the review technique: "a department offers many courses; a course has many sections; each section is taught by one instructor; a student majors in one department; students enroll in sections, many to many, with a grade." If the sentence and the diagram disagree, one of them is wrong.

5.4 Entity types and entity sets

An entity type is the intensional description — "STUDENT is anything the university admits and tracks with id, name, major, admission year, credits, GPA." An entity set is the extension — the actual current students, our 12. The distinction is Chapter 2's schema/instance distinction applied to ER: entity types are drawn, entity sets are counted, and the same participation and cardinality rules constrain every legal instance of the set.

Type discipline matters more than it first appears. Deciding whether something is an entity, an attribute, or a relationship is the central judgment of ER modeling, and the test is always the same: does the thing have recorded properties of its own, or relationships of its own? A department has a name, building, and budget, and employs instructors — entity. A room has a code and is scheduled for sections — entity (a future fact we might model). A student's GPA — no independent existence, just a property — attribute. "Section 12 of CSE221 in Fall 2026" — an entity, because sections have rooms, capacities, times, and instructors, independent of any student. When a property starts needing its own properties, it graduates from attribute to entity: the moment the registrar wants to record seats per room and projector type, room stops being a string on course_section and becomes an entity in its own right.

One more degree of freedom: entities may be strong (identified by their own attributes) or weak (identified only through a relationship) — the subject of Section 5.6. The default is strong; weakness is a modeling decision, not a defect.

5.5 Cardinality and participation constraints

Two numbers make relationships precise. Cardinality (mapping) says how many of each side may pair with one of the other:

• 1:1 — one department head per department (in a fuller university model), one student per transcript.
• 1:N — one department offers many courses; each course belongs to one department.
• M:N — students and sections: a student enrolls in many sections, a section enrolls many students.

Participation says whether every instance must take part:

• Total participation (mandatory) — every course_section must reference a course; a section without a course is nonsense. Drawn as a double line in Chen notation, a mandatory end in crow's foot.
• Partial participation (optional) — an instructor need not teach any section this term (instructor 106 teaches exactly one, but the model allows zero); a student's major_dept_id may be NULL before declaration.

Cardinality plus participation fully determines the foreign-key shape of Chapter 6: 1:N puts the key on the N side; total participation makes that key NOT NULL; M:N forces a bridge table. Our canonical facts in ER terms: DEPARTMENT–COURSE is 1:N, total on the course side; INSTRUCTOR–COURSE_SECTION is 1:N with partial participation on the section side (instructor_id may be NULL); STUDENT–COURSE_SECTION is M:N, partial on both sides (a student may be enrolled in nothing — our dataset happens to have none, but the rule allows it), resolved by ENROLLMENT with total participation (an enrollment row must have both its student and its section). Getting these numbers right at the ER stage is the cheapest correctness investment in the whole design process; every one of them becomes a constraint later.

5.6 Weak entities and identifying relationships

A weak entity is one whose instances cannot be identified by their own attributes alone — only in the context of an owner entity, through an identifying relationship. Its key is the owner's key plus a discriminator (partial key) unique within the owner's context.

The university offers a clean example. Suppose sections do not get global section numbers but are numbered within their course — "CSE221, section 2, Fall 2026." Then COURSE_SECTION is weak, owned by COURSE, with discriminator (section number, semester, year): the identifying key is (course_id, section_no, semester, year). The canonical schema instead assigns a global surrogate section_id — the strong-entity route — but both are legal models of the same world, and the trade-off (readability of natural keys versus stability of surrogates) is Chapter 6's business.

Another classic weak-entity shape: dependent — an instructor's emergency contacts, identified by (instructor_id, dependent_name), owned by INSTRUCTOR through the identifying relationship has-dependent. The signature of weakness in a narrative: the phrase "numbered within" or "identified per" something.

Weak entities map to tables whose primary key includes the owner's key (Chapter 6), and their identifying relationship is total by definition — a dependent cannot exist without its instructor. Double-rectangle and double-diamond symbols record this in Chen notation (Appendix F). When you meet a proposed weak entity, always ask whether the discriminator will stay unique within the owner — and whether the owner relationship will stay 1:N — because both assumptions are baked into the key.

5.7 Specialization and generalization

Real enterprises have subtypes. A specialization splits an entity type into subtypes by a distinguishing feature: STUDENT into {UNDERGRAD, GRAD}; PERSON into {STUDENT, INSTRUCTOR, ALUMNUS}. Generalization is the inverse — discovering that several types share properties and abstracting a supertype (PERSON) above them. The two are the same hierarchy read in opposite directions, drawn as an "ISA" triangle:

                    PERSON
                   /  │   \            (ISA: is-a hierarchy)
          STUDENT  INSTRUCTOR  ALUMNUS

Subtype hierarchies carry two constraints that must be decided, not defaulted:

• Disjoint vs. overlapping. Can one instance belong to two subtypes? STUDENT and INSTRUCTOR overlap in real universities (teaching assistants); UNDERGRAD and GRAD are disjoint.
• Total vs. partial specialization. Must every supertype instance be in some subtype? Every PERSON at a university is at least one of the three — total; a VEHICLE fleet might have some vehicles that are neither CAR nor TRUCK — partial.

Subtypes inherit the supertype's attributes and relationships, and may add their own: GRAD adds thesis_title; INSTRUCTOR adds salary, hire_date. The ER decision is which entities get their own tables — and Chapter 6 gives the three standard mappings: separate table per subtype (clean, needs joins), one table with a type column (simple, sparse NULLs), or supertype table plus subtype tables (the compromise). The choice is driven by how differently the subtypes behave, not by taste.

5.8 Enhanced ER modeling

The Enhanced ER (EER) model extends ER with the machinery large models actually need:

• Aggregation treats a relationship and its participants as one higher-level entity, so it can participate in further relationships. When the university records who approves each enrollment — the approval relates the INSTRUCTOR to the enrollment, not to the student or section alone — aggregate ENROLLMENT into an abstract entity and hang the approves relationship on it. (In practice, modeling the bridge table ENROLLMENT as a first-class entity does the same job, which is why aggregation is rare in tools but common in exams.)
• Categories (union types) model an entity whose members come from multiple supertypes: PAYER is the union of STUDENT and SPONSOR; VEHICLE of CAR and TRUCK. Membership is "one of these," not "all of these."
• Specialization lattices and multiple inheritance — a TEACHING_ASSISTANT is both STUDENT and INSTRUCTOR; attributes flow from two parents, and design must reconcile overlapping keys.
• Attribute-level refinements — composite hierarchies (address → street → {number, name}) and derived-attribute formulas recorded in the data dictionary so that later designers do not "re-discover" them as stored facts.

EER is deliberately expressive; the engineering discipline is restraint. Aggregate or union-type machinery is justified only when the plain model would lose information or force falsehoods (a payer really is either a student or a sponsor, and pretending otherwise creates a fake entity). For the university database, subtype machinery would model graduate students — a design Chapter 29's case studies exercise fully.

5.9 Developing an ER model from requirements

Here is the method applied end-to-end to our running example. The requirements specification, condensed: The university consists of departments, each with a name, building, and budget, and each offering courses (code, title, credit hours) — a course belongs to exactly one department. Students are admitted with an ID, name, major department, admission year, earned credits, and GPA. Instructors have IDs, names, departments, hire dates, and salaries. Each semester, courses run as sections with a room and seat capacity; each section is taught by exactly one instructor (possibly none yet, if staffing is pending). Students enroll in sections and eventually receive one letter grade per enrollment; enrollment in progress has no grade yet. The system must support transcripts, departmental course lists, instructor workload reports, and seat availability.

Step 1 — nouns to entities. Department, course, student, instructor, section. ("Semester" is an attribute of section, not an entity: it has no facts of its own; "grade" is an attribute of a relationship.)

Step 2 — attributes per entity, marking keys and nullability from the spec: DEPARTMENT(dept_id✱, dept_name, building, budget); STUDENT(student_id✱, full_name, major_dept_id, admission_year, total_credits, gpa); and so on — the attribute lists are exactly Appendix H's column lists, which is the point of doing the work.

Step 3 — verbs to relationships with cardinality and participation: DEPARTMENT offers COURSE (1:N, total on course); DEPARTMENT majors STUDENT (1:N, partial on student, since major_dept_id may be unset); INSTRUCTOR teaches COURSE_SECTION (1:N, partial on section — staffing may be pending); COURSE runs COURSE_SECTION (1:N, total on section); STUDENT enrolls-in COURSE_SECTION (M:N, partial both sides, with attribute grade).

Step 4 — rules to constraints: grades from a fixed letter scale; GPA between 0.00 and 4.00; capacity positive; (student, section) pairs unique. These live in the data dictionary until Chapter 6 declares them.

Step 5 — draw, read aloud, review with users, revise. The final diagram is the crow's-foot ERD of Section 5.3 with the five entities and four relationships above — and no weak entities, no subtypes, no aggregation: the model is as simple as the enterprise allows, which is the highest compliment a data model receives.

The striking outcome: nowhere in this process did we design tables. The six tables of the canonical schema fall out mechanically in Chapter 6 — and that mechanical predictability is what makes ER modeling worth learning before SQL.

Chapter Summary

• Design proceeds requirements → conceptual model (ER) → logical model (relational, Chapter 6) → normalized physical schema (Chapter 7).
• Requirements come from interviews, documents, use cases, and data sources, and are distilled into entity, attribute, and rule lists plus a data dictionary.
• Entities are independently existing things; attributes are their properties — simple, composite, multivalued, or derived; relationships connect entities and may carry attributes (grades belong to enrollments, not to students or sections).
• ER diagrams come in Chen and crow's-foot notations; read them aloud as sentences to review them.
• Entity types are intensional descriptions; entity sets are their current instances; the entity/attribute/relationship test is "does it have properties and relationships of its own?"
• Cardinality (1:1, 1:N, M:N) and participation (total, partial) fully determine the future foreign-key layout.
• Weak entities are identified through their owners by (owner key + discriminator); identifying relationships are total.
• Specialization/generalization add subtype hierarchies with disjointness and totality constraints; EER adds aggregation, categories, and lattices — to be used sparingly.
• A five-step method — nouns, attributes, verbs+cardinalities, rules, review — produces the university ER model, which Chapter 6 maps onto the canonical six-table schema.

Key Terms

Laboratory Exercises

1. From this sentence list every entity, attribute (typed), and relationship candidate: "The library lends multiple copies of books to members; each loan records a due date; members pay fines, which have amounts and dates." Expected result: entities — BOOK, COPY, MEMBER, LOAN, FINE; attributes — due date on LOAN, amount/date on FINE; relationships — BOOK has COPY (1:N), MEMBER makes LOAN, LOAN incurs FINE.
2. Draw the university ERD twice — once in Chen notation, once in crow's foot — including the grade attribute on ENROLLS. Expected result: diagrams matching Section 5.3 and 5.9; grade hangs off the relationship/diamond, not an entity.
3. Classify each attribute as simple, composite, multivalued, or derived, and justify: full_name; budget; a department's phone_numbers; a student's current_class_standing (from credits). Expected result: composite (or simple if the university never uses parts — say which policy you assume); simple; multivalued; derived.
4. Write the cardinality and participation of INSTRUCTOR-teaches-COURSE_SECTION from the canonical schema, and prove your participation answer from the DDL in Appendix H. Expected result: 1:N, partial participation on the section side — instructor_id is nullable in course_section.
5. Model section meetings as a weak entity: a section meets in a pattern of (day, period) pairs, numbered within the section. Give the identifying relationship, owner, and full key. Expected result: MEETING, owned by COURSE_SECTION via is-scheduled; key = (section_id, day, period) — or (section_id, meeting_no) if meetings are numbered.
6. Add subtypes to the model: GRAD and UNDERGRAD students, with GRADE_LEVEL and THESIS_TITLE. State your disjointness and totality choices in one sentence each. Expected result: e.g. — disjoint (a student is one or the other), total specialization (every student is one); any justified choice accepted.

Review Questions and Exercises

1. Name three requirements-gathering sources and one kind of fact each is best at revealing. Interviews — workarounds and exception rules; documents/forms — attribute and report candidates; observation — the real process versus the stated one.
2. Why are derived attributes not stored by default, and when is storing one justified? Storing invites update anomalies when the underlying data change; justified when computation is expensive and freshness requirements are loose (warehouse aggregates, Chapter 25).
3. A design stores the grade on STUDENT. What modeling error is this, and what is the correct home for the grade? The grade belongs to the student-section pair — a relationship attribute; storing it on STUDENT cannot express different grades per section (and vice versa).
4. Distinguish entity type from entity set with the university's numbers. STUDENT is the type; the set is the current 12 students — the schema/instance distinction of Chapter 2.
5. State the cardinality and participation of DEPARTMENT-offers-COURSE, and the SQL shape each implies. 1:N, total on the course side; course carries a NOT NULL dept_id foreign key to department.
6. When is an attribute "really" an entity? Give the test and one university example of the promotion. When it needs properties or relationships of its own; room becomes an entity once capacity, projector type, or scheduling relationships are required.
7. Define weak entity, discriminator, and identifying relationship with the dependent-contacts example. DEPENDENT is identified only within INSTRUCTOR by dependent_name (discriminator) via the has-dependent identifying relationship; key = (instructor_id, dependent_name).
8. Why must an identifying relationship be total, and what does that imply for the weak entity's table? A weak entity cannot exist without its owner; its table's owner foreign key is NOT NULL and part of the primary key.
9. Explain the difference between disjoint/overlapping and total/partial specialization, with a university example of each combination. Disjointness concerns subtype overlap; totality concerns whether everyone has a subtype; e.g. UNDERGRAD/GRAD disjoint and total, STUDENT/INSTRUCTOR overlapping and partial (a person may be neither).
10. Give the three standard mappings of a specialization to tables and one criterion for choosing each. Table per subtype (subtypes behave very differently), single table with type column (subtypes differ little), supertype + subtype tables (shared core, distinct extras).
11. What is aggregation for, and what practical modeling technique usually replaces it? Letting a whole relationship participate in another relationship; modelers usually promote the relationship to a first-class associative entity (ENROLLMENT) instead.
12. From "each purchase order has an id, references one supplier, and lists products with quantities," list the entities, the M:N relationship, and the relationship attribute. Entities — PURCHASE_ORDER, SUPPLIER, PRODUCT; M:N — ORDER–PRODUCT via lists; relationship attribute — quantity (with the order and product as context).

Mini-Project

Extend the university ER model into a fuller registration system. New requirements: (1) courses have prerequisites — other courses, many to many, with a minimum grade required in each; (2) sections meet in a weekly pattern of meetings, each with a day, start period, and end period, identified within the section; (3) when a section is full, students join a waitlist position — one active waitlist entry per student per section, with an entry timestamp; (4) instructors may be teaching assistants, who are also students. Draw the full EERD (any notation), mark every cardinality and participation, mark weak entities and subtypes with their constraints, and write a one-paragraph justification for every modeling judgment — especially whether the waitlist is an entity, a relationship, or an attribute. Then write the three most interesting English sentences your diagram can answer that the base model cannot. This model becomes the input to Chapter 6's mapping laboratories.