Question 1

First normal form (1NF) and multivalued attributes If a relation has no multivalued attributes and contains only atomic values in each column, the relation is in which normal form?

Accepted Answer

Introduction / Context:Normalization is a stepwise method for improving relational design by eliminating redundancy and anomalies. The very first step, First Normal Form (1NF), requires that each field contain only atomic (indivisible) values and that there are no repeating groups or multivalued attributes stored in a single column. Given Data / Assumptions: The relation stores one value per attribute per row (atomicity). No column contains lists, arrays, or delimited multiple values. There are no repeating group columns that vary in count across rows. Concept / Approach: 1NF focuses on atomicity and tabular integrity: rows and columns form a simple table where each intersection holds one value of the appropriate domain. Eliminating multivalued attributes often requires moving them into separate rows or a related child table to preserve atomicity and support efficient querying and indexing. Step-by-Step Solution: Inspect each attribute to ensure it stores a single value per row.Identify any repeating groups or multivalued fields (for example, phone_numbers).Refactor multivalued attributes into a child relation (for example, PersonPhone with one row per number).Confirm that after refactoring, the table meets 1NF requirements. Verification / Alternative check: Normalization theory defines 1NF precisely as the absence of repeating groups and multivalued attributes. Higher normal forms (2NF, 3NF, BCNF, 4NF) address functional and multivalued dependencies beyond atomicity but assume 1NF as a prerequisite. Why Other Options Are Wrong: 2NF and 3NF address partial and transitive dependencies and presuppose 1NF. BCNF refines 3NF with stricter determinant rules. 4NF addresses nontrivial multivalued dependencies but still assumes 1NF first. Common Pitfalls: Keeping comma-separated lists in a single column, which breaks indexing and constraints. Confusing composite attributes with multivalued ones; composite can be split into components, but must still store one value per component. Final Answer: First normal form (1NF)

Question 2

Entity–Relationship (ER) to Relational mapping: When converting a many-to-many unary (recursive) relationship on a single entity into relations, how many separate relations do you typically end up with in the relational schema?

Accepted Answer

Introduction / Context:During logical database design, we translate Entity–Relationship (ER) models into relational tables. A common point of confusion is how to map a many-to-many unary (recursive) relationship, where instances of the same entity relate to each other (for example, Employee mentors Employee). Understanding the correct number of relations ensures a clean, normalized schema. Given Data / Assumptions: The ER model has one entity type participating twice in a many-to-many relationship with itself. We want a normalized relational mapping. Primary keys and foreign keys are used to enforce relationships. Concept / Approach: In relational mapping, a many-to-many relationship is represented by an associative (junction) relation. For a unary many-to-many, we still need a separate relation to store the pairs of related instances, but both foreign keys point to the same parent entity’s primary key. Step-by-Step Solution: Create Relation 1: the base entity table (for example, Employee(emp_id, ...)).Create Relation 2: the associative table (for example, Mentorship(mentor_id, mentee_id, ...)).Define foreign keys: mentor_id and mentee_id both reference Employee(emp_id).Add any attributes of the relationship (for example, start_date) to the associative table. Verification / Alternative check: If you attempt to store the relationship inside the entity table itself, you cannot represent many-to-many pairs without repeating groups or violating normalization. The associative table is necessary and sufficient. Why Other Options Are Wrong: One relation: cannot represent many-to-many pairs cleanly. Three or four relations: unnecessary for a single unary relationship; two suffice. Common Pitfalls: Forgetting to add a composite key or unique constraint on (mentor_id, mentee_id) to prevent duplicates; confusing unary with binary relationships. Final Answer: Two relations are created.

Question 3

Relational database keys: Which option best defines a foreign key in the context of relational schema design and referential integrity?

Accepted Answer

Introduction / Context:Foreign keys are crucial for enforcing referential integrity between related tables. They ensure that every child row points to a valid parent row, which preserves consistency and prevents orphaned records in relational databases. Given Data / Assumptions: We have at least two relations (tables) with a logical parent–child relationship. The parent relation has a primary key that uniquely identifies each row. The child relation stores a reference to that parent key. Concept / Approach: A foreign key is an attribute (or set of attributes) in the child relation whose values must match existing values of the parent relation’s primary key. This constraint enforces valid references and is central to normalized design. Step-by-Step Solution: Identify the parent relation and its primary key (for example, Customer(customer_id)).Place the foreign key in the child relation (for example, Orders(customer_id)).Define the foreign key constraint so Orders.customer_id references Customer.customer_id. Verification / Alternative check: Try inserting a child row with a non-existent parent key; the database should reject it. Deleting a parent may require cascading or restriction to preserve integrity. Why Other Options Are Wrong: Any attribute: too vague; not all attributes are foreign keys. Same as primary key: different concepts; one enforces identity, the other enforces reference. Serves no purpose: incorrect; foreign keys are essential for data integrity. Common Pitfalls: Mismatched data types between PK and FK, missing indexes on foreign keys, and failing to handle deletes/updates appropriately. Final Answer: An attribute that serves as the primary key of another relation

Question 4

Primary key fundamentals: In a properly designed relation, a primary key is best described as which of the following?

Accepted Answer

Introduction / Context:The primary key (PK) is the cornerstone of relational design. It guarantees that each tuple (row) is unique and provides a stable target for foreign key references from other tables, enabling consistent joins and integrity. Given Data / Assumptions: Each relation represents one entity type or a well-defined conceptual grouping. The PK may be a single attribute or a composite of multiple attributes. PK attributes are non-null and unique. Concept / Approach: A primary key is a minimal set of attributes whose combined values uniquely identify each row. The database enforces uniqueness and non-nullability, providing a reliable mechanism to locate and associate records across the schema. Step-by-Step Solution: Choose candidate keys (attributes that can uniquely identify rows).Select one candidate to serve as the primary key based on stability and simplicity.Apply NOT NULL and UNIQUE semantics; create indexes as needed. Verification / Alternative check: Check that count(rows) equals count(DISTINCT PK) and that no PK value is NULL. This confirms correctness of the chosen PK. Why Other Options Are Wrong: Any attribute: not necessarily unique. Uniquely identifies each column: columns are identified by names, not runtime values. Derived attribute: derivation does not imply uniqueness or suitability as PK. Common Pitfalls: Choosing volatile attributes (like email) as PKs; better to use stable natural keys or surrogate keys. Final Answer: An attribute that uniquely identifies each row

Question 5

Relational model basics: In classical relational theory, a relation is most appropriately viewed as which of the following structures?

Accepted Answer

Introduction / Context:Although mathematically a relation is a set of tuples over a set of attributes, in practical database systems we visualize relations as tables. This tabular view helps practitioners reason about rows, columns, and constraints. Given Data / Assumptions: We consider the standard implementation of the relational model in SQL systems. Rows represent tuples (records) and columns represent attributes (fields). The table metaphor maps directly to operations like SELECT, INSERT, UPDATE, and DELETE. Concept / Approach: A two-dimensional table has rows and columns. This model suits set-based operations, keys, and relational algebra concepts (selection, projection, join). Higher “dimensions” are unnecessary for representing classical relations. Step-by-Step Solution: Map attributes → columns.Map tuples → rows.Treat the entire relation as a set with constraints (keys, FKs, checks). Verification / Alternative check: Relational algebra and SQL statements operate on two-dimensional structures (tables/views). Multidimensional cubes belong to OLAP, not core relational theory. Why Other Options Are Wrong: Three/four/five-dimensional tables are not standard relational representations; those terms apply to analytical models, not base relations. Common Pitfalls: Confusing star/snowflake OLAP cubes with normalized OLTP tables. Final Answer: two-dimensional tables.

Question 6

Properties of a well-formed relation (table) in the relational model: Which statement is true for every relation?

Accepted Answer

Introduction / Context:Relational tables obey certain structural properties that simplify querying and ensure clarity. Recognizing these properties helps avoid design mistakes and ambiguous schemas. Given Data / Assumptions: Relations are named structures consisting of attributes (columns) and tuples (rows). Schema-level naming and constraints matter for correctness and usability. Set semantics reduce reliance on ordering. Concept / Approach: Every relation must have a unique name within its schema/database catalog to avoid ambiguity. Rows should be unique (enforced logically by keys), and column names must be unique within a table to reference them unambiguously. Row and column order is not semantically significant in relational theory (SQL implementations may display a default order, but semantics do not depend on it). Step-by-Step Solution: Check name uniqueness at the schema/catalog level.Enforce row uniqueness via keys, not by row order.Ensure attribute names are unique within a relation. Verification / Alternative check: Attempt to create two tables with the same name; the DBMS rejects duplicates. Attempt to reference ambiguous column names; SQL requires disambiguation or prohibits duplicates in the same table. Why Other Options Are Wrong: Rows “not unique”: incorrect—keys enforce uniqueness. Duplicate attribute names: invalid; leads to ambiguity. Column order significant: relational semantics do not depend on order. Common Pitfalls: Relying on implicit row order in queries; always use ORDER BY to define output order. Final Answer: Each relation has a unique name.

Question 7

ER to Relational mapping for binary many-to-many relationships: When converting a binary many-to-many relationship into relations, how many relations will typically be present in the final schema (including participating entities)?

Accepted Answer

Introduction / Context:Binary many-to-many (M:N) relationships are common in ER models (for example, Students enroll in Courses). Correctly mapping them prevents redundancy and preserves the ability to store multiple associations between the same pair of entities with additional attributes (like enrollment_date or grade). Given Data / Assumptions: Two distinct entity types participate in an M:N relationship. We seek a normalized relational mapping. Relationship attributes (if any) must be preserved. Concept / Approach: For a binary M:N, we create an associative (junction) relation in addition to the two entity relations. The associative table stores pairs of foreign keys referencing each entity’s primary key and can include relationship attributes. Step-by-Step Solution: Create Entity Relation A (for example, Student(student_id, ...)).Create Entity Relation B (for example, Course(course_id, ...)).Create Associative Relation AB (for example, Enrollment(student_id, course_id, grade, ...)).Define foreign keys from AB to A and B; add a composite PK or unique constraint. Verification / Alternative check: Without a third relation, you cannot represent many pairs without repeating groups. The associative relation is necessary and sufficient. Why Other Options Are Wrong: One/Two relations: cannot capture many-to-many multiplicity properly. Four relations: unnecessary overhead for a simple binary M:N. Common Pitfalls: Omitting a composite key in the associative table; failing to migrate relationship attributes into the associative relation. Final Answer: Three relations are created.

Question 8

Entity integrity rule in the relational model: Which statement correctly expresses the entity integrity constraint for primary keys?

Accepted Answer

Introduction / Context:Entity integrity is a foundational rule in relational databases that ensures each row represents a real, uniquely identifiable entity instance. It specifies how primary keys must behave to guarantee unambiguous identification. Given Data / Assumptions: Every table has a designated primary key. Primary keys are used as targets for foreign keys from other tables. Null represents the absence of a value and cannot identify a row. Concept / Approach: The entity integrity rule states that a primary key must be unique and contain no null values. Nulls would imply “unknown” or “not applicable,” which violates the identity requirement of the key. Composite primary keys are allowed; uniqueness is essential, not single-column status. Step-by-Step Solution: Define PK attributes as NOT NULL.Enforce uniqueness on the PK (single or composite).Use FK constraints in child tables to reference the PK. Verification / Alternative check: Attempt to insert a row with a NULL in any PK component; the DBMS should reject it. Check that duplicate PK values are also rejected. Why Other Options Are Wrong: “No composite PK”: false—composite keys are valid. “No PK may be unique”: false—the PK must be unique. “PK may not equal FK values”: FKs commonly equal PK values; that is the point of referencing. Common Pitfalls: Misusing nullable surrogate keys; allowing NULLs in parts of composite keys; failing to index PKs for performance. Final Answer: no primary key attribute may be null.

Question 9

Relational model composition: Which option best characterizes the relational model at a high level?

Accepted Answer

Introduction / Context:The relational model organizes data using relations, which most SQL practitioners represent as tables. This structure underpins relational algebra and the set-oriented operations performed by SQL queries. Given Data / Assumptions: Relations map to tables with rows and columns. Constraints and keys enforce integrity. SQL is the standard language for defining and manipulating relations. Concept / Approach: In the relational paradigm, data is structured into normalized tables, reducing redundancy and anomalies. Queries operate on these tables through declarative set-based expressions rather than procedural row-by-row code. Step-by-Step Solution: Model entities and relationships as relations (tables).Define keys and constraints to preserve integrity.Use SQL to select, join, and aggregate data across tables. Verification / Alternative check: Inspect any mainstream RDBMS catalog; objects are tables, constraints, indexes, and views operating over tabular data. Why Other Options Are Wrong: “Data redundancy”: normalization aims to reduce redundancy. “Non-SQL languages”: SQL is standard for relational operations. “Unorganized data”: the model is highly structured. Common Pitfalls: Confusing unstructured document stores with relational databases; ignoring normalization principles. Final Answer: data in the form of tables.

Question 10

Recognizing normal forms: If a relation has no multivalued attributes and also contains no partial dependencies (with respect to any candidate key), in which normal form is the relation?

Accepted Answer

Introduction / Context:Normalization progresses through successive normal forms, each removing specific sources of redundancy and anomalies. Understanding the criteria for first, second, and higher normal forms helps analysts evaluate schema quality. Given Data / Assumptions: No multivalued attributes (implies 1NF is satisfied: atomic attributes). No partial dependencies (non-key attribute depending on part of a composite key). Candidate keys may be composite. Concept / Approach: First normal form requires atomic (non-repeating) attributes and no repeating groups. Second normal form requires 1NF and additionally prohibits partial dependencies on a proper subset of a candidate key. Since both conditions are met, the relation is in 2NF (though not necessarily 3NF, which also eliminates certain transitive dependencies). Step-by-Step Solution: Confirm 1NF: verify all attributes are atomic and there are no repeating groups.Check for partial dependencies: ensure no non-key attribute depends only on part of a composite key.Conclude 2NF status if both checks pass; further check transitive dependencies for 3NF. Verification / Alternative check: Analyze determinants of each non-key attribute; if each depends on the whole key (and attributes are atomic), the relation is at least 2NF. Why Other Options Are Wrong: 1NF: too weak; we also removed partial dependencies. 3NF/4NF: require additional conditions (transitive dependencies and multivalued dependencies, respectively) not given here. Common Pitfalls: Assuming 2NF implies 3NF automatically; overlooking transitive dependencies such as key → attribute1 and attribute1 → attribute2. Final Answer: Second normal form

Question 11

Understanding transitive dependency: Which option best describes a transitive dependency within a relation?

Accepted Answer

Introduction / Context:Transitive dependencies create hidden redundancy by allowing a non-key attribute to determine another non-key attribute indirectly through the primary key. Recognizing and removing them is key to achieving Third Normal Form (3NF) or Boyce–Codd Normal Form (BCNF). Given Data / Assumptions: The relation is at least in 1NF. We focus on dependencies among attributes. Key attributes determine non-key attributes directly; the issue arises when non-key attributes determine other non-key attributes. Concept / Approach: A transitive dependency occurs when key → A and A → B, where A and B are non-key attributes. Thus, key → B holds transitively via A. This creates redundancy and potential anomalies, because changing A may require updating B everywhere A appears. Step-by-Step Solution: Identify key → non-key dependencies (normal and expected).Look for non-key → non-key dependencies (A → B).If key → A and A → B with A, B non-key, then B is transitively dependent on the key. Verification / Alternative check: Group by A and check whether B varies within those groups; if not, A determines B. Combined with key → A, that implies a transitive path key → B. Why Other Options Are Wrong: Dependencies among key attributes are not the target; keys are determinants by definition. “In 1NF/2NF” describes normal forms, not the dependency type itself. Common Pitfalls: Confusing derived/calculated values with dependencies; mixing up partial and transitive dependencies in composite-key situations. Final Answer: A functional dependency between two or more nonkey attributes.

Question 12

ER-to-Relational Mapping — Regular (Strong) Entity When mapping a regular (strong) entity type from an entity–relationship model into the relational model, which statement correctly describes how many relations (tables) are created for that single e…

Accepted Answer

Introduction:Converting an entity–relationship (ER) design into a relational schema is a foundational database skill. A common question is how many relations (tables) result from mapping a single regular, also called strong, entity. Understanding this baseline helps you reason about more complex cases such as weak entities, multivalued attributes, and associative entities. Given Data / Assumptions: The source construct is a regular (strong) entity with a single primary key attribute or a composite key. Attributes are atomic and already satisfy first normal form. No multivalued attributes or repeating groups are present for this specific mapping step. Concept / Approach:The standard rule is straightforward: one regular entity becomes one base relation. All simple attributes of the entity become columns of that relation. The entity’s identifier becomes the relation’s primary key. Only if additional modeling features exist (for example, multivalued attributes) would extra relations be introduced, but those are separate steps, not part of the core strong-entity mapping. Step-by-Step Solution:Create a relation named after the entity (or a clear, singularized name).Place every simple attribute as a column in that relation.Designate the entity identifier as the primary key of the relation.Do not create extra relations unless you encounter multivalued attributes, weak entities, or relationships requiring separate tables. Verification / Alternative check:Check any introductory database textbook procedure: strong entity to single relation is the canonical first mapping step. Any departure implies the presence of special attributes or relationships that trigger additional rules. Why Other Options Are Wrong: Two, three, or four relations imply extra constructs (multivalued attributes, associative entities) not stated here. A view is not the standard product of ER-to-relational mapping for base data; a base relation is. Common Pitfalls:Accidentally creating additional tables for simple attributes or failing to declare the appropriate primary key after mapping. Final Answer:One relation is created.

Question 13

ER-to-Relational Mapping — Ternary Relationship with an Associative Entity When mapping a ternary relationship that has been modeled with an associative (intersection) entity, which statement correctly describes the number of relations (tables) crea…

Accepted Answer

Introduction:Ternary relationships connect three entity types simultaneously. In many methodologies, especially when there are attributes on the relationship or business rules to capture, we model a ternary relationship as an associative entity. Understanding how this maps into the relational model ensures correct representation of the three-way association and its attributes. Given Data / Assumptions: There are three regular entity types participating, each mapped to its own relation. The ternary relationship is represented as an associative entity with its own attributes (possibly including a surrogate key). Standard ER-to-relational rules are applied. Concept / Approach:Each of the three regular entities becomes one relation. The associative entity, which captures the ternary relationship and any attributes on that relationship, becomes a fourth relation. The associative relation typically includes foreign keys referencing the three participating entity relations, and may define a composite primary key or a surrogate primary key with unique constraints over the three foreign keys. Step-by-Step Solution:Map Entity A to Relation A, Entity B to Relation B, and Entity C to Relation C.Create Relation R for the associative entity representing the ternary relationship.Include foreign keys in R: A_id, B_id, C_id referencing A, B, C respectively.Define the primary key as either (A_id, B_id, C_id) or a surrogate key with a unique constraint on those three columns. Verification / Alternative check:Compare to the mapping for binary relationships with an associative entity: the count increases by one for each additional base entity and one for the associative entity itself. For ternary, that totals four relations (three entities plus one associative relation). Why Other Options Are Wrong: One or two relations ignore the necessary representation of all participants. Three relations omit the associative entity relation. Conditional creation based on mandatory participation does not remove the need for the associative relation. Common Pitfalls:Trying to decompose a ternary directly into three separate binaries without preserving the intended three-way constraint, which can change semantics. Final Answer:Four relations are created.

Question 14

ER-to-Relational Mapping — Supertypes and Subtypes When mapping a supertype/subtype hierarchy into relations, which statement correctly captures the key assignment across the hierarchy?

Accepted Answer

Introduction:Supertypes and subtypes model specialization. In the relational mapping, maintaining identity across the hierarchy is critical. The conventional approach is to propagate the supertype’s identifier into each subtype, preserving a one-to-one correspondence of rows that share the same identity. Given Data / Assumptions: A single supertype has one or more subtypes. Each subtype row represents a specialized form of the supertype row. The mapping should preserve shared identity and enable referential integrity. Concept / Approach:The standard strategy assigns the supertype’s primary key to every subtype as both the primary key and a foreign key referencing the supertype relation. This guarantees that each subtype row corresponds to exactly one supertype row. Alternatives, such as independent surrogate keys in subtypes, complicate identity management and break the clean one-to-one mapping. Step-by-Step Solution:Create a relation for the supertype with its primary key.Create a relation for each subtype.Include the supertype key in each subtype relation as its primary key.Declare that key also as a foreign key referencing the supertype relation. Verification / Alternative check:Check entity counts and identity flows: a subtype instance cannot exist without the corresponding supertype instance when total participation applies, and the shared key enforces that relationship neatly. Why Other Options Are Wrong: Assigning subtype keys upward breaks identity semantics. Claiming no link or no PK/FK relationship ignores the core integrity requirement. Using unrelated surrogate keys in subtypes loses the one-to-one coupling by default. Common Pitfalls:Forgetting to declare the subtype key as both primary and foreign key, which weakens integrity across the hierarchy. Final Answer:The supertype primary key is assigned to each subtype.

Question 15

ER-to-Relational Mapping — Multivalued Attribute When converting a multivalued attribute of an entity into the relational model, which statement correctly describes the number of relations created for that entity and attribute?

Accepted Answer

Introduction:Multivalued attributes violate first normal form because a single attribute can store multiple values for one entity instance. The standard relational fix is to split those values into a separate relation so each value is captured as a distinct row, preserving atomicity and enabling proper constraints and indexing. Given Data / Assumptions: We have a regular entity with a multivalued attribute (for example, phone numbers for a customer). We are applying standard ER-to-relational mapping rules. No additional complexities such as composite multivalued attributes are assumed. Concept / Approach:The entity maps to its own relation. The multivalued attribute maps to a second relation that includes a foreign key referencing the entity’s primary key and a column for the attribute’s single atomic value. The primary key of the second relation is usually composite (entity key plus the attribute value or a sequence number), or a surrogate key with a unique constraint paired with the foreign key and value. Step-by-Step Solution:Create Relation E for the entity, with its primary key.Create Relation E_A for the multivalued attribute.Include E’s primary key in E_A as a foreign key.Store one attribute value per row in E_A to satisfy first normal form. Verification / Alternative check:Querying the separate relation confirms that each attribute value is atomic and independently constrainable (for example, uniqueness within the parent scope). Why Other Options Are Wrong: One relation keeps repeating values in a single row and violates first normal form. Three or four relations add unnecessary complexity for a single multivalued attribute. Views do not replace the need for base storage structures. Common Pitfalls:Using multiple similarly named columns (Phone1, Phone2, Phone3) instead of a proper child table, which hampers scalability and constraints. Final Answer:Two relations are created.

Question 16

Entity–Relationship (ER) to relational mapping: Do weak entities get mapped into their own relation (table), or do they not get mapped at all?

Accepted Answer

Introduction / Context:This item checks your understanding of how weak entities in an Entity–Relationship (ER) model are transformed (mapped) into relational tables. Weak entities are those that do not have a full primary key by themselves and rely on a strong (owner) entity for identification. The question asks whether weak entities do not get mapped into a relation at all. Given Data / Assumptions: A weak entity has a partial key (sometimes called a discriminator). A weak entity participates in an identifying relationship with a strong entity. Relational mapping aims to produce tables with primary keys and foreign keys that preserve identification and referential integrity. Concept / Approach:During ER-to-relational mapping, every entity set—strong or weak—becomes a relation (table). For weak entities, the relation’s primary key is composite: it includes the owner entity’s primary key (as a foreign key) plus the weak entity’s partial key. This preserves the identifying relationship and ensures uniqueness of weak-entity rows relative to their owner. Step-by-Step Solution:Create a relation for the strong (owner) entity with its own primary key.Create a relation for the weak entity with all of its attributes.Add the owner’s primary key attributes to the weak entity’s relation as foreign key columns.Form the weak entity’s primary key by combining the owner’s key columns with the weak entity’s partial key.Declare the identifying foreign key constraint to enforce ownership. Verification / Alternative check:Try a common example such as Order (owner) and OrderLine (weak). OrderLine clearly becomes its own table; its primary key often includes order_id plus line_number, and order_id is a foreign key to Order. This confirms weak entities indeed map to relations. Why Other Options Are Wrong:Depends only on diagram notation: Incorrect because mapping rules are conceptual, not just notation-driven.True for EER only: Incorrect; standard ER mapping already creates a relation for weak entities. Common Pitfalls:Students sometimes think weak entities “merge” into the owner’s table. While occasionally a design can be denormalized that way, the canonical mapping creates a separate relation for the weak entity with an identifying foreign key. Final Answer:Incorrect

Question 17

In a one-to-many (1:M) relationship, does the primary key from the “one” side migrate to the “many” side as a foreign key on the “many” side?

Accepted Answer

Introduction / Context:This question targets a fundamental transformation rule in ER-to-relational mapping: how to place foreign keys when converting a one-to-many relationship into tables. Understanding this rule underpins correct referential integrity and efficient joins. Given Data / Assumptions: The relationship cardinality is 1:M (exactly one instance on one side relates to zero or many on the other). Both participating entity types map to relations with defined primary keys. We want to preserve relationship semantics in the relational schema. Concept / Approach:In a 1:M relationship, the standard mapping rule is to include the primary key of the “one” side as a foreign key attribute in the relation representing the “many” side. This allows each “many” record to reference its unique parent and enables referential integrity constraints to prevent orphaned rows. Step-by-Step Solution:Identify the “one” side relation and its primary key (e.g., customer.customer_id).Identify the “many” side relation (e.g., order).Add customer_id to the order table as a foreign key referencing customer(customer_id).Declare the foreign key constraint and, as needed, on delete/update rules.Use indexes on the foreign key to improve join performance. Verification / Alternative check:Think of common examples: Department (1) to Employee (M). department_id appears in Employee as a foreign key, which aligns with the rule and ensures each employee references one department. Why Other Options Are Wrong:Incorrect: contradicts the core rule for 1:M mapping.True only when both are weak: unnecessary; standard rule applies to regular strong entities.Star schemas only: a star schema is just a specialized pattern; the rule is general. Common Pitfalls:Putting the foreign key on the wrong side (on the “one” side) or attempting to create a separate relationship table, which is unnecessary for pure 1:M and adds complexity. Final Answer:Correct

Question 18

Keys and nullability: May a primary key attribute (or any component of a composite primary key) be null in a properly designed relational table?

Accepted Answer

Introduction / Context:Primary keys uniquely identify rows in a table. This unique identification fails if the key can be null, because null represents “unknown or inapplicable” and does not compare equal to anything, including itself. Hence, nulls are incompatible with strict uniqueness semantics for primary keys. Given Data / Assumptions: A primary key must uniquely identify every row and must be stable and minimal. Database systems enforce NOT NULL on primary key columns by design or by best practice. Composite keys inherit the same rule: no component may be null. Concept / Approach:If a primary key contained null, two issues arise: (1) you could not guarantee uniqueness (because null is not equal to any value); (2) referential integrity could break, as foreign keys referencing null would be ambiguous or invalid for identification. Therefore, primary keys must be defined as NOT NULL. Step-by-Step Solution:Define a primary key column (or set of columns).Apply NOT NULL constraints to all key components.Create unique indexes as needed to enforce uniqueness.Use surrogate keys (IDENTITY, SERIAL, UUID) if natural keys can be missing or unstable.Validate incoming data to prevent nulls in key columns. Verification / Alternative check:Attempt to insert a row with null in a primary key column in a standards-compliant database; the operation will be rejected due to NOT NULL enforced by the primary key constraint. Why Other Options Are Wrong:Correct: contradicts relational rules for primary keys.Allowed only if surrogate: surrogate keys are also primary keys; null still disallowed.Allowed once per table: not true; PK columns can never be null. Common Pitfalls:Confusing “unique but nullable” candidates with primary keys; some DBMSs allow multiple nulls in unique columns, but not in primary key columns. Final Answer:Incorrect

Question 19

Mapping many-to-many relationships: Is the primary key of the new junction (associative) relation typically a composite key formed from the primary keys of the two participating entities?

Accepted Answer

Introduction / Context:When mapping an M:N (many-to-many) relationship from an ER model to a relational schema, we create a new relation—often called a junction or associative table—that connects the two entity tables. The widely taught canonical design uses a composite primary key based on both entities’ primary keys. Given Data / Assumptions: Two entities A and B participate in an M:N relationship. Each entity is mapped to its own relation with a primary key. We seek a normalized design preserving all relationship instances without redundancy. Concept / Approach:The junction relation includes the primary key of A (as a foreign key) and the primary key of B (as a foreign key). Declaring both together as the junction’s composite primary key ensures each pair appears at most once, preventing duplicates and preserving the relationship’s multiplicity. Step-by-Step Solution:Map A and B to separate relations with their PKs (say a_id and b_id).Create relation A_B(a_id, b_id, ...relationship attributes...).Declare a_id references A(a_id) and b_id references B(b_id).Declare PRIMARY KEY (a_id, b_id) in A_B.Add additional attributes to A_B if the relationship carries its own data (e.g., created_at, role). Verification / Alternative check:Try inserting duplicate pairs into the junction table; the composite primary key prevents duplicates, which confirms correct design. Why Other Options Are Wrong:Incorrect: contradicts standard normalization practice.True only without surrogates: even when adding a surrogate key, the natural composite key is typically enforced via a unique constraint.Only in denormalized designs: the composite key approach is actually the normalized standard. Common Pitfalls:Adding only one foreign key to the junction, which loses M:N semantics; omitting a unique constraint when introducing a surrogate key, enabling duplicates. Final Answer:Correct

Question 20

Terminology clarity: Is an “enterprise key” simply a primary key that is unique within a single relation, or is it intended to be globally unique across the enterprise?

Accepted Answer

Introduction / Context:Different key terms capture different scopes of uniqueness. A primary key uniquely identifies rows within a single table. An enterprise key, by contrast, is a key whose values are unique across the entire organization—across all relevant tables and systems—so that the same identifier consistently refers to the same real-world entity everywhere. Given Data / Assumptions: A primary key enforces per-table uniqueness and not-null constraints. An enterprise key indicates cross-system, cross-table uniqueness. Organizations often use enterprise identifiers (e.g., global customer_id) that are propagated across databases. Concept / Approach:The statement claims an enterprise key is “a primary key whose value is unique for a given relation.” That description matches an ordinary primary key, not an enterprise key. Therefore, the statement is incorrect. Enterprise keys enable master data consistency and referential clarity across multiple data stores, not just within a single relation. Step-by-Step Solution:Distinguish per-table uniqueness (primary key) from cross-enterprise uniqueness (enterprise key).Identify use cases: a customer_number shared by billing, CRM, and support systems.Recognize governance needs: enterprise key management ensures no collisions across domains.Implement synchronization and validation mechanisms to maintain global uniqueness.Map enterprise keys into local schemas (as primary keys or alternate keys) consistently. Verification / Alternative check:Examine data integration architectures (MDM systems). Enterprise keys serve as a common join key across applications, demonstrating their broader scope beyond one relation. Why Other Options Are Wrong:Correct: would wrongly equate enterprise keys with ordinary per-table primary keys.True only in OLTP: scope is organizational, not workload-specific.True only with GUIDs: GUIDs are one implementation, not the definition. Common Pitfalls:Confusing enterprise keys with surrogate keys; thinking any unique value qualifies as “enterprise” without organizational governance. Final Answer:Incorrect

Logical Database Design Questions

Questions

Practice smarter