Question 2

TNF Company has been using a file-oriented system to store data for decades. In a recent review, the management found out that there are numerous errors in the stored data. Therefore, the management is now considering switching to a database system. You are tasked to review the existing system and recommend how to make the transition into a database system.

You start from reviewing the inventory file. The existing system stores all data in a single file with each of the following data elements as a single column:

Required:

1. Identify and describe an update anomaly, an insert anomaly, and a delete anomaly that would occur when the data is stored in a single file. For each anomaly, explain clearly the situation and list three data elements that would be affected by the anomaly.

2. Design a relational database that is free of anomalies. For each table in the relational database, indicate clearly the table name, the primary key of the table, the non-key attributes, and any foreign key from other tables.

Hint: you can introduce new data elements as unique identifiers for items in a table to be the primary key of the table.

3. Design queries to answer the following questions. For each query, indicate clearly the Table, Field, any necessary criteria, and any operations needed (e.g. SUM).

a. How many products in category A does the company have in stock?

b. How many units of products were returned to Supplier A last month (March 20X2)?