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.