Functional dependency definition in DBMS Theory - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Functional Dependency

Which of the following best describes a functional dependency in a database?

A A process of normalizing data to reduce redundancy.

B A rule that allows multiple values for a single attribute in a table.

C A constraint that enforces referential integrity between tables.

D A relationship where one attribute uniquely determines another attribute.

💡 Hint

Think about how one piece of data can determine another in a table.

A functional dependency means that if you know the value of one attribute (or set of attributes), you can find exactly one value of another attribute. This is key in database design.

📋 Factual

intermediate

Identifying Functional Dependency Notation

In the notation A → B, what does this represent in terms of functional dependency?

A Attribute B functionally determines attribute A.

B Attributes A and B are independent of each other.

C Attribute A functionally determines attribute B.

D Attributes A and B are both primary keys.

💡 Hint

Look at the arrow direction carefully.

The arrow points from the determinant to the dependent attribute. So, A → B means A determines B.

🚀 Application

advanced

Applying Functional Dependency in a Table

Given a table with columns StudentID, StudentName, and Major, which functional dependency is correct?

A StudentID → StudentName

B StudentName → Major

C Major → StudentID

D StudentName → StudentID

💡 Hint

Consider which attribute uniquely identifies a student.

StudentID uniquely identifies a student, so it determines the StudentName. The reverse is not necessarily true because names can repeat.

🔍 Analysis

advanced

Analyzing Functional Dependency Impact

What is the impact of violating a functional dependency in a database table?

A It ensures data is always normalized.

B It causes data redundancy and inconsistency.

C It allows multiple primary keys in a table.

D It improves query performance.

💡 Hint

Think about what happens when one attribute does not uniquely determine another.

Violating functional dependency can lead to duplicate or conflicting data, causing redundancy and inconsistency.

❓ Reasoning

expert

Determining Functional Dependency from Data

Consider the following data in a table:

EmployeeID | Department
1          | Sales
2          | Sales
3          | HR
4          | HR
5          | Sales

Which functional dependency is true based on this data?

A EmployeeID → Department

B Department → EmployeeID

C EmployeeID and Department are independent

D Department uniquely determines EmployeeID

💡 Hint

Check if one attribute value always leads to exactly one value of the other.

Each EmployeeID corresponds to exactly one Department, so EmployeeID determines Department. But one Department has many EmployeeIDs, so Department does not determine EmployeeID.

Practice

1. What does the functional dependency X -> Y mean in a database?

easy

A. Knowing X allows you to find Y uniquely

B. X and Y are unrelated data sets

C. Y determines X uniquely

D. X and Y are always equal

Solution

1. Step 1: Understand the notation X -> Y

   This notation means that the value of X determines the value of Y uniquely in the database.

2. Step 2: Interpret the meaning in simple terms

   If you know X, you can find Y without ambiguity. This is the core idea of functional dependency.

3. Final Answer:

   Knowing X allows you to find Y uniquely -> Option A

4. Quick Check:

   Functional dependency means X determines Y [OK]

Hint: Remember: X determines Y means knowing X gives Y [OK]

Common Mistakes:

• Confusing which attribute determines which
• Thinking X and Y are unrelated
• Assuming dependency means equality

2. Which of the following is the correct way to write a functional dependency?

easy

A. X -> Y

B. X = Y

C. X <-> Y

D. X + Y

Solution

1. Step 1: Recall the standard notation for functional dependency

   The correct notation uses an arrow pointing from X to Y, written as X -> Y.

2. Step 2: Eliminate incorrect options

   X = Y means equality, X <-> Y suggests bidirectional dependency, and X + Y is not a valid notation here.

3. Final Answer:

   X -> Y -> Option A

4. Quick Check:

   Functional dependency uses arrow notation [OK]

Hint: Look for arrow notation X -> Y for functional dependency [OK]

Common Mistakes:

• Using equals sign instead of arrow
• Confusing bidirectional with functional dependency
• Using plus sign mistakenly

3. Given a table with columns StudentID and StudentName, which functional dependency is correct?

medium

A. StudentName -> StudentID

B. StudentID -> StudentName

C. StudentID -> StudentID

D. StudentName -> StudentName

Solution

1. Step 1: Identify the unique identifier

   Usually, StudentID uniquely identifies a student, so it determines StudentName.

2. Step 2: Check the direction of dependency

   Knowing StudentID gives StudentName, but not necessarily the other way around.

3. Final Answer:

   StudentID -> StudentName -> Option B

4. Quick Check:

   Unique ID determines name [OK]

Hint: Unique keys determine other attributes [OK]

Common Mistakes:

• Assuming name uniquely identifies ID
• Confusing attribute roles
• Ignoring uniqueness of StudentID

4. Identify the error in this functional dependency statement: EmployeeName -> EmployeeID where EmployeeID is unique.

medium

A. EmployeeName must be numeric

B. EmployeeID cannot be unique

C. Functional dependency must be bidirectional

D. EmployeeName may not uniquely determine EmployeeID

Solution

1. Step 1: Understand uniqueness of EmployeeID

   EmployeeID is unique, so it can determine EmployeeName, but not vice versa.

2. Step 2: Analyze the given dependency

   EmployeeName may repeat for different employees, so it cannot uniquely determine EmployeeID.

3. Final Answer:

   EmployeeName may not uniquely determine EmployeeID -> Option D

4. Quick Check:

   Non-unique attribute cannot determine unique ID [OK]

Hint: Only unique attributes determine others [OK]

Common Mistakes:

• Assuming all attributes are unique
• Thinking dependency is always two-way
• Confusing attribute types

5. In a database, if A -> B and B -> C, what can we conclude about the functional dependency between A and C?

hard

A. B -> A

B. C -> A

C. A -> C

D. C -> B

Solution

1. Step 1: Understand transitive dependency

   If A determines B, and B determines C, then A determines C by transitivity.

2. Step 2: Apply the rule to given dependencies

   Since A -> B and B -> C, it follows that A -> C.

3. Final Answer:

   A -> C -> Option C

4. Quick Check:

   Functional dependencies are transitive [OK]

Hint: Chain dependencies to find indirect ones [OK]

Common Mistakes:

• Reversing dependency direction
• Ignoring transitive property
• Confusing attributes order