Conceptual and Logical Data Modeling

Introduction

Database quality is determined long before SQL tables are created. Developers must first discover what information an organization needs, identify the rules governing that information, and represent those rules in a model that stakeholders and engineers can validate.

Conceptual modeling describes a domain independently of database technology. Logical modeling transforms that description into relations, keys, attributes, and integrity constraints without committing to storage structures such as indexes or partitions. Physical design, addressed later in the database lifecycle, determines how the logical schema is implemented and optimized in a particular DBMS.

Poor modeling produces duplicated data, ambiguous meanings, invalid relationships, fragile application logic, and expensive migrations. A sound model instead functions as a shared contract among domain experts, developers, analysts, security professionals, and database administrators.

Learning Objectives

After completing this chapter, students should be able to:

• elicit and document data requirements and business rules;
• distinguish conceptual, logical, and physical models;
• identify entities, attributes, identifiers, and relationships;
• specify relationship degree, cardinality, and optionality;
• model weak, associative, recursive, and subtype entities;
• interpret Crow’s Foot and related modeling notations;
• transform an entity–relationship model into a relational schema;
• implement keys and participation rules using SQL constraints;
• evaluate modeling limitations, governance concerns, and design trade-offs.

1. Levels of Data Modeling

The three-schema perspective separates descriptions of meaning from implementation details.

A conceptual model may state that students enroll in course offerings. A relational logical model introduces STUDENT, COURSE_OFFERING, and ENROLLMENT relations. A physical PostgreSQL design might then add a B-tree index on ENROLLMENT(StudentID), choose specific data types, and partition records by academic year.

The boundaries are useful but not absolute. Naming keys during conceptual design can clarify identity, while preliminary data-type discussion can expose misunderstood requirements. The important discipline is to avoid allowing an early implementation choice to redefine the business domain without justification.

2. Requirements Discovery

2.1 Sources of requirements

Reliable models are based on evidence rather than assumptions. Common evidence sources include:

• interviews with domain experts and end users;
• existing forms, reports, spreadsheets, and databases;
• policies, contracts, legislation, and operating procedures;
• observation of workflows and exceptional cases;
• API payloads and integration specifications;
• representative data samples;
• workshops in which stakeholders review candidate models.

Existing databases are useful evidence, but they are not necessarily authoritative. Legacy schemas can contain historical compromises, undocumented duplication, or constraints enforced only by applications.

2.2 Data requirements and process requirements

A data requirement describes information that must be retained, such as a student’s institutional identifier. A process requirement describes an operation, such as registering a student for an offering. Processes often reveal data requirements, but the two should not be confused. A process may change while the underlying concept remains stable.

For each proposed attribute, analysts should ask:

1. What does it mean?
2. Who supplies it?
3. Is it required, optional, derived, or historical?
4. Can it have more than one value?
5. What values are valid?
6. Does it contain personal, confidential, or regulated information?
7. How long must it be retained?

2.3 Writing business rules

A business rule is a precise, testable statement constraining data or operations. Good rules are atomic: each statement expresses one independently verifiable fact.

Weak statement: A customer normally has orders and payment information.

Improved rules:

• A customer may place zero or more sales orders.
• Each sales order must be placed by exactly one customer.
• Each sales order must contain one or more order lines.
• Each order line refers to exactly one product.
• An order line records the quantity sold and the agreed unit price.

Terms such as normally, appropriate, recent, and several are unsuitable unless formally defined. Rules should also distinguish current policy from historical reality. If orders must remain auditable after a product is retired, deleting the product may need to be prohibited or represented as a status change.

A useful requirements catalogue records a rule identifier, statement, source, rationale, owner, validation method, and effective date. Traceability allows designers to explain why each relationship or constraint exists.

3. Entity–Relationship Foundations

3.1 Entities and entity types

An entity is a distinguishable occurrence about which data is recorded, such as one student or one course. An entity type describes the common structure of such occurrences. In informal discussion, entity is often used for both meanings, but the distinction remains useful.

A sound entity type generally has:

• multiple possible occurrences;
• a stable business meaning;
• an identifier;
• attributes or relationships worth recording.

Entity names should normally be singular nouns: STUDENT rather than STUDENTS. A concept should not be modeled as an entity merely because it appears as a noun. Whether Address is an entity, a composite attribute, or several scalar attributes depends on identity, reuse, history, and business rules.

3.2 Attributes

An attribute describes an entity or relationship. Important classifications include:

Atomicity is relative to intended use. A full address can be atomic in a mailing-only system but inadequate when searches by city or postal code are required.

Derived values should be stored only when performance, auditability, or historical meaning justifies the risk of inconsistency. A customer’s present age should usually be calculated from date of birth and an evaluation date. By contrast, the unit price agreed at the time of sale should be stored because it is a historical fact, not merely copied from the product’s current price.

3.3 Identifiers and keys

An identifier is an attribute or minimal attribute set that uniquely distinguishes entity occurrences. In the relational model:

• a superkey uniquely identifies rows but may contain unnecessary attributes;
• a candidate key is a minimal superkey;
• the primary key is the selected candidate key;
• alternate keys are candidate keys not selected as primary;
• a composite key contains more than one attribute;
• a surrogate key is an implementation-generated identifier without domain meaning;
• a natural key is formed from meaningful domain attributes.

Surrogate keys can improve stability and simplify foreign keys, but they do not eliminate business uniqueness rules. If course codes must be unique, adding CourseID does not remove the need for UNIQUE(CourseCode).

A good primary key is unique, non-null, minimal, stable, and reasonably compact. Mutable facts such as telephone numbers are usually poor identifiers.

4. Relationships, Cardinality, and Optionality

A relationship represents an association among entity types. Its degree is the number of participating entity types: unary, binary, ternary, and so forth.

For each role in a relationship, a minimum and maximum participation can be written as (min,max). Common multiplicities are:

• (0,1): zero or one;
• (1,1): exactly one;
• (0,N): zero or many;
• (1,N): one or many.

The minimum expresses optionality; the maximum expresses cardinality. These constraints must be read in both directions.

Figure: Correct Crow’s Foot representation of COURSE and COURSE_OFFERING.

At the COURSE end, two bars indicate exactly one. At the COURSE_OFFERING end, the circle indicates a minimum of zero and the crow’s foot indicates a maximum of many. There is no mandatory-participation bar at the zero-to-many endpoint.

4.1 One-to-one relationships

In a one-to-one relationship, each occurrence on either side relates to at most one occurrence on the other. For example, each employee may be assigned at most one locker, and each locker may be assigned to at most one employee. Optionality may differ between the two roles.

A one-to-one relationship can sometimes indicate that two entity types should be merged. Separation remains justified when they have different security classifications, lifecycles, optional attributes, or subtype meanings.

4.2 One-to-many relationships

A department may employ many employees, while each employee belongs to one department. This is a one-to-many relationship. In a relational schema, the department key normally becomes a foreign key in EMPLOYEE, the many side.

If every employee must belong to a department, the foreign key is NOT NULL. If participation is optional, it may be nullable. Foreign-key existence and nullability jointly implement the exactly-one rule.

4.3 Many-to-many relationships

A student can enroll in many offerings, and an offering can contain many students. A direct many-to-many relationship cannot be represented by placing a single foreign key in either relation. It is transformed into an associative relation such as ENROLLMENT.

ENROLLMENT can also hold relationship attributes, including enrollment date, status, and grade. These values describe the pairing of a student and an offering rather than either entity independently.

4.4 Recursive and higher-degree relationships

A recursive relationship connects occurrences of the same entity type. An EMPLOYEE may supervise other employees. Role names such as supervisor and subordinate prevent ambiguity.

A ternary relationship should not be decomposed automatically into three binary relationships. Suppose SUPPLIER supplies PART to PROJECT. The fact may depend on the complete triple. Three independent binary associations could incorrectly imply combinations that never occurred.

5. Weak, Associative, and Subtype Entities

5.1 Weak entities

A weak entity cannot be uniquely identified by its own attributes alone. It is identified through an owner entity plus a partial key. For example, DEPENDENT may use EmployeeID together with DependentNumber.

Its relational key is:

EmployeeID is both part of the primary key and a foreign key to EMPLOYEE. A weak entity is existence-dependent on its owner. Not every existence-dependent entity is necessarily weak: an entity with its own globally unique identifier may depend on another entity for lifecycle purposes without depending on it for identification.

5.2 Associative entities

An associative entity resolves a many-to-many relationship. Its identifier may be the combination of parent keys or a surrogate key. If a surrogate key is used, the natural combination often still needs a UNIQUE constraint.

For ENROLLMENT, a composite key of (StudentID, OfferingID) means one student can have at most one enrollment record for a particular offering. If repeated enrollment attempts must be retained, AttemptNumber or EnrollmentID may be required, and the business definition of uniqueness must be reconsidered.

5.3 Supertypes and subtypes

A supertype holds common attributes, while subtypes represent specialized roles. PERSON might have STUDENT and INSTRUCTOR subtypes. Two independent constraints must be specified:

• completeness: total if every supertype occurrence belongs to a subtype, otherwise partial;
• disjointness: disjoint if an occurrence can belong to at most one subtype, otherwise overlapping.

Common relational implementations include one table for the hierarchy, one table per concrete subtype, or a supertype table plus subtype tables sharing its primary key. The choice affects nullability, integrity enforcement, query complexity, and extensibility.

6. From Conceptual Model to Relational Schema

The transformation process is systematic but still requires semantic judgment.

6.1 Mapping rules

1. Strong entity: Create one relation containing its simple attributes. Select a primary key and preserve alternate keys with UNIQUE constraints.
2. Composite attribute: Store the components required by the application rather than an undifferentiated structure where independent access is needed.
3. Multivalued attribute: Create a separate relation containing the owner key and the value. Its primary key commonly combines both.
4. One-to-many relationship: Place the one-side primary key as a foreign key on the many side. Use NOT NULL when the many-side participation is mandatory.
5. One-to-one relationship: Place a foreign key on an appropriate side and add UNIQUE. Prefer the side with total participation or the side that minimizes nulls, while considering lifecycle and security.
6. Many-to-many relationship: Create an associative relation containing foreign keys to both participants and any relationship attributes.
7. Weak entity: Include the owner key and partial key in the weak entity’s primary key.
8. Recursive relationship: Use a self-referencing foreign key for one-to-many recursion or an associative relation for many-to-many recursion.
9. N-ary relationship: Create a relation containing foreign keys to all participants. Determine the candidate key from the actual semantics rather than assuming every foreign key is always required in it.

6.2 Constraint preservation

Mapping does not guarantee that every conceptual rule is directly enforceable by ordinary table constraints. Primary keys, foreign keys, UNIQUE, NOT NULL, and CHECK constraints handle many row-level rules. Cross-row rules, minimum child counts, temporal overlaps, and aggregate limits may require transactions, triggers, deferred constraints, or application services.

For example, a foreign key ensures that every ORDER_LINE references an existing SALES_ORDER. It does not ensure that every SALES_ORDER has at least one line. Enforcing the latter requires controlling the order lifecycle, such as allowing an empty draft but permitting transition to submitted only after a line exists.

6.3 Normalization after mapping

An ER model and normalization address related but distinct concerns. After mapping, designers should examine functional dependencies. The notation X → Y means that equal values of attribute set X imply equal values of attribute set Y within every valid relation state.

If CourseID determines CourseTitle, storing CourseTitle repeatedly in ENROLLMENT creates redundancy and update anomalies. It belongs in COURSE unless a historical snapshot is intentionally required. Third normal form or Boyce–Codd normal form is commonly sought, although deliberate denormalization may be justified by measured performance or audit requirements.

7. Worked Example: Sales Orders

Assume the following validated rules:

• A customer may place zero or more orders.
• Each order belongs to exactly one customer.
• A product may appear on zero or more order lines.
• Each line refers to exactly one order and one product.
• Quantity is a positive integer.
• UnitPrice records the price agreed at the time of sale.

The many-to-many relationship between SALES_ORDER and PRODUCT becomes ORDER_LINE. The following script is independently executable in PostgreSQL and modern MySQL when run in the shown order. It explicitly creates PRODUCT before any foreign key references it.

CREATE TABLE customer (
    customer_id INTEGER PRIMARY KEY,
    customer_name VARCHAR(120) NOT NULL,
    email VARCHAR(254) NOT NULL UNIQUE
);

CREATE TABLE product (
    product_id INTEGER PRIMARY KEY,
    product_name VARCHAR(160) NOT NULL,
    current_price DECIMAL(12,2) NOT NULL,
    CONSTRAINT ck_product_price CHECK (current_price >= 0)
);

CREATE TABLE sales_order (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date DATE NOT NULL,
    order_status VARCHAR(20) NOT NULL,
    CONSTRAINT fk_order_customer
        FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
    CONSTRAINT ck_order_status
        CHECK (order_status IN ('DRAFT', 'SUBMITTED', 'CANCELLED'))
);

CREATE TABLE order_line (
    order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    unit_price DECIMAL(12,2) NOT NULL,
    PRIMARY KEY (order_id, product_id),
    CONSTRAINT fk_line_order
        FOREIGN KEY (order_id) REFERENCES sales_order(order_id),
    CONSTRAINT fk_line_product
        FOREIGN KEY (product_id) REFERENCES product(product_id),
    CONSTRAINT ck_line_quantity CHECK (quantity > 0),
    CONSTRAINT ck_line_price CHECK (unit_price >= 0)
);

The primary key permits a product to appear only once per order. If separate lines for the same product are legitimate, LineNumber could be added and the primary key changed to (OrderID, LineNumber). This illustrates why key selection must follow business meaning rather than convention.

8. Illustrative Case: CityLearn Course Registration

Background

CityLearn is a fictional higher-education provider replacing spreadsheets used for course registration. A course is a catalog definition, while a course offering is a scheduled delivery of that course during a term.

Problem

The spreadsheets confuse courses with offerings, repeat student details, allow duplicate registrations, and contain grades without a clear term or section. Managers cannot reliably determine enrollment counts or available capacity.

Analysis

Stakeholder workshops establish these rules:

• A course may have zero or many offerings.
• Each offering is for exactly one course and occurs in exactly one term.
• A term may contain zero or many offerings.
• An instructor may teach zero or many offerings.
• An offering may initially have no assigned instructor but has at most one instructor.
• Students and offerings have a many-to-many relationship resolved by ENROLLMENT.
• A student may enroll at most once in a given offering.
• Capacity must be greater than zero.
• A grade may be absent until assessment is completed.

Approach and logical solution

The central logical schema is:

The mandatory CourseID and TermID values correctly implement the rule that every offering participates in exactly one course and exactly one term.

CREATE TABLE student (
    student_id INTEGER PRIMARY KEY,
    student_name VARCHAR(120) NOT NULL,
    email VARCHAR(254) NOT NULL UNIQUE
);

CREATE TABLE course (
    course_id INTEGER PRIMARY KEY,
    course_code VARCHAR(20) NOT NULL UNIQUE,
    course_title VARCHAR(160) NOT NULL
);

CREATE TABLE term (
    term_id INTEGER PRIMARY KEY,
    term_name VARCHAR(60) NOT NULL UNIQUE,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    CONSTRAINT ck_term_dates CHECK (end_date >= start_date)
);

CREATE TABLE instructor (
    instructor_id INTEGER PRIMARY KEY,
    instructor_name VARCHAR(120) NOT NULL,
    email VARCHAR(254) NOT NULL UNIQUE
);

CREATE TABLE course_offering (
    offering_id INTEGER PRIMARY KEY,
    course_id INTEGER NOT NULL,
    term_id INTEGER NOT NULL,
    instructor_id INTEGER,
    section_number VARCHAR(12) NOT NULL,
    capacity INTEGER NOT NULL,
    CONSTRAINT uq_offering UNIQUE (course_id, term_id, section_number),
    CONSTRAINT fk_offering_course
        FOREIGN KEY (course_id) REFERENCES course(course_id),
    CONSTRAINT fk_offering_term
        FOREIGN KEY (term_id) REFERENCES term(term_id),
    CONSTRAINT fk_offering_instructor
        FOREIGN KEY (instructor_id) REFERENCES instructor(instructor_id),
    CONSTRAINT ck_offering_capacity CHECK (capacity > 0)
);

CREATE TABLE enrollment (
    student_id INTEGER NOT NULL,
    offering_id INTEGER NOT NULL,
    enrolled_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) NOT NULL,
    grade VARCHAR(5),
    PRIMARY KEY (student_id, offering_id),
    CONSTRAINT fk_enrollment_student
        FOREIGN KEY (student_id) REFERENCES student(student_id),
    CONSTRAINT fk_enrollment_offering
        FOREIGN KEY (offering_id) REFERENCES course_offering(offering_id),
    CONSTRAINT ck_enrollment_status
        CHECK (status IN ('ENROLLED', 'WITHDRAWN', 'COMPLETED'))
);

Outcome

The schema separates stable catalog data from term-specific delivery, prevents duplicate enrollment, and preserves optional instructor assignment and grading. Enrollment cannot exceed capacity through these constraints alone because that rule depends on counting multiple rows. CityLearn must enforce capacity in a concurrency-safe transaction that locks or atomically updates the relevant offering.

Learning points

• Similar names do not imply identical entities: COURSE and COURSE_OFFERING have different identities and lifecycles.
• NOT NULL foreign keys implement mandatory participation from the child side.
• Composite keys can directly express relationship uniqueness.
• Aggregate rules such as capacity require more than ordinary row constraints.
• Models must be validated using normal cases, exceptions, and concurrent operations.

9. Notation and Communication

Crow’s Foot notation is widely used in relational database practice. Chen notation represents entities with rectangles, relationships with diamonds, and attributes with ovals. UML class diagrams use classes, associations, and multiplicities such as 0..* and 1. Each notation can represent similar semantics, but symbols must not be mixed without a legend.

A model should include definitions, not only shapes. Reviewers should be able to answer:

• What does each entity represent?
• Which identifiers are stable and unique?
• Which participation rules are mandatory?
• Are relationship attributes located correctly?
• What happens when records are retired or deleted?
• Which rules cannot be implemented declaratively?

10. Governance, Ethics, and Security

A data model determines what an organization can record, infer, retain, and expose. Modeling is therefore also a governance activity.

Personal data should be limited to justified purposes. Sensitive attributes may require stronger authorization, encryption, masking, auditing, or separation into restricted relations. Designers should document retention and deletion rules, including legal reasons that may require preservation. A supposedly optional demographic field can still create discrimination or re-identification risks.

Deletion behavior must reflect policy. Cascading deletion is convenient but dangerous for financial, educational, or clinical history. Status-based retirement, anonymization, or restricted deletion may be preferable. Constraints should also prevent orphaned records and ambiguous ownership.

Naming and domain definitions should use inclusive, precise language. Models must account for legitimate variation rather than forcing people or organizations into unjustified categories.

11. Contemporary Practice and AI-Assisted Modeling

Modern teams increasingly use schema-as-code, migration tools, model repositories, data catalogs, and automated checks in continuous integration. Version-controlled models make changes reviewable and traceable. Database reverse-engineering tools can reveal implemented structures, while data profiling can identify undeclared uniqueness, null patterns, and inconsistent domains.

Generative AI can propose entities, SQL DDL, or documentation from requirements. It can also invent rules, omit optionality, select unstable keys, or generate constraints unsupported by the target DBMS. AI output should therefore be treated as a draft. Domain experts must validate semantics, engineers must test generated SQL, and sensitive production data should not be disclosed to unauthorized AI services.

12. Common Challenges and Best Practices

Frequent modeling failures include treating reports as entities, storing multiple values in one column, confusing an entity with its current state, overlooking history, and assuming that application validation replaces database integrity.

Recommended practices are:

1. Begin with business language and define every term.
2. Record rules in both directions for every relationship.
3. Distinguish missing, unknown, not applicable, and not yet recorded values.
4. Preserve natural uniqueness even when surrogate keys are used.
5. Resolve many-to-many relationships explicitly.
6. Test the model with realistic examples and boundary cases.
7. Document rules that require transactions, triggers, or services.
8. Review privacy, retention, ownership, and deletion semantics.
9. Separate conceptual meaning from premature physical optimization.
10. Keep diagrams, dictionaries, migration scripts, and deployed schemas synchronized.

Chapter Summary

Conceptual modeling captures domain meaning through entities, attributes, identifiers, relationships, cardinalities, and optionality. Logical modeling transforms those concepts into relations with keys and integrity constraints. Strong, weak, associative, recursive, and subtype entities address different identity and relationship structures.

Minimum and maximum participation must be interpreted independently and in both relationship directions. During relational mapping, mandatory many-side participation normally becomes a NOT NULL foreign key, while many-to-many relationships become associative relations. Some rules, including minimum child counts, capacity limits, and temporal restrictions, cannot be enforced through basic table constraints alone.

Effective models arise from traceable requirements, stakeholder validation, normalization analysis, governance review, and careful implementation. Modeling tools and AI can improve productivity, but they cannot replace semantic judgment or domain accountability.

Key Terms

Alternate key: A candidate key not selected as the primary key.

Associative entity: An entity that represents a many-to-many relationship and may contain relationship attributes.

Attribute: A named property describing an entity or relationship.

Business rule: A precise and testable statement governing data or operations.

Candidate key: A minimal set of attributes that uniquely identifies a relation row.

Cardinality: The maximum number of relationship occurrences in which an entity occurrence may participate.

Composite key: A key formed from two or more attributes.

Conceptual model: A technology-independent representation of domain concepts and relationships.

Entity: A distinguishable occurrence about which data is maintained.

Foreign key: An attribute set whose values reference a candidate key in another or the same relation.

Functional dependency: A constraint in which one attribute set determines another.

Identifier: An attribute or minimal attribute set that distinguishes entity occurrences.

Logical model: A representation of data using structures of a chosen data model, such as relations and keys.

Optionality: The minimum participation of an entity in a relationship.

Primary key: The candidate key selected as the principal row identifier.

Recursive relationship: A relationship among occurrences of the same entity type.

Subtype: A specialized entity type inheriting the identity and common properties of a supertype.

Surrogate key: An identifier generated without inherent domain meaning.

Weak entity: An entity identified using its owner’s key together with a partial key.

Review Questions

Short Answer Questions

1. Distinguish conceptual, logical, and physical data models.
2. What is the difference between cardinality and optionality?
3. Why does a surrogate key not eliminate a business uniqueness constraint?
4. What characterizes a weak entity?
5. Why can a nullable foreign key fail to implement mandatory participation?

Long Answer Questions

1. Explain a defensible process for eliciting, documenting, validating, and tracing business rules.
2. Compare one-to-one, one-to-many, and many-to-many relationships and explain their relational implementations.
3. Discuss the advantages and disadvantages of natural, composite, and surrogate keys.
4. Explain how supertypes and subtypes can be mapped into relations, including relevant trade-offs.
5. Evaluate the relationship between ER modeling, normalization, and integrity-constraint design.

Analytical / Application Questions

1. Model a library in which members borrow copies of books. Distinguish BOOK from BOOK_COPY, state all cardinalities and optionalities, and transform the model into relations.
2. A hospital records doctors, patients, appointments, and diagnoses. Identify which concepts are entities or associative entities, propose keys, and explain how historical diagnoses should be preserved.
3. Extend the CityLearn design to support team teaching and prerequisite courses. State the new business rules, resolve resulting many-to-many or recursive relationships, and identify constraints that require more than primary and foreign keys.

Multiple Choice Questions with Answers

Question 1

Which statement best distinguishes a conceptual model from a logical relational model?

A. A conceptual model defines indexes, while a logical model defines partitions.

B. A conceptual model captures domain meaning, while a logical model organizes that meaning into relations, keys, and constraints.

C. A conceptual model is always DBMS-specific, while a logical model is never technology-dependent.

D. A conceptual model contains SQL, while a logical model contains only business prose.

Correct Answer: B. A conceptual model captures domain meaning, while a logical model organizes that meaning into relations, keys, and constraints.

Question 2

In Crow’s Foot notation, which endpoint correctly represents zero-to-many participation?

A. An optionality circle followed by a crow’s foot, with no mandatory-participation bar

B. Two mandatory-participation bars

C. A mandatory-participation bar followed by a crow’s foot

D. An optionality circle followed by exactly-one bars

Correct Answer: A. An optionality circle followed by a crow’s foot, with no mandatory-participation bar

Question 3

Each COURSE_OFFERING must belong to exactly one COURSE. Which logical design most directly implements this rule?

A. Place a nullable OfferingID foreign key in COURSE.

B. Place a nullable CourseID foreign key in COURSE_OFFERING.

C. Place a NOT NULL CourseID foreign key in COURSE_OFFERING.

D. Place CourseID in ENROLLMENT only.

Correct Answer: C. Place a NOT NULL CourseID foreign key in COURSE_OFFERING.

Question 4

Why is ENROLLMENT modeled as an associative entity between STUDENT and COURSE_OFFERING?

A. It converts a one-to-one relationship into a recursive relationship.

B. It resolves a many-to-many relationship and can store attributes such as status and grade.

C. It eliminates the need for student identifiers.

D. It ensures every offering reaches its capacity.

Correct Answer: B. It resolves a many-to-many relationship and can store attributes such as status and grade.

Question 5

Which primary key is appropriate for a weak DEPENDENT entity identified by its owning employee and a dependent number unique only within that employee?

A. DependentNumber only

B. EmployeeID only

C. EmployeeID and DependentNumber

D. EmployeeName and DependentNumber

Correct Answer: C. EmployeeID and DependentNumber

Question 6

A table uses a surrogate CourseID as its primary key, but CourseCode must also be unique. What additional constraint is required?

A. CHECK(CourseID = CourseCode)

B. UNIQUE(CourseCode)

C. A nullable foreign key on CourseCode

D. No additional constraint is required.

Correct Answer: B. UNIQUE(CourseCode)

Question 7

Which rule cannot generally be guaranteed by a foreign key from ORDER_LINE to SALES_ORDER alone?

A. Every order line references an existing order.

B. Every order line stores an order identifier.

C. Every sales order contains at least one order line.

D. An order line cannot reference a nonexistent order.

Correct Answer: C. Every sales order contains at least one order line.

Question 8

In the CityLearn design, why is InstructorID nullable in COURSE_OFFERING?

A. An offering may temporarily have no assigned instructor.

B. An instructor may teach several offerings.

C. Instructor identifiers are not unique.

D. Every offering must have several instructors.

Correct Answer: A. An offering may temporarily have no assigned instructor.

Question 9

What does the functional dependency CourseID → CourseTitle mean?

A. A course title must identify exactly one student.

B. Equal CourseID values must have equal CourseTitle values in every valid relation state.

C. CourseID and CourseTitle must both be foreign keys.

D. CourseTitle must be stored in every enrollment row.

Correct Answer: B. Equal CourseID values must have equal CourseTitle values in every valid relation state.

Question 10

Which statement about AI-assisted data modeling is most appropriate?

A. Generated schemas can be deployed without review if they compile.

B. AI removes the need to consult domain experts.

C. AI output should be validated for semantics, constraints, DBMS compatibility, and privacy.

D. Production personal data should always be supplied to public AI services for better results.

Correct Answer: C. AI output should be validated for semantics, constraints, DBMS compatibility, and privacy.