SQLite
SQLite INSERT, UPDATE, DELETE Query with Example
The data modification clauses in SQLite are INSERT, UPDATE, and DELETE statements. It is used for...
Functional Dependency (FD) is a constraint that determines the relation of one attribute to another attribute in a Database Management System (DBMS). Functional Dependency helps to maintain the quality of data in the database. It plays a vital role to find the difference between good and bad database design.
A functional dependency is denoted by an arrow "→". The functional dependency of X on Y is represented by X → Y. Let's understand Functional Dependency in DBMS with example.
Example:
Employee number | Employee Name | Salary | City |
1 | Dana | 50000 | San Francisco |
2 | Francis | 38000 | London |
3 | Andrew | 25000 | Tokyo |
In this example, if we know the value of Employee number, we can obtain Employee Name, city, salary, etc. By this, we can say that the city, Employee Name, and salary are functionally depended on Employee number.
In this tutorial, you will learn:
Here, are some key terms for Functional Dependency in Database:
Key Terms | Description |
Axiom | Axioms is a set of inference rules used to infer all the functional dependencies on a relational database. |
Decomposition | It is a rule that suggests if you have a table that appears to contain two entities which are determined by the same primary key then you should consider breaking them up into two different tables. |
Dependent | It is displayed on the right side of the functional dependency diagram. |
Determinant | It is displayed on the left side of the functional dependency Diagram. |
Union | It suggests that if two tables are separate, and the PK is the same, you should consider putting them. together |
Below are the Three most important rules for Functional Dependency in Database:
There are mainly four types of Functional Dependency in DBMS. Following are the types of Functional Dependencies in DBMS:
Multivalued dependency occurs in the situation where there are multiple independent multivalued attributes in a single table. A multivalued dependency is a complete constraint between two sets of attributes in a relation. It requires that certain tuples be present in a relation. Consider the following Multivalued Dependency Example to understand.
Example:
Car_model | Maf_year | Color |
H001 | 2017 | Metallic |
H001 | 2017 | Green |
H005 | 2018 | Metallic |
H005 | 2018 | Blue |
H010 | 2015 | Metallic |
H033 | 2012 | Gray |
In this example, maf_year and color are independent of each other but dependent on car_model. In this example, these two columns are said to be multivalue dependent on car_model.
This dependence can be represented like this:
car_model -> maf_year
car_model-> colour
The Trivial dependency is a set of attributes which are called a trivial if the set of attributes are included in that attribute.
So, X -> Y is a trivial functional dependency if Y is a subset of X. Let's understand with a Trivial Functional Dependency Example.
For example:
Emp_id | Emp_name |
AS555 | Harry |
AS811 | George |
AS999 | Kevin |
Consider this table of with two columns Emp_id and Emp_name.
{Emp_id, Emp_name} -> Emp_id is a trivial functional dependency as Emp_id is a subset of {Emp_id,Emp_name}.
Functional dependency which also known as a nontrivial dependency occurs when A->B holds true where B is not a subset of A. In a relationship, if attribute B is not a subset of attribute A, then it is considered as a non-trivial dependency.
Company | CEO | Age |
Microsoft | Satya Nadella | 51 |
Sundar Pichai | 46 | |
Apple | Tim Cook | 57 |
Example:
(Company} -> {CEO} (if we know the Company, we knows the CEO name)
But CEO is not a subset of Company, and hence it's non-trivial functional dependency.
A Transitive Dependency is a type of functional dependency which happens when t is indirectly formed by two functional dependencies. Let's understand with the following Transitive Dependency Example.
Example:
Company | CEO | Age |
Microsoft | Satya Nadella | 51 |
Sundar Pichai | 46 | |
Alibaba | Jack Ma | 54 |
{Company} -> {CEO} (if we know the compay, we know its CEO's name)
{CEO } -> {Age} If we know the CEO, we know the Age
Therefore according to the rule of rule of transitive dependency:
{ Company} -> {Age} should hold, that makes sense because if we know the company name, we can know his age.
Note: You need to remember that transitive dependency can only occur in a relation of three or more attributes.
Normalization is a method of organizing the data in the database which helps you to avoid data redundancy, insertion, update & deletion anomaly. It is a process of analyzing the relation schemas based on their different functional dependencies and primary key.
Normalization is inherent to relational database theory. It may have the effect of duplicating the same data within the database which may result in the creation of additional tables.
The data modification clauses in SQLite are INSERT, UPDATE, and DELETE statements. It is used for...
Before we learn about a database, let us understand - What is Data? In simple words, data can be...
To write SQL queries in an SQLite database, you have to know how the SELECT, FROM, WHERE, GROUP...
What is the DELETE Query? MySQL DELETE command is used to delete rows that are no longer required from...
What is ER Modeling? Entity Relationship Model (ER Modeling) is a graphical approach to database...
SQL is the standard language for dealing with Relational Databases. SQL can be used to insert,...