if uncontrolled, what can lead to data anomalies?​

Main Body

Chapter x ER Modelling

Adrienne Watt

One important theory developed for the entity relational (ER) model involves the notion of functional dependency (FD).  The aim of studying this is to better your understanding of relationships amid data and to gain enough formalism to assistance with practical database design.

Similar constraints, FDs are drawn from the semantics of the application domain. Essentially,functional dependencies draw how individual attributes are related. FDs are a kind of constraint among attributes within a relation and contribute to a good relational schema design. In this affiliate, we will wait at:

• The basic theory and definition of functional dependency
• The methodology for improving schema designs, also called normalization

Relational Design and Redundancy

By and large, a good relational database design must capture all of the necessary attributes and associations. The design should do this with a minimal corporeality of stored information and no redundant information.

In database design, redundancy is generally undesirable because information technology causes problems maintaining consistency afterward updates. Nevertheless, redundancy tin can sometimes pb to performance improvements; for example, when redundancy tin be used in identify of a join to connect data. A join is used when you need to obtain information based on 2 related tables.

Consider Effigy 10.1:  customer 1313131 is displayed twice, in one case for business relationship no. A-101 and again for account A-102. In this case, the customer number is not redundant, although in that location are deletion anomalies with the table. Having a separate client tabular array would solve this trouble. However, if a branch accost were to change, information technology would have to be updated in multiple places. If the customer number was left in the table as is, then you wouldn't demand a branch table and no bring together would exist required, and performance is improved .

Effigy ten.1. An example of redundancy used with bank accounts and branches.

Insertion Anomaly

An insertion anomaly occurs when you are inserting inconsistent information into a tabular array. When we insert a new record, such equally account no. A-306 in Figure 10.2, we need to check that the co-operative information is consistent with existing rows.

Effigy 10.2. Example of an insertion bibelot.

Update Anomaly

If a branch changes accost, such as the Circular Hill co-operative in Figure 10.iii, we need to update all rows referring to that branch. Changing existing data incorrectly is chosen an update anomaly.

Figure 10.3. Example of an update anomaly.

Deletion Anomaly

A deletion anomaly occurs when y'all delete a record that may contain attributes that shouldn't exist deleted.For case, if nosotros remove information nearly the final account at a branch, such equally business relationship A-101 at the Downtown branch in Figure x.four, all of the co-operative information disappears.

Effigy 10.4. Instance of a deletion anomaly.

The problem with deleting the A-101 row is nosotros don't know where the Downtown co-operative is located and nosotros lose all information regarding client 1313131. To avoid these kinds of update or deletion problems, we need to decompose the original tabular array into several smaller tables where each tabular array has minimal overlap with other tables.

Each depository financial institution account table must contain information nearly one entity only, such as the  Branch or Customer, as displayed in Figure x.5.

Figure 10.five. Examples of bank business relationship tables that incorporate 1 entity each, by A. Watt.

Following this practice volition ensure that when co-operative information is added or updated information technology will merely bear upon one tape. And so, when client information is added or deleted, the branch information will not exist accidentally modified or incorrectly recorded.

Example: employee project table and anomalies

Figure 10.6 shows an instance of an employee project table. From this table, we can assume that :

1. EmpID and ProjectID are a composite PK.
2. Project ID determines Budget (i.e., Project P1 has a budget of 32 hours).

Effigy 10.half dozen. Example of an employee projection tabular array, by A. Watt.

Next, let's look at some possible anomalies that might occur with this table during the following steps.

1. Action: Add row {S85,35,P1,9}
2. Trouble: At that place are two tuples with conflicting budgets
3. Action: Delete tuple {S79, 27, P3, one}
4. Problem: Step #3 deletes the upkeep for projection P3
5. Action: Update tuple {S75, 32, P1, 7} to {S75, 35, P1, vii}
6. Problem: Step #5 creates two tuples with unlike values for project P1's upkeep
7. Solution: Create a separate table, each, for Projects and Employees, as shown in Effigy ten.7.

Figure ten.7. Solution: separate tables for Project and Employee, by A. Watt.

How to Avert Anomalies

The best approach to creating tables without anomalies is to ensure that the tables are normalized, and that's accomplished by understanding functional dependencies. FD ensures that all attributes in a table belong to that table. In other words, it volition eliminate redundancies and anomalies.

Instance: dissever Project and Employee tables

Figure 10.8. Separate Project and Employee tables with data, by A. Watt.

Past keeping data carve up using individual Projection and Employee tables:

1. No anomalies will be created if a budget is changed.
2. No dummy values are needed for projects that have no employees assigned.
3. If an employee's contribution is deleted, no important information is lost.
4. No anomalies are created if an employee'southward contribution is added.

deletion bibelot: occurs when you delete a record that may contain attributes that shouldn't be deleted

functional dependency (FD): describes how individual attributes are related

insertion bibelot: occurs when yous are inserting inconsistent information into a table

join:used when you need to obtain information based on ii related tables

update anomaly: changing existing data incorrectly

1. Normalize Effigy 10.9.
   

   

   Figure 10.ix. Tabular array for question 1, by A. Watt.

2. Create a logical ERD for an online picture show rental service (no many to many relationships). Use the following description of operations on which your business rules must be based:The online movie rental service classifies picture show titles according to their type: comedy, western, classical, science fiction, cartoon, action, musical, and new release. Each type contains many possible titles, and most titles within a blazon are available in multiple copies. For example, note the following summary:Blazon Championship
   Musical My Fair Lady (Copy i)
   My Fair Lady (Copy 2)
   Oklahoma (Copy 1)
   Oklahoma (Copy 2)
   Oklahoma (Copy 3)
   etc.
3. What iii data anomalies are probable to be the consequence of information redundancy? How tin such anomalies be eliminated?

Also seeAppendix B: Sample ERD Exercises

Attribution

This chapter of Database Design (including images, except every bit otherwise noted) is a derivative copy of Relational Design Theory  by  Nguyen Kim Anh  licensed underCreative Commons Attribution License 3.0 license

The post-obit material was written past Adrienne Watt:

1. Example: employee project table and anomalies
2. How to Avoid Anomalies
3. Key Terms
4. Exercises

tickellgoodincen1944.blogspot.com

Source: https://opentextbc.ca/dbdesign01/chapter/chapter-10-er-modelling/

Share this post