Why functional dependencies guide schema design in DBMS Theory - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Functional Dependencies in Schema Design

Which statement best explains why functional dependencies are important in database schema design?

A They define the physical storage format of the database tables.

B They help identify which attributes uniquely determine other attributes, guiding normalization.

C They specify the order in which tables should be joined in queries.

D They determine the color scheme used in database management tools.

💡 Hint

Think about how to avoid data duplication and inconsistencies.

Functional dependencies show which attributes depend on others, helping to organize data to reduce redundancy and improve integrity.

❓ query_result

intermediate

Identifying Violations of Functional Dependencies

Given a table Students(ID, Name, Major, Advisor) with the functional dependency ID → Name, Major, Advisor, which query result indicates a violation of this dependency?

DBMS Theory

SELECT ID, Name, Major, Advisor FROM Students WHERE ID = 101;

A [{ID: 101, Name: 'Alice', Major: 'Math', Advisor: 'Dr. Smith'}, {ID: 101, Name: 'Alice', Major: 'Math', Advisor: 'Dr. Smith'}]

B [{ID: 101, Name: 'Alice', Major: 'Math', Advisor: 'Dr. Smith'}]

C []

D [{ID: 101, Name: 'Alice', Major: 'Math', Advisor: 'Dr. Smith'}, {ID: 101, Name: 'Alice', Major: 'Physics', Advisor: 'Dr. Smith'}]

💡 Hint

Look for rows with the same ID but different values in other columns.

If the same ID has different Majors, the functional dependency ID → Major is violated.

📋 Factual

advanced

Writing a Functional Dependency Expression

Which of the following correctly expresses the functional dependency that attribute A determines attribute B in a relation?

A A = B

B A => B

C A → B

D A <- B

💡 Hint

Functional dependencies use a specific arrow symbol.

The correct notation for functional dependency is A → B, meaning A functionally determines B.

❓ optimization

advanced

Choosing Schema Design Based on Functional Dependencies

You have a relation R(A, B, C, D) with functional dependencies A → B and C → D. Which schema design best reduces redundancy?

A Split into two tables: <code>R1(A, B)</code> and <code>R2(C, D)</code>.

B Create one table <code>R(A, B, C)</code> and another <code>R2(D)</code>.

C Split into three tables: <code>R1(A, B)</code>, <code>R2(C, D)</code>, and <code>R3(A, C)</code>.

D Keep all attributes in one table <code>R(A, B, C, D)</code>.

💡 Hint

Think about separating attributes based on their dependencies.

Splitting into two tables based on the dependencies reduces duplication and improves normalization.

🔍 Analysis

expert

Detecting Functional Dependency Violation in Schema

Consider a table Orders(OrderID, CustomerID, CustomerName, ProductID, ProductName) with the functional dependencies OrderID → CustomerID, ProductID and CustomerID → CustomerName. Which problem arises if CustomerName is stored in the Orders table?

A CustomerName may be duplicated and inconsistent if a customer has multiple orders.

B OrderID will not uniquely identify rows in the table.

C ProductName will be missing from the table.

D CustomerID cannot be used as a foreign key.

💡 Hint

Consider what happens when the same customer places many orders.

Storing CustomerName in Orders causes duplication and risks inconsistency if the name changes.