Analysing the Relation Schema for Update Anomaly

Fwe

ICT503

DATABASE SYSTEMS

ASSESSMENT 1

PART B

Student ID –

Student Name -

Analysing the Relation Schema for Update Anomaly

There are many attributes from different entities in the database within the provided relation schema called Abnormal_Rel: ProductID, BranchID, CampaignID, MemberID, etc.; many other attributes related to products, members, and campaigns. This schema also has the possibility of creating update anomalies, which create inconsistent data and redundancy.

Analysing the Concept of Update Anomalies:

An update anomaly is a condition where a particular data item is stored in many places; therefore, there are many updates when changes are needed. Whenever any attribute in the developed schema is changed, the modified attribute may take positions in several rows; thus, inconsistency may arise. For Abnormal_Rel, let one think of the CampaignStartDate and the CampaignEndDate. These dates are replicated for each product related to a particular campaign.

For instance, if a marketing campaign has been shifted a week earlier in its time frame than the previous campaigns, one is required to shift the start date of the new campaign on all records that are linked to that campaign. If one of these records is omitted, there will be data disparities for some of the entries with proper start dates and others without the suitable date. This not only deteriorates the quality of data but also increases the problem of data management since users need to make changes in multiple rows instead of a single record (Choi et al., 2021, p. 14).

Example of the Update Anomaly

Suppose a marketing campaign whose CampaignID is one is linked with three different products. The original CampaignStartDate is in 2024, to be precise, the first of October of 2024. If the start date is suddenly changed to 2024-11-01, then this change is required to be entered in all three rows in which the CampaignID column has one as its value. If an Employee does not update one of the rows, inaccuracies arise; some rows will show the date has changed, while others will show the date has not been changed. This not only complicates the possibility of setting timelines for campaigns but also prevents making decisions where data is questionable.

Normalization of the Relation Schema to Third Normal Form (3NF)

Normalisation in Database Systems simply means the arrangement of data in a database to eliminate repeating data. Partition relations in a system to come up with small tables that address the data relation to be met without straining the technique (Kraska et al., 2021, p. 3).

Step 1: First Normal Form (1NF)

About 1NF, all attributes must be subdivisible, and all tables should have an exception for a foreign key, one unique column. As can be seen in the case of Abnormal_Rel, the attributes are usually atomic. However, the situation should be controlled in such a way that there will be no more repeating of a certain group. This leads to the fact that the relation is divided into separate tables that represent different entities.

Step 2: Second Normal Form (2NF)

After that, the accounts with Common Non-1:1 Partial Dependencies should be removed, as well as the 1:1 Partial Accounts. Analysing the attributes of Abnormal_Rel confirms that the ProductType, PackageType, Brand, and others are based upon ProductID, and the member-related attributes are based upon MemberID. Therefore, it is decomposed the relation as follows:

• Product Table:

Product (ProductID, ProductType, PackageType, Brand, YearProduced, Price)

• Member Table:

Member (MemberID, FirstName, LastName, Email, MembershipLevel, MemberExpDate, BranchID)

Step 3: Third Normal Form (3NF)

The final step, and perhaps the most crucial step, on which not yet commented, is to remove all transitive dependencies, whereby non-key parts are dependent on other non-key parts. In the Member table, BranchID can lead to many other branch-related attributes being added many times, leading to redundancy (Martinez et al., 2023, p. 117).

Thus, it is separated into the Branch information:

Branch Table:

Branch (BranchID, BranchName, Location)

By organizing the schema into:

1. Product (ProductID, ProductType, PackageType, Brand, YearProduced, Price)

2. Branch (BranchID, BranchName, Location)

3. Member (MemberID, FirstName, LastName, Email, MembershipLevel, MemberExpDate, BranchID)

4. Campaign (CampaignID, CampaignStartDate, CampaignEndDate, Discount)

5. Stock (ProductID, BranchID, StockLevel)

It ensure that all relations are in 3NF.

References

Choi, H., Lee, S. and Jeong, D., 2021. Forensic recovery of SQL server database: Practical approach. IEEE Access, 9, pp.14564-14575. https://ieeexplore.ieee.org/abstract/document/9328241

Kraska, T., Alizadeh, M., Beutel, A., Chi, E.H., Ding, J., Kristo, A., Leclerc, G., Madden, S., Mao, H. and Nathan, V., 2021. Sagedb: A learned database system. https://dspace.mit.edu/handle/1721.1/132282.2

Martinez, I.E., Villaescusa, D.G., Mugarza, I., Yarza, I. and Agirre, I., 2023. Software Updates Monitoring & Anomaly Detection. In IoTBDS (pp. 115-122). https://www.scitepress.org/Papers/2023/118220/118220.pdf