A relational database table is considered to have successfully satisfied the criteria of the Second Normal Form (2NF) if it is already in 1NF and completely eliminates which of the following dependencies?
💻 sql Snippet
-- Violation of 2NF (stId -> stName is a partial dependency on composite key):
-- CLASS(crId, stId, stName, grade)
A.Transitive dependency
B.Functional dependency
C.Partial dependency
D.Reflexive dependency
The structural rules and formal definition of the Second Normal Form (2NF) are theoretically established upon which of the following functional dependency concepts?
💻 text Snippet
-- Conceptual definition of 2NF:
-- A relation is in 2NF if all non-key attributes are fully functionally dependent on the key.
A.Reflexive dependency
B.Full functional dependency
C.Transitive dependency
D.Partial dependency
If a database relation 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?
💻 text Snippet
-- Transitive Dependency Chain causing anomalies in 2NF table:
-- Primary Key -> Non-Key Column A -> Non-Key Column B
A.Transitive dependency
B.Partial dependency
C.Reflexive dependency
D.Full functional dependency
If a relational database table is in the First Normal Form (1NF) but fails to meet the criteria for the Second Normal Form (2NF), what type of dependency must be identified and removed through decomposition?
💻 sql Snippet
-- Violation of 2NF with composite key (crId, stId):
-- CLASS(crId, stId, stName, grade) where stId -> stName (Partial Dependency)
A.Transitive dependency
B.Reflexive dependency
C.Partial dependency
D.Full functional dependency
A relational database table is formally considered to be in the Third Normal Form (3NF) if it satisfies which of the following prerequisites?
💻 sql Snippet
-- Prerequisite check for 3NF:
SELECT * FROM MyTable;
A.It is already in second normal form
B.It has a transitive dependency
C.It has a partial dependency
D.It has multivalued attributes
Under database normalization theory, how are the operational update, insertion, and deletion anomalies caused by a transitive dependency removed from a relation?
💻 sql Snippet
-- Transitive Dependency Violation:
-- CustomerID -> SalesMan and SalesMan -> Region
A.By changing the primary key of the table
B.By reducing the number of rows in the table
C.By breaking down (decomposing) the relation into smaller tables
D.By reducing the number of columns in the table
A transitive dependency is a major normalization bottleneck. Under database design rules, a transitive dependency is defined as a situation where:
💻 sql Snippet
-- Transitive Dependency:
-- stId (Primary Key) -> prName (Non-key) -> prCredits (Non-key)
A.Part of a composite key functionally determines a key attribute.
B.A non-key attribute is functionally dependent on another non-key attribute.
C.A non-key attribute is independent of all other non-key attributes.
D.The primary key attribute is functionally determined by a non-key attribute.
Under standard relational database normalization rules, a database table is formally considered to be in the Third Normal Form (3NF) if it satisfies which of the following prerequisites?
💻 sql Snippet
-- Redundant 2NF schema with transitive dependency:
-- CustomerID -> SalesMan and SalesMan -> Region
CREATE TABLE SALES (
CustomerID INT PRIMARY KEY,
Name VARCHAR(50),
SalesMan VARCHAR(50),
Region VARCHAR(50)
);
A.It is already in second normal form (2NF)
B.It contains a partial dependency on a composite key
C.It has multivalued attributes in some of its column cells
D.It contains a transitive dependency between non-key fields
Under database normalization theory, a transitive dependency is a major structural bottleneck. Which of the following correctly defines how a transitive dependency occurs?
💻 sql Snippet
-- Transitive Dependency chain: StudentID -> ProgramName -> ProgramCredits
-- STUDENT(StudentID, StudentName, ProgramName, ProgramCredits)
A.A primary key attribute is functionally dependent on a non-key attribute
B.A portion of a composite primary key is dependent on another key attribute
C.A non-key attribute is functionally independent of all other non-key attributes
D.A non-key attribute is functionally dependent on another non-key attribute
Consider the relation schema 'STD(stId, stName, stAdr, prName, prCrdts)' with the functional dependencies 'stId -> stName, stAdr, prName, prCrdts' and 'prName -> prCrdts'. Which type of functional dependency exists in this relation that violates the Third Normal Form (3NF)?
💻 text Snippet
STD(stId, stName, stAdr, prName, prCrdts)
Functional Dependencies:
- stId -> stName, stAdr, prName, prCrdts
- prName -> prCrdts
A.Composite dependency
B.Reflexive dependency
C.Terminal dependency
D.Transitive dependency
