The following question is based upon the SALES relation above that lists details
ID: 3564458 • Letter: T
Question
The following question is based upon the SALES relation above that lists details of sales made in a hardware shop. Commission Percentage: the percentage of the total sales made by a salesperson that is paid as commission to that salesperson. Year of Hire: the year the salesperson was first hired Department Number: the number of the department where the salesperson works Manager Name: name of the manager of the department You have been asked to design a relational database based on this design. You know that there are problems with the current design and that it will need to be modified in order to work effectively. You need to write a report that addresses the following: What are the specific problems associated with the current design and why do they arise? How would you change the current design and how does your new design address the problems you have identified with the current design. In order to receive high marks for this question, you will need to demonstrate an understanding of the theories discussed in Topics 1, 2 and 3 and how they apply to this problem. Simply providing the amended design (even if it is correct) will only attract a small percentage of the marks for this question.Explanation / Answer
Problem with current design are :
1. Data Redundancy : There are large number of redundant data. When a particular salesman like Wilson sales a good, a large amount of column need to be filled again like Salesman Number, Commission, Manager Name ,Joining Data which is same for salesman. Due to redundant data, database become large in size and need more space and money to maintain.
2. Update Anamoly : This database is hard to update. This arise due to many rows having identical value for a column. If value of that column is change ,it need to be update for many rows. It make database hard to maintain. For example, if manger of Wilson is changed from Scott to Lopez, 3 rows need to be updated.
3. Insertion Anamoly : It is hard to insert new data when some column is NULL for row. This arise due to interdependence of various unrelated data.Suppose column SalesMan No and Product No is set as not null and a new SalesMan joins the company and his data(Name,Manger/Joining year) is need to be updated in database. The values cant be update in database as Product No cant be set as NULL.
4. Delete Anamoly : In current database,required data will be lost if a whole row is deleted. This arise due to interdependence of various unrelated data. For example, Sales of SalesMan Woodrow got returned, then whole row get deleted and all information related to Woodrow like name, SaleMan No,Manger Name got delted too.
2. There problem can be solved by normalization of database. Problem can be solved by :
a) Identification of primary key for table.
b) Dividing the table further so that unrelated data are not dependent on each other.
c) Every Non key attribute should be dependent on key attribute. This help to remove redundant data.
d) No non key attribute should be dependent on any other non key attribute.
3NF database :
Current Database need to be divided into 3 database:
1. Table salesManInfo
Primary Key : SalesMan Number
2. Table ProductInfo
Primary Key : Product Number
3. Table DepartmentInfo :
Primary Key : Department Number
Lopez
4. Table salesInfo
Primary Key : SalesMan Number and Product Number
And so on.
How the new design helps in resolving the 3 other anamolies?
2. Update Anamoly : In previous ,if price of Hammer is increased from $17.55 to $18 , 2 rows need to be updated. But in current design , if price of Hammer need to be update only one row of Table ProductInfo need to be updated.
3. Insertion Anamoly : In previous design ,suppose column SalesMan No and Product No is set as not null and a new SalesMan joins the company and his data(Name,Manger/Joining year) is need to be updated in database. The values cant be update in database as Product No cant be set as NULL .
In current design, the record of new SalesMan can be updated in SalesManInfo table. And when new SalesMan sales his product, salesInfo table can be updated later for new SalesMan.
4. Delete Anamoly : In previous database design, if Sales of SalesMan Woodrow got returned, then whole row get deleted and all information related to Woodrow like name, SaleMan No,Manger Name got delted too.
In current design, if sales of SalesMan Woodrow got returned, the one row in SalesInfo table will be deleted. But his personal record(Name,Year of Joining,Department,Manager Name) will be avaliable in SalesManInfo table for future use.
SalesMan Number SalesMan Name Commission % Year of Hire Department No. 437 Bilstein 10 1995 73 585 Reardon 15 2001 59 204 Woodrow 10 1998 73 361 Wilson 20 2001 73Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.