Under database normalization theory, a transitive dependency is a major structural bottleneck. A transitive dependency occurs when:
💻 text Snippet
stId -> prName -> prCrdts
A.Part of the key is dependent on key attribute
B.Non-key attribute is dependent on another non-key attribute
C.Primary key attribute is dependent on non-key attribute
D.Non-key attribute is not dependent on another non-key attribute
When designing a relational database, under which specific scenario do database designers need to explicitly evaluate and worry about Second Normal Form (2NF) compliance?
💻 sql Snippet
-- Composite primary key scenario requiring 2NF verification:
-- EMPLOYEE(EmpID, CourseTitle, Name, DeptName, DateCompleted)
A.When the primary key consists of a single attribute
B.When the primary key contains non-unique values
C.When the primary key is composite (consisting of multiple attributes)
D.When the primary key contains redundant values
If a relational database table has successfully satisfied the Second Normal Form (2NF) requirements but is not yet in Third Normal Form (3NF), then update, insertion, and deletion anomalies are due to which of the following dependencies?
💻 sql Snippet
-- Non-3NF table with transitive dependency
-- CustomerID -> SalesMan -> Region
A.Reflexive dependency
B.Full functional dependency
C.Transitive dependency
D.Partial dependency
If a relational database table has successfully satisfied the Second Normal Form (2NF) requirements but is not yet in Third Normal Form (3NF), which type of dependency is responsible for causing update, insertion, and deletion anomalies?
💻 sql Snippet
-- Transitive Dependency Violation (stId -> prName -> prCrdts):
-- STUDENT(stId, Name, prName, prCrdts)
A.Transitive dependency
B.Partial dependency
C.Reflexive dependency
D.Full functional dependency
From the following options, choose the correct statement that defines the structural criteria and rules for a database relation to be in the Third Normal Form (3NF).
💻 text Snippet
-- Definition of 3NF:
-- A relation is in 3NF if and only if it is in 2NF and there are no transitive dependencies.
A.There is a partial dependency in the relation.
B.A non-key attribute is dependent on another non-key attribute.
C.There is redundant data in the relation.
D.No non-key attribute is dependent on another non-key attribute (i.e., there are no transitive dependencies).
A relational database table is considered to be normalized to the Third Normal Form (3NF) if it satisfies the criteria of the Second Normal Form (2NF) and conforms to which of the following rules?
💻 sql Snippet
-- Transitive Dependency (Violation of 3NF):
-- CustomerID -> SalesMan -> Region
A.It contains multivalued attributes in some of its rows.
B.It has a partial dependency where non-key attributes depend on part of a composite key.
C.It has a transitive dependency where a non-key attribute determines another non-key attribute.
D.No non-key attribute is dependent on any other non-key attribute (i.e., there is no transitive dependency).
Given a database relation in Second Normal Form (2NF) with the schema 'SALES(CustomerID, Name, SalesMan, Region)' and the following functional dependencies:
- CustomerID -> Name, SalesMan
- SalesMan -> Region
What structural normalization step is required to convert this relation into Third Normal Form (3NF) to eliminate operational update and deletion anomalies?
💻 sql Snippet
-- Transitive Dependency Violation in 2NF relation:
-- CustomerID -> SalesMan and SalesMan -> Region
A.Decompose into two relations:
- CUSTOMER(CustomerID, Name, SalesMan)
- SALESMAN(SalesMan, Region)
where 'SalesMan' in CUSTOMER acts as a foreign key referencing SALESMAN.
B.Decompose into two relations:
- CUSTOMER(CustomerID, Name)
- REGION(CustomerID, SalesMan, Region)
where 'CustomerID' acts as a composite key in both tables.
C.Merge with another database table to eliminate the 'SalesMan' column, moving the 'Region' column directly into a flat CUSTOMER table.
D.Declare 'SalesMan' as a primary key in the SALES table, making the relation rely on a composite primary key consisting of {CustomerID, SalesMan}.
Which of the following database dependency concepts is applicable and eliminated to achieve the Third Normal Form (3NF)?
💻 sql Snippet
-- Redundant transitive dependency (non-key depending on non-key):
-- Employee(EmpID -> DeptID -> DeptName)
-- 3NF Decomposition into two tables:
-- Employee(EmpID, DeptID)
-- Department(DeptID, DeptName)
A.Full functional dependency
B.Any kind of dependency
C.Transitive dependency
D.Partial functional dependency
Which of the following database dependency concepts is directly applicable and eliminated to achieve the Third Normal Form (3NF)?
💻 sql Snippet
-- Redundant transitive dependency (non-key depending on non-key):
-- Employee(EmpID -> DeptID -> DeptName)
-- 3NF Decomposition into two tables:
-- Employee(EmpID, DeptID)
-- Department(DeptID, DeptName)
A.Full functional dependency
B.Any kind of dependency
C.Transitive dependency
D.Partial functional dependency
Under standard relational database normalization guidelines, which of the following statements is TRUE regarding the rules of normal forms?
💻 sql Snippet
-- Single Primary Key Attribute automatically satisfies 2NF:
CREATE TABLE Teacher (
TeacherID VARCHAR(5) PRIMARY KEY,
Name VARCHAR(30)
);
A.A relation is automatically in 2NF if its primary key consists of a single attribute.
B.A relation in 3NF is permitted to have transitive dependencies between non-key columns.
C.To achieve 1NF, a relation must allow multi-valued attributes within a single cell.
D.Partial dependencies are acceptable in 2NF relations as long as they are mapped to composite keys.
