Tasks: Continuing with the employee database you created for Group Project #2, you'll need to address...
Fantastic news! We've Found the answer you've been seeking!
Question:
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?
Expert Answer:
Related Book For
Concepts of Database Management
ISBN: 978-1285427102
8th edition
Authors: Philip J. Pratt, Mary Z. Last
Posted Date:
Students also viewed these databases questions
-
Managing Scope Changes Case Study Scope changes on a project can occur regardless of how well the project is planned or executed. Scope changes can be the result of something that was omitted during...
-
Compare to traditional organizational development approaches. To get back on track and find a way to function more smoothly, should this taskforce use an Appreciative Inquiry approach or a more...
-
Suppose you are holding a circular ring of wire in front of you and (a) Suddenly thrust a magnet, South Pole first, away from you toward the center of the circle. Is a current induced in the wire?...
-
The data show the number of U.S. space launches for the 10-year periods from 1960 to 2009. Construct a time series graph for the data and analyze the graph. Year Launches 614 247 199 60-69 70-79...
-
The John Gore Organization owns and operates the Charles Playhouse, a theater in Boston, Massachusetts. Evelyn Castillo has diabetes, a disability under the Americans with Disabilities Act (ADA)....
-
JOB ORDER COSTING TRANSACTIONS B & L Enterprises makes garage doors. During the month of February, the company had four job orders: 303, 304, 305, and 306. Overhead was applied at predetermined...
-
The Relational Model and Logical Design (a) In the context of the relational data model, describe the following terms: (i) relation (ii) primary key (iii) foreign key (iv) null ...
-
Revenues for certain commodities with a readily available market may be recognized at production since uncertainties with respect to the sale are likely to be minimal. Select one: True False
-
(57) find the area of each triangle ABC A= 30.50 b= 13.00 cm Area of triangle = Lbh 2 C= 112.60 formula bh 2
-
Saint Joseph Hospital buys 600 boxes of latex gloves every year. Each box costs the hospital $8. The cost to place an order for the gloveswhich covers the employee staff time, shipping costs, and the...
-
Suppose BHP decides to trade in the energy options market. o Today's spot price of energy is $3,261.80. o You have obtained data for the following option contracts for your analysis. All options...
-
This Forum task contains 3 key elements: a. Share your own ideas/knowledge with your colleagues what's your best understanding or definition of capacity management, b. Provide a simple/clear...
-
HI-TECH Electronics makes highly complex electronic test equipment for thePC and telecoms industry.HI-TECH has identified thatto remain competitive in the global market it needs to cut costs and...
-
Introduction: Having the ability to effectively communicate is one of the most important skills a business executive can possess. As French businesswoman and author Mirelle Guilliano has said,...
-
What are the four types of poultry production systems? Explain each type.
-
What is a form?
-
Answer each of the following questions using the Colonial Adventure Tours data shown in Figures 1-15 through 1-19. No computer work is required. 1. List the last name of each guide that does not live...
-
In Example 1, why is the data type for the Postal Code field CHAR and not SMALLINT or INTEGER? Is the length of the field long enough? Why or why not?
-
The mean height of American males is 69.5 inches. The heights of the 43 male U.S. presidents* (Washington through Obama) have a mean 70.78 inches and a standard deviation of 2.77 inches. Treating the...
-
Contrast the assumptions for a finite distributed lag model with those for an infinite distributed lag model.
-
Compute delay, interim, and total multipliers for finite and infinite distributed lag models.
Study smarter with the SolutionInn App