Question

******Please don't use handwriting and i need unique answer ... Thank you

Q3:

Consider the following relation:

Student-Dept = (Student-ID, Course, SportActivity, Dept-Name, Building)

Having following multivalued dependencies:

            F ={ Student-ID ®® Course

                   Student-ID ®® SportsActivity               

      Dept-Name ®® Building }

Explain in your own words why the Student-Dept relation is not in 4NF. Then, convert the Student-Dept relation in 4NF. Also, provide the justification for each step you perform during normalization (4NF). Note: The SportActivity here means any sport a student is participating in. For example, a student with ID = 123 can participate in soccer and badminton.

Q4:

In your own word, explain why do designers use Denormalization? What is the limitation of using Denormalization? Name and explain a better alternative approach than Denormalization.

Answer 1

The answer to question no 4

why do designers use Denormalization?

Denormalization is a methodology utilized on a formerly standardized information base to expand execution. The thought behind it is to add excess information where we figure it will help us the most. We can utilize additional credits in a current table, add new tables, or even make occurrences of existing tables. The standard objective is to diminish the showing season of select questions to making information more open to the inquiries or by creating summed up reports in independent tables. This cycle can bring some new issues, and we'll examine them later.

A standardized information base is the beginning stage for the denormalization cycle. It's critical to separate from the information base that has not been standardized and the information base that was standardized first and afterward denormalized later. The subsequent one is alright; the first is regularly the consequence of an awful information base plan or an absence of information.

What is the limitation of using Denormalization?

1. Disk space: This is expected, as we’ll have duplicate data.
2. Data anomalies: We have to be really mindful of the fact that in more than one location, data will now be modified. We need to change each piece of duplicate information accordingly. For calculated values and studies, that also applies. For all tasks that must be performed simultaneously, we will do this by using triggers, transfers, or procedures.
3. Documentation: We should appropriately report each denormalization decide that we have applied. On the off chance that we change information base plan later, we'll need to take a gander at all our special cases and mull over them indeed. Possibly we needn't bother with them any longer since we've comprehended the issue. Or on the other hand perhaps we have to add to existing denormalization rules. (For instance: We added another characteristic to the customer table and we need to store its set of experiences esteem along with all that we as of now store. We'll need to change existing denormalization decides to accomplish that).
4. Slowing other operations: We should assume that data addition, alteration, and deletion operations would slow down. This may be an advantage if these operations happen very occasionally. Basically, we will split one slow selection into a greater number of slower queries for insertion / update / delete. Although the whole machine may be greatly slowed down mechanically by a very complicated select question, slowing down several "smaller" operations does not harm the usability of our program.
5. More coding: Rules 2 and 3 will entail extra coding, but some select queries will be significantly simplified at the same time. If we denormalize an current database, in order to benefit from our work, we would have to change these chosen queries. For current documents, we'll even have to change values for newly introduced attributes. This would take a little bit of coding, too.

Name and explain a better alternative approach than Denormalization.

In spite of the fact that, denormalized composition can significantly improve execution under extraordinary read-stacks however the updates and embeds become intricate as the information is copy and thus must be refreshed/embedded in more than one spots.

One clean approach to tackling this issue is using triggers. For instance for our situation where the requests table has the product_name segment too, when the estimation of product_name must be refreshed, at that point it should essentially be possible in the accompanying manner:

• Have a trigger arrangement on the items table that refreshes the product_name on any update to the items table.
• Execute the update question on the items table. The information would consequently be refreshed in the requests table in light of the trigger.

Nonetheless, while denormalizing the composition, do mull over, the occasions you would refresh records contrasted with the occasions you would execute SELECTs. When blending standardization and denormalization, center around denormalizing tables that are understood concentrated, while tables that are compose escalated keep them standardized.