SE371 · Chapter 7 · Annotated SlidesWorking with Databases
Original
Overview

Working with Databases

MVC for server applications, the role of databases in web development, SQL vs. NoSQL (key–value and document stores, MongoDB), how websites use databases, ORMs, and building a CRUD API with Sequelize: connection, models, routes, queries with WHERE operators, associations and validators.

How to read this page

Every original slide is shown unchanged. Yellow marks on a slide point to a numbered note: hover or tap a mark to jump to its note, and hover a note to see its marks. Questions tagged Past exam appeared on a real paper; Prediction questions are likely angles, not confirmed. Pick an answer to each question: a wrong pick tells you why it is wrong, so you can try again.

All 47 slides
  1. 1 Fundamentals of Web Development
  2. 2 Technical notes
  3. 3 Complex server application
  4. 4 MVC architectural pattern
  5. 5 Example of MVC
  6. 6 The Role of Databases in Web Development
  7. 7 Databases and Web Development
  8. 8 NoSQL Databases
  9. 9 Why (and Why Not) Choose NoSQL?
  10. 10 Key-Value Stores
  11. 11 Document Store
  12. 12 MongoDB
  13. 13 Relational data versus document store data
  14. 14 Comparing relational databases (MySQL) to document data model (MongoDB)
  15. 15 How websites use databases
  16. 16 Object Relational Mapping libraries
  17. 17 g1
  18. 18 Configure the database connection
  19. 19 Connect to the database
  20. 20 Create the data model
  21. 21 Which HTTP method to use: What is CRUD?
  22. 22 Page routes
  23. 23 Create a new element in the DB
  24. 24 Calling the add employee api endpoint
  25. 25 Retrieving data form the DB
  26. 26 Search for employees by id
  27. 27 Search for employees by criteria
  28. 28 Use projections on the result
  29. 29 Install following Extensions
  30. 30 Fundamentals of Web Development
  31. 31 Connecting to your server from VSCode
  32. 32 Generating Mock data
  33. 33 Generating Mock data (2)
  34. 34 More on WHERE clause: AND
  35. 35 More on WHERE clause: OR
  36. 36 More on WHERE clause: Delete all
  37. 37 More on WHERE clause: Not Equal
  38. 38 More on WHERE clause: gt, lt
  39. 39 Insert data in a table at creation
  40. 40 Work with model associations
  41. 41 Get an employee and his associated country
  42. 42 Data validation and constraints
  43. 43 Sequelize validators
  44. 44 Sequelize validators (2)
  45. 45 Sequelize validators: example
  46. 46 Class Task
  47. 47 Slide 47
Slide 1 / 47

Fundamentals of Web Development

Slide 1: Fundamentals of Web Development
Slide 2 / 47

Technical notes

Slide 2: Technical notes
Slide 3 / 47

Complex server application

Slide 3: Complex server application
Slide 4 / 47

MVC architectural pattern

Slide 4: MVC architectural pattern

Notes

  1. MVC on the server: models (data), views (templates), controllers (route handlers).

  2. Seen again Chapter 6: Server-Side Node, slide 34: Express routes

Slide 5 / 47

Example of MVC

Slide 5: Example of MVC
Slide 6 / 47

The Role of Databases in Web Development

Slide 6: The Role of Databases in Web Development
Slide 7 / 47

Databases and Web Development

Slide 7: Databases and Web Development
Slide 8 / 47

NoSQL Databases

Slide 8: NoSQL Databases

Notes

  1. NoSQL: key–value stores (Redis), document stores (MongoDB), column and graph databases. Flexible schema, horizontal scaling.

Slide 9 / 47

Why (and Why Not) Choose NoSQL?

Slide 9: Why (and Why Not) Choose NoSQL?
Slide 10 / 47

Key-Value Stores

Slide 10: Key-Value Stores
Slide 11 / 47

Document Store

Slide 11: Document Store
Slide 12 / 47

MongoDB

Slide 12: MongoDB
Slide 13 / 47

Relational data versus document store data

Slide 13: Relational data versus document store data

Notes

  1. Relational: tables, fixed schema, joins, SQL. Document: JSON-like documents that can embed related data.

Exam angle

PredictionQ1

MongoDB stores data as:

Slide 14 / 47

Comparing relational databases (MySQL) to document data model (MongoDB)

Slide 14: Comparing relational databases (MySQL) to document data model (MongoDB)
Slide 15 / 47

How websites use databases

Slide 15: How websites use databases
Slide 16 / 47

Object Relational Mapping libraries

Slide 16: Object Relational Mapping libraries

Notes

  1. ORM (Object Relational Mapping, e.g. Sequelize): work with tables as JavaScript objects/classes instead of writing SQL.

Exam angle

PredictionQ1

Sequelize is a(n):

Slide 17 / 47

g1

Slide 17: g1
Slide 18 / 47

Configure the database connection

Slide 18: Configure the database connection
Slide 19 / 47

Connect to the database

Slide 19: Connect to the database
Slide 20 / 47

Create the data model

Slide 20: Create the data model
Slide 21 / 47

Which HTTP method to use: What is CRUD?

Slide 21: Which HTTP method to use: What is CRUD?

Notes

  1. Memory trick POST creates, GET reads, PUT/PATCH updates, DELETE deletes.

Exam angle

PredictionQ1

An API endpoint that updates an existing employee should use:

Slide 22 / 47

Page routes

Slide 22: Page routes
Slide 23 / 47

Create a new element in the DB

Slide 23: Create a new element in the DB

Notes

  1. Model.create({…}) inserts a row.

Slide 24 / 47

Calling the add employee api endpoint

Slide 24: Calling the add employee api endpoint
Slide 25 / 47

Retrieving data form the DB

Slide 25: Retrieving data form the DB

Notes

  1. findAll(), findByPk(id), findOne({ where: {…} }).

Slide 26 / 47

Search for employees by id

Slide 26: Search for employees by id
Slide 27 / 47

Search for employees by criteria

Slide 27: Search for employees by criteria
Slide 28 / 47

Use projections on the result

Slide 28: Use projections on the result
Slide 29 / 47

Install following Extensions

Slide 29: Install following Extensions
Slide 30 / 47

Fundamentals of Web Development

Slide 30: Fundamentals of Web Development
Slide 31 / 47

Connecting to your server from VSCode

Slide 31: Connecting to your server from VSCode
Slide 32 / 47

Generating Mock data

Slide 32: Generating Mock data
Slide 33 / 47

Generating Mock data (2)

Slide 33: Generating Mock data (2)
Slide 34 / 47

More on WHERE clause: AND

Slide 34: More on WHERE clause: AND
Slide 35 / 47

More on WHERE clause: OR

Slide 35: More on WHERE clause: OR
Slide 36 / 47

More on WHERE clause: Delete all

Slide 36: More on WHERE clause: Delete all
Slide 37 / 47

More on WHERE clause: Not Equal

Slide 37: More on WHERE clause: Not Equal
Slide 38 / 47

More on WHERE clause: gt, lt

Slide 38: More on WHERE clause: gt, lt
Slide 39 / 47

Insert data in a table at creation

Slide 39: Insert data in a table at creation
Slide 40 / 47

Work with model associations

Slide 40: Work with model associations

Notes

  1. Associations: hasMany / belongsTo / hasOne / belongsToMany; include them in queries.

Slide 41 / 47

Get an employee and his associated country

Slide 41: Get an employee and his associated country
Slide 42 / 47

Data validation and constraints

Slide 42: Data validation and constraints
Slide 43 / 47

Sequelize validators

Slide 43: Sequelize validators

Notes

  1. Validators on fields: allowNull: false, validate: { isEmail: true, len: [2, 50] }.

Slide 44 / 47

Sequelize validators (2)

Slide 44: Sequelize validators (2)
Slide 45 / 47

Sequelize validators: example

Slide 45: Sequelize validators: example
Slide 46 / 47

Class Task

Slide 46: Class Task
Slide 47 / 47

Slide 47

Slide 47: Slide 47