Week3 MAKEME

Homework week 1

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

Exercise 1 : Normal forms

Hack Your Future is building a new website and we need your help. This is the data we want to show for each course/class.

The idea was to create two tables: class(id, description, numberofstudents, startdate, teacherid, teachername, teacheremail) and students(id, name, grade).

Can you help us to normalize the database? Write a single javascript file with commands to create the tables for the database and insert the data from the example above.

Exercise 2 : Transactions

Exercise 3 : SQL injection

Write a node-JS program to

1. create a database company
2. create a table Employees with at least the following fields (emp_no, emp_name, salary, reports_to,).
3. create a table Departments with at least the following fields (dept_no, dept_name, manager)
4. Create a table Projects with at least the following fields (proj_no, proj_name, starting_date, ending_date)

• You are encouraged to add more fields for all tables.
• Insert 10-20 rows in each table with relevant fields.
• You can either write queries in a text file and upload the text file
• You can also take the SQL dump of the company database and submit the dumpfile.sql (recommended).

Part 2 : Select queries on the "world" database

For this part of the homework, use the world.sql file in the week1 folder. The world.sql is an SQL dump which is a list of SQL commands. Thes commands create the country and city tables and insert a lot of values in them. Now you can, write SQL queries on this database.

Write a node-JS program to query (using select statements) the world database to answer following questions
1. What are the names of countries with population greater than 8 million
2. What are the names of countries that have “land” in their names ?
3. What are the names of the cities with population in between 500,000 and 1 million ?
4. What's the name of all the countries on the continent ‘Europe’ ?
5. List all the countries in the descending order of their surface areas.
6. What are the names of all the cities in the Netherlands?
7. What is the population of Rotterdam ?
8. What's the top 10 countries by Surface Area ?
9. What's the top 10 most populated cities?
10. What is the population of the world ?

• You can submit one queries.js that contains all ten queries.