hyte-web-dev

Relational Databases

(recap from 1st year)

TOC

  1. Relational Database Concepts
  2. SQL basics
    • Creating a Database
    • Querying data
    • Updating data
    • Deleting data
    • SQL scripts
  3. Assignment

Relational Database Concepts

Database

Database model

Relational database model

DiaryEntries table example data

entry_id user_id entry_date mood weight sleep_hours notes created_at
1 1 2024-01-10 Happy 70.5 8 Had a great day, felt energetic 2024-01-10 20:00:00
2 1 2024-01-11 Tired 70.2 6 Long day at work, need rest 2024-01-11 20:00:00
3 2 2024-01-10 Stressed 65.0 7 Busy day, a bit stressed out 2024-01-10 21:00:00

Users table example data

user_id username password email created_at user_level
1 johndoe temp-pw-1 johndoe@example.com 2024-01-01 09:00:00 regular
2 janedoe temp-pw-2 janedoe@example.com 2024-01-02 10:00:00 admin
3 mike_smith temp-pw-3 mike@example.com 2024-01-03 11:00:00 regular

What is the relation between these two tables?

Primary key (PK)

Foreign key (FK)

erDiagram

Users {
    int user_id PK "Primary Key"
    varchar username
    varchar password
    varchar email
    datetime created_at
    enum user_level "regular | admin | moderator"
}

DiaryEntries {
    int entry_id PK "Primary Key"
    int user_id FK "Foreign Key"
    date entry_date
    varchar mood
    decimal weight
    int sleep_hours
    text notes
    datetime created_at
}

Users ||--o{ DiaryEntries : "has"

Integrity constraints

Database server and client

Terminology in Finnish

Structured Query Language, SQL

Creating a database

-- Remove existing database completely (needs a root access to DBMS)
DROP DATABASE IF EXISTS database_name;
-- Create a new database (needs a root access to DBMS)
CREATE DATABASE database_name;
-- Connect to the database
USE database_name;

-- Example
DROP DATABASE IF EXISTS HealthDiary;
CREATE DATABASE HealthDiary;
USE HealthDiary;

Notes:

Creating tables

CREATE TABLE table_name (
  column_name1 data_type [NOT NULL] [UNIQUE] [PRIMARY KEY],
  column_name2 data_type [NOT NULL] [UNIQUE],
  column_name3 data_type [NOT NULL] [UNIQUE],
  ...
  column_nameN data_type [NOT NULL] [UNIQUE],
  PRIMARY KEY (column_name1)
);

Example:

-- Create a table for users
CREATE TABLE Users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Create a table for diary entries
CREATE TABLE DiaryEntries (
    entry_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    entry_date DATE NOT NULL,
    mood VARCHAR(50),
    weight DECIMAL(5,2),
    sleep_hours INT,
    notes TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

-- ALTER example, adding a new column to existing table
ALTER TABLE Users ADD COLUMN user_level VARCHAR(10) DEFAULT 'regular';

Inserting data

INSERT INTO table_name (column1, column2, column3, ...)
  VALUES (value1, value2, value3, ...);

-- or

INSERT INTO table_name
  VALUES (value1, value2, value3, ...);

Examples:

-- Inserting a single record, without specifying column names
INSERT INTO Users VALUES (1, 'johndoe', 'temp-pw-1', 'johndoe@example.com', '2024-01-02 10:00:00', 'regular');

-- Iserting multiple user rows at once (default values like created_at are inserted without need to specify them)
INSERT INTO Users (username, password, email, user_level) VALUES
  ('janedoe', 'temp-pw-2', 'janedoe@example.com', 'admin'),
  ('mike_smith', 'temp-pw-3', 'mike@example.com', 'moderator');

-- Example when FK constraint fails (if user_id 15 does not exist)
INSERT INTO DiaryEntries (user_id, entry_date, mood, weight, sleep_hours, notes, created_at) VALUES
  (15, '2024-01-10', 'Happy', 70.5, 8, 'Had a great day, felt energetic', '2024-01-10 20:00:00');

-- Inserting multiple diary entries
INSERT INTO DiaryEntries (user_id, entry_date, mood, weight, sleep_hours, notes, created_at) VALUES
  (1, '2024-01-10', 'Happy', 70.5, 8, 'Had a great day, felt energetic', '2024-01-10 20:00:00'),
  (1, '2024-01-11', 'Tired', 70.2, 6, 'Long day at work, need rest', '2024-01-11 20:00:00'),
  (2, '2024-01-10', 'Stressed', 65.0, 7, 'Busy day, a bit stressed out', '2024-01-10 21:00:00');

Note: referenced records (user_id in this case) in other tables must exist (foreign key constraint) There are other ways and tools for mass importing data, e.g. from CSV files, other databases, etc.

Querying data

Select statement:

SELECT [ALL | DISTINCT] column1[,column2]
  FROM table1[,table2]
  [WHERE conditions]
  [GROUP BY column-list]
  [HAVING conditions]
  [ORDER BY column-list [ASC | DESC] ]
  [LIMIT number];

-- Select all columns from the DiaryEntries table:
SELECT * FROM DiaryEntries;

-- Select all diary entries for a specific user:
SELECT * FROM DiaryEntries WHERE user_id = 2;

-- Select usernames and emails from the Users table:
SELECT username, email FROM Users;

-- Select the 2 newest diary entries:
SELECT * FROM DiaryEntries ORDER BY created_at DESC LIMIT 2;

Operators and functions

Examples:

-- Find users with a username starting with 'J':
SELECT * FROM Users WHERE username LIKE 'J%';

-- Select users created after a certain date:
SELECT * FROM Users WHERE created_at > '2024-01-01';

Querying several tables

Examples:

-- "cartesian product" of two tables:
SELECT * FROM Users, DiaryEntries;

-- Select all columns from both tables where user_id matches:
SELECT * FROM Users, DiaryEntries WHERE Users.user_id = DiaryEntries.user_id;

-- Select all diary entries along with the username of the owner using a (inner) join:
SELECT DiaryEntries.*, Users.username 
  FROM DiaryEntries 
  JOIN Users ON DiaryEntries.user_id = Users.user_id;

-- Select all diary entries along with the username where the mood is 'Happy':
SELECT DiaryEntries.*, Users.username
  FROM DiaryEntries
  JOIN Users ON DiaryEntries.user_id = Users.user_id
  WHERE DiaryEntries.mood = 'Happy';

-- Select all diary entries and all users whether they have diary entries or not. 
SELECT 
  Users.user_id, 
  Users.username, 
  DiaryEntries.entry_id, 
  DiaryEntries.entry_date, 
  DiaryEntries.mood, 
  DiaryEntries.notes
FROM 
  Users
LEFT OUTER JOIN DiaryEntries 
  ON Users.user_id = DiaryEntries.user_id;

Updating data

Database state is changed by following operations:

Update statement

UPDATE table_name
  SET column1 = value1, column2 = value2, ...
  WHERE condition;

UPDATE changes column values for record(s)

Examples:

-- Change user_level of user with id 1 to 'admin'
UPDATE Users SET user_level = 'admin' WHERE user_id = 1;

-- Change mood of entry with id 1 to 'Outstanding'
UPDATE DiaryEntries SET mood = 'Outstanding' WHERE entry_id = 1;

Delete statement

DELETE FROM table_name
  WHERE condition;

DELETE removes records from a database

Examples:

DELETE FROM DiaryEntries WHERE entry_id = 2;

SQL scripts

Week assignment 3

Think about requirements of a health diary app. What kind of data is needed?

  1. Test the example queries in this material
  2. Design one or two tables more for the app (in addition to the ones we already have in examples)
  3. Draw a diagram of the database structure (including relations between tables)
  4. Define the data types for the columns
  5. Define primary keys and foreign keys
  6. Implement SQL statements for creating the tables
  7. Insert multiple records of mock data into the tables
  8. Think about use cases for the data from the application point of view and provide examples how to update, delete and query the data in a meaningful way
  9. Create a db/ folder into your project repo
    • Create a script file including all your SQL statements (create tables, insert data, queries, updates, etc.)
    • Place your db design files (e.g. image) into the folder

  10. Add, commit and push the script to your Git and Github repo:

     git checkout -b week3
 git add .
 git commit -m "Add database design and sql script"
 git push origin week3

Returning: Check assignment in OMA.