
Quick Contact
DBMS Tutorial
- DBMS Tutorial
- What is Database Management System (DBMS)?
- Components of DBMS
- Applications of DBMS
- Three Schema DBMS Architecture
- Difference between DBMS and RDBMS?
- Difference between File Oriented System and DBMS
- Types of Data Models
- DBMS Schema and Instances
- Data Independence and Data Abstraction
- Database Users and Administrator
- DBMS Languages and Interfaces
DBMS ER Model
DBMS Relational Data Model
DBMS Normalization
Fifth Normal Form (5NF)
A relation R is in 5NF, also known as projection join normal form (PJ/NF)-If and only if each join dependency in R is a consequence of the candidate keys of R.
Example
Consider a relation SPJ shown below this relation is “all key” and involves no FDs or MVDs and so is 4NF. Figure shows
- The three projections SP, PJ and JS of SPJ and
- The effect of joining SP and PJ over P# and then joining the result and JS over (J#, S#). Note that the result of the first join is to produce a copy of the original SPJ plus one spurious tuple and then the effect of the second. Join is to eliminate that tuple.
The statement SPJ is equal to join of its three projection SP, PJ and JS are equivalent to the statement.

If the pair < s1,p1 > appears in SP
And the pair < p1,j1 > appears in PJ
And the pair < j1,s1 > appears in JS
Then the triple < s1,p1,j1 > appears in SPJ.
This is a constant, just like an FD or an MVD. This constraint is known as a join dependency (J.D). So, in this example, we say that SPJ satisfies the join dependency(“SP,PJ,JS“).
Definition of Join Dependency
A relation R satisfies join dependency (R1,R2,R3….Rn) if and only if R is equivalent to the join of (R1,R2,R3….Rn) where Ri is the subsets of the collection of attributes of R.
Relation SPJ suffers from several problems over update operations.
Example
Consider the relation SPJ
SPJ
S# | P# | J# |
---|---|---|
S1 | P1 | J1 |
S1 | P2 | J1 |
- If < S2,P1,J1 > inserted. < S1,P1,J1 > must also be inserted.
- Yet converse is not true.
Note:It is that constraint on SPJ is equivalent to < S1,P1,J2 >, < S2,P1,J1 >, appears in SPJ then < S1,P1,J1 > also appears in SPJ, so it is due to the presence of JD. This problem is removed when it is three decomposed.
What is Relational Decomposition?
Decomposition is the mechanism of busting the relation into multiple relations. Decompositions of relations must be certain that from the decomposed relations, an original relation can be reconstructed. If decomposition of a relation is treated carelessly, it can lead to information loss.
Types of Decomposition
Two types of decomposition are as follows.

1. Lossless Decomposition
Given the data is not hidden from the relationship because it is decomposed, later the decomposition would be lossless.
The lossless decomposition authenticates that the joining of the relationship will outcome in an equivalence relation as it was decomposed.
A relation is a lossless decomposition because the natural joins of all over the decomposition provide a standard relation.
Example
Consider the relation schema Employee_Department.
EMP-ID | EMP-NAME | DESG | SALARY | DEPT-ID | DEPT-NAME | DEPT-LOC |
---|---|---|---|---|---|---|
111 | Rohan | Manager | 20000 | D01 | Finance | Delhi |
112 | Kumar | Developer | 25000 | D02 | Production | Noida |
113 | Mahesh | Tester | 20000 | D03 | Sales | Mumbai |
114 | Anita | Professor | 30000 | D04 | Marketing | Delhi |
115 | Raman | Manager | 22000 | D05 | Testing | Gurugram |
116 | Gaurav | Developer | 15000 | D06 | Sales | Mumbai |
Decompose the above relation into two relations to check whether decomposition is lossless or lossy.
Therefore, we have decomposed the connection, which is Employee and Department.
Relation1: Employee Table
EMP-ID | EMP-NAME | DESG | SALARY |
---|---|---|---|
111 | Rohan | Manager | 20000 |
112 | Kumar | Developer | 25000 |
113 | Mahesh | Tester | 20000 |
114 | Anita | Professor | 30000 |
115 | Raman | Manager | 22000 |
116 | Gaurav | Developer | 15000 |
Relation2: Department Table
DEPT-ID | EMP-ID | DEPT-NAME | DEPT-LOC |
---|---|---|---|
D01 | 111 | Finance | Delhi |
D02 | 112 | Production | Noida |
D03 | 113 | Sales | Mumbai |
D04 | 114 | Marketing | Delhi |
D05 | 115 | Testing | Gurugram |
D06 | 116 | Sales | Mumbai |
Now, when these two relations are joined on the common field “EMP-ID”, then the resultant relation will show as.
Employee ⋈ Department
EMP-ID | EMP-NAME | DESG | SALARY | DEPT-ID | DEPT-NAME | DEPT-LOC |
---|---|---|---|---|---|---|
111 | Rohan | Manager | 20000 | D01 | Finance | Delhi |
112 | Kumar | Developer | 25000 | D02 | Production | Noida |
113 | Mahesh | Tester | 20000 | D03 | Sales | Mumbai |
114 | Anita | Professor | 30000 | D04 | Marketing | Delhi |
115 | Raman | Manager | 22000 | D05 | Testing | Gurugram |
116 | Gaurav | Developer | 15000 | D06 | Sales | Mumbai |
Therefore, the decomposition is a lossless join decomposition.
Hence, the Employee table includes (EMP-ID, EMP-NAME, DESG, SALARY) and Department table include (DEPT-ID, DEPT-NAME, and DEPT-LOC), then it isn’t always feasible to connect these two tables or relations, because there may be no common field between them. And it develops into lossy join decomposition.
2. Dependency Preservation
Dependency is an essential constraint on the information.
Each dependency needs to be fulfilled by always one decomposed table.
If (P → Q) contains, therefore two sets are functional dependent. And, it gets more helpful for inspecting the dependency only if both sets in a similar relation.
This decomposition features can simply completed by supporting the functional dependency.
In this feature, it permits to analysis the redevelops without calculating the natural join of the database mechanism.
Enroll Yourself in Live Training: DBMS Training