Databases

Databases : Why?

• Difference between databases and files
• Performance, security and reusability concerns

Databases : Goals

• Store and query data
• Transparent implementation for user
• Used accross several applications

Databases : 3 different languages

• Model data : Data Definition Language (Data Structure)
• Modifiy data : Data Manipulation Language (Data modification)
• Control data : Data Control Language

Databases : 3 different languages

• DDL represents the data (Entities and Relationships)
• DML gives a simple way to modify data
• DCL ensures that data are correctly modified and that integrity is preserved

DDL - Data Definition Language

Commands to define and modify database structure:

• CREATE: Create databases, tables, views
• ALTER: Modify existing structure (add/drop columns, constraints)
• DROP: Delete databases, tables, views
• TRUNCATE: Remove all data from a table (structure remains)

Key point: DDL operations affect the schema, not the data itself

DML - Data Manipulation Language

Commands to manage data within tables:

• SELECT: Retrieve data from one or more tables
• INSERT: Add new records to a table
• UPDATE: Modify existing records
• DELETE: Remove records from a table

Key point: DML operations work with data, not structure

Most of your daily work will be DML queries, especially SELECT

DCL - Data Control Language

Commands to control access and permissions:

• GRANT: Give users access rights to database objects
• REVOKE: Remove access rights from users

Example scenarios:

• Allow a user to SELECT from a table but not UPDATE
• Grant admin full control, analysts read-only access
• Revoke DELETE permission from junior developers

Security principle: Give minimum necessary permissions

Table Structure

• The table represents an entity (equivalent of a UML Class), it is also called relation.
• A relation (table) is constituted of attributes (columns), and rows of this table are called tuples.
• Each rows represents one instance of the entity represented by this table

Table Structure (2)

Column types (most common ones)

Column types (Advanced)

Best Practice: Store files on disk/cloud, keep only references (URLs/paths) in database

Linking with other tables

• It exists some possibility to declare links to other tables
• It is part of the Control function of Databases
• Primary keys to define uniqueness for tuples
• Foreign keys to define external data linked to the current tuple

Linking with other tables (2)

ACID Properties

Databases ensure data integrity through ACID properties:

Database Normalization - Why?

• Reduce redundancy: Store each piece of information only once
• Avoid anomalies: Prevent inconsistencies during updates
• Improve data integrity: Maintain accuracy and reliability
• Optimize storage: Use disk space efficiently

Example of redundancy problem:

Order1: Customer="John", Address="123 Main St", Product="Laptop"
Order2: Customer="John", Address="123 Main St", Product="Mouse"
→ If John moves, we must update multiple rows!

Solution: Separate Customers table + Orders table

Normal Forms Overview

Most databases aim for 3NF as a practical balance

Types of Relationships

Database vs Files: Advantages

Choosing Data Types: Best Practices

• Use the smallest type possible: INT vs BIGINT - save space
• VARCHAR vs TEXT: Use VARCHAR when length is known/limited
• CHAR vs VARCHAR: CHAR for fixed-length (e.g., country codes)
• DATETIME vs TIMESTAMP: TIMESTAMP for timezone-aware dates
• DECIMAL for money: Never use FLOAT for currency
• ENUM for fixed lists: Gender, Status, Priority levels

Wrong data types can lead to performance issues and data loss!

Recap: Key Takeaways

• ✓ Databases provide centralized, structured data storage
• ✓ ACID properties ensure data integrity
• ✓ Three languages: DDL (structure), DML (data), DCL (access)
• ✓ Normalization reduces redundancy and anomalies
• ✓ Relationships connect related data across tables
• ✓ Good design is crucial for long-term maintainability

Next lecture: Entity-Relationship modeling and practical database design!

Practical Setup: Installing MySQL

To practice with MySQL, we'll use the ZIP distribution (no installer required)

• Step 1: Download MySQL Community Server (ZIP Archive) from MySQL Downloads
• Step 2: Extract the ZIP to a folder (e.g., C:\mysql or ~/mysql)
• Step 3: No installation needed - we'll initialize and run from command line

This method gives you full control and is perfect for learning!

MySQL Setup: Initialize the Database

After extracting MySQL, initialize the data directory (first time only)

# Navigate to MySQL bin directory
cd C:\mysql\bin   # Windows
cd ~/mysql/bin    # macOS/Linux

# Initialize MySQL with insecure mode (no root password)
mysqld --initialize-insecure --console

# This creates the data directory and system tables
# Look for: [Server] /usr/local/mysql/bin/mysqld: ready for connections

--initialize-insecure creates root user with NO password (easier for learning)

MySQL Setup: Start the Server

Start MySQL server from command line

# Start MySQL server (run in one terminal)
mysqld --console

# You should see:
# [Server] X Plugin ready for connections on port 33060
# [Server] MySQL Server ready for connections on port 3306

# Leave this terminal running - this is your MySQL server!

Keep the server terminal open. To stop: Press Ctrl+C

MySQL Setup: Connect via Command Line

Open a new terminal and connect to MySQL

# Connect as root (no password needed with --initialize-insecure)
mysql -u root

# You should see:
# Welcome to the MySQL monitor.
# mysql>

# Try your first command:
mysql> SHOW DATABASES;
mysql> CREATE DATABASE test_db;
mysql> USE test_db;
mysql> CREATE TABLE hello (id INT, message VARCHAR(50));
mysql> INSERT INTO hello VALUES (1, 'Hello MySQL!');
mysql> SELECT * FROM hello;

Installing DBeaver (Visual Database Tool)

For a better experience, use DBeaver - a free, universal database client

• Step 1: Download from dbeaver.io/download
• Step 2: Install (Windows: .exe, macOS: .dmg, Linux: .deb/.rpm)
• Step 3: Launch DBeaver

Why DBeaver?

• ✓ Visual query builder and results viewer
• ✓ SQL syntax highlighting and auto-completion
• ✓ ER diagrams and database exploration
• ✓ Supports MySQL, PostgreSQL, Oracle, and many others

DBeaver: Start Working!

• 1. After connecting, expand the MySQL connection in the Database Navigator
• 2. Right-click on connection → SQL Editor → New SQL Script
• 3. Type your first query:

  SHOW DATABASES;
  CREATE DATABASE my_first_db;
  USE my_first_db;
  CREATE TABLE students (
      id INT PRIMARY KEY AUTO_INCREMENT,
      name VARCHAR(100),
      age INT
  );
  INSERT INTO students (name, age) VALUES ('Alice', 20);
  SELECT * FROM students;

• 4. Press Ctrl+Enter (Windows/Linux) or Cmd+Enter (macOS) to execute

Summary: Your MySQL Setup is Complete!

You now have two ways to work with MySQL:

Next step: Complete the homework exercises using either tool!