What is 5nf in DBMS.
Subject
Database Management System
Standard
Computer Science Engineering
Views
576
Asked By
Qayanat
Answer / Solution
The Fifth Normal Form (5NF), also known as Project-Join Normal Form (PJNF), is a level of database normalization that goes beyond the Third Normal Form (3NF) and the Boyce-Codd Normal Form (BCNF). It addresses the problem of join dependencies in a relational database schema.
To understand 5NF, let's first discuss the concept of a join dependency. A join dependency arises when a relation can be decomposed into two or more smaller relations, and the original relation can be obtained by joining these smaller relations based on certain attributes.
5NF aims to eliminate join dependencies by decomposing a relation into smaller, non-redundant relations, such that every join dependency is represented explicitly. It ensures that a database schema is free from any redundancy or anomalies caused by join operations.
The conditions for a relation to be in 5NF are as follows:
- The relation must be in Fourth Normal Form (4NF).
- The relation must not have any nontrivial join dependencies.
A nontrivial join dependency occurs when a relation can be expressed as a join of two or more smaller relations, and this join is not implied by any of the candidate keys or superkeys of the original relation.
To achieve 5NF, the relation is decomposed into smaller relations based on the nontrivial join dependencies. Each smaller relation will represent a subset of the attributes and tuples from the original relation, ensuring that no redundancy exists.
It's important to note that achieving 5NF may lead to an increase in the number of relations in the database schema, as the decomposition process creates new relations to represent the join dependencies explicitly. This can have implications on query performance and maintainability, so careful consideration is required when deciding to decompose a relation into 5NF.
Overall, the Fifth Normal Form (5NF) addresses the elimination of join dependencies in a relational database schema, ensuring that the schema is free from redundancy and anomalies caused by join operations. It represents the highest level of normalization and is typically used in complex database designs where preserving data integrity and minimizing anomalies is crucial.
Answer / Solution
Fifth Normal Form (5NF), also known as Project-Join Normal Form (PJNF), is a level of normalization in database design that goes beyond the Boyce-Codd Normal Form (BCNF). It aims to eliminate redundancy and anomalies caused by multi-valued dependencies and join dependencies. 5NF is considered the highest level of normalization and ensures the absence of all kinds of redundancy in a database schema.
To understand 5NF, let's first discuss the concept of multi-valued dependencies (MVDs) and join dependencies.
Multi-valued dependencies occur when a relation has attributes that depend on only part of a candidate key rather than the entire key. In other words, if changing the value of one attribute within a multi-valued dependency can result in multiple possible values for another attribute, then a multi-valued dependency exists.
Join dependencies occur when a relation can be decomposed into multiple smaller relations, and the original relation can be reconstructed by joining these smaller relations together.
Now, let's define 5NF. A relation is said to be in 5NF if and only if every join dependency in the relation is implied by the candidate keys of the relation.
Here's an example to illustrate 5NF:
Consider a database schema with three relations: "Teachers," "Subjects," and "Assignments.". Teachers (TeacherID, TeacherName, SubjectIDs) Subjects (SubjectID, SubjectName) Assignments (AssignmentID, TeacherID, SubjectID, AssignmentName)
In this example, the TeacherID attribute is the primary key in the Teachers relation, and the SubjectID attribute is the primary key in the Subjects relation.
Now, let's examine the multi-valued dependencies and join dependencies in this schema:
Multi-valued Dependencies:
- Teachers relation: TeacherID ->> SubjectIDs (A teacher can be associated with multiple subjects.)
Join Dependencies:
- Teachers relation + Subjects relation = Assignments relation (The Assignments relation can be reconstructed by joining the Teachers and Subjects relations using their common attributes.)
To achieve 5NF, we need to decompose the schema into smaller relations, eliminating the multi-valued dependencies and join dependencies.
Decomposed Relations: Teachers (TeacherID, TeacherName) TeacherSubjects (TeacherID, SubjectID) Subjects (SubjectID, SubjectName) Assignments (AssignmentID, TeacherID, SubjectID, AssignmentName)
By decomposing the schema, we have eliminated the multi-valued dependencies and ensured that every join dependency is implied by the candidate keys of the relations. Each relation now represents a separate entity with its own attributes, reducing redundancy and improving data integrity.
In summary, 5NF eliminates redundancy and anomalies caused by multi-valued dependencies and join dependencies. It ensures that all join dependencies are implied by the candidate keys, resulting in a highly normalized and well-structured database design.
Answer / Solution
Lets take the example of 5NF.
| SUBJECT | LECTURER | SEMESTER |
|---|---|---|
| Computer | Anshika | Semester 1 |
| Computer | John | Semester 1 |
| Math | John | Semester 1 |
| Math | Akash | Semester 2 |
| Chemistry | Praveen | Semester 1 |
In the above table, John takes both Computer and Math class for Semester 1 but he doesn't take Math class for Semester 2. In this case, combination of all these fields required to identify a valid data.
Suppose we add a new Semester as Semester 3 but do not know about the subject and who will be taking that subject so we leave Lecturer and Subject as NULL. But all three columns together acts as a primary key, so we can't leave other two columns blank.
So to make the above table into 5NF,
We can decompose it into three relations Relation1, Relation2 & Relation3:
Relation1
| SEMESTER | SUBJECT |
|---|---|
| Semester 1 | Computer |
| Semester 1 | Math |
| Semester 2 | Math |
| Semester 1 | Chemistry |
Relation2
| SUBJECT | LECTURER |
|---|---|
| Computer | Anshika |
| Computer | John |
| Math | John |
| Math | Praveen |
Relation3
| SEMESTER | LECTURER |
|---|---|
| Semester 1 | Anshika |
| Semester 1 | John |
| Semester 2 | John |
| Semester 1 | Praveen |
Top Trending Questions
- 9116 Views Thomson’s model of an atom, explain how the atom is neutral as a whole.
- 5114 Views What are the main characteristics of peer to peer systems?
- 4675 Views Draw a plot of α-particle scattering by a thin foil of gold to show the variation of the number of the scattered particles with scattering angle.
- 3712 Views Difference between Synchronous and Asynchronous Transmission
- 3484 Views Explain protocols in details.
- 3432 Views Explain advantages and disadvantages of mobile computing.
- 3321 Views Differentiate between homogeneous and heterogeneous mixtures with examples
- 3065 Views Define olfactory indicators. Name two subtances which can be used as olfactory indicator
- 3059 Views Properties of the Real Number System
- 2939 Views The first term of an AP is 5, the last term is 45 and the sum is 400. Find the number of terms and the common difference.
- 2747 Views What are canal rays? Why anode rays are called canal rays. Mention two postulates of J.J. Thomsons Model
- 2714 Views What is the difference between interactive mode and script mode in Python?
- 2700 Views Different Data Mining Tools in details
- 2614 Views Explain various Access Modifiers in Java
- 2586 Views How do you download files using ftp?
- 2581 Views What type of information is provided by MIS and DSS?
- 2521 Views What is ftp? How do you use ftp?
- 2517 Views What do you mean by data archiving?
- 2510 Views What is animation? How are animated video clips generated? Also, What are the applications of computer graphics?
- 2492 Views What Is data mining? What is the role of data archiving in data mining?
- 2490 Views Adaptability and adaption in mobile computing.
- 2489 Views What is a text-to-speech system? What are its applications?
- 2478 Views What type of creative works are given copyright? What is the period of a copyright?
- 2466 Views What is a mainframe? What are the good and bad characteristics of a mainframe?
- 2462 Views What is a recordable CDROM? How does it work?
- 2413 Views Ramkali saved Rs 5 in the first week of a year and then increased her weekly saving by Rs 1.75. If in the nth week, fher weekly savings become Rs 20.75, find n.
- 2406 Views Where is the Sahara desert located?
- 2398 Views What is speech recognition? What are its applications?
- 2395 Views What do you understand by aerodynamically floating head?
- 2320 Views What is voice mail? What are the advantages and disadvantages of voice mail?
- 2317 Views Explain the design steps of the transform mapping.
- 2314 Views Explain different types of Data Mining Techniques.
- 2308 Views What is the basic difference between transaction processing system and MIS?
- 2293 Views What is the difference between transaction processing and on-line transaction processing
- 2263 Views Write the distribution of electrons in carbon and sodium atoms
- 2250 Views Necessary conditions for autotrophic nutrition
- 2230 Views What Are the Different Types of Machine Learning?
- 2224 Views Write first four terms of the A.P. when the first term a and the common difference d are given as follows
- 2217 Views What is a DVDROM? What is its typical capacity?
- 2204 Views How do you ensure information integrity?
- 2162 Views When will you say a body is in Uniform acceleration and non-uniform acceleration?
- 2162 Views Write advantages and disadvantages of Hierarchical Data Model
- 2160 Views Explain Atomic number, Mass number, Isotopes and Isobars with some examples. Also give any two uses of isotopes.
- 2127 Views How are water and minerals transported in plants?
- 2118 Views What does an Electrical Circuit Mean, Electrical Current and its units.
- 2112 Views What is telnet? How is it used?
- 2100 Views A sum of Rs 700 is to be used to give seven cash prizes to students of a school for their overall academic performance. If each prize is Rs 20 less than its preceding prize, find the value of each of the prizes.
- 2100 Views What is SRS? Why is it important? How does one arrive at a SRS?
- 2082 Views The houses of a row are numbered consecutively from 1 to 49. Show that there is a value of x such that the sum of the numbers of the houses preceding the house numbered x is equal to the sum of the numbers of the houses following it. Find this value of x. [Hint : Sx-1 = S49 – Sx ]
- 2081 Views Which term of the AP: 21, 18, 15, . . . is – 81? Also, is any term 0? Give reason for your answer.
- 2065 Views Naresh earns 30800 per month. He keeps 50% for household expenses. what he save per month
- 2063 Views What is feasibility analysis? How is it used?
- 2059 Views What are the factors which influence the adsorption of a gas on a solid?
- 2055 Views Write a note on civilizing mission of the colonisers and Huynh Phu So
- 2052 Views Calculate the molecular masses of the following elements, Hydrogen, Oxygen, Chlorine, Carbon Di Oxide
- 2050 Views 60 percent of the students in a school are girls, If the total number of girls in the school is 690
- 2035 Views 1500 families with two children are selected randomly according to the data given. Compute the probability of a family, chosen
- 2027 Views What is rational number
- 2013 Views Define parallel connection and series connection?
- 2010 Views What is the role of desorption in the process of catalysis?
- 2009 Views What are the activities carried out during system implementation phase?
- 2006 Views Why did James Mill and Thomas Macaulay think that European education was essential in India?
- 1984 Views Explain how data mining may be used by a State Road Transport Corporation
- 1976 Views Nested Loops in C
- 1967 Views What are the different parts of a URL? Explain the purpose of each part
- 1964 Views Explain in detail about Tableau in data visualization?
- 1957 Views What rights does copyright give to the owner of the copyright?
- 1942 Views How will you find the valency of chlorine, sulphur and magnesium?
- 1924 Views What are polyatomic ions? Give examples?
- 1917 Views Describe the ideas behind the Tonkin Free School. To what extent was it a typical example of colonial ideas in Vietnam.
- 1915 Views The sum of the third and the seventh terms of an AP is 6 and their product is 8. Find the sum of the first sixteen terms of the AP.
- 1915 Views Class 8 Quiz MCQ Basic Questions
- 1899 Views A long solenoid with 15 turns per cm has a small loop of area 2 cubic cm placed inside the solenoid to its axis.
- 1889 Views What’s the difference between a generative and discriminative model?
- 1881 Views looping structure in python
- 1872 Views What is meant by Intellectual Property Rights (IPRs)?
- 1854 Views Which gas is usually liberated when an acid reacts with a metal?
- 1834 Views In a cricket match, a batsman hits a boundary 6 times out of 30 balls she plays. Find the probability that she did not hit a boundary.
- 1809 Views 2 cubes each of volume 64 cm3 are joined end to end. Find the surface area of the resulting cuboids.
- 1806 Views What is open source software? List some open source software.
- 1797 Views How is our atmosphere different from the atmospheres on Venus and Mars?
- 1778 Views Difference between Artificial intelligence and Machine learning
- 1772 Views Connect python to mysql database
- 1771 Views Explain the inference rules for functional dependencies in DBMS
- 1770 Views What is system evaluation? Why is it required?
- 1769 Views What is the syntax of Java Inheritance? Explain with example.
- 1731 Views Distinguish between speed and velocity.
- 1711 Views What is data Mining.
- 1705 Views What is Internet telephony? In what way is it different from Internet radio?
- 1703 Views If the sum of the first n terms of an AP is 4n − n2, what is the first term (that is S1)? What is the sum of first two terms?
- 1696 Views What is a Trojan? In what way is it different from a virus? How do you remove a Trojan from your PC?
- 1653 Views Explain why, when a motorcar makes a sharp turn at a high speed, we tend to get thrown to one side.
- 1648 Views What is a mobile computing?
- 1611 Views What is Internet radio? What are the advantages and disadvantages of Internet radio.
-
1603 Views
Check whether the following is quadratic equation:
(x + 1)2 = 2(x – 3) - 1601 Views Distinguish between the meaning of the terms adsorption and absorption. Give one example of each.
- 1585 Views What is Inheritance in Java? Explain.
- 1583 Views Why did a major protest erupt in 1926 in the Saigon Native Girls School in Vietnam ? Explain.
- 1577 Views How soil is formed?
- 1565 Views What is vector graphics? Where are vector graphics used?
- 1554 Views feature of mobile computing
- 1550 Views To counter the Chinese influence what steps did the French take in the sphere of education?
- 1543 Views Achievements of the Hindustan socialist Republican Association
- 1540 Views How do you know that your PC has been infected by a virus? If it is infected. how do you remove the infection?
- 1536 Views What is a computer virus? How does it infect computer systems?
- 1534 Views What is the difference between MIS and DSS?
- 1531 Views The sum of 4th and 8th terms of an A.P. is 24 and the sum of the 6th and 10th terms is 44. Find the first three terms of the A.P.
- 1523 Views What is plagiarism? Is copyright violation a criminal offence?
- 1498 Views Give an example of client-server computing
- 1495 Views What is a thin client? Why are thin clients preferred in certain applications?
- 1492 Views Types of data in mobile computing
- 1484 Views What are the three types of intellectual property? What kind of rights does IPR on computer software normally give?
- 1459 Views Shweta obtained 18 marks in a test of 25 marks. What was her percentage of marks
- 1452 Views How do you ensure security of your database?
- 1443 Views Data Mining History
- 1438 Views What is mathematics
- 1438 Views List some IT related products which are given patent rights and trademarks.
- 1426 Views What is information integrity? How is it different from data security?
- 1422 Views Two spherical bobs, one metallic and the other of glass, of the same size are allowed to fall freely from the same height above the ground
- 1407 Views What is data security? What is the difference between data privacy and security?
