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.

Practice

1. What is the main reason functional dependencies are important in database schema design?

easy

A. They replace the need for primary keys

B. They speed up database queries automatically

C. They allow storing data in a single large table

D. They help organize data to avoid duplication and errors

Solution

1. Step 1: Understand functional dependencies

   Functional dependencies show how one attribute or set of attributes determines another attribute in a table.

2. Step 2: Connect to schema design

   Using these dependencies helps split tables to reduce duplication and avoid data inconsistencies.

3. Final Answer:

   They help organize data to avoid duplication and errors -> Option D

4. Quick Check:

   Functional dependencies guide clean design = They help organize data to avoid duplication and errors [OK]

Hint: Functional dependencies prevent data repetition and errors [OK]

Common Mistakes:

• Thinking functional dependencies speed up queries directly
• Believing all data should be in one table
• Confusing functional dependencies with keys

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

easy

A. A + B means A and B are combined

B. A -> B means A functionally determines B

C. A = B means A equals B

D. A * B means A multiplies B

Solution

1. Step 1: Recall functional dependency notation

   Functional dependency is written as A -> B, meaning A determines B.

2. Step 2: Check other options

   Other options show arithmetic or equality, not functional dependencies.

3. Final Answer:

   <code>A -> B</code> means A functionally determines B -> Option B

4. Quick Check:

   Functional dependency symbol = -> [OK]

Hint: Look for arrow (->) to show functional dependency [OK]

Common Mistakes:

• Confusing functional dependency with equality
• Using arithmetic symbols instead of ->
• Misreading notation as assignment

3. Given a table with attributes StudentID, CourseID, Instructor and the functional dependency CourseID -> Instructor, what does this imply?

medium

A. Each CourseID is taught by exactly one Instructor

B. Each Instructor teaches multiple CourseIDs

C. StudentID determines Instructor

D. Instructor determines CourseID

Solution

1. Step 1: Interpret the functional dependency

   CourseID -> Instructor means for each CourseID, there is one Instructor assigned.

2. Step 2: Understand implication

   This means each course has exactly one instructor, but an instructor can teach many courses.

3. Final Answer:

   Each CourseID is taught by exactly one Instructor -> Option A

4. Quick Check:

   CourseID determines Instructor = Each CourseID is taught by exactly one Instructor [OK]

Hint: Left side determines right side in functional dependency [OK]

Common Mistakes:

• Assuming Instructor determines CourseID
• Confusing StudentID with CourseID
• Thinking one instructor per student

4. A table has attributes OrderID, ProductID, Quantity, Price with functional dependencies OrderID, ProductID -> Quantity and ProductID -> Price. Which of the following is a problem in this design?

medium

A. Price depends only on ProductID, causing redundancy if repeated for each order

B. Quantity depends on OrderID only, which is incorrect

C. OrderID determines Price, which violates functional dependency rules

D. ProductID and Quantity together determine OrderID, which is wrong

Solution

1. Step 1: Analyze functional dependencies

   OrderID, ProductID -> Quantity means quantity depends on both order and product, which is correct.

2. Step 2: Identify redundancy issue

   ProductID -> Price means price depends only on product, so storing price in the order table repeats it many times.

3. Final Answer:

   Price depends only on ProductID, causing redundancy if repeated for each order -> Option A

4. Quick Check:

   Price repeated per order causes redundancy = Price depends only on ProductID, causing redundancy if repeated for each order [OK]

Hint: Check if attribute depends on full key or causes repetition [OK]

Common Mistakes:

• Ignoring redundancy caused by partial dependencies
• Mixing up which attribute depends on what
• Assuming all dependencies are correct

5. You have a table Employee(ID, Name, Dept, DeptHead) with functional dependencies ID -> Name, Dept and Dept -> DeptHead. How should you redesign the schema to avoid redundancy?

hard

A. Remove DeptHead from the schema entirely

B. Keep one table but add DeptHead as a primary key

C. Split into two tables: Employee(ID, Name, Dept) and Department(Dept, DeptHead)

D. Combine Dept and DeptHead into one attribute in Employee table

Solution

1. Step 1: Identify functional dependencies

   ID -> Name, Dept means employee ID determines name and department; Dept -> DeptHead means department determines its head.

2. Step 2: Apply normalization

   To avoid repeating DeptHead for every employee in the same department, split into two tables: one for employees and one for departments with heads.

3. Final Answer:

   Split into two tables: Employee(ID, Name, Dept) and Department(Dept, DeptHead) -> Option C

4. Quick Check:

   Separate tables for dependent attributes = Split into two tables: Employee(ID, Name, Dept) and Department(Dept, DeptHead) [OK]

Hint: Split tables where one attribute depends on another non-key attribute [OK]

Common Mistakes:

• Trying to keep all data in one table causing redundancy
• Misusing primary keys for dependent attributes
• Removing important data like DeptHead