SQL homework Developers are often tasked with creating interfaces that make it easy for non-developers to view and interact with information stored in databases. Often these interfaces are known...

SQL homework

Developers are often tasked with creating interfaces that make it easy for non-developers to view and interact with information stored in databases. Often these interfaces are known asContentManagementSystems. In this homework assignment, your challenge is to architect and build a solution for managing a company's employees using node, inquirer, and MySQL.

Instructions

Design the following database schema containing three tables:

• 
  

  department:

  
  
  
  
  • id- INT PRIMARY KEY
  
  
  • name- VARCHAR(30) to hold department name
  
  
  
  


• 
  

  role:

  
  
  
  
  • id- INT PRIMARY KEY
  
  
  • title- VARCHAR(30) to hold role title
  
  
  • salary- DECIMAL to hold role salary
  
  
  • department_id- INT to hold reference to department role belongs to
  
  
  
  


• 
  

  employee:

  
  
  
  
  • id- INT PRIMARY KEY
  
  
  • first_name- VARCHAR(30) to hold employee first name
  
  
  • last_name- VARCHAR(30) to hold employee last name
  
  
  • role_id- INT to hold reference to role employee has
  
  
  • manager_id- INT to hold reference to another employee that manager of the current employee. This field may be null if the employee has no manager
  
  
  
  

Build a command-line application that at a minimum allows the user to:

• 
  

  Add departments, roles, employees

  
  


• 
  

  View departments, roles, employees

  
  


• 
  

  Update employee roles

  
  

Bonus points if you're able to:

• 
  

  Update employee managers

  
  


• 
  

  View employees by manager

  
  


• 
  

  Delete departments, roles, and employees

  
  


• 
  

  View the total utilized budget of a department -- ie the combined salaries of all employees in that department

  
  

We can frame this challenge as follows:

As a business owner I want to be able to view and manage the departments, roles, and employees in my company So that I can organize and plan my business

How do you deliver this? Here are some guidelines:

• 
  

  Use theMySQLNPM package to connect to your MySQL database and perform queries.

  
  


• 
  

  UseInquirerJsNPM package to interact with the user via the command-line.

  
  


• 
  

  Useconsole.tableto print MySQL rows to the console. There is a built-in version ofconsole.table, but the NPM package formats the data a little better for our purposes.

  
  


• 
  

  You may wish to have a separate file containing functions for performing specific SQL queries you'll need to use. Could a constructor function or a class be helpful for organizing these?

  
  


• 
  

  You will need to perform a variety of SQL JOINS to complete this assignment, and it's recommended you review the week's activities if you need a refresher on this.

  
  

Hints

• 
  

  You may wish to include aseed.sqlfile to pre-populate your database. This will make development of individual features much easier.

  
  


• 
  

  Focus on getting the basic functionality completed before working on more advanced features.

  
  


• 
  

  Review the week's activities for a refresher on MySQL.

  
  


• 
  

  Check outSQL Boltfor some extra MySQL help.

  
  

Minimum Requirements

• 
  

  Functional application.

  
  


• 
  

  GitHub repository with a unique name and a README describing the project.

  
  


• 
  

  The command-line application should allow users to:

  
  




• 
  

  Add departments, roles, employees

  
  


• 
  

  View departments, roles, employees

  
  


• 
  

  Update employee roles

  
  

I've already done most of this one, I just need it completed. The files attached have most everything set up already.