Question 1

In relational database theory, a relation is best described as what kind of table?

Accepted Answer

Introduction / Context: The term relation has a precise meaning in relational database theory. Understanding its structure is foundational for normalization, keys, and query formulation. Given Data / Assumptions: Each relation has attributes (columns) and tuples (rows). Cells contain atomic values under first normal form assumptions. Ordering of rows and columns is not semantically important. Concept / Approach: A relation is modeled as a two-dimensional table: one dimension for attributes (columns) and one for tuples (rows). This abstraction allows the use of operations like selection, projection, and join. Step-by-Step Solution: Map the definition of a relation to a rectangular arrangement of rows and columns.Confirm that there are exactly two orthogonal directions: attributes and tuples.Eliminate options that imply one or three dimensions. Verification / Alternative check: Relational algebra operations work on sets of tuples with defined attributes, consistent with a two-dimensional structure. Why Other Options Are Wrong: Column or one-dimensional table: ignores the row dimension. Three-dimensional table: not applicable to basic relational structures; multidimensional models belong to OLAP cubes, not base relations. Common Pitfalls: Confusing relations with multidimensional cubes or hierarchical structures. The relational model is simpler and strictly two-dimensional. Final Answer: two-dimensional table.

Question 2

In relational terminology, a row of a table is synonymous with which term?

Accepted Answer

Introduction / Context: Different database communities use slightly different terms. Knowing standard synonyms helps when reading documentation and exam questions. Given Data / Assumptions: A table consists of rows and columns. Each row represents a single tuple, which is a set of attribute values. Legacy terminology often calls a row a record and a column a field. Concept / Approach: In classical relational theory, a row is a tuple. In many practical database texts and tools, the same thing is called a record. A column is an attribute or field. Step-by-Step Solution: Identify that the question wants the synonym used in practice.Row corresponds to tuple; common SQL tools label it as a record.Select the option that matches this common synonym. Verification / Alternative check: Check user interfaces of DBMS tools: they typically speak of records (rows) and fields (columns). Why Other Options Are Wrong: Relation is the entire table, not a row. Column or field refers to attributes, not tuples. Common Pitfalls: Mixing up field and record; remember field equals column, record equals row. Final Answer: record.

Question 3

The classes of relations produced by normalization to avoid modification anomalies are called what?

Accepted Answer

Introduction / Context: Normalization organizes relational schemas to reduce redundancy and eliminate update, insert, and delete anomalies. The outcomes are categorized into named stages. Given Data / Assumptions: Well-known levels include first, second, and third normal form, plus Boyce–Codd and beyond. Anomalies arise when one table tries to represent multiple themes or has improper dependencies. Concept / Approach: Each stage of normalization corresponds to a normal form. These forms impose increasingly strict rules based on functional and other dependencies to mitigate anomalies. Step-by-Step Solution: Identify the artifact that names the stages: they are the normal forms.Eliminate options that are inputs or constraints rather than the classification.Select normal forms as the correct label. Verification / Alternative check: Textbook definitions explicitly refer to 1NF, 2NF, 3NF, BCNF, 4NF, and 5NF as normal forms addressing anomalies. Why Other Options Are Wrong: Referential integrity constraints enforce key–foreign key consistency, not the classification of normalized schemas. Functional dependencies are relationships used to reason about normalization, not the stages themselves. Common Pitfalls: Confusing the tools (dependencies, constraints) with the results (normal forms). Final Answer: normal forms.

Question 4

A relation that is free of all modification anomalies is in which normal form?

Accepted Answer

Introduction / Context: Higher levels of normalization progressively remove sources of redundancy and anomalies. The strongest commonly cited form in theory ensures complete freedom from modification anomalies. Given Data / Assumptions: First normal form removes repeating groups and enforces atomic values. Second and third normal forms address partial and transitive dependencies with respect to keys. Boyce–Codd and beyond tighten conditions further; domain/key normal form is even stronger. Concept / Approach: Domain/key normal form states that every constraint on the relation is a logical consequence of the domain definitions and key constraints. Under this condition, update, insert, and delete anomalies are eliminated in principle. Step-by-Step Solution: Recall which normal forms still permit certain rare anomalies (even BCNF can in edge cases).Recognize that domain/key normal form is the theoretical target where all constraints derive from domains and keys.Select domain/key normal form as the answer. Verification / Alternative check: Classic literature acknowledges that domain/key normal form is sufficient to guarantee freedom from all modification anomalies. Why Other Options Are Wrong: First, second, and third normal forms reduce but do not eliminate all possible anomalies. Common Pitfalls: Assuming third normal form or BCNF automatically eliminates every anomaly. Edge cases still exist; domain/key normal form is stricter. Final Answer: Domain/key normal form

Question 5

Multivalued dependency constraint: which action is a standard solution to eliminate the problem?

Accepted Answer

Introduction / Context: Multivalued dependencies indicate that a table tries to represent multiple independent sets of facts about the same key, leading to redundancy and anomalies. Given Data / Assumptions: A single relation contains attributes that vary independently with respect to a key. Our goal is to remove redundancy and update anomalies. Concept / Approach: Fourth normal form requires that for every nontrivial multivalued dependency, we decompose the table into separate relations so that each captures a single theme of attributes dependent on the key. Step-by-Step Solution: Identify the independent attribute sets associated with the same key.Decompose the original relation into two relations: key with attribute set A, and key with attribute set B.Ensure that lossless join holds and that redundancy is removed. Verification / Alternative check: After decomposition, insertions or deletions in one attribute set no longer force spurious changes in the other set, confirming that the multivalued dependency problem is resolved. Why Other Options Are Wrong: Changing or creating a new theme without decomposition does not address the structural redundancy. Adding a composite key does not eliminate the independent variability of attributes. Common Pitfalls: Trying to patch symptoms with extra keys or triggers rather than performing the correct decomposition. Final Answer: split the relation into two relations, each with a single theme.

Question 6

A relation is in which normal form if it is in Boyce–Codd normal form and also has no multivalued dependencies?

Accepted Answer

Introduction / Context: Normalization levels build on one another. Beyond Boyce–Codd normal form, the next target addresses multivalued dependencies explicitly. Given Data / Assumptions: The relation already satisfies Boyce–Codd normal form. We now eliminate nontrivial multivalued dependencies. Concept / Approach: Fourth normal form is achieved when a relation is in Boyce–Codd normal form and contains no nontrivial multivalued dependencies. This ensures that attributes do not vary independently in a way that causes redundancy. Step-by-Step Solution: Confirm BCNF holds (determinants are candidate keys).Check for multivalued dependencies; if present, decompose appropriately.Once none remain, the relation is in fourth normal form. Verification / Alternative check: After decomposition, updates to one attribute set need not cascade to another, validating the removal of multivalued dependency anomalies. Why Other Options Are Wrong: Second and third normal forms are weaker and do not mention multivalued dependencies. Domain/key normal form is stricter than fourth and is not required merely by BCNF plus removal of multivalued dependencies. Common Pitfalls: Assuming BCNF alone handles multivalued dependencies. It does not; fourth normal form is required. Final Answer: fourth normal form.

Question 7

Functional dependency logic: If attribute A functionally determines both attributes B and C, what else must be true?

Accepted Answer

Introduction / Context: Functional dependencies capture how attributes determine one another. They are central to normalization reasoning and key identification. Given Data / Assumptions: We are told A determines B and A determines C. We seek a statement that must be true given this premise. Concept / Approach: If A determines B and C, then it trivially follows that A determines B. The dependency is one of those explicitly stated. The converse dependencies do not automatically follow without additional information. Step-by-Step Solution: From the premise: A → B and A → C are both given.Therefore, A → B is necessarily true.No inference supports B → A, C → A, or (B, C) → A without extra assumptions. Verification / Alternative check: Consider a concrete example. A as a primary key can determine other attributes. That does not imply any non-key attribute determines the key. Why Other Options Are Wrong: B → A or C → A would imply reversal of dependency, which is not guaranteed. (B, C) → A also does not follow from A determining B and C. Common Pitfalls: Assuming determinacy is symmetric. It is not; functional dependency is directional. Final Answer: A → B.

Question 8

In relational terms, a tuple refers to which element of a relation (table)?

Accepted Answer

Introduction / Context: Terminology bridges theory and practice. The term tuple is standard in relational theory, while many tools say row or record. Given Data / Assumptions: A relation has attributes (columns) and tuples (rows). Each tuple contains values for every attribute of the relation. Concept / Approach: A tuple is a single record in the relation, represented as a row with one value per attribute. Step-by-Step Solution: Map theoretical terms to common terms: tuple equals row; attribute equals column.Discard options that denote the whole table or a key.Choose row of a table. Verification / Alternative check: SQL statements like insert into table values (...) operate on tuples, i.e., rows. Why Other Options Are Wrong: Column describes an attribute, not a tuple. Two dimensional table refers to the entire relation, not one tuple. Key is a set of attributes used to identify tuples, not the tuple itself. Common Pitfalls: Using record and field inconsistently; remember tuple equals row and attribute equals column. Final Answer: row of a table.

Question 9

Dependency inference: Every time attribute A appears it has the same B value, but C varies. What dependency statement is therefore true?

Accepted Answer

Introduction / Context: Functional dependencies are derived from observed or intended business rules. This question asks you to infer a dependency from observed consistency. Given Data / Assumptions: Whenever A has a certain value, B always takes the same value with it. C does not remain the same for a given A; it can vary. Concept / Approach: If B is uniquely determined by A, we express this as A determines B. Since C can vary for the same A, A does not determine C, and therefore A does not determine the pair (B, C) either. Step-by-Step Solution: From the observation: A values map to exactly one B value, so A → B holds.Because C differs for the same A, A → C does not hold.Because C varies, A → (B, C) cannot hold either.No information supports (B, C) → A. Verification / Alternative check: Construct a small sample with fixed pairs of A and B but multiple C values; you will see that B is functionally dependent on A while C is not. Why Other Options Are Wrong: A → C contradicts the stated variation of C for a given A. A → (B, C) fails because A cannot determine both when C is not fixed. (B, C) → A is not implied; it would require each pair of B and C to map to exactly one A. Common Pitfalls: Assuming that if A determines one attribute, it determines all related attributes. Each dependency must be justified independently. Final Answer: A → B.

Question 10

In relational database design, what do we call a group of one or more attributes (columns) that uniquely identifies a single row (tuple) in a table?

Accepted Answer

Introduction / Context: Relational databases rely on identifiers to distinguish one row from another. The concept that captures this ability to uniquely pinpoint a row is fundamental to normalization, integrity constraints, and query optimization. Given Data / Assumptions: A “row” is also called a tuple. An “attribute” is a column in a relation (table). One or more attributes may be needed to uniquely identify a row. We are working within standard relational theory terminology. Concept / Approach: A set of attributes that uniquely identifies tuples is called a key. If the key is minimal (no proper subset is a key) it is a candidate key; once chosen for identification, it becomes the primary key. A set that is not minimal but still uniquely identifies rows is a superkey. Step-by-Step Solution: Identify the term that denotes uniqueness across rows → keys provide uniqueness.Recognize that “one or more attributes” suggests either a single-attribute key or a composite key; both are keys.Therefore, the general term that fits all such cases is “Key.” Verification / Alternative check: Texts on relational theory define key/superkey/candidate key precisely as uniqueness constraints over attributes; this aligns with the prompt. Why Other Options Are Wrong: Determinant: Any attribute set on the left side of a functional dependency; not necessarily unique for all rows. Tuple: A row, not the identifier of a row. Relation: A table, not its identifier. Composite attribute: An attribute made of components; unrelated to uniqueness by itself. Common Pitfalls: Confusing “determinant” with “key”; determinants may not be minimal or unique across the entire relation. Final Answer: Key

Question 11

Within the relational model, what best describes a key with respect to a table (relation)? Choose the statement that correctly characterizes a key's role.

Accepted Answer

Introduction / Context: A key is central to ensuring entity integrity in a relational table. Understanding what a key actually does—beyond its physical representation—helps avoid design mistakes. Given Data / Assumptions: A key may be single-attribute or multi-attribute (composite). The table conforms to relational rules (no duplicate rows under a candidate/primary key). We distinguish a key’s purpose from its implementation details. Concept / Approach: The purpose of a key is to uniquely identify each row (tuple). Whether this key is one column or multiple columns is incidental; the defining property is uniqueness and minimality (for candidate keys). Step-by-Step Solution: Focus on the function, not structure → identification of a row is the function of a key.Composite vs. single-attribute keys both can serve as identifiers.Therefore, the accurate, general statement is “identifies a row.” Verification / Alternative check: Relational design guidelines define a key as a unique identifier for tuples. Why Other Options Are Wrong: Must be two or more columns / can only be one column: Both are false—keys can be either. Identifies a column: Keys are constraints over columns; they don’t identify columns. Used only for sorting: Sorting is unrelated; keys provide uniqueness and referential anchors. Common Pitfalls: Believing all keys must be composite or single—design drives the choice. Final Answer: identifies a row.

Question 12

In relational terminology, an attribute is best defined as which of the following within a table (relation)?

Accepted Answer

Introduction / Context: Relational vocabulary maps everyday database objects to formal terms. Correctly matching “attribute” clarifies subsequent topics like functional dependencies and normalization. Given Data / Assumptions: “Attribute” is a formal term from relational theory. Tables (relations) are composed of rows (tuples) and columns (attributes). Keys and indexes are separate constructs. Concept / Approach: An attribute is the column definition—name and domain (data type)—for values stored per row. Attributes collectively define the schema of the relation. Step-by-Step Solution: Map terms: attribute ↔ column; tuple ↔ row; relation ↔ table.Therefore, “attribute” means “column of a table.” Verification / Alternative check: Any relational database textbook or SQL standard-based glossary reflects this mapping. Why Other Options Are Wrong: Two-dimensional table / row / key / index: Those are different concepts; an attribute is not a whole table, a row, a key, or an index. Common Pitfalls: Confusing attribute with field in certain contexts; “field” is an implementation term, while “attribute” is the relational theoretical term. Final Answer: column of a table.

Question 13

Functional dependencies: If attributes A and B together determine attribute C (written (A, B) → C), what else is necessarily true?

Accepted Answer

Introduction / Context: Functional dependencies (FDs) express how attributes relate. Understanding what can and cannot be inferred from a given FD is central to normalization (e.g., 2NF, 3NF, BCNF). Given Data / Assumptions: We are given (A, B) → C. No other FDs or key information are provided. All terms follow standard relational theory. Concept / Approach: A “determinant” is any attribute set on the left side of a functional dependency. If two attributes together determine another, the pair (A,B) is a determinant, and it is composite because it contains more than one attribute. From (A,B) → C, we cannot infer A → C or B → C unless additional FDs hold. Step-by-Step Solution: Start with (A,B) → C.Identify the determinant: the LHS, (A,B), is the determinant.Because there are two attributes on the LHS, it is a composite determinant.No inference allows A → C or B → C individually without extra information.C is on the RHS, so C is not a determinant by this FD alone. Verification / Alternative check: Armstrong’s axioms do not permit decomposing an FD's LHS like (A,B) → C into A → C or B → C in general. Why Other Options Are Wrong: A → C / B → C: Not guaranteed; they require additional FDs. C is a determinant: Not from this FD; C is the dependent attribute. A alone is always a key: Not implied; keys require uniqueness over all tuples, which is not established. Common Pitfalls: Assuming multi-attribute determinants imply each attribute determines the RHS—this is incorrect without further constraints. Final Answer: (A,B) is a composite determinant.

Question 14

Primary key selection: From which set is the primary key of a relation chosen in relational database design?

Accepted Answer

Introduction / Context: Every relation should have a declared primary key. Understanding where it comes from ensures proper entity integrity and sound normalization. Given Data / Assumptions: Multiple candidate keys may exist for a relation. Primary key is the designated identifier in the schema. Foreign keys create relationships to other relations. Concept / Approach: A candidate key is a minimal superkey—no proper subset uniquely identifies rows. The primary key is selected from the set of candidate keys to serve as the official identifier. Step-by-Step Solution: Identify all candidate keys (minimal unique sets).Choose one candidate key as the primary key.Other candidate keys remain alternate keys. Verification / Alternative check: Database design references consistently define the primary key as a chosen candidate key. Why Other Options Are Wrong: Composite keys: Some candidate keys are composite, but not all composites are candidate keys. Determinants: Not necessarily minimal or unique. Foreign keys: Reference other relations and need not be unique in the referencing table. Common Pitfalls: Assuming any unique set is a candidate key even if not minimal; minimality is required. Final Answer: candidate keys.

Question 15

Creating relationships in the relational model: Which construct is used to relate rows across two tables (parent and child)?

Accepted Answer

Introduction / Context: Relational databases link tables through referential constraints, enabling normalized designs while preserving navigability across entities. Given Data / Assumptions: A parent table has a primary (or candidate) key. A child table references that key to relate its rows. Standard SQL referential integrity rules apply. Concept / Approach: A foreign key in the child table references the candidate/primary key in the parent table. This enforces referential integrity: each foreign key value must match an existing parent key or be null (subject to constraints). Step-by-Step Solution: Identify the parent’s identifying key (primary/candidate).Declare a foreign key in the child referencing the parent’s key.RDBMS enforces insert/update/delete rules to maintain integrity. Verification / Alternative check: SQL DDL (FOREIGN KEY ... REFERENCES ...) codifies this mechanism. Why Other Options Are Wrong: Composite keys: Can be foreign or candidate keys but don’t by themselves create relationships. Determinants / candidate keys: Important internally; relationships are implemented via foreign keys. Common Pitfalls: Confusing a parent’s primary key (identifier) with the child’s foreign key (reference); both are needed to form the link. Final Answer: foreign keys.

Question 16

Relational terminology mapping: In database theory and SQL practice, the word “table” is synonymous with which formal term?

Accepted Answer

Introduction / Context: Relational databases have a precise vocabulary. Mapping common SQL terms to their theoretical counterparts helps avoid ambiguity, especially in normalization and dependency discussions. Given Data / Assumptions: SQL term “table” corresponds to a theoretical concept. “Row” and “column” also have formal names. Concept / Approach: “Relation” is the formal term for a table. A row is a “tuple,” and a column is an “attribute.” Step-by-Step Solution: Table → Relation.Row → Tuple.Column → Attribute. Verification / Alternative check: Standard texts (Date, Ullman, Ramakrishnan & Gehrke) use this mapping. Why Other Options Are Wrong: Record / field: Common in older file systems terminology. Column / tuple: Refer to components of a relation, not to the relation itself. Common Pitfalls: Intermixing file-system and relational terms can obscure theoretical properties and constraints. Final Answer: relation.

Question 17

Data anomalies: In some poorly designed tables, changes to data cause undesirable side effects. What are these consequences commonly called in normalization theory?

Accepted Answer

Introduction / Context: Normalization aims to reduce redundancy and prevent problematic side effects when inserting, updating, or deleting data. Recognizing anomalies helps justify decomposition into higher normal forms. Given Data / Assumptions: Anomalies arise from redundancy and inappropriate attribute grouping. Types include insertion, update, and deletion anomalies. We assume standard relational definitions. Concept / Approach: “Modification anomalies” is the umbrella term for unwanted effects caused by data modifications in non-normalized designs. They reflect design flaws that normalization (1NF, 2NF, 3NF, BCNF) tries to address. Step-by-Step Solution: Identify symptom: odd side effects when changing data.Classify as insertion/update/deletion anomalies.Conclude: These are modification anomalies. Verification / Alternative check: Normalization literature uses the term “modification anomalies” extensively as the motivation for decomposing relations. Why Other Options Are Wrong: Referential integrity constraints: Rules that prevent orphaned references, not the anomalies themselves. Normal forms: Targets/criteria to prevent anomalies. Transitive dependencies: A specific dependency type that can cause anomalies but is not the name of the consequence. Common Pitfalls: Confusing the cause (dependencies) or cure (normal forms) with the symptom (anomalies). Final Answer: modification anomalies.

Question 18

Relational requirements: Which of the following is NOT a restriction for a table to qualify as a relation under the relational model?

Accepted Answer

Introduction / Context: Relations have formal properties that distinguish them from arbitrary tables. These properties underpin set-based reasoning and eliminate ambiguity in queries. Given Data / Assumptions: Using classical relational model assumptions. Atomicity of cells (1NF) is required. Row uniqueness is a requirement for a relation. Attributes have names and defined domains. Concept / Approach: In the relational model, tuples are unordered and attributes are a set, not an ordered list. While SQL interfaces may display columns in an order, the model does not require any column ordering. Atomic values, uniform domains per attribute, unique rows, and unique attribute names are expected. Step-by-Step Solution: Check each property: atomic cells → required.Column entries same type/domain → required.No duplicate rows → required.Column order → NOT required in the model (order is not semantically significant). Verification / Alternative check: Relational algebra treats relations as sets of tuples over a set of attributes; sets are unordered. Why Other Options Are Wrong: They each state valid relational restrictions (atomicity, domain consistency, row uniqueness, unique attribute names). Common Pitfalls: Assuming the presentation order in a UI reflects a theoretical requirement; it does not. Final Answer: The columns must be ordered.

Question 19

Functional dependency scope: A functional dependency (FD) is a relationship between or among which elements of a relation?

Accepted Answer

Introduction / Context: Functional dependencies formalize how one set of attributes determines another, guiding normalization to remove redundancy and anomalies. Given Data / Assumptions: An FD is typically written X → Y. X and Y are sets of attributes from the same relation schema. We assume classical relational theory. Concept / Approach: An FD states that, for any two rows, if the X attributes match, then the Y attributes must also match. This is a constraint among attributes of a relation, not among tables or rows as entities. Step-by-Step Solution: Define FD: a constraint on attributes within a relation’s schema.Conclude: The relationship is among attributes, not whole tables/rows. Verification / Alternative check: Armstrong’s axioms and normalization proofs always reason over attribute sets. Why Other Options Are Wrong: Tables / relations / rows: While FDs apply to a relation schema, the relationship itself is among attributes inside that schema. Indexes: Physical structures; unrelated to the logical constraint definition. Common Pitfalls: Thinking FDs are about table-to-table relationships; that is the role of referential integrity via foreign keys. Final Answer: attributes.

Question 20

In relational database design, when the values in one or more attributes used as a foreign key must already exist as matching values in a candidate key of another table, what have we enforced?

Accepted Answer

Introduction / Context: Foreign keys connect related tables. A core rule ensures that any foreign key value in a child table corresponds to an existing key value in the parent table. This rule prevents orphan records and maintains consistency across relations. Given Data / Assumptions: A foreign key is defined in one table to reference a candidate key (often the primary key) in another table. The question asks which concept requires that referenced values exist before they can be used. We are working in a standard relational database context (SQL systems such as PostgreSQL, SQL Server, MySQL, Oracle). Concept / Approach: Referential integrity is the constraint that enforces valid references between related tables. It ensures every foreign key value either matches a candidate key value in the referenced table or is null (if allowed), thereby protecting data consistency. Step-by-Step Solution: Identify the rule: child table foreign key values must correspond to existing parent table key values.Map that rule to the relational term that enforces it. This is referential integrity.Eliminate distractors that describe different topics (normalization, anomalies, dependencies). Verification / Alternative check: Consider a DELETE of a parent row. Without referential integrity, child rows referencing it would become orphans. With referential integrity, the DBMS blocks the delete, cascades the change, or sets nulls according to the defined action. Why Other Options Are Wrong: Transitive dependency: a normalization concept about attribute dependencies, not cross-table references. Insertion anomaly: a symptom of poor design, not a constraint. Normal form: a classification of schema quality; it does not directly enforce cross-table references. Common Pitfalls: Assuming primary keys alone guarantee cross-table consistency. They do not; you must define foreign keys with referential actions. Final Answer: referential integrity constraint

The Relational Model and Normalization Questions