3.2Database Design and Normalization (Relational Model, E-R Diagrams, Normalization (1NF-3NF), Primary/Foreign Keys, Referential Constraints)

Database design is one of the most consistently tested areas in the AP exam's technology domain. Where FE centered on the result of normalization (which tables it splits into), AP goes further, testing whether you can explain why that split is necessary in terms of functional dependency, and whether you can correctly model business requirements as an E-R diagram. Being able to judge the difference between "partial functional dependency" and "transitive functional dependency" from a concrete example — not just by memorizing the terms — is the key to scoring well.

3.2.1E-R diagrams and data modeling

• An E-R diagram (entity-relationship diagram) is a modeling technique that depicts business objects as entities and the connections between them as relationships. Entities have attributes (columns), and relationships carry a cardinality — one-to-one, one-to-many, or many-to-many.
• A many-to-many relationship cannot be represented directly in a relational table, so an associative entity (a junction table) holding both sides' primary keys is introduced, decomposing the many-to-many relationship into two one-to-many relationships. Example: "students" and "courses" are many-to-many, but inserting an associative entity, "enrollments" (student ID, course ID, grade), represents it correctly.
• Data modeling is the process of organizing business requirements and extracting entities, attributes, and relationships into an E-R diagram. Reconciling inconsistent naming used in the business (e.g., "customer" and "client" that actually refer to the same object) and avoiding duplicate entities is what determines design quality.

3.2.2Normalization and judging functional dependency

• Functional dependency is a relationship where the value of one column (the determinant) uniquely determines the value of another (the dependent) — e.g., "product ID -> product name." First normal form (1NF) eliminates repeating groups so every cell holds a single value. Second normal form (2NF) additionally separates out columns that depend on only part of a composite primary key (a partial functional dependency).
• Third normal form (3NF) additionally separates out columns that depend on a non-key column (a transitive functional dependency). The judgment procedure is mechanical, in two steps: (1) are there columns not depending on the whole primary key (a partial dependency -> a 2NF violation)? (2) are there columns depending on a non-key column (a transitive dependency -> a 3NF violation)?
• The primary key uniquely identifies a row (no duplicates, no NULLs). The foreign key references another table's primary key. A referential constraint (referential integrity) permits only foreign-key values that actually exist in the referenced table; the DBMS either rejects an update/delete that would violate it (e.g., deleting a parent row first) or, if configured, handles the child rows too via cascading.

Consider designing an E-R diagram for an order-management system. A "customer" entity and a "product" entity are in a many-to-many relationship, since one order can involve multiple products and the same product can be ordered by multiple customers. Because a relational table cannot represent many-to-many directly, a new associative entity, "order line," is introduced holding both primary keys (customer ID, product ID) plus quantity and unit price, decomposing the relationship into two one-to-many relationships: "customer - order line" and "product - order line." Next, suppose this "order line" table is given a composite primary key of (order ID, product ID), and, alongside quantity and unit price, the "product name" is mistakenly added as a column too. Since the product name depends only on the product ID, which is part of the composite key, and not on the order ID, this is a partial functional dependency — a second normal form violation. It is resolved by splitting out a "product" table (product ID, product name, unit price) and leaving only the product ID as a foreign key in the order-line table. Furthermore, if the "order" table held order ID, customer ID, and customer name together, the customer name depends on the non-key column customer ID rather than the primary key (order ID) — a transitive functional dependency, a third normal form violation. It is resolved by splitting off a "customer" table (customer ID, customer name), leaving the customer ID as a foreign key in the order table. Now, if you try to delete a customer from the "customer" table while that customer's orders still exist in the "order" table, the referential constraint means the DBMS either rejects the delete, or you must configure cascading delete so the orders are removed too. Enabling cascading delete carelessly can cause a business problem where past order history is lost the moment a customer is deleted, so in practice, the design decision to use a logical delete (deactivation via a flag) instead of an actual delete is common.

3.2.3Section summary

• A many-to-many relationship cannot be represented directly — decompose it into two one-to-many relationships via an associative entity. Reconciling business naming drives E-R diagram quality
• Mechanically judge: a partial functional dependency (depending on part of a composite key) = a 2NF violation; a transitive functional dependency (depending on a non-key column) = a 3NF violation
• Foreign key + referential constraint = referential integrity. On delete, decide between rejection and cascading (a logical delete is also an option to preserve history)