MySQL Database Fundamentals

Creating a Database in MySQL

• Assuming standard installation of MySQL Community Edition, three existing databases should be visible in the left navigator pane.
• In MySQL, "schema" and "database" are interchangeable.
• MySQL Workbench offers two methods: built-in wizards or writing SQL statements.

Creating a Database Using the Wizard

• Click the new schema button.
• Name the database (e.g., movies).
• Character set and collation can be left to default.
  • Character set: Determines which characters can be stored in the database. UTF-8 is a common choice for supporting a wide range of characters.
  • Collation: Specifies the rules for comparing characters. This affects sorting and searching.
• The Workbench generates an SQL statement to create the database.

Creating a Database Manually with SQL

• Open a new SQL tab.
• Use the CREATE DATABASE movies; statement.
• Refresh the navigator pane to see the new database.

Creating a Table

• Set the movies database as the default schema.
• Right-click on 'tables' and click 'create table'.
• Example table: movie_basic with columns for movies dataset (genre, release year, director, studio, critic rating).

Table Columns

• ID: Integer, primary key, not null, auto-increment.
  • Auto-increment ensures that each new row gets a unique ID automatically.
• Title: VARCHAR(100).
  • VARCHAR stores variable-length string data, up to a specified maximum length (here, 100 characters).
• Genre: VARCHAR(20).
• Release Year: Integer.
• Director: VARCHAR(40).
• Studio: VARCHAR(30).
• Critic Rating: DECIMAL(2,1) with a default value of 0.
  • DECIMAL(2,1) allows for a number with up to 2 digits, with 1 digit after the decimal point.
• PK: Primary Key
• NN: Not Null
• UQ: Unique
• UN: Unsigned
• ZF: Zero Fill
• AI: Auto Increment
• G: Generated Column

Creating Table with SQL

• Use the CREATE TABLE statement.
• Example:

CREATE TABLE movies_basic (
        ID INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
        title VARCHAR(100),
        genre VARCHAR(20),
        release_year INT,
        director VARCHAR(40),
        studio VARCHAR(30),
        critics_rating DECIMAL(2,1) DEFAULT 0
    );

• Whitespace and capitalization of SQL keywords are optional but improve readability.

Altering Tables

• Use the ALTER TABLE statement to modify existing tables.
• Example:

ALTER TABLE movies_basic
    ADD COLUMN box_office_gross FLOAT,
    RENAME COLUMN critics_rating TO critic_rating,
    CHANGE COLUMN director director VARCHAR(50);

Primary and Foreign Keys

• Primary Key: Uniquely identifies each row in a table. A table can only have one primary key.
  • Ensures that each record in the table is unique and can be easily identified.
• Foreign Key: A primary key from one table included in another table to establish a relationship. Reduces redundancy and ensures data integrity (database normalization).
  • Enforces referential integrity, preventing orphaned records and ensuring consistency across related tables.

Database Models

• Database models contain structural information (tables, views, relationships) without data.
• MySQL Workbench can reverse engineer an existing database to create a model.
  • This feature helps in understanding the structure of an existing database by visually representing its tables, columns, and relationships.
• EER (Entity Relationship) diagrams visualize table relationships.

Bulk Data Loading

- MySQL Workbench provides tools for importing data from files (e.g., CSV).