Week3 MAKEME

Homework week 3

This homework is divided into several exercises. Please submit each exercise in a separate folder.

Exercise 1 : Skillz

In this exercise you will use the ´company´ database.

HR wants to keep track of the skills of different employees. For this, they suggest adding a ´skills´ column in the ´employee´ database. The idea is to write the skills as a string, for example: "Node.JS, SQL, React" or "SCRUM, product management" etc.

emp_no	emp_name	salary	reports_to	skills
1	John	5000		SCRUM, product management
2	Daenerys	3000	1	Node.JS, SQL, React

You know that this is not good database design so you suggest an alternative approach that complies with database normal forms.

Hint: you need to add extra table(s).

Submit the SQL queries to create the extra table(s) for employee skills with the correct columns.

Exercise 2 : Transactions

In this exercise you will use the company database.

It turns out that many department are adopting a flat structure. In this flat structure, there is one manager for the department and everyone reports to him. There are no middle-managers. You need to write a javascript function that access as parameters a department number and employee number function flatify(dept_no, emp_no). The function then updates the database and makes this employee a manager for the department and also sets all employees in that department to report to him. Because the function uses two queries make sure to use transactions. In case of an error rollback the transaction.

Exercise 3 : SQL injection

In this exercise you will use the world database.

You are given the below function which returns the population of a specific country or city.

function getPopulation(cityOrCountry, name,cb) {
  // assuming that connection to the database is established and stored as conn
  conn.query(`SELECT Population FROM ${cityOrCountry} WHERE Name = ${name}`, function (err, result) {
    if (err) cb(err);
    if ( result.length == 0) cb(new Error('Not found'));
    cb(null, result[0].name);
  });
}

Give an example of a value that can be passed as ´name´ that would take advantage of SQL-injection (for example, to insert new fake data in the database).

Rewrite the function so that it is no longer vulnerable to SQL injection and submit your code.