You've been added to the DB design team for a small company, and tasked with creating...

Transcribed Image Text:

You've been added to the DB design team for a small company, and tasked with creating a table or tables as needed to keep track of the company's employees (this will eventually be integrated with the existing DB solution for HR and payroll that the company maintains). Employees may be full-time (aka exempt), part- time (aka hourly), or volunteer (aka unpaid hourly). Employees who are full-time earn an annual salary that ranges from 10k/yr to 300k/yr and gets assigned at the time they first join the company and may get increased about once a year (in which case it would be great to keep a history of salaries assigned to each employee over time). Hourly employees can earn from $15/hr to $100/hr depending on the kind of work they are doing, and they may get raises in which case their assigned rate goes up. Volunteers don't earn any money. For every employee we need to know contact information and who they report to (i.e. their supervisor; every employee reports to someone except for the CEO who reports to the board). We also need to know what type of role or position each employee has. We might want to track that by date as well, as hopefully people are moving through the ranks. Note: You have professional license here to figure out how you feel it makes to interpret these requirements; you just need to make sure your approach makes sense. Using this scenario, please do the following: 1. Create an ERD for the entities and attributes necessary. Do not create entities beyond was is specifically required for this scenario (e.g. no mention is made of tracking a schedule for hourly, so do NOT create an entity for schedule). Remember that you are only drafting a solution for a larger system. (take your time here as your choices will drive everything that comes after) 2. Create a relational schema for the ERD you created. 3. Create SQL statements to create the tables you have identified. Be sure and use constraints to define the PKs and FKs. Add drop table statements before your create statements so that the code may be run repeatedly for testing. 4. Create SQL statements to insert at least one row into each table you have created. 5. Create a SQL statement or statements shows, as well-formatted single output table, the total number of each type of employee and the average compensation by type. 6. BONUS (optional): Add notes about assumptions you made and questions you would like to get answered to better develop your approach. Your submission for this exercise should be in two parts: 1. A PDF of your ERD and Relational Schemas 2. A text file of your SQL statements (with each statement well annotated with comments) and your output for #5. You've been added to the DB design team for a small company, and tasked with creating a table or tables as needed to keep track of the company's employees (this will eventually be integrated with the existing DB solution for HR and payroll that the company maintains). Employees may be full-time (aka exempt), part- time (aka hourly), or volunteer (aka unpaid hourly). Employees who are full-time earn an annual salary that ranges from 10k/yr to 300k/yr and gets assigned at the time they first join the company and may get increased about once a year (in which case it would be great to keep a history of salaries assigned to each employee over time). Hourly employees can earn from $15/hr to $100/hr depending on the kind of work they are doing, and they may get raises in which case their assigned rate goes up. Volunteers don't earn any money. For every employee we need to know contact information and who they report to (i.e. their supervisor; every employee reports to someone except for the CEO who reports to the board). We also need to know what type of role or position each employee has. We might want to track that by date as well, as hopefully people are moving through the ranks. Note: You have professional license here to figure out how you feel it makes to interpret these requirements; you just need to make sure your approach makes sense. Using this scenario, please do the following: 1. Create an ERD for the entities and attributes necessary. Do not create entities beyond was is specifically required for this scenario (e.g. no mention is made of tracking a schedule for hourly, so do NOT create an entity for schedule). Remember that you are only drafting a solution for a larger system. (take your time here as your choices will drive everything that comes after) 2. Create a relational schema for the ERD you created. 3. Create SQL statements to create the tables you have identified. Be sure and use constraints to define the PKs and FKs. Add drop table statements before your create statements so that the code may be run repeatedly for testing. 4. Create SQL statements to insert at least one row into each table you have created. 5. Create a SQL statement or statements shows, as well-formatted single output table, the total number of each type of employee and the average compensation by type. 6. BONUS (optional): Add notes about assumptions you made and questions you would like to get answered to better develop your approach. Your submission for this exercise should be in two parts: 1. A PDF of your ERD and Relational Schemas 2. A text file of your SQL statements (with each statement well annotated with comments) and your output for #5.

Answer rating: 100% (QA)

1 Create an EntityRelationship Diagram ERD Identify the entities In this case you have mentioned employees rolespositions and salaries Identify the at... View the full answer