Please prove detailly answer for all the questions blew, thank you so much. (1)
ID: 3741865 • Letter: P
Question
Please prove detailly answer for all the questions blew, thank you so much.
(1) ( File Structures )
Consider the File Structure for the XYZ Company Project Management data given below. Then, respond to each of the following questions related to the structure.
ProjectCode
ProjectManager
MgrTelephone
MgrLocation
ProjBidPrice
31-205A
Sami A. Allen
773-555-1216
2900 S. Federal St. Chicago, IL 60616
$46,000
37-403B
Cecily D. Worth
773-555-1217
27 Beckley Rd. Battle Creek, MI 49015
$1,342,000
33-906T
Daisy B. Burns
773-555-8821
2543 W. Foster Ave. Chicago, IL 60625
$847,320
29-107D
Alice M. Zane
773-555-1219
7202 Harrison Ave. Rockford, IL 61112
$1,449,000
21-929A
Dean P. Pence
773-555-2222
6302 N. Northwest Hwy. Chicago, IL 60631
$903,117
41-386C
Cecily D. Worth
773-555-1217
27 Beckley Rd. Battle Creek, MI 49015
$1,805,000
26-903C
Sami A. Allen
773-555-1216
123 Lane St. Chicago, IL 60616
$78,081
29-227A
Cecily D. Worth
773-555-1217
27 Beckley Rd. Battle Creek, MI 49015
$2,550,273
(a) If the XYZ Company wishes to display a listing of the Project Codes alphabetically by the right - most character, what problem(s), if any, would you encounter?
Would it make any sense to solve this problem by altering the file structure?
(b) What problem would you encounter if you desire to produce a listing by state? How would you solve this problem by altering the file structure?
(c) If you wanted to produce a listing of the file contents by last name, area code, city, state or zip code, how would you alter the file structure?
(d) What data redundancies do you detect? How could those redundancies lead to anomalies?
(e) The ProjBidPrice column, in the given table, appears to have a wide range of values. An XYZ Company database clerk suggests splitting the Project Management data into two file structures, as this may make it easier to search for values in this column. What issues, if any, could be resolved by proceeding with the clerk’s suggestions? What issues, if any, could be resolved by proceeding with the clerk’s suggestions?
(2) ( Database File Structures: Data Redundancy )
Consider the File Structure for the ABC Company Project Management data given below. Then, respond to each of the following questions related to the structure.
ProjectNum
ProjectName
EmpNum
EmpName
JobCode
Job_Chg_Hour
Proj_Hours
Emp_Phone
1001
Thunder
121
Daisy B. Burns
AB
80.00
14.2
773-555-1216
1001
Thunder
217
Alice M. Zane
CD
65.00
15.7
773-555-1216
1002
Chicago
821
Dean P. Pence
BD
95.00
14.4
773-555-1231
1002
Chicago
219
Cecily D. Worth
CD
80.00
17.2
773-555-1217
1003
Archimedes
222
Sami A. Allen
EH
65.00
24.9
773-555-8821
1003
Archimedes
121
Cecily D. Worth
AB
95.00
37.8
773-555-1219
1003
Archimedes
516
Denny T. Li
HW
80.00
22.2
773-555-2222
1004
Emerald
355
Danny T. Li
UG
65.00
19.7
773-555-2222
1005
Diamond
217
Alice M. Zane
CD
80.00
19.5
773-555-1216
1005
Diamond
222
Sami A. Allen
BD
95.00
22.3
773-555-8821
(a) List and discuss two additional fields that could or should appear on the above table structure.
(b) Identify and discuss the serious data redundancy problems exhibited by the file structure shown in the given data sheet.
ProjectCode
ProjectManager
MgrTelephone
MgrLocation
ProjBidPrice
31-205A
Sami A. Allen
773-555-1216
2900 S. Federal St. Chicago, IL 60616
$46,000
37-403B
Cecily D. Worth
773-555-1217
27 Beckley Rd. Battle Creek, MI 49015
$1,342,000
33-906T
Daisy B. Burns
773-555-8821
2543 W. Foster Ave. Chicago, IL 60625
$847,320
29-107D
Alice M. Zane
773-555-1219
7202 Harrison Ave. Rockford, IL 61112
$1,449,000
21-929A
Dean P. Pence
773-555-2222
6302 N. Northwest Hwy. Chicago, IL 60631
$903,117
41-386C
Cecily D. Worth
773-555-1217
27 Beckley Rd. Battle Creek, MI 49015
$1,805,000
26-903C
Sami A. Allen
773-555-1216
123 Lane St. Chicago, IL 60616
$78,081
29-227A
Cecily D. Worth
773-555-1217
27 Beckley Rd. Battle Creek, MI 49015
$2,550,273
Explanation / Answer
(a) If the XYZ Company wishes to display a listing of the Project Codes alphabetically by the right - most character, what problem(s), if any, would you encounter?
There will be a confusion created when 2 rightmost character are same eg:- 41-386C, 26-903C, then how we do decide when should be displayed first. Both have the same rightmost character, may be the second last character can break the tie and so on. So this will be a major problem that we may encounter. Also, even if the rightmost character is unique, we need to sort the entire records and make sure the Id maps to that particular record only after sorting is done else it will lead to data Inconsistency issues.
(b) What problem would you encounter if you desire to produce a listing by state? How would you solve this problem by altering the file structure?
If we decide to sort the records by state, if two records have similiar state names, then we cant decide which record will be displayed first based on sorting, maybe Pincode can help to break the tie in such a case. Also we are given a Location from this its very difficult to make out which is the State Name that we need to consider. Eg:- 27 Beckley Rd. Battle Creek, MI 49015, in this its nearly difficult to figure out the State Name to be used for Sorting. I will create a separate column in the file called STATE_NAME and then use this column for Sorting purpose.
(c) If you wanted to produce a listing of the file contents by last name, area code, city, state or zip code, how would you alter the file structure?
So, i will first create a new File, with the column headings as:- Last_Name, Area_Code, City, State, ZipCode and then from the original file break out each piece of info and place under the newly created headings in the new file so that data retrieval and sorting of records are much easier. But data extraction process will be too painful and tedious task.
(d) What data redundancies do you detect? How could those redundancies lead to anomalies?
I see the Manager Name who is tagged to 2 different projects, if his Mobile Number gets updated, we need to make sure to update all such references else it will lead to update Anomaly.
Eg:- Sami Allen handles 2 projects, and both records contains his Mobile#, If we update his Mobile #, we need to update 2 references in this case, else data will become inconsistent.
Solution:- Create a separate Table, called CONTACT_DETAILS, which will contain (MANAGER_NAME, MOBILE_NO). So update this table for the corresponding Manager Name, and it will reflect everywh
31-205A
Sami A. Allen
773-555-1216
26-903C
Sami A. Allen
773-555-1216
e) The ProjBidPrice column, in the given table, appears to have a wide range of values. An XYZ Company database clerk suggests splitting the Project Management data into two file structures, as this may make it easier to search for values in this column. What issues, if any, could be resolved by proceeding with the clerk’s suggestions? What issues, if any, could be resolved by proceeding with the clerk’s suggestions?
Advantages:-
Issues:-
Please let me know in case of any clarifications required. Thanks!
31-205A
Sami A. Allen
773-555-1216
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.