Every time when I complete my new Database design, I always ask a questioned my self “is this right Database Design?”. My simple logic is to follow the basic; “There is nothing that is truly right or wrong and that what we think of as right or wrong is really just determined by our requirement and the logic We use to handle our database”.  

Here are some basics that is necessary to remember while designing New Database.
 

What is a functional dependency?

Functional dependency is a relationship that occurs when one attribute of relation is uniquely determines another attribute. If ‘R’ is a relation with attributes ‘A’ and ‘B’, a functional dependency between the attributes is represented as A→B, which specifies ‘B’ is functionally dependent on ‘A’. where; A is a determinant set, B is a dependent attribute. Each value of ‘A’ is associated with exactly one B value. Functional dependency in a database assists as a constraint between two sets of attributes. Functional dependency defining is an important part of relational database design and it contributes to aspect normalization.

Functional Dependency is when one attribute governs other attribute in a DBMS system. Functional Dependency plays a vital role to make big difference between good and bad database design.

Example:

Employee number	Employee Name	Salary	City
1	Martin	70000	San Francisco
2	Aviska	58000	London
3	Andrew	45000	Tokyo

 

In above example, if we know the Employee number, we can find Employee Name, city, salary, etc.

With this fact, we can say that the city, Employee Name, and salary are functionally depended on Employee number.

A functional dependency is symbolized by an arrow →

The functional dependency of A on B is represented by A →B

 

Multivalued dependency in DBMS

Multivalued dependency is the situation where there are multiple independent multivalued attributes occurred in a single table. A multivalued dependency is a constraint between two sets of attributes in a relation. It requires that certain tuples be present in a relation.

Example:

Car_model	Maf_year	Color
A001	2010	Green
A001	2013	Metallic
A005	2012	Black
A005	2019	Metallic
A010	2017	Blue
A033	2016	Gray

 

In above example, color and maf_year are independent of each other but dependent on car_model. In this example, these two columns (color and maf_year) are said to be multi value dependent on car_model.

The Trivial dependency is a set of attributes which are included in that attribute.

So, A → B is a trivial functional dependency if B is a subset of A.

For example:

Emp_id	Emp_name
AS555	Harry
AS811	George
AS999	Kevin

 

In this table 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}.

Non trivial functional dependency in DBMS

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
Google	Sundar Pichai	47
Apple	Tim Cook	58

Example:

(Company} → {CEO} (if we know the Company, we know name of CEO)

Here CEO is not a subset of Company, and Thence it's non-trivial functional dependency.