Week1 README

This document is divided into two parts. First is the pre-class preparation and the second is the reading material.

Pre-Class Preparation

1. Install MySQL using the following official docs
2. MAC users may install Brew Package Manager via ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)" then use brew install mysql

MySQL setup

This setup assumes MySQL version 8.0. Windows users should use Microsoft MySQL Command Line client. Linux and MAC users should use gnome-terminal and Terminal respectively. Microsoft MySQL Command Line client gives you a msql> prompt after typing in your root password. Note that this password is the one you used for root user of the mysql. Linux and MAC users can execute mysql -uroot -p and then type the password. Following commands should be run under the mysql> prompt:

mysql> create user 'hyfuser'@'localhost' identified with mysql_native_password by 'hyfpassword';

# If this does not work try the alternative command:
mysql> create user 'hyfuser'@'localhost' identified by 'hyfpassword';

# This command creates a user 'hyfuser' with password 'hyfpassword' for
# the database server at 'localhost'

mysql> grant all privileges on *.* to 'hyfuser'@'localhost';
# This command gives all permissions to user 'hyfuser'.
# (*.*) means every table of every database.

mysql> create database userdb;
#This command creates a database 'userdb'

Node setup

This setup assumes that you have Node.js 0.6 or higher. We use mysqljs driver which can be installed using npm install mysql

Verification of the correct setup

Run node connection-test.js from VScode(Windows) or the terminal(Linux or MAC). The output should be The solution is: 2. connection-test.js can be found in the Week1 folder.

Reading material

What is information?

Information is something that adds knowledge. It tells you something you didn't know before. For example:

"At 9:30 on Sunday November 10th 2019 Wouter Kleijn was walking on the Sarphatistraat in Amsterdam."

This is information because it adds knowledge: you now know where Wouter was at a particular point in time. For a computer this might be a bit difficult to understand though because it is just an English sentence. It's much easier if you structure it this way:

• Address:
  • Street: Sarphatistraat
  • City: Amsterdam
• Who: Wouter Kleijn
• When: 2019-11-10T09:30:00+01:00 (+01:00 refers to the Amsterdam timezone)
• Activity: Walking

A database will allow you to store structured information and at a later point in time retrieve that information again. You can ask the database: "Who was walking on the Sarphatistraat on November the 10th 2019?" and the database will tell us: "Wouter Kleijn".

Entities & Relationships

You abstract (generalize) to make sense of the world. You do it everywhere, all the time. Even without thinking about it. As Zachery Tellman states: "To abstract is to treat things which are different as equivalent". You speak of a dog called Bello, and since it's a dog you also know that Bello is an animal and a pet. This is not something that Bello will tell you, humans made all this up. Dog, animal, pet, humans, all these are abstractions.

One way of abstracting is to think of entities and their relationships. An entity is an abstraction. It represents a certain category of things, like: humans, women, men, animals, pets, broken bicycles, chairs, music, teachers, chewing gum, and planets. You can think of a pet as an entity that has a relationship to another entity human, its owner. More formally you can say a human owns zero or more pets.

When creating an application you need to think of all the entities and their relationships that are relevant to our application, you call this the application's domain. Together these entities and relationships form the domain model for your application.

What is a database?

A database's primary purpose is to provide a mechanism for storing and retrieving structured information. There are many different types of databases but they all provides these two capabilities.

If you just consider these two properties (information storage and retrieval) you could implement a database using just a JavaScript array:

1. Information storage. Pushing onto the array adds knowledge:

const musicians = [
  "John Coltrane",
  "Miles Davis",
  "Thelonious Monk",
  "Sonny Rollins"
];

musicians.push("Steve Lehman");

2. Information access. Using the [] operator you can access what was previously stored:

console.log(musicians[0]); // prints: 'John Coltrane'

While this JavaScript database is a nice and simple example, You will focus primarily on much more sophisticated relational databases, in particular MySQL.

In a relational database you model entities in terms of tables (think spreadsheets) and you model relationships between those tables. A relationship can be one of the three following types:

1. One-to-one, for example: "a person has one profile"
2. One-to-many, for example: "in a house live zero or more people"
3. Many-to-many, for example: "a musician can play one or more instruments and an instrument can be played by one or more musicians"

What is the role of a database in an application?

A database setup typically involves two components, a server and a client. The server is the actual database management system and runs as a process on a machine either on your computer or on another computer in a data center somewhere. The client is a program that talks to the database management system (the server), so it has to know where that server is running. The client then creates a TCP connection (if you're interested in some more details look up the TCP protocol, otherwise you can forget about TCP for now) to the server. To do this the client needs to know:

• the server's address
  • an IP address like 192.168.1.5
  • or a name like my-db-server
• a username
• a password
• the name of the database

The client would be your web application talking to the database. The reason why you would want to use a database is that you can store information in a reliable and structured way. The database will ensure your information is stored safely (if setup correctly), with a high degree of reliability. The database also allows you to structure your information in such a way that we're able to find what we're looking for.

What is SQL

SQL (Structured Query Language) is a programming language used for managing the data that is stored in a DBMS (DataBase Management System). There are several implementations (software) of DBMS. Each software provides its own query language. For this course, you will learn MySQL.

What are datatypes

When you store data in MySQL, each datum (singular of the word data) needs to be associated with its type. For example numbers like 42, 1636 or -345 are all associated with the type int. Following is the list of most frequently used data types.

Type	Description	Example Value
int	Numbers	42
float	Decimal numbers	3.14
varchar(N)	String with variable maximum of N characters	"Dragon"
text	String with fixed maximum of 65535 characters	"Positive"
datetime	Store date and time without timezone	2019-01-01 22:10:23
timestamp	Store date with timezone (e.g. last login)	2019-01-01 22:10:23 UTC
BLOB	Store binary files	an image

There are many more data types. You can read about them here

Using SQL to CRUD (Create, Read, Update, Delete)

With the knowledge of all the datatypes, you can now create tables that contain the data with these datatypes. Tables contain columns and columns have datatypes. For example, in a column with names of students, you cannot have numbers.

• MySQL provides a CREATE TABLE statement that creates a table with columns. You can choose the table name, column names but you have to choose the pre-defined datatypes supported by MySQL. For example, a column Registration number cannot have the data type number. It must use int because it represents the numeric datatype.

• MySQL provides SELECT statement which reads (columns and rows) from a table with or without filtration.

• MySQL provides UPDATE statement which changes the contents of (columns and rows of) a table.

• MySQL provides DELETE statement which can delete rows of tables. In order to delete columns, you need to use ALTER and DROP statements.

Database dump

A database dump (aka SQL dump) contains a record of the table structure and/or the data from a database and is usually in the form of a list of SQL statements. (An example file named world.sql is present in the Week1 folder)

1. Collecting the dump of an existing database from terminal mysqldump -uroot -p database_name > dump-file.sql
2. Applying the dump from mysql command prompt (mysql>) source /path/to/the/dump/file
3. Applying the dump from the terminal(with generally a dollar prompt $) mysql -uroot -p [database] < /path/to/the/dump/file