Tasks: Continuing with the employee database you created for Group Project #2, you'll need to address...

Transcribed Image Text:

Tasks: Continuing with the employee database you created for Group Project #2, you'll need to address the concerns raised within this document by your supervisor, Matt Kozi. If you are working with a new group your team can determine which former database your team will leverage. If any records within the database references your former group number in group project #2, then you are to update said records to your current group number for group project #3. 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 'EmployeePersonal' 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 start sending all the angry employees your way. Forward me the SOL afterwards so I can review it. thanks. P.s. I attached a few of the complaints below; "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" Matt Kozi 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 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 Employee Address 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. 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. 1) Create and test a user-defined function named LastNameFirst#, where the # is your group number, that combines two parameters named FName and LName from the Employee entity into a single concatenated name field formatted as: LName, FName Be sure to include the comma and space. Provide the SQL script as your answer. 2) Create and test a view called Employees Medical Concerns that contains the employee name of any employee who has a known allergy, along with their allergy. List a concatenated field aliased as EmpName, containing both Fname and Lname attributes as LName, FName, followed by a second field containing the AllergyName field. Provide the SQL script as your answer. 3) Part of your responsibilities as a DBA has you routinely cleansing data to ensure proper data formats are being enforced across all tables. This activity, while important, consumes a significant part of your time that could be better spent working on more value-add projects. You want to partially automate the process through the development of stored procedures but would need approval from your boss. Write a brief memo to Matt Kozi, Solutions Architect, explaining the purpose of stored procedures and how you would implement stored procedures to simplify administration of the database. For your example to Mr. Kozi, you will be focusing on the Salary field in the Employee table. This component assumes that your salary field was already set to INT and has no formatting. Task: Convert the INT to a CHAR (SQL command) Stored Procedure: Insert commas, decimal places and dollar signs where appropriate in the data for the salary attribute for the Employee entity. To answer this question, you are to provide a written explanation, not the SQL script. 4) Provide your employer with the SQL script needed to build the stored procedure listed above. Section C: Updating documentation The following questions require a written explanation that must be included as a separate file from your SQL scripts. You may include Section B, Question 3's written response in this part of the submission. 1) Create a dependency graph that shows dependencies among the updated set of tables, views and stored procedures. 2) Explain why you need to develop dependency graphs which include views and database objects such as stored procedures when we already have logical and physical data models. How do dependency graphs and data models differentiate? 3) Through your recent work, you implemented a new stored procedure, while stored procedures can be helpful, they can also present challenges to data stability. Assume the following scenario is true; A stored procedure that exports health data from the Employee Personal table was running, and another stored procedure that reformats emergency contact phone data could also be running. a) Assume we just executed the stored procedure you have created for this assessment while the other two stored procedures were also running and an error occurred. Give an example of a dirty read, a nonrepeatable read, and a phantom read among this group of stored procedures. b) What concurrency control measures are appropriate for the stored procedure that you are creating? c) What concurrency control measures are appropriate for the two other stored procedures? Tasks: Continuing with the employee database you created for Group Project #2, you'll need to address the concerns raised within this document by your supervisor, Matt Kozi. If you are working with a new group your team can determine which former database your team will leverage. If any records within the database references your former group number in group project #2, then you are to update said records to your current group number for group project #3. 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 'EmployeePersonal' 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 start sending all the angry employees your way. Forward me the SOL afterwards so I can review it. thanks. P.s. I attached a few of the complaints below; "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" Matt Kozi 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 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 Employee Address 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. 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. 1) Create and test a user-defined function named LastNameFirst#, where the # is your group number, that combines two parameters named FName and LName from the Employee entity into a single concatenated name field formatted as: LName, FName Be sure to include the comma and space. Provide the SQL script as your answer. 2) Create and test a view called Employees Medical Concerns that contains the employee name of any employee who has a known allergy, along with their allergy. List a concatenated field aliased as EmpName, containing both Fname and Lname attributes as LName, FName, followed by a second field containing the AllergyName field. Provide the SQL script as your answer. 3) Part of your responsibilities as a DBA has you routinely cleansing data to ensure proper data formats are being enforced across all tables. This activity, while important, consumes a significant part of your time that could be better spent working on more value-add projects. You want to partially automate the process through the development of stored procedures but would need approval from your boss. Write a brief memo to Matt Kozi, Solutions Architect, explaining the purpose of stored procedures and how you would implement stored procedures to simplify administration of the database. For your example to Mr. Kozi, you will be focusing on the Salary field in the Employee table. This component assumes that your salary field was already set to INT and has no formatting. Task: Convert the INT to a CHAR (SQL command) Stored Procedure: Insert commas, decimal places and dollar signs where appropriate in the data for the salary attribute for the Employee entity. To answer this question, you are to provide a written explanation, not the SQL script. 4) Provide your employer with the SQL script needed to build the stored procedure listed above. Section C: Updating documentation The following questions require a written explanation that must be included as a separate file from your SQL scripts. You may include Section B, Question 3's written response in this part of the submission. 1) Create a dependency graph that shows dependencies among the updated set of tables, views and stored procedures. 2) Explain why you need to develop dependency graphs which include views and database objects such as stored procedures when we already have logical and physical data models. How do dependency graphs and data models differentiate? 3) Through your recent work, you implemented a new stored procedure, while stored procedures can be helpful, they can also present challenges to data stability. Assume the following scenario is true; A stored procedure that exports health data from the Employee Personal table was running, and another stored procedure that reformats emergency contact phone data could also be running. a) Assume we just executed the stored procedure you have created for this assessment while the other two stored procedures were also running and an error occurred. Give an example of a dirty read, a nonrepeatable read, and a phantom read among this group of stored procedures. b) What concurrency control measures are appropriate for the stored procedure that you are creating? c) What concurrency control measures are appropriate for the two other stored procedures?