Databases

The problem with variables in our code is that every time we reload our pages, variables are re-initialized and we can't keep track of their values. We could use cookies or local storage to keep data between a user's sessions, but are limited in size, and can be deleted by the user when clearing browser's history. Therefore, we could use a server-side program to store data in variables, and also share them between users when needed. But variables are limited to the memory of the server, can be vulnerable to software bugs, hardware failures or power outages… That's why we need persistent data storage, i.e. writing data in files, to prevent these kind of issues.

We've talked about .json files and have seen that we can load them using jQuery, so we could use that to store and load data. But this technique becomes problematic when dealing with large dataset: we have to load the whole data everytime (which can exceed the memory available), then extract the data we need. Databases are a more convenient and efficient way to store and retrieve data.

Formally, a database refers to a set of related data and the way it is organized. Access to this data is usually provided by a database management system (DBMS), a software that allows users to interact with one or more databases and provides access to all of the data contained in the database (although restrictions may exist that limit access to particular data). The DBMS provides various functions that allow definition, creation, update and query of large quantities of information and provides ways to manage how that information is organized (see CRUD for more details).

Because of the close relationship between them, the term "database" is often used casually to refer to both a database and the DBMS used to manipulate it.

Database designers typically organize the data to model aspects of reality in a way that supports processes requiring information, such as (for example) modelling the availability of rooms in hotels in a way that supports finding a hotel with vacancies.

Computer scientists may classify database-management systems according to the database models that they support. Relational databases became dominant in the 1980s. These model data as rows and columns in a series of tables, and the vast majority use SQL (Structured Query Language) for writing and querying data.

Relational DBs can be thought of as spreadsheets, but we've seen that we're more and more confronted to data that need more complex structure. Therefore, since the 2000s, non-relational databases became more popular, refered to as NoSQL because they use different query languages.

In this module, we're going to explore Firebase, which allows us to send and retrieve data from our client-side using a NoSQL database. Firebase is a commercial service (from Google), but it has a pretty decent free plan, more than enough for our small apps.

We first need to create a Firebase project, go to Firebase and follow these steps:

1. Start by clicking "Add a project"
2. Give a name to your project then click on "accept conditions" and "create a new project"
3. Click on "continue"
4. Click on "Database"
5. Scroll to "Realtime Database" and click "create a database"
6. Select "Start in test mode" and click "Activate"
   (we're not going to cover Authentication in this module)
7. Finally we have our database.
   You can click on "ignore"

   Right now, this is just an empty JSON structure, therefore the null value. We can add key/value pairs by clicking on the "+" (add child)

8. We can add simple key/value pairs: click on "Add"
9. And we can create JSON-like structure:
   - click on "+" (add child)
   - enter a key name in the "child box" and click on the "+" (add child) in the "child box" to enter new key/value pairs

👆👆👆 Click on the steps to view a screen capture of what you should see on your screen

Firebase provides a handy setup to add to our web applications, it includes the link to a JS library to use Firebase as well as the configuration keys needed to access to our newly created database.

1. Click on "Settings", "Project settings" in the sidebar
2. Add a web app by clicking on the "</>"
3. Register your web app: set a name and click the "Register app" button
4. You should now get the code to include in our Web page: the link to the Firebase client library and the code needed to connect to our Firebase project
5. You can now click the "Continue to console" to get back to your Firebase project's settings and see your brand new registered app

👆👆👆 Click on the steps to view a screen capture of what you should see on your screen

We can now create a new Glitch project and integrate Firebase:

Make sure to also integrate Firebase Realtime Database library as in the example above 👆
You can find latest version of the library on Firebase Documentation.

This doesn't do much for now but we have everything ready to start using our Firebase Realtime Database.

Now that we have access to our Firebase project, we need to see 2 things:

• To access our database, we just need this line: var database = firebase.database();
• Firebase Database uses "References" to represent specific locations in our DB. References are used for reading or writing data. For example, with the data on the right, we could create a reference pointing to name like this: var nameRef = database.ref( "name" ); or var prop1Ref = database.ref( "myObject/prop1" ); to point to myObject.prop1. Instead of the classic dot notation we've been using with JSON, References are using paths.

Let's use this to add some data to our DB:

Copy/paste your own credentials to test it with your Firebase project.

And you should now have this data in your database (along what you previously added manually):
(If you're editing this in another tab, not in the embed, make sure to click on "Show Live" to execute your script.)

.set() and .update() methods can also take a second parameter: a callback function to execute once the Reference is set or updated in our database, see firebase.database.Reference documentation.

We can now write, update and delete data in our database. Let's see how to retrieve our data using the Reference.on() method:

Set your credentials then click on the page to set a new value to the "random" Reference in our database, this then triggers our Reference.on( "value", … ) and its callback that extract the data from the snapshot (snapshot.val()) and update the text of our h1 element.

You can open your app view in another tab to test the "multi users" effect:

Queries are a powerful feature of databases and DBMS, by providing a way to search, sort and filter data. Let's say for example that we have a dataset of users in our database (see users.json in the embed below), using Queries we could filter them by age and retrieve only the users that are between 30 and 35:

Based on the same construction as the favoriteFruit query, write a query to find all the users with their property eyeColor equals to blue.

Diving into queries is a bit beyond the scope of this course but absolutely needed for a complete overview of databases and DBMS features.

You can read more about Firebase RealTime DataBase Queries here and here, or even use the Querybase library for complex queries.

• Daniel Shiffman's Database as Service: Firebase
• Firebase Documentation
• Fireabase Web Guide: Get started for JS Client
• Read and Write Data on the Web
• firebase.database documentation
• Firebase Web Codelab, step by step tutorial

There are a lot of different Databases and DBMS (see Storage examples on Glitch). SQL and relational databases are very widely used, but require more setup/different languages, if you're interested in learning more about SQL, read these selected links:

• Select Star SQL an interactive tutorial for learning SQL
• SQLite Tutorial on tutorialspoint

Quiz

Test your knowledge

Databases Project

Assignment

Objectives

You should now have a better understanding of what databases are and how they are integrated in web services. We are now ready to take a step back from all these new technical concepts in order to grasp how they can add value to various projects, increase their efficiency or improve their impact. You'll conduct and document an exploratory research on existing web projects (if possible partially based on databases) answering to your Sustainable Development Goal subject (cf the chapter Introduction).

You should make a difference between what your subject is and what the associated SD Goal is. Here you have to focus on your subject as stated in the page Introduction, which is more specific than the general SD Goal.

The pedagogical objectives of this assignment are the following: The pedagogical objectives of this documentation project are the following:

• Gather information:
• on your SD Goal and more specifically on your subject
• on the way databases are actually integrated in web projects
• Identify
• actual needs on your subject
• potential users and/or beneficiaries of that type of projects
• Practice
• formulating relevant research questions
• creating a user persona
• producing a user scenario
• Project yourself
• in the opportunities brought by web and digital technologies, in particular databases, to a project of general interest
• in a dynamic of social innovation

This assignment is also the only way to get well prepared for your group project, so be sure to get involved now to make the most of the four weeks of collective prototyping that will follow! Indeed, this research process corresponds to a phase of empathy and inspiration, which is an essential step for generating new ideas.

Instructions

This assignment is mandatory. If you update your work but the link doesn't change, you don't need to re-submit it.

On Glitch, make sure you're always connected when you make your assignment!
Assignments done without being connected are graded 0!

Your assignment will take the form of a web page created and hosted through glitch, just as in the previous part. It should summarize the results of your exploratory research and reflect your understanding of the technologies and of your subject's issues. Your documentation project will take the form of a web page created and hosted through glitch, just as in the previous part. It should summarize the results of your exploratory research and reflect your understanding of the technologies and of your subject's issues.

At first, you'll look for existing projects addressing your subject in an interesting way. They must be digital projects, ideally based on web technologies or on a mobile app. If they use databases in a way or another, it's even better! You'll probably use mainly a search engine to discover and document such projects, but feel free to use other ways to gather information: specialised magazines, tech or social entrepreneurship events, organizations, incubators... You'll then have to summarize briefly four projects that seem relevant to you, following the detailed structure given below.

Don't forget to write down all your sources during your research!

Then in a second time you will focus on only one of these projects. The choice is up to you, but be sure to explain us why you chose this project among the others. You will deepen both the user experience aspects and the technical principle.

This assignment is evaluated on the quality of your thinking and of your documentation, not on your ability to code or recreate such projects using Firebase. But if you feel like experimenting more databases tools, try to simulate one of the projects, you could earn bonus points!

Structure of your assignment

Be sure to follow strictly the structure given below. Your layout must be very clear and highlight the information hierarchy. Keep in mind that the ability to properly structure and style your page with HTML/CSS will be evaluated!

Your assignment has to be structured as it follows:

Your documentation project has to be structured as it follows:

Presentation

• Subject
The subject assigned to you on the page 'Subject'
• Technological module
API / Database / IA, which one did you choose?

A. Exploratory research

1. Name of the project n°1

Insert an image to illustrate the project.
1. Project carriers

   The persons, companies, organizations, institutions who carry the project

2. Beneficiaries

   Who benefit from this project? In some projects, the entity can be non-human, such as a certain type of animal species etc.

3. Users

   Who is using this project? There can be several types of users. Also, users may be the same persons as beneficiaries, but not necessarily.

4. Need

   The need the project is answering to

5. Principle

   How is the project answering to the need? What solution is it offering?

6. Main technologies involved

   A list of the main technologies that make the project work.

7. Sources

   Link to the resources you used, the website of the project etc.

8. Other info

   Optional

2. Name of the project n°2

Proceed as for project n°1

3. Name of the project n°3

Proceed as for project n°1

4. Name of the project n°4

Proceed as for project n°1

B. Deepening

1. Name of the selected project

Insert one or several images to illustrate this project.
1. Carriers and actors of the project

   In addition to the persons who carry the project, other actors can contribute to it.

2. Research question

   Take the time to formulate the research question the project is answering to. See in 'Methodological tools' below what is expected from a good research question.

3. The reason you selected this project

2. User scenario

1. Users

   A reminder of who the users are

2. Persona

   Create a relevant persona that could use this project and describe that persona. See tips in 'Methodological tools' below.

3. Key features

   Every action that your user can do with a product is called a feature. The most important features are called key features: list them by order of importance in the form VERB + NOUN (ex: "order food")

4. UX storyboard

   Insert an image of your UX storyboard. It can be a picture if you draw it by hand, or a screenshot / image if you created it on a computer. See tips in 'Methodological tools' below.

3. Technical analysis

1. General principle

   One to two phrases to explain briefly how the project technically works

2. Technical overview - Database version

   Write down a detailed technical overview of the project in an "Database version", that is:

   • If no databases are involved in the existing project you're currently documenting, you'll have to imagine how databases could be integrated in the project for it to have a greater impact / efficiency. Then describe precisely how it would work.
   • If databases are involved in the existing project you're documenting, then the "Database version" is the same as the existing one.
   See what is expected from the detailed technical overview in 'Methodological tools' below.

3. Added value thanks to databases

   In two to three sentences, summarize how databases improve the project (whether you're working on a fictionnal 'Database-version' or on the existing one that already involves databases)

4. Simulation

If you feel like experimenting more database tools, you can try to simulate the project, for example with Firebase. This part is optional (bonus points). Screenshots, source code, link to your projects ... All efforts appreciated!

---

Complementary sources

• Source 1
• Source 2
• Source 3

Methodological tools

This section gives you detailed methodological tips and clarifies what is expected from your documentation on the points Research question, Persona, UX storyboard and Technical overview. Be sure to read it carefully!

Research question

As the name suggests, a research question (sometimes called problematique) is particularly central in a research project, such as a thesis or even a dissertation. But it is more generally a fundamental starting point when you're trying to tackle an issue through a well-defined problem. If you want to propose a relevant solution, which meets a need or a demand, you absolutely must take your time to carefully formulate your research question.

So basically, what is a research question? It is the question that the project sets out to answer. Remember that you have to be the most precise as possible. Your research question must meet all the criterias below:

• it expresses the specific problem the project aims to solve
• it mentions the potential users and ideally the end-beneficiaries
• it gives the context the project focuses on
• it is formulated in an interrogative form

You may not need right now to invent original solutions, but you will have to be innovative in the Part 3 of this course, so take this opportunity to practice formulating a good research question now.

Here are some examples of bad research questions:

• ❌ How can we reduce noise pollution?
  → This is much much too general.
• ❌ How to reduce noise pollution in big cities?
  → Still too general, no context and no users / beneficiaries mentioned.
• ❌ How does the Ambi-city open source mobile app use noise pollution data collected by citizens to educate to this major health issue?
  → This describes the project more than the issue and the context.
• ❌ How can we reduce noise pollution in schools?
  → We're starting to get somewhere, but we're still not sure who the users of the project are and the issue is a bit vague.
• ❌ People raising kids under 8 years old in big cities should have a priority access to the flats that are less exposed to noise pollution.
  → This is a statement, not a question.

Here are some better examples of what can be expected as research questions. As you can see, they are all addressing noise pollution but in quite different ways:

• ✅ How can we motivate city residents to collect noise pollution data with their phones in order to contribute to academic research?
• ✅ How can the municipalities use noise pollution data to orient their urban planning policy?
• ✅ How can we provide reliable noise pollution maps to vulnerable people (e.g. people with chronic high sleep disturbance, cardiovascular problems, etc.) at key moments such as renting a flat or accepting a job offer?
• ✅ How can noise pollution time data improve the daily life of people suffering from anxiety in urban areas?

Now it's your turn to try to formulate a relevant and precise research question regarding the project you're documenting!

Persona

A persona in user-centered design and marketing is a fictional character created to represent a user type that might use a site, brand, or product in a similar way.

W. Lidwell; K. Holden; J. Butler, Universal Principles of Design

Personas are useful in considering the goals, desires, and limitations of users in order to help to guide decisions about a product such as choices of features, interactions, visual design...

Ok so how are we supposed to define such a fictional character? First we must be sure that we're talking about the right type of persons: our concerns here are the actual users of the product, who are not necessarily the same people as the end-beneficiaries. Also, we'll focus on only one type of users.

Then we have to build a profile of the type of users we focus on, by defining a set of characteristics such as their name, motivations, family status, age, etc. What are their habits? Their hobbies or activities? Do they have a job, study? What are their goals, their limitations? Here is a small list of what should be mentioned in your persona's description:

• First name
• Age
• Activity / Profession
• Place of residence, country, environment (city, countryside, etc.)
• Family status
• Income
• List of hobbies and passions
• List of needs, desires, dreams
• List of problems and frustrations
• Major issue related to the subject

You can present all this info the way you want as long as you defined at least all the above characteristics. Fell free to add pictures if it makes your persona more tangible!

UX storyboard

When you'll design a product or service in a user-oriented approach, you'll have to take in account the user experience (UX) you want to offer. Before diving into subtle ergonomic details, we first need to describe the user journey's big picture. A very common and effective tool to do so is the UX storyboard. A storyboard is a sequence of drawings describing a scene. In our case, we'll use storyboards to illustrate how the user interacts with the product. Here are some tips to help you draw a relevant UX storyboard:

First step
Write down the user scenario on a draft, in 5 to 6 steps rather general, with words and not drawings. It is the base of your process, don't include it to your assignment. It must start with the initial situation and end with the resolved situation, and should contain:

• The context of the interaction
• The user's motivations
• The actions performed with the product
• The consequences of the interaction

If your scenario is too long, try to combine some steps or simplify your user flow.

Second step
Transform your scenario in a storyboard by illustrating each step with pens and paper: you'll then have 5 to 6 boxes, the first one illustrating the initial situation and the last one the resolved situation. These drawings will help you show the environment of the user, the key aspects of their interaction with the product, and the evolution of the situation. If you think that your drawings are not clear enough, you can add a short caption under each box to describe the situation. Here are some tips for drawing your storyboard:

• Don't draw wireframes (i.e. the details of the interface), but only the element essential to understand the main interaction (like a single button)
• You can use text in your drawings, for example when your characters talk or think, or to give info about the environment ("in the subway", "at the office" etc.)
• In general, don't draw too much details. Use thick felt-tip pens to avoid multiplying details.
• The artistic esthetic of your drawings is not important at all, but if you really don't feel comfortable with pens and paper you can go with digital illustration.

It's the result of that step, that is the illustrated storyboard, that you will have to include in your assignment (picture of your storyboard or screenshots / .png export of your digital illustrations)

Technical overview - Database version

In this part, you'll write down a detailed technical overview of the project in a "Database version", that is:

• If no databases are involved in the existing project you're currently documenting, you'll have to imagine how databases could be integrated in the project for it to have a greater impact / efficiency. Then describe precisely how it would work.
• If databases are involved in the existing project you're documenting, then the "Database version" is the same as the existing one.

To give a good and detailed technical overview of the project, you must at least answer the following questions:

• What are the different databases used in your project?
• How are they structured? Can you list approximatively the main types of data and if they are rather stored once, updated regularly, updated in real-time?
  
  Example with a very simplified uber-like app:
  • Drivers' database
    • driver's id number - stored once
    • driver's name - stored once
    • driver's license plate - stored once
    • driver's bank account - stored once
    • driver's grade - updated regularly
    • driver's vehicle type - stored once
    • driver's number of journeys - updated regularly
  • Customers' database
    • *** - stored once
    • *** - stored once
    • *** - stored once
  • Journeys' database
    • journey's id - stored once
    • departure time - stored once
    • current time - updated in real-time
    • driver id - stored once
    • customer id - stored once
    • departure location - stored once
    • arrival location - stored once
    • cost - stored once
    • duration - updated once
  • Event log database
    • errors - updated regularly
    • logs - updated regumarly
• For each of the following types of action, describe at least one example occuring in the project:
  • a data is created
  • a data is read
  • a data is updated
  • a data is deleted
• For each of the following types query, describe at least one example occuring in the project:
  • search
  • filter
  • sort

You should give as much (relevant) technical details as possible. Don't hesitate to include links of external resources. And remember to always cite your sources!

---

Evaluation criteria

It's time to start your exploratory research! The evaluation criteria are the following:

Participation: 4 points
Time spent on course + interaction with resources / examples

Exploratory research: 4 points
Quality of your exploratory research on the four projects

Selected project deepening: 5 points
Choice of the project and research question, definition of users, persona, key features and UX storyboard

Technical analysis: 4 points
Technical overview - Database version, added value thanks to databases

HTML & CSS quality: 3 points
Page's structure, use of skeleton, custom CSS, code clarity

Simulation: bonus points
Simulation of the project with ml5.js or other machine learning technologies