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

Practice - 5 Tasks

1fill in blank

easy

Complete the SQL query to select the attribute that functionally determines others.

DBMS Theory

SELECT [1] FROM Employees;

Drag options to blanks, or click blank then click option'

ADepartment

BSalary

CEmployeeID

DAge

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing an attribute that is not unique, like Salary or Department.

The EmployeeID uniquely identifies each employee, so it functionally determines other attributes.

2fill in blank

medium

Complete the SQL query to find rows where the functional dependency might be violated.

DBMS Theory

SELECT EmployeeID, Department FROM Employees WHERE Department [1] 'Sales';

Drag options to blanks, or click blank then click option'

A<>

B=

C>

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' which selects only Sales department employees.

We want to find employees not in the Sales department to check if the dependency holds, so we use <> (not equal).

3fill in blank

hard

Fix the error in the SQL query that checks functional dependency violations.

DBMS Theory

SELECT [1], COUNT(*) FROM Employees GROUP BY [1] HAVING COUNT(*) > 1;

Drag options to blanks, or click blank then click option'

ASalary

BEmployeeID

CDepartment

DAge

Attempts:

3 left

💡 Hint

Common Mistakes

Grouping by non-unique attributes like Department or Salary.

Grouping by EmployeeID helps find duplicates violating uniqueness, which breaks functional dependency.

4fill in blank

hard

Fill both blanks to create a query that finds attributes functionally dependent on EmployeeID.

DBMS Theory

SELECT EmployeeID, [1] FROM Employees WHERE [2] = 'HR';

Drag options to blanks, or click blank then click option'

ASalary

BDepartment

CEmployeeID

DAge

Attempts:

3 left

💡 Hint

Common Mistakes

Using EmployeeID in the WHERE clause instead of Department.

Salary depends on EmployeeID, and we filter by Department = 'HR' to check functional dependency within HR.

5fill in blank

hard

Fill all three blanks to create a query that checks for functional dependency violations by counting duplicates.

DBMS Theory

SELECT [1], [2], COUNT(*) FROM Employees GROUP BY [3] HAVING COUNT(*) > 1;

Drag options to blanks, or click blank then click option'

AEmployeeID

BDepartment

CEmployeeID, Department

DSalary

Attempts:

3 left

💡 Hint

Common Mistakes

Grouping by only one attribute when two are needed.

Select EmployeeID and Department, group by both to find duplicates violating functional dependency.