Good database design and normalization critical thinking example

Downsides to the Approach Used to Store and Manage NSJI’s Data

There are several downsides to the approach used to store and manage NSJI’s data. These problems are as a result of the repeating entries in the Excel data. The first problem is that of poor and inefficient space utilization in data storage (Hall 398). Although data storage costs are low and almost insignificant, the data as stored in the Excel file occupies more storage space than necessary. For instance, there exist two rows associated with employer with ID 10122 that contain repeated employer details such as EmployerName, Address, ContactFirstName, and ContactLastName. The second problem is potential inconsistencies when updating data. Assuming an employer or firm changes its contact person, then all entries associated with that employer ought to be updated accordingly. Not only does this increase the NSJI’s staff workload, it also increases the probability of leaving some entries unchanged, which causes data inconsistencies. From an auditor’s perspective, failing to update all records will cause audit problems because it will be difficult to produce an accurate audit report without up-to-date data. For the company, having data that is not current will affect their operations as it could lead to errors (Hall 399). If, for instance, an employer’s contact person details are not updated on all records associated with the employer, a potential client may end up calling the wrong person who may not even be working for the company at that time. This can decrease the credibility of the company and cause loss of revenue. Another problem that the company may face due to the use of flat files is task-data dependency. This happens when an employee or user changes his or her role and then encounters problems trying to access data that he or she needs to carry out the new tasks (Hall 399).

Minimizing Impact of Problems

An ERD (Conceptual Model) for NSJI
The following are the tables:
Employer (EmployerID, EmployerName, Comments, NAICSCode, AddressID, NAICSCode, ContactPersonID, Position, Phone)
Foreign key: NAICSCode to NAISCDetails , AddressID to Address and ContactPersonID to ContactPerson
NAISCDetails (NAICSCode, NAICSDesc)
ContactPerson (ContactPersonID, ContactFirstName, ContactLastName, Web)
Position (PositionID, PositionTitle, Wage, HoursPerWeek, Experience, StartDate, EndDate, Openings, EmployerID)
Foreign key: EmployerID to Employer
Data Model in 3rd Normal Form

Works Cited

Hall, James A. Accounting Information Systems. 7th ed. Mason, OH: South-Western Cengage Learning, 2011.