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.

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 34 : SQL injection