Multi-valued and Joined Dependencies

In contrast to the functional dependency, the multi-valued dependency requires that certain tuples be present in a relation. Therefore, a multi-valued dependency is also referred to as a tuple-generating dependency. Multi-valued dependencies is also called as MVDs. It express a condition among tuples of a relation that exists when the table or relation is trying to represent more than one many-many relationship. Then certain columns or attributes become independent from one another and their values must appear in all combinations. A joined dependency is denoted by JD (R1, R2,……, Rn ) on relational schema R. It specifies a constraint on the states r of R. The constraint states that every legal state r of R is equal to the join of its projections on R1, R2,….., Rn. That is, for every such r we have: ∏R1 (r) * ∏R2 (r) * … * ∏Rn (r) = r The lossless-join property refers to the fact that whenever we decompose the relations using normalization we can rejoin the relations to produce the original relation. A lossless-join dependency is called as a property of decomposition which ensures that no spurious tuples are generated when relations are natural joined. There are cases when it is necessary to decompose a relation into more than two relations to guarantee a lossless-join. Fifth normal form (5NF) is based on join dependencies. A relation is said to be in fifth normal form (5NF), if and only if every non-trivial join dependency is implied by the super keys of R. A table is said to be in 5NF if and only if it is in 4NF and every join dependency in it is implied by the candidate keys. 5NF is always achievable.

Summary

In contrast to the functional dependency, the multi-valued dependency requires that certain tuples be present in a relation. Therefore, a multi-valued dependency is also referred to as a tuple-generating dependency. Multi-valued dependencies is also called as MVDs. It express a condition among tuples of a relation that exists when the table or relation is trying to represent more than one many-many relationship. Then certain columns or attributes become independent from one another and their values must appear in all combinations. A joined dependency is denoted by JD (R1, R2,……, Rn ) on relational schema R. It specifies a constraint on the states r of R. The constraint states that every legal state r of R is equal to the join of its projections on R1, R2,….., Rn. That is, for every such r we have: ∏R1 (r) * ∏R2 (r) * … * ∏Rn (r) = r The lossless-join property refers to the fact that whenever we decompose the relations using normalization we can rejoin the relations to produce the original relation. A lossless-join dependency is called as a property of decomposition which ensures that no spurious tuples are generated when relations are natural joined. There are cases when it is necessary to decompose a relation into more than two relations to guarantee a lossless-join. Fifth normal form (5NF) is based on join dependencies. A relation is said to be in fifth normal form (5NF), if and only if every non-trivial join dependency is implied by the super keys of R. A table is said to be in 5NF if and only if it is in 4NF and every join dependency in it is implied by the candidate keys. 5NF is always achievable.

Things to Remember

  • In contrast to the functional dependency, the multi-valued dependency requires that certain tuples be present in a relation. Therefore, a multi-valued dependency is also referred to as a tuple-generating dependency.
  • Multi-valued dependencies is also called as MVDs. It express a condition among tuples of a relation that exists when the table or relation is trying to represent more than one many-many relationship. Then certain columns or attributes become independent from one another and their values must appear in all combinations.

  • A joined dependency is denoted by JD (R1, R2,……, Rn ) on relational schema R. It specifies a constraint on the states r of R. The constraint states that every legal state r of R is equal to the join of its projections on R1, R2,….., Rn. That is, for every such r we have:
    ∏R1 (r) * ∏R2 (r) * … * ∏Rn (r) = r

  • The lossless-join property refers to the fact that whenever we decompose the relations using normalization we can rejoin the relations to produce the original relation. A lossless-join dependency is called as a property of decomposition which ensures that no spurious tuples are generated when relations are natural joined. 

  • Fifth normal form (5NF) is based on join dependencies. A relation is said to be in fifth normal form (5NF), if and only if every non-trivial join dependency is implied by the super keys of R. A table is said to be in 5NF if and only if it is in 4NF and every join dependency in it is implied by the candidate keys. 5NF is always achievable.

MCQs

No MCQs found.

Subjective Questions

Q1:

Write the concept of UNDP ( United Nations Development Program ) and also write its aims ?


Type: Short Difficulty: Easy

Show/Hide Answer
Answer: <p>UNDP is the UN's global development network, an organization advocating for change and connecting countries to knowledge, experience, and resources to help people build a better life. It is the largest multilateral organization in the world. It is working in 177 countries and territories, working with them on their own solution to global and national development challenge. It is established in 1955 and has been supporting the Nepalese people in their struggle against poverty since 1963.</p>
<p>&nbsp;</p>
<p><strong>Aims</strong></p>
<p>_ To support the people in their struggle against poverty.</p>
<p>_ To build up the capacity of government agencies, civil society, and community group to fight poverty.</p>
<p>_ To bring these groups and Nepal's donor together to design and implement successful poverty alleviation projects.</p>

Q2:

Whata re the functions of UNDP ?


Type: Long Difficulty: Easy

Show/Hide Answer
Answer: <p>UNDP links and coordinates global and national efforts to achieve the goals and national development priorities laid out by host countries. UNDP focuses primarily on five developmental challenges:</p>
<p>&nbsp;</p>
<p><strong>Democratic governance</strong></p>
<p>UNDP supports national democratic transitions by providing policy advice and technical support, improving institutional and individual capacity within countries, educating populations about and advocating for democratic reforms, promoting negotiation and dialogue, and sharing successful experiences from other countries and locations. UNDP also supports existing democratic institutions by increasing dialogue, enhancing national debate, and facilitating consensus on national governance programmes.</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p><strong>Poverty reduction</strong></p>
<p>UNDP helps countries develop strategies to combat poverty by expanding access to economic opportunities and resources, linking poverty programmes with countries&rsquo; larger goals and policies, and ensuring a greater voice for the poor. UNDP also works at the macro level to reform trade, encourage debt relief and foreign investment, and ensure the poorest of the poor benefit from globalisation.</p>
<p>&nbsp;</p>
<p>On the ground, UNDP sponsors developmental pilot projects, promotes the role of women in development , and coordinates efforts between governments, NGOs, and outside donors.</p>
<p>&nbsp;</p>
<p><strong>Crisis prevention and recovery</strong></p>
<p>UNDP works to reduce the risk of armed conflicts or disasters and promote early recovery after the crisis has occurred. UNDP works through its country offices to support local government in needs assessment, capacity development, coordinated planning, and policy and standard setting.</p>
<p>&nbsp;</p>
<p><strong>Environment and Energy</strong></p>
<p>As the poor are disproportionately affected by environmental degradation and lack of access to clean, affordable water, sanitation and energy services, UNDP seeks to address environmental issues in order to improve developing countries&rsquo; abilities to develop sustainably, increase human development and reduce poverty.</p>

Videos

No videos found.

Multi-valued and Joined Dependencies

Multi-valued and Joined Dependencies

Multi-valued Dependencies

In contrast to the functional dependency, the multi-valued dependency requires that certain tuples be present in a relation. Therefore, a multi-valued dependency is also referred to as a tuple-generating dependency. Multi-valued dependencies are also called as MVDs. It expresses a condition among tuples of a relation that exists when the table or relation is trying to represent more than one many-many relationship. Then certain columns or attributes become independent from one another and their values must appear in all combinations.

Formal Definition:

Let R be a relation schema and let α ⊆ R and β ⊆ R. The multi-valued dependency is:

α →→β

holds on R if in any legal relation r(R), for all pairs for tuples t1 and t2in r such that t1[α ] = t2 [α ], there exists tuples t3 and t4in r such that:

t1[α ] = t2 [α ] =t3[α ] = t4[α ]

t3[ β ] =t1[ β]

t3[ R-β ] =t2[ R-β ]

t4[ β ] =t2[ β ]

t4[ R-β ] =t1[ R-β]

Tabular representation of α →→β

.

Example: Customers(name, addr, phones, sodasLiked)

  • A customer's phones are independent of the sodas they like.
  • Many-to-many relationships: Customers⇔ Phones and Customers⇔ Sodas
  • Each phone appears with each soda in all combinations.
  • E.g.: For 3 phones (Home, Work, Cell) and 10 sodas, we need 30 tuples
  • There is only one FD: name→ addr

Tuples implied by name->->phones

  • If we have tuples:

    .

If we have first two tuples (1 and 2), then the last two tuples (3 and 4) must also be in the relation. Name-phone and Name-soda relations are independent.

Joined Dependencies

A joined dependency is denoted by JD (R1, R2,……, Rn ) on relational schema R. It specifies a constraint on the states r of R. The constraint states that every legal stater of R is equal to the join of its projections on R1, R2,….., Rn. That is, for every such r we have:

∏R1 (r) * ∏R2 (r) * … * ∏Rn (r) = r

The lossless join property refers to the fact that whenever we decompose the relations using normalization we can rejoin the relations to produce the original relation. A lossless-join dependency is called as a property of decomposition which ensures that no spurious tuples are generated when relations are natural joined. There are cases when it is necessary to decompose a relation into more than two relations to guarantee a lossless-join.

Fifth normal form (5NF) is based on join dependencies. A relation is said to be in the fifth normal form (5NF), if and only if every non-trivial join dependency is implied by the super keys of R. A table is said to be in 5NF if and only if it is in 4NF and every join dependency in it is implied by the candidate keys. 5NF is always achievable. Examples are:

  • Consider R (S_id, S_name, Status, City) with S_id and S_name candidate keys
  • ( {S_id, S_name, Status}, {S_id, City} ) is a JD because S_id is a candidate key in R
  • ( {S_id, S_name}, {S_id, Status}, {S_name, City} ) is a JD because S_id and S_name are both candidate keys in R.

Example: Consider a relation Supply (sname, partName, projName)

.

References:

  1. H.F.Korth and A. Silberschatz,"Database system concepts",McGraw Hill,2010
  2. A.K.Majumdar and p, Bhattacharya,"Database Management Systems",Tata McGraw Hill,India,2004
  3. F.Korth, Henry. Database System Concepts. 6th edition.



Lesson

Database Constraints and Normalization

Subject

Computer Engineering

Grade

Engineering

Recent Notes

No recent notes.

Related Notes

No related notes.