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

Weak entities do not get mapped into a relation.

Accepted Answer

False

Question 17

In a 1:M relationship, the primary key on the one side migrates to the many side to become the foreign key on the many side.

Accepted Answer

True

Question 18

A primary key may be null.

Accepted Answer

False

Question 19

The primary key of the new relation in a many-to-many relationship is a composite key comprised of the primary keys of each of the binary entities.

Accepted Answer

True

Question 20

An enterprise key is a primary key whose value is unique for a given relation.

Accepted Answer

False

Logical Database Design Questions