⇦ Back

WDX-180

Web Development X

Week 33 | Intro to Databases

Week 33 - Day 1 | Introduction to Databases

Schedule

Watch the lectures
Study the suggested material
Practice on the topics and share your questions

Study Plan

Your instructor will share the video lectures with you. Here are the topics covered:

Part 1: Gravatars and hashes.
Part 2: Intro to Relational Databases
Part 3: Intro to Relational Databases

References & Resources:

A Shelfish Starter Guide to Databases
Try SQL online (no DB installation on your system required)
- https://sqlfiddle.com (Select SQLite)
- https://www.db-fiddle.com (Select SQLite)
SQL at W3Schools
The School Database as a Spreadsheet
Gravatar
- https://docs.gravatar.com/api/avatars/images/
- https://docs.gravatar.com/api/profiles/
Calculate SHA256 using the CLI:
- echo -n somestring | sha256sum
How we store information in a relational database:
- Find the Entities you need: Think about the type of data you want to store (think Entities, the types of things we want to store: products, movies, students, transactions, accounts, gravatars)
  - define Entity: “a thing with distinct and independent existence.”
- Think about the specific properties that describe these Entities
- Create Tables for each of these Entities
  - You have to come up a very specific and precise Schema (aka Shape): boils down to number of columns, name of these columns and the data types (integers, strings)
- Think and describe the relationship between those Entities
  - One-to-Many, One-to-One, Many-to-Many
The SQL ISO Standard
List of relational database management systems
SQL.js (SQLite compiled from C to JS (webassembly))
- SQLite available data types
- Cheatsheets:
  - https://vhernando.github.io/sqlite3-cheat-sheet
  - https://www.sqlitetutorial.net/sqlite-cheat-sheet/

Exercises

Watch and try all the examples in this video: SQL Tutorial for Beginners | SQL Crash Course
Google for examples (real-life) of all the SQL relations (1-1, 1-m, m-m)
https://sqlfiddle.com/
https://www.db-fiddle.com/
Challenge: add a phone table to the School Database with a 1-1 relation
Study this SO thread to learn more about secure DB IDs:
- Exposing database IDs - security risk?
How to use Gravatar to display users’ avatar on your app based on their email, without exposing their email
- 1) First you have to get the user’s email (always with consent)
- 2) Calculate a special hash based on their email:
  - https://docs.gravatar.com/api/avatars/hash/
  - https://www.gravatar.com/avatar/3b3be63a4c2a439b013787725dfce802?d=identicon
  - One-way hash: email => hash
  - There’s no way to get the email <= hash (no other way round)
  - Guide for Node.js
  - SHA256 for the Browser
Study the resources
Install SQLite on your system and explore the Getting Started CLI guide
- Don’t forget: While exploring new technologies, try to be active on Slack!

IMPORTANT: Make sure to complete all the tasks found in the daily Progress Sheet and update the sheet accordingly. Once you’ve updated the sheet, don’t forget to commit and push. The progress draft sheet for this day is: /user/week33/progress/progress.draft.w33.d01.csv

You should NEVER update the draft sheets directly, but rather work on a copy of them according to the instructions found here.

Week 33 - Day 2 | SQL Databases

Schedule

Study the suggested material
Practice on the topics and share your questions

Study Plan

Today is a good opportunity to watch the following videos related to Relational Databases (aka SQL-databases):

One of the best introductions: SQL Tutorial for Beginners | SQL Crash Course (1h)
Intro to Databases by Al Zimmerman
An amazing introduction to SQL by the incredible Eddie Woo
If you are into Podcasts or just want to take a little break AFK and take a walk, here’s a great episode by “Kopec Explains Software”, explaining Databases. We highly recommend that you check out the other episodes of this Podcast.

When you are done going through the theory, jump into practice through this interactive SQL book:

Select Star SQL
Here’s the repository with the code accompanying the “SQL Crash Course”
- https://github.com/in-tech-gration/sql-crash-course

Week 33 - Day 3 | SQLite

Schedule

Watch the lectures
Study the suggested material
Practice on the topics and share your questions

Study Plan

Your instructor will share the video lectures with you. Here are the topics covered:

Part 1: Relational Databases - A Full stack overview
Part 2: SQLite & The Database Murder Mystery

You can find the lecture diagrams here

Lecture Notes & Questions:

CLI: sqlite3
- .help
- .quit
- Create a new DB: sqlite3 test.db

Lecture Questions:

What is the difference between SQL and MySQL?
- SQL is the Structured Query Language standard
- MySQL is a Database and a variation of the SQL
  - Software + SQL: a DB server that speaks a particular dialect of SQL
- SQLite is a Database software and a SQL dialect
- MySQL, SQLite, PostgreSQL, SQL Server are RDBMS
What Database should I learn or use?
- Try to stick with either MySQL or PostgreSQL (through Supabase)
  - MariaDB is the open source version of MySQL
After I install SQLite on Windows, there’s no sqlite3 or sqlite command available. What’s going on?
- Check the PATH configuration:

References & Resources:

Star this repo please: https://github.com/NUKnightLab/sql-mysteries
Some cloud SQL services: https://gist.github.com/bmaupin/0ce79806467804fdbbf8761970511b8c
VSCode Extension: SQLite Viewer
Visual JOIN
DB Browser for SQLite

Exercises

Solve the DB Mystery game: https://in-tech-gration.github.io/database-mysteries/
Design the Schema for what your Smartphone’s Contact table looks like and start adding entries and querying. Think how your phone will look up a number when you write the name. SMS => Emily => query => 6912345678 => Send message.
Try: https://selectstarsql.com/

You should NEVER update the draft sheets directly, but rather work on a copy of them according to the instructions found here.

Week 33 - Day 4 | SQL Practice Day

Schedule

Study the suggested material
Practice on the topics and share your questions

Study Plan

Time to earn your first SQL certificate!

Head over to the Exercises section below and start the Solo Learn SQL course.

Exercises

Time to earn your first SQL certificate! Try to complete the Solo Learn SQL intro course.
Done with SoloLearn? On with SQLite Tutorial.
SQL Commands

A nice resource for SQL and SQL joins also check inner join

You should NEVER update the draft sheets directly, but rather work on a copy of them according to the instructions found here.

Week 33 - Day 5 | Node.js & SQLite

Schedule

Watch the lectures
Study the suggested material
Practice on the topics and share your questions

Study Plan

Your instructor will share the video lectures with you. Here are the topics covered:

Going Fullstack: Connecting SQLite with our Node.js app

References & Resources:

Environment(al) variables: are key/value pairs that are stored in a text file (usually prefixed with .env) and are read during build time and made available to either the Node.js or the Browser.
CAUTION: Make sure to read the documentation and understand how to set private env variables that will only be available on the server (Node.js) and how to set public env variables (available in the Browser). Ensure that you are strictly following the documentation procedures. (Be extra cautious and careful with things like Vite, Create React App, Webpack)
FE Frameworks:
- React
- Angular
- Vue
Full Stack Frameworks:
- Next.js (React)
- Nest.js (Angular)
- Nuxt.js (Vue)
DB Browser: a GUI for SQLite
DB Beaver: https://dbeaver.io (also supports other databases)
VSCode SQLite Viewer
SQLite3
- Show Table Schema: PRAGMA table_info(table_name);
- Ctrl+L will clear the sqlite console
npm install PACKAGE will immediately install the PACKAGE and run any post/pre build scripts
npq install PACKAGE runs some verifications before installing
Supabase
VSCode: fold on first level: Ctrl+K+1, second level: Ctrl+K+2, etc.

Exercises

Read: https://vitejs.dev/guide/env-and-mode to learn all about the security measures and best practices
SQLite Quick start: https://sqlite.org/quickstart.html
Study the sqlite3 package docs (API):
Quick start example can be found here: https://github.com/TryGhost/node-sqlite3#usage
https://github.com/TryGhost/node-sqlite3/wiki
- https://github.com/TryGhost/node-sqlite3/wiki/API
Challenge: change the “:memory:” value to something silly and Google for what the error that comes back mean: Error: SQLITE_ERROR: table lorem already exists…
Learn more about Prepared Statements https://en.wikipedia.org/wiki/Prepared_statement
- Always use prepared Statements when the queries involve some kind of user input (CONSIDER ALL USER INPUT EVIL / GUILTY UNTIL PROVEN INNOCENT)
CHALLENGE: Find out what the rowid is all about and it comes from. Check what the AS SQL operator does.

You should NEVER update the draft sheets directly, but rather work on a copy of them according to the instructions found here.

Weekly feedback: Hey, it’s really important for us to know how your experience with the course has been so far, so don’t forget to fill in and submit your mandatory feedback form before the day ends. Thanks you!

Project maintained by in-tech-gration Hosted on GitHub Pages — Theme by mattgraham

WDX-180

Week 33 | Intro to Databases

Week 32 ⇦

⇨ Week 34

Week 33 - Day 1 | Introduction to Databases

Schedule

Study Plan

Exercises

Week 33 - Day 2 | SQL Databases

Schedule

Study Plan

Week 33 - Day 3 | SQLite

Schedule

Study Plan

Exercises

Week 33 - Day 4 | SQL Practice Day

Schedule

Study Plan

Exercises

Week 33 - Day 5 | Node.js & SQLite

Schedule

Study Plan

Exercises