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.

Exercise 4 : Mongo CRUD

Use the import of the world database into Atlas (haha!) that was shown in class.

CRUD stands for Create, Read, Update, Delete. You will:

1. Create a new record (document) for a new city (your home town, say)
2. Update that record with a new population
3. Read the document that you just updated in two ways : finding by the city name, and then by the country code
4. Delete the city

You will do the above steps in the database directly (A), and again using node.js libraries mongoose and express (B).

A) Submit the 5 mongodb commands in a file called mongoqueries.txt

B) Edit the code from your own Week 3 Node.js homework by creating a new branch.

git checkout my-week3-nodejs-branch-already-existed
git checkout -b dbweek3-mongo-new-branchname

Require mongoose. Create a new route which allows you to CRUD the above. Use your atlas connection details. Follow this guide . When done, commit and make a pull request to your own repository:

git push -u origin
git request-pull my-week3-nodejs-branch-already-existed origin dbweek3-mongo-new-branchname

Find the pull request (PR) in github.com, assign the homework grader to notify them (first add them as collaborator to your repo in Settings). And copy the url of the PR to the TOP of your makeme file

cd databases/Week3
{ echo -n 'https://github.com/MYGITHUBNAME/Node.js/pull/123456/files '; cat MAKEME.md; } > MAKEME.md

Exercise 5 : Relational vs Not-Only-SQL

Add a 6th and 7th query to mongoqueries.txt which answer

1. Amongst the countries with more than 50% Arabic speakers, what is the average percentage Arabic speakers? Use this mongo sql comparison
2. List the top 10 cities of Western Europe in descending order of population. If aggregation pipelines were covered in class, follow a stackoverflow guideline . Otherwise, use the lookup function . You can also choose instead to do this in node & mongo