Databases

Announcements

• Vitamin 6 due tonight
• Vitamin 7 due next Wednesday
• Project 2 due in two weeks!
• You need to get your design checked off in OH AND submit your Figma file on Gradescope under [Design] Assignment. Sign up on ED!
• Make sure to add your Group members if you are working with others.
• Attendance emails coming out soon
• If you’ve missed more than 3 without letting us know with a valid reason beforehand, you can make up for attendance by attending our upcoming guest lecture (more info coming soon)
• If you need an extension, fill out the extension form!

​

Attendance (Google Forms)

Secret word: ‘’Haas”

Databases Intro

Databases

• TLDR: place where you store and organize data
• IG uses databases to store profile, photos, likes, comments
• When you refresh a React page with a counter, the counter goes back to 0. Databases allows the values to persist across computers, sessions, refreshes.
• We use them to save and retrieve data easily and keep them organized and secure
• Share data between frontend (what users see) and backend (the logic)

How is data structured?

• Depends on the type of database. Two common styles:
• SQL (relational) → SQL (db system)
• NoSQL (non relational) → MongoDB (db system)

SQL: organized spreadsheet

​

NoSQL: flexible folders w/notes

SQL = Structured, safe, consistent, great for complex relationships and queries. Examples: E-Commerce, Banking, Airline Systems

​

NoSQL = Fast, flexible, perfect for apps that change often or grow quickly. Examples: Social Media, Chat Apps, Streaming Platforms

​

Overall

Supabase (Relational DB Example)

​

The dotted lines show relationships between tables

​

For example:

messages.channel_id → channels.id

messages.user_id → users.id

Each box is a table (rows + columns)

Firebase (Non-relational DB Example)

looks more like JSON, not a table with rows and cols

SQL & Relational Databases

What is SQL?

• SQL - structured query language
• A language you use to talk to a database (like python)
• SQL code example: (we’ll teach you SQL in the next lecture)
• SELECT * FROM users WHERE age > 18;
• “Select all columns from the table called user keeping only the rows where the age cell is greater than 18”
• If you took data100/etc you already learned this :D
• Uses fixed schemas

SQL is Declarative

• For a declarative language: You describe what data you want and not how to fetch it
• EX. SELECT * FROM users WHERE age > 18;
• Here we are telling the database what we want, the database will figure out the process of retrieving the data
• Different from languages like JavaScript, which is imperative (more process-oriented, like for loops and if-statements)
• Analogy: It’s like ordering at a restaurant. You tell the waiter "I want a medium-rare steak" (SQL); you don't go into the kitchen and tell them which burner to turn on or how long to sear each side (Procedural programming)

What is a schema?

• Schema: a structure that defines how data is organized in a database
• SQL requires a schema
• This code defines a schema (structure)
• It defines a table and every user must have
• Id, name, age, email
• Id age must be number, etc
• If it doesn’t follow schema, will reject data
• Ex. INT: Only whole numbers. If a user tries to enter "Twenty-One" as their age, the database will error an and reject the data
• Why? → Ensures consistency

What is a relational database?

A relational database is a type of database that stores data in tables, kind of like Excel spreadsheets.

Each table is made up of:

• Rows = each row is a record (like a person)�
• Columns = each column is a field (like name, age, email)

Each row is related to a single user. We can connect this to other tables using a common field like ID → thus RELATIONAL database, relational table

SQL vs NoSQL = Relational vs non-relational databases?

• SQL is a language… not a database… why are we calling this SQL vs NoSQL? Why not SQL vs MongoDB or something?
• It’s just a nickname for databases because SQL is the main language for relational databases
• NoSQL just means “not only SQL” which has many different DB, not just MongoDB

NoSQL & Non-relational Databases

What is NoSQL?

• NoSQL = Not only SQL
• Basically just refers to databases that don’t use traditional table structure like SQL
• Does not use fixed schemas - flexible
• Stored in Non-relational databases

What is a non-relational database?

• A non-relational database is a type of database that does not store data in tables
• Other formats the data can be stored:
• Documents -> JSON objects (MongoDB!!)
• Key-value pairs (Redis)
• Wide-column (Apache Cassandra)
• Graph → nodes and edges as relationships (Neo4j)

• Stores data as documents. Each document looks like a JSON object
• Other documents don’t need all the fields (some documents won’t have name, age, hobbies, or add more stuff like “fave colors”)

MongoDB

What is MongoDB

• Type of NoSQL database that stores data in a flexible way
• Similar to Firebase (the example we showed for a Non-relational DB we showed earlier)
• Mongo uses
• Database (container for folders)
• Collections (folders)
• Documents (files within folders written in JSON (JavaScript-style objects)
• A document represents ONE OBJECT/ENTITY (ie. ONE USER)
• Must create multiple documents inside collection for multiple users

• In a database called “company”, a collection called “Users”, here is a document example.

How does MongoDB work?

You need

• Cluster: A group of MongoDB servers that hold your data (either locally or in the cloud).
• The MongoDB Shell – like a terminal where you type commands to talk to MongoDB.
• MongoDB Atlas - A cloud service that hosts MongoDB clusters for you, so you don't have to install it.

MongoDB Atlas - create cluster

• Go to mongodb.com/cloud/atlas
• Sign up and create a Free Cluster
• Set up your username and password
• Get a Connection String (like: mongodb+srv://...)
• Paste it into mongosh or your app to connect.
• Video for guidance: https://www.youtube.com/watch?v=pILdgCr4w3s&ab_channel=CodeOrCode

Make sure to select these! (free!!)

PASSWORD: DO NOT USE

SPECIAL SYMBOLS JUST USE

NUMBERS AND LETTERS

Stop here for now…

You’ll need it later

How to download MongoDB

https://www.mongodb.com/docs/v7.0/administration/install-community/

​

*On MacOS: requires homebrew!

How to start MongoDB

1. If on MacOS, start the MongoDB server: brew services start mongodb-community@8.0
2. mongosh - connect to MongoDB locally using the Mongo Shell

Some MongoDB commands

Start MongoDB:

• mongod – Start the MongoDB server
• mongosh – Open the MongoDB shell (command-line interface in the terminal)

In mongosh:

• use <dbName> – Switch to (or create) a database
• show dbs – Show all databases
• db.createCollection("collectionName") – Create a new collection
• db.collection.insertOne({...}) – Insert one document
• db.collection.insertMany([{...}, {...}]) – Insert multiple documents
• db.collection.find() – Find all documents
• db.collection.findOne({ key: value }) – Find one matching document
• db.collection.updateOne({ filter }, { $set: { key: value } }) – Update a single document

MongoDB on VS Code

You can alternatively download mongodb on VS Code in extensions!

MongoDB & Express

Full Stack Breakdown

Why Start a Server for MongoDB?

• Each has its own server to listen and respond to requests and interact with each other
• MongoDB is a backend database that needs a server
• Because it’s not just a file, it’s a process that accepts connections, handles operations, and manages indexes, memory, etc
• Backend server
• So it can receive requests from react frontend, process data, “talk” to database, send back a result
• They need to interact ASAP, so they can listen and respond (like talking to each other on the phone)

Mongo Servers in Express

• We learned how to create endpoints for a server in Express
• Mongoose lets us create a server we can access with Express
• Let’s put it all together!

​

Plan:

1. Create a new database with mongosh and create some test data
2. Create an endpoint to fetch data from MongoDB in Express
3. Start the server
4. Access the endpoint in a browser

Terminal

You can also do this by creating a new cluster in Atlas

server.js

Access the endpoint

1. Run node index.js
2. Test the endpoint in your browser by navigating to http://localhost:3000/school
3. Mongo adds some internal identifiers like _id and __v (underscore indicates they are private)

Graph Databases & Neo4j

What is a graph database?

A graph database is a database designed to store things and the connections between them.

It uses:

• nodes = things
• edges / relationships = how those things are connected
• properties = extra info about them

A graph database like Neo4j is usually classified as non-relational / NoSQL because it does not use the relational table model even though it illustrates relationships.

When should you use it?

Use a graph database when relationships between data are the core focus.

Best for:

• social networks
• recommendations
• fraud detection
• course prerequisites
• knowledge graphs

Choose it over SQL when you need to traverse connections.

Use SQL instead when your app is mostly structured records like users, orders, and payments.

​

https://neo4j.com/

Neo4j (a Graph DB)

Cypher

Cypher is Neo4j’s query language

​

CREATE

(a:Course {code: 'CS61A', name: 'SICP'}),

(b:Course {code: 'CS61B', name: 'Data Structures'}),

(c:Course {code: 'CS70', name: 'Discrete Math'}),

(d:Course {code: 'CS162', name: 'Operating Systems'}),

(b)-[:PREREQ]->(a),

(c)-[:PREREQ]->(b),

(d)-[:PREREQ]->(c);

​

Commands

cd ~/Downloads

mkdir neo4j-demo

cd neo4j-demo

npm init -y

npm install express neo4j-driver dotenv

mkdir public

touch server.js .env public/index.html

node server.js

​

server.js

.env

NEO4J_URI=neo4j+s://your-database-id.databases.neo4j.io

NEO4J_USERNAME=neo4j

NEO4J_PASSWORD=your_password_here

​

public/index.html

Attendance (Google Forms)

Secret word: ‘’Haas”