Finding Additional functional dependencies in a relation
Last Updated :
04 May, 2020
A
functional dependency is simply a constraint between two sets of attributes from the database. A functional dependency is used in normalization. A functional dependency is denoted by an arrow → .The functional dependency of A on B is represented by A → B. Functional Dependency plays a vital role in finding the difference between good and bad database design.
A→B
Above Functional dependency is pronounced as :
- A determines B
- A functionally determines B
- B is functionally dependent on A
- For a given value of A, we can functionally determine the value of B.
A and B can be a set of attributes, they need not be single attributes always.
Example :
AB→B, A→ ACB etc.
With a given relation, we are also given a set of Functional dependencies most of the time according to the semantics of the database design.
Most interesting thing is that we can yield more
additional functional dependencies by applying some rules on the functional dependencies given.
Example:
A relation R(AB) is given with functional dependencies F : { A→B, B→A }. Find out the additional functional dependencies derivable from the given functional dependencies on the relation R.
Explanation:
Step-1: Finding out the set of possible functional dependencies:
Assume the functional dependency as A→B. A functional dependency will have Left side that is A here and Right side that is B here.
So the valid attribute we can have in either side is given below:
\begin{center}
\begin{tabular}{ |c|c|c| }
\hline
Left & & Right \\
\emptyset & & \emptyset \\
A & * & A \\
B & & B \\
AB & & AB \\
\hline
\end{tabular}
\end{center}
Therefore, number of functional dependencies possible = 4*4 = 16
Step-2: Listing out all possible functional dependencies with 2 attributes AB of the relation R:
Following are the list of all 16 possible functional dependencies with 2 attributes AB of the relation R:
\begin{center}
\begin{tabular}{ |c|c|c| }
\hline
\emptyset & \rightarrow & \emptyset \\
\emptyset & \rightarrow & A \\
\emptyset & \rightarrow & B \\
\emptyset & \rightarrow & AB \\
\hline
\end{tabular}
\end{center}
\begin{center}
\begin{tabular}{ |c|c|c| }
\hline
A & \rightarrow & \emptyset \\
A & \rightarrow & A \\
A & \rightarrow & B \\
A & \rightarrow & AB \\
\hline
\end{tabular}
\end{center}
\begin{center}
\begin{tabular}{ |c|c|c| }
\hline
B & \rightarrow & \emptyset \\
B & \rightarrow & A \\
B & \rightarrow & B \\
B & \rightarrow & AB \\
\hline
\end{tabular}
\end{center}
\begin{center}
\begin{tabular}{ |c|c|c| }
\hline
AB & \rightarrow & \emptyset \\
AB & \rightarrow & A \\
AB & \rightarrow & B \\
AB & \rightarrow & AB \\
\hline
\end{tabular}
\end{center}
Step-3: Checking if the possible functional dependencies are derivable or not:
In this step, we check if the possible functional dependencies are derivable by a given set of functional dependencies or not, by using the closure set of attributes.
Φ → Φ : derivable since its a trivial functional dependency.
A→Φ : derivable since phi is a subset of A
A→A : derivable since its a trivial functional dependency.
A→B : derivable from the given functional dependency A→B.
A→AB : derivable since the closure set of A gives AB.
B→Φ : derivable since phi is a subset of B
B→A : derivable from the given functional dependency B→A.
B→B : derivable since its a trivial functional dependency.
B→AB : derivable since the closure set of B gives AB.
AB→Φ : derivable since its a trivial functional dependency.
AB→A : derivable since its a trivial functional dependency.
AB→B : derivable since its a trivial functional dependency.
AB→AB : derivable since its a trivial functional dependency.
The rest of the possible dependencies are not valid.
Therefore, the total number of additional functional dependencies that can be determined by the given functional dependencies A→B and B→A on relation R is 13, including A→B, B→A are listed below :
Φ→Φ, A→Φ, A→A, A→B, A→AB, B→Φ, B→A, B→B, B→AB, AB→Φ, AB→A, AB→B, AB→AB
Similar Reads
What is OSI Model? - Layers of OSI Model The OSI (Open Systems Interconnection) Model is a set of rules that explains how different computer systems communicate over a network. OSI Model was developed by the International Organization for Standardization (ISO). The OSI Model consists of 7 layers and each layer has specific functions and re
13 min read
DBMS Tutorial â Learn Database Management System Database Management System (DBMS) is a software used to manage data from a database. A database is a structured collection of data that is stored in an electronic device. The data can be text, video, image or any other format.A relational database stores data in the form of tables and a NoSQL databa
7 min read
Introduction of ER Model The Entity-Relationship Model (ER Model) is a conceptual model for designing a databases. This model represents the logical structure of a database, including entities, their attributes and relationships between them. Entity: An objects that is stored as data such as Student, Course or Company.Attri
10 min read
TCP/IP Model The TCP/IP model is a framework that is used to model the communication in a network. It is mainly a collection of network protocols and organization of these protocols in different layers for modeling the network.It has four layers, Application, Transport, Network/Internet and Network Access.While
7 min read
Operating System Tutorial An Operating System(OS) is a software that manages and handles hardware and software resources of a computing device. Responsible for managing and controlling all the activities and sharing of computer resources among different running applications.A low-level Software that includes all the basic fu
4 min read
Computer Network Tutorial A Computer Network is a system where two or more devices are linked together to share data, resources and information. These networks can range from simple setups, like connecting two devices in your home, to massive global systems, like the Internet. Below are the main components of a computer netw
7 min read
Normal Forms in DBMS In the world of database management, Normal Forms are important for ensuring that data is structured logically, reducing redundancy, and maintaining data integrity. When working with databases, especially relational databases, it is critical to follow normalization techniques that help to eliminate
7 min read
ACID Properties in DBMS In the world of DBMS, transactions are fundamental operations that allow us to modify and retrieve data. However, to ensure the integrity of a database, it is important that these transactions are executed in a way that maintains consistency, correctness, and reliability. This is where the ACID prop
8 min read
Introduction of DBMS (Database Management System) A Database Management System (DBMS) is a software solution designed to efficiently manage, organize, and retrieve data in a structured manner. It serves as a critical component in modern computing, enabling organizations to store, manipulate, and secure their data effectively. From small application
8 min read
SQL Query Interview Questions SQL or Structured Query Language, is the standard language for managing and manipulating relational databases such as MySQL, Oracle, and PostgreSQL. It serves as a powerful tool for efficiently handling data whether retrieving specific data points, performing complex analysis, or modifying database
15+ min read