Section A: Repairs, Administration and Concerns Your supervisor has sent you a series of emails with...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Section A: Repairs, Administration and Concerns Your supervisor has sent you a series of emails with remedies required for the databases functionality, you are to build out the SQL to address the concerns that have been raised, you do not need to create a formal write-up to your supervisor for this section, just the SQL as a .SQL file. 1) Address issues with the 'Employee Personal' table From: Matt Kozi To: You Subject: Employee allergy table issues, fix? Hey, we've been receiving lots of complaints from HR lately regarding failed insurance submissions that the employees are suffering from. Apparently there is some sort of screw-up with processing insurance claims for medication as our system isn't allowing employees to list out all of their known allergies/conditions and our insurer is rejecting their medical claims as these were not "known conditions". We did some investigative work and it seems like the problem is originating on your end. After going through the database documentation I noted a design error with the Employee Personal table, it only allows employees to list out a single allergy as 'AllergyName'. The problem is that employees can have lots of allergies, we don't know how many allergies that an employee can have, most have none, but some of the employees have tons of allergies. Can you do something to allow employees to list out as many allergies as they need? We can't restrict this, it has to be open-ended, we never know what conditions our employees may have. I get that you may need to update the table designs, but if you could have this done within the next two weeks it would be great, else HR is going to table designs, but if you could have this done within the next two weeks it would be great, else HR is going to start sending all the angry employees your way. Forward me the SQL afterwards so I can review it, thanks. P.s. I attached a few of the complaints below; Matt Kozi "Company Insurance refuses to cover my medication costs because my allergy isn't recorded in the employee system. I tried to add my allergies/medication information but it just deletes my existing data. I need to be able to record multiple allergies and descriptions" . "Every time I update my allergy information with HR, my previous listed allergy gets deleted or dropped or something, can we please do something about this?" "I have more than ten allergies, how am I suppose to fit that in one input field? Was any thought put into this system at all?" "I tried listing out all of my allergies separated by commas in the AllergyName field, and now I can't file any insurance claims, I'm paying out of pocket for meds right now, this is ridiculous" Solutions Architect, CBR mKozi@cbr.org From: Matt Kozi To: You Subject: Expanding database usability Hey, this is similar to the last email, but several employees have complained that they have multiple addresses but cannot record them all in the system given the current design, adjust to afford employees the ability to have multiple addresses. I don't think we can really modify the structure of the existing tables to fix this...we might need an intersection table between to resolve this? Anyway, you're the DBA, I'll let you figure it out, just forward me the SQL afterwards so I can review it, thanks. Matt Kozi Solutions Architect, CBR mKozi@cbr.org mKozi@cbr.org From: Matt Kozi To: You Subject: Database stability concerns Okay, I know I keep emailing, but we keep coming across issues with the Employee Database. Did you realize you have a PK-PK connection between EmployeeAddress entity and Address entity? I get you are trying to make the design/system more efficient by grouping the same fields on the Address table for facility address and employee address, but with a PK-PK connection there is no way you can enforce referential integrity... Can you just fix the issue so we have a proper PK-FK relationship? This shouldn't be a big fix, just make sure we aren't drastically changing the structure of the database, the last thing we need is to knock offline and apps running off of the database.... As always, send me the SQL commands you built to fix this, thanks. Matt Kozi Solutions Architect, CBR mKozi@cbr.org Section B: Expanding database functionality You will submit your SQL answers for questions 1,2, and 4 for this section as a SQL file. Your response for Question 3 will need to be included with the .PDF submission for Section C. Section A: Repairs, Administration and Concerns Your supervisor has sent you a series of emails with remedies required for the databases functionality, you are to build out the SQL to address the concerns that have been raised, you do not need to create a formal write-up to your supervisor for this section, just the SQL as a .SQL file. 1) Address issues with the 'Employee Personal' table From: Matt Kozi To: You Subject: Employee allergy table issues, fix? Hey, we've been receiving lots of complaints from HR lately regarding failed insurance submissions that the employees are suffering from. Apparently there is some sort of screw-up with processing insurance claims for medication as our system isn't allowing employees to list out all of their known allergies/conditions and our insurer is rejecting their medical claims as these were not "known conditions". We did some investigative work and it seems like the problem is originating on your end. After going through the database documentation I noted a design error with the Employee Personal table, it only allows employees to list out a single allergy as 'AllergyName'. The problem is that employees can have lots of allergies, we don't know how many allergies that an employee can have, most have none, but some of the employees have tons of allergies. Can you do something to allow employees to list out as many allergies as they need? We can't restrict this, it has to be open-ended, we never know what conditions our employees may have. I get that you may need to update the table designs, but if you could have this done within the next two weeks it would be great, else HR is going to table designs, but if you could have this done within the next two weeks it would be great, else HR is going to start sending all the angry employees your way. Forward me the SQL afterwards so I can review it, thanks. P.s. I attached a few of the complaints below; Matt Kozi "Company Insurance refuses to cover my medication costs because my allergy isn't recorded in the employee system. I tried to add my allergies/medication information but it just deletes my existing data. I need to be able to record multiple allergies and descriptions" . "Every time I update my allergy information with HR, my previous listed allergy gets deleted or dropped or something, can we please do something about this?" "I have more than ten allergies, how am I suppose to fit that in one input field? Was any thought put into this system at all?" "I tried listing out all of my allergies separated by commas in the AllergyName field, and now I can't file any insurance claims, I'm paying out of pocket for meds right now, this is ridiculous" Solutions Architect, CBR mKozi@cbr.org From: Matt Kozi To: You Subject: Expanding database usability Hey, this is similar to the last email, but several employees have complained that they have multiple addresses but cannot record them all in the system given the current design, adjust to afford employees the ability to have multiple addresses. I don't think we can really modify the structure of the existing tables to fix this...we might need an intersection table between to resolve this? Anyway, you're the DBA, I'll let you figure it out, just forward me the SQL afterwards so I can review it, thanks. Matt Kozi Solutions Architect, CBR mKozi@cbr.org mKozi@cbr.org From: Matt Kozi To: You Subject: Database stability concerns Okay, I know I keep emailing, but we keep coming across issues with the Employee Database. Did you realize you have a PK-PK connection between EmployeeAddress entity and Address entity? I get you are trying to make the design/system more efficient by grouping the same fields on the Address table for facility address and employee address, but with a PK-PK connection there is no way you can enforce referential integrity... Can you just fix the issue so we have a proper PK-FK relationship? This shouldn't be a big fix, just make sure we aren't drastically changing the structure of the database, the last thing we need is to knock offline and apps running off of the database.... As always, send me the SQL commands you built to fix this, thanks. Matt Kozi Solutions Architect, CBR mKozi@cbr.org Section B: Expanding database functionality You will submit your SQL answers for questions 1,2, and 4 for this section as a SQL file. Your response for Question 3 will need to be included with the .PDF submission for Section C.
Expert Answer:
Posted Date:
Students also viewed these general management questions
-
In the figure below, which compares different DNA extraction kits (e.g, MO BIO and PSP), which one of the two trees shows the co-occurrence of microbial taxa? Proportion of max 0 0.2 0.4 0.6 0.8...
-
Read the following as well as any other published articles on the bankruptcy of the partnership of Laventhol & Horwath: Laventhol Says It Plans to File for Chapter 11, The Wall Street Journal,...
-
Marci Ling is the bookkeeper for Samco Company, Inc. Marci has been trying to get the company's balance sheet to balance. She finally got it to balance, but she still isn't sure that it is correct....
-
Beginning finished goods inventory is 10,000 units, anticipated sales volume is 60,000 units, and the desired ending finished goods inventory is 12,000 units. What number of units should be produced?
-
Assume that Olive Corporation, in Comprehensive Problem 1, is an S corporation owned 50 percent by Linda Holiday and 50 percent by Ralph Winston. The corporation is not subject to any special taxes....
-
A firm has issued preferred stock at its $ 1 4 5 per share par value. The stock will pay a $ 1 5 annual dividend. The cost of issuing and selling the stock was $ 4 per share. The cost of the...
-
Jorge is a registered nurse at a facility that cares for about 80 elderly patients. Jorge has been at the facility longer than any of the other nurses and has his choice of schedule; he believes he...
-
Provide the calculation for determining the present value of a bond with the following characteristics: a par value of $1,000, a coupon rate of 8.29 percent (paid annually), and a maturity period of...
-
The cash-flow diagram is provided. a. If P = $2,000, A = $250, and i% = 9% per year, then N = ? b. If P = $2,000, A = $250, and N= 8 years, then i = ? c. If A = $250, 1% = 9% per year, and N=4 years,...
-
Solve x+3=2
-
What is a weakness of using a static budget? Discuss in details.
-
Current mobile devices offer Everything from ordering a pizza to playing video games to doing your banking . IS having this consumer control and access a benefit or are We ceding some control over to...
-
2-15. A spherical charge distribution has a volume charge density which is a function only of r, the distance from the center of the distribution. In other p(r). If p(r) is as given below, determine...
-
d) For die casting processes: 1. What are the most common metals processed using die casting and discuss why other metals are not commonly die casted? 2. Which die casting machines usually have a...
Study smarter with the SolutionInn App