Key Concepts and Demos

1. Introduction to Databases

• Database: A structured collection of data.
• Database Schema: Blueprint of how data is stored and related.
• Tables: Store data in rows (records) and columns (attributes).
• SQL (Structured Query Language): Used to interact with databases.

2. Creating a Database

• Use CREATE DATABASE database_name;
• Activate the database using USE database_name;

Example:

CREATE DATABASE intro_to_data;

USE intro_to_data;

3. Creating Tables

• Tables contain columns (attributes) that store specific types of data.
• CREATE TABLE table_name with column names & data types.

Example:


CREATE TABLE equipment_maintenance (

    equipment_id INT,

    maintenance_cost DECIMAL(10,2),

    description VARCHAR(255),

    is_operational BOOLEAN,

    last_maintenance_date DATE

);

• ; (semicolon) is required at the end of SQL statements.

4. Inserting Data

• Use INSERT INTO table_name VALUES (…);
• Must match the order of columns.

Example:


INSERT INTO equipment_maintenance VALUES 

(101, 2500.75, ‘Engine maintenance’, TRUE, ‘2024-01-01’),

(102, 500.00, ‘Aircon maintenance’, FALSE, ‘2023-12-15’);

Constraints in SQL

• Constraints are rules that enforce data integrity.

Constraint	Function
NULL	Allows empty values
NOT NULL	Prevents empty values
UNIQUE	Ensures unique values in a column
PRIMARY KEY	Uniquely identifies a row (combination of NOT NULL & UNIQUE)
FOREIGN KEY	Links a column to another table’s primary key

Example: Adding constraints while creating a table
sql

CREATE TABLE equipment (

    equipment_id INT PRIMARY KEY,

    name VARCHAR(255) NOT NULL,

    type VARCHAR(255) NOT NULL

);

• If constraints are not met, SQL will return an error.

Primary Keys

• Unique Identifier for each row.
• Must be Unique & Not Null.

Example:
sql

CREATE TABLE customers (

    customer_id INT PRIMARY KEY,

    first_name VARCHAR(50),

    last_name VARCHAR(50),

    email VARCHAR(100)

);

• Best practices:
  • Keep primary keys constant.
  • Use integer (auto-incrementing) IDs.

Foreign Keys (Linking Tables)

• Links a column in one table to the primary key in another.
• Enforces Referential Integrity.

Example:
sql

CREATE TABLE maintenance_log (

    log_id INT PRIMARY KEY,

    equipment_id INT NOT NULL,

    maintenance_date DATE,

    FOREIGN KEY (equipment_id) REFERENCES equipment (equipment_id)

);

• This ensures that equipment_id in maintenance_log must exist in equipment.

Checking Tables & Debugging

• View all tables: SHOW TABLES;
• Check table structure: DESCRIBE table_name;
• Delete table: DROP TABLE table_name;
• Delete database: DROP DATABASE database_name;

Common Errors & Solutions

Error	Possible Fix
Missing semicolon (;)	Ensure statements end with ;
Reserved word error	Check SQL syntax and keywords
Connection issues in DBeaver	Ensure MySQL is installed & configured correctly
Error inserting data	Check constraints (e.g., NOT NULL, UNIQUE)