Webbo3 Data Analysis Bootcamp · SQL Module · Lesson 1

Database Fundamentals: Understanding Databases, Installing MySQL, and Creating Your First Tables

A foundational lesson covering what databases and database management systems are, how to install MySQL and MySQL Workbench, and how to create databases and tables with the right data types.

Every application you use, from your banking app to your favorite e-commerce site, stores its data somewhere. That somewhere is a database. Understanding how databases work is not optional for a data analyst. It is the foundation upon which everything else, SQL queries, data extraction, reporting, and analysis, is built. This lesson starts at the very beginning. What is a database? What is the difference between a DBMS and an RDBMS? How do you install the most popular open-source database in the world? And how do you create your first database and table with the correct data types? By the end of this lesson, you will have a working MySQL installation and your first functional database ready for data.

1. What Is a Database, a DBMS, and an RDBMS?

These three terms are often used interchangeably by beginners, but they mean very different things. Getting the distinction right now will prevent confusion for the rest of this bootcamp and your entire career.

A database is the data itself. It is an organized collection of structured information stored electronically. Think of it as a digital filing cabinet where related pieces of information are grouped together so they can be accessed, managed, and updated efficiently. A database can store text, numbers, dates, images, and more, but the key word is organized. Without organization, a collection of files on your desktop is just storage. A database is storage with structure and rules.

A DBMS is the software that manages the database. DBMS stands for Database Management System. It is the middleman between you and the raw data. You do not open a database file directly like you open a Word document. You send commands to the DBMS, and the DBMS reads, writes, updates, and deletes data on your behalf. The DBMS handles security, concurrency when multiple users access the same data simultaneously, backup and recovery, and query optimization. Examples of DBMS software include MySQL, PostgreSQL, Oracle, Microsoft SQL Server, MongoDB, and SQLite. Some of these are relational. Some are not.

An RDBMS is a specific type of DBMS. RDBMS stands for Relational Database Management System. It is a DBMS that stores data in tables, rows, and columns, and enforces relationships between those tables using keys. The relational model, invented by Edgar Codd at IBM in 1970, is the dominant paradigm in data management today. In an RDBMS, data is not stored in random files or nested documents. It lives in two-dimensional tables where each row represents one record and each column represents one attribute of that record. Tables can relate to each other through primary keys and foreign keys, which prevents data duplication and ensures consistency. MySQL, PostgreSQL, Oracle, and Microsoft SQL Server are all RDBMS products.

The difference between DBMS and RDBMS in practice. A DBMS like MongoDB stores data as flexible JSON-like documents. There are no rigid tables, no fixed columns, and no enforced relationships. This flexibility is useful for unstructured or rapidly changing data, like social media posts or product catalogs with wildly varying attributes. An RDBMS like MySQL enforces structure. Every table has a defined schema. Every column has a defined data type. Relationships are explicit and enforced by the database engine. This rigidity is a feature, not a bug, because it guarantees data integrity, which is why banks, hospitals, and e-commerce platforms overwhelmingly choose RDBMS for their core transactional data. As a data analyst, you will work with both, but RDBMS skills are more universally required.

The global RDBMS market is projected to reach USD 118.4 billion by 2027, growing at over 11 percent annually. This is not a legacy technology. It is the backbone of modern business, and SQL is the language you use to talk to it.

2. Installing MySQL and MySQL Workbench

MySQL is the world's most popular open-source RDBMS. It powers Facebook, Twitter, YouTube, and countless other applications. MySQL Workbench is the official graphical user interface for MySQL. It lets you write queries, design tables visually, manage users, and import data without memorizing command-line syntax. You need both installed to follow this bootcamp.

Installing MySQL Community Server on Windows.

1. Go to the official MySQL Community Server download page at dev.mysql.com/downloads/mysql. This is the only source you should use. Third-party download sites may bundle unwanted software.

2. Select the latest available version, for example MySQL 9.1.0 or newer. Choose Windows as your operating system. Select the Windows MSI Installer, which is the easiest option for beginners.

3. Click Download. On the next page, click "No thanks, just start my download" to skip the account creation step.

4. Run the downloaded MSI file. The MySQL Installer launches. Choose Setup Type: select Server Only if you only need the database engine, or Full if you want all tools including connectors and documentation. For this bootcamp, Server Only is sufficient because you will install Workbench separately.

5. Proceed through the installation with default settings. When you reach the Authentication Method step, choose Use Strong Password Encryption for Authentication. This is the modern standard.

6. When prompted, set a root password. This is the master password for your MySQL server. Write it down and store it securely. There is no password recovery through the installer. If you forget it, you must reset it manually using command-line procedures.

7. On the Windows Service step, leave "Configure MySQL Server as a Windows Service" checked. This ensures MySQL starts automatically every time you boot your computer. The default service name is MySQL80 or MySQL81 depending on your version.

8. Click Execute to apply the configuration, then Finish. MySQL is now installed and running.

Installing MySQL Community Server on Mac.

1. Go to dev.mysql.com/downloads/mysql. Select macOS as your operating system.

2. Choose the correct architecture. If your Mac has an Intel processor, select x86. If it has an Apple Silicon chip, M1, M2, or M3, select ARM. Not sure? Click the Apple icon in the top-left corner, then About This Mac, and look for Chip or Processor.

3. Download the DMG Archive. Click "No thanks, just start my download."

4. Open the downloaded .dmg file. Double-click the .pkg installer inside. Proceed with the default installation settings.

5. When prompted, create a MySQL root password. Write it down securely.

6. After installation, open System Preferences or System Settings, find MySQL, and confirm the server status shows Running.

Installing MySQL Workbench.

1. Go to dev.mysql.com/downloads/workbench. Select your operating system, Windows or macOS, and the same architecture you chose for the server.

2. On Windows, download the MSI installer and run it with default settings. On Mac, download the DMG, open it, and drag the MySQL Workbench icon into your Applications folder.

3. Launch MySQL Workbench. On first launch, you may see a security prompt. Click Open to proceed.

4. Click the plus icon next to MySQL Connections to create a new connection. Use localhost as the hostname and 3306 as the port. These are the defaults. Enter the root password you created during MySQL installation. Click Test Connection. If you see "Successfully made the MySQL connection," click OK to save.

5. Double-click your saved connection to open the SQL editor. You now have a working MySQL environment. The left panel shows your schemas, or databases. The center panel is where you write SQL. The bottom panel shows query results.

3. Creating a Database: CREATE DATABASE

Before you can create tables, you need a database to hold them. A database in MySQL is a container, a named space where related tables, views, stored procedures, and indexes live together. Think of it as a folder on your computer. You would not dump every file you own into one folder. You create folders for Work, School, Photos, and so on. Databases work the same way. A company might have one database for sales, another for inventory, and another for human resources.

The CREATE DATABASE statement. In the MySQL Workbench SQL editor, type the following and press the lightning bolt icon or Ctrl + Enter to execute:

CREATE DATABASE webbo3_retail;

This creates a database named webbo3_retail. The semicolon at the end is required. It tells MySQL that the statement is complete. If you forget it, MySQL waits for more input. Database names in MySQL are case-sensitive on Linux but not on Windows or Mac. The safest practice is to use lowercase with underscores, which works everywhere.

Avoiding errors if the database already exists. If you run CREATE DATABASE twice on the same name, MySQL throws an error saying the database already exists. To prevent this, use:

CREATE DATABASE IF NOT EXISTS webbo3_retail;

This version silently does nothing if the database already exists, which is useful when running scripts multiple times.

Selecting the database to use. Creating a database does not automatically make it active. You must tell MySQL which database you want to work in. Type:

USE webbo3_retail;

After running this, the database name appears in the toolbar or status bar of MySQL Workbench, confirming that subsequent commands will apply to this database. If you skip this step and try to create a table, MySQL will ask which database you mean.

Seeing what databases exist. To list all databases on your MySQL server, run:

SHOW DATABASES;

You will see system databases like information_schema, mysql, performance_schema, and sys alongside your own. Do not delete or modify the system databases. They are required for MySQL to function.

4. Creating Tables: CREATE TABLE

A table is where your actual data lives. It has a name, a set of columns, and a set of constraints that enforce rules on the data. Creating a table is the most important schema design decision you will make, because once a table contains data, changing its structure becomes harder. Get the design right from the start.

The basic CREATE TABLE syntax. Here is a simple example for a customers table:

CREATE TABLE customers (
  customer_id INT PRIMARY KEY AUTO_INCREMENT,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE,
  phone VARCHAR(20),
  registration_date DATE DEFAULT (CURRENT_DATE),
  is_active BOOLEAN DEFAULT TRUE
);

Let us break this down line by line. customer_id is an integer that serves as the primary key, meaning it uniquely identifies every row in the table. AUTO_INCREMENT tells MySQL to generate this number automatically, starting at 1 and increasing by 1 for each new row. You never have to manually assign a customer_id. first_name and last_name are variable-length text fields with a maximum of 50 characters each. NOT NULL means these columns cannot be left empty. Every customer must have a name. email is also text, up to 100 characters, and UNIQUE means no two customers can share the same email address. phone is optional, so it has no NOT NULL constraint. registration_date is a date, and DEFAULT (CURRENT_DATE) means if you insert a row without specifying a date, MySQL fills it with today's date automatically. is_active is a boolean, defaulting to TRUE, which means new customers are active by default unless you specify otherwise.

Constraints and why they matter. Constraints are rules enforced by the database engine. PRIMARY KEY ensures uniqueness and provides a fast lookup index. NOT NULL prevents missing data in critical columns. UNIQUE prevents duplicates in columns like email or national ID numbers. FOREIGN KEY, which you will learn in a later lesson, links one table to another and enforces referential integrity. These constraints are not suggestions. They are enforced at the database level, which means even if a buggy application tries to insert invalid data, the database refuses. This is why RDBMS systems are trusted for financial and medical data.

Seeing the table structure. After creating a table, you can inspect it with:

DESCRIBE customers;

This shows every column, its data type, whether it allows NULL, its default value, and any extra attributes like auto-increment. It is your quick reference for what the table expects.

Deleting a table. If you make a mistake and need to start over:

DROP TABLE IF EXISTS customers;

The IF EXISTS clause prevents an error if the table does not exist. Use DROP TABLE with caution. It permanently deletes the table and all its data. There is no undo in SQL.

5. Data Types: INT, VARCHAR, DATE, DECIMAL, BOOLEAN, TEXT

Choosing the right data type for each column is one of the most consequential decisions in database design. The wrong choice wastes storage, slows queries, and can corrupt your data. The right choice ensures integrity, efficiency, and clarity. MySQL offers dozens of data types, but these six are the ones you will use in almost every table you build.

INT: Integer numbers. INT stores whole numbers, both positive and negative, from approximately negative 2.1 billion to positive 2.1 billion. For most counting and ID purposes, INT is the standard choice. If you know your numbers will be small, for example a status code from 1 to 5, you can use TINYINT, which stores from negative 128 to positive 127 and uses one byte instead of four. If your numbers will exceed 2.1 billion, use BIGINT. The principle is simple: use the smallest data type that fits your range. Smaller types use less disk space, fit more rows in memory, and make indexes faster.

VARCHAR: Variable-length text. VARCHAR(n) stores text strings up to n characters. The n you specify is the maximum, not the allocation. If you store "Lagos" in a VARCHAR(100) column, MySQL uses only 5 bytes plus a small overhead, not 100 bytes. This makes VARCHAR efficient for names, emails, addresses, and any text where length varies. Choose your n carefully. VARCHAR(50) for a first name is usually enough. VARCHAR(255) for an email is generous. Do not default to VARCHAR(1000) for everything, because while the storage is efficient, index sizes grow with the maximum declared length, and some operations become slower.

DATE: Calendar dates. DATE stores dates in the format YYYY-MM-DD, for example 2026-06-23. It occupies three bytes and supports date arithmetic natively. You can subtract one DATE from another to get the number of days between them. You can extract the month, year, or day of week with built-in functions. Never store dates as text in a VARCHAR column. Text dates cannot be sorted correctly, cannot be used in date calculations, and waste space compared to the native DATE type. If you need both date and time, use DATETIME or TIMESTAMP instead of DATE.

DECIMAL: Exact decimal numbers. DECIMAL(p, s) stores numbers with exact precision, where p is the total number of digits and s is the number of digits after the decimal point. DECIMAL(10, 2) can store numbers up to 99,999,999.99. This is the only data type you should use for money. Never use FLOAT or DOUBLE for financial data. FLOAT and DOUBLE are approximate types. They store numbers in scientific notation and can introduce tiny rounding errors, for example storing 0.1 as 0.10000000149011612. In financial calculations, those errors compound and become real money problems. DECIMAL stores the exact value you specify, to the last cent.

BOOLEAN: True or false values. MySQL does not have a dedicated BOOLEAN type internally. When you declare a column as BOOLEAN, MySQL treats it as TINYINT(1). TRUE is stored as 1 and FALSE as stored as 0. This is a convenience, not a limitation. You can insert TRUE, FALSE, 1, or 0 interchangeably. BOOLEAN is perfect for flags like is_active, is_paid, is_verified, and so on. It uses one byte per row, which is extremely efficient.

TEXT: Large text blocks. TEXT stores strings up to 65,535 characters, which is roughly 64 kilobytes. For even larger text, MEDIUMTEXT stores up to 16 megabytes and LONGTEXT up to 4 gigabytes. Use TEXT for descriptions, comments, blog posts, and any content where the length is unpredictable and potentially long. Do not use TEXT for short strings like names or emails. TEXT columns are stored separately from the rest of the row, which makes queries on them slightly slower than VARCHAR. Reserve TEXT for data that genuinely needs the space.

A practical habit to build now: before creating any table, write down every column you need and ask three questions for each. What is the smallest data type that fits the expected range? Is this column required for every row, or can it be NULL? Does this column need to be unique? Answering these questions before you write CREATE TABLE will save you from painful migrations later.

Using AI to Move Faster in Database Setup

The concepts in this lesson are foundational and must be understood manually. But once you grasp them, AI can dramatically speed up the mechanical work of writing CREATE TABLE statements, choosing data types, and troubleshooting installation issues.

1. Use Copilot or ChatGPT to generate CREATE TABLE scripts from descriptions.
Instead of typing every column and constraint by hand, describe your table in plain language and let AI write the SQL. For example: "Create a MySQL table for an e-commerce order with order_id as auto-increment primary key, customer_id as integer foreign key referencing customers, order_date as date defaulting to today, total_amount as decimal with two decimal places, and status as a string that can be 'pending', 'paid', 'shipped', or 'cancelled'." The AI will generate a complete, syntactically correct CREATE TABLE statement with appropriate data types and constraints. Your job is to review it, verify the data types match your actual requirements, and adjust any lengths or defaults before executing.

2. Ask AI to explain error messages in plain English.
MySQL error messages can be cryptic. If you get something like "ERROR 1064: You have an error in your SQL syntax," paste the entire error and your query into an AI assistant and ask: "What is wrong with this CREATE TABLE statement and how do I fix it?" AI can spot missing commas, mismatched parentheses, reserved words used as column names, and data type incompatibilities faster than manual scanning. This turns frustrating debugging into a quick conversation.

3. Get AI to suggest optimal data types for your specific use case.
If you are unsure whether to use VARCHAR(50) or VARCHAR(100) for a product SKU, or whether TINYINT is enough for a rating scale, describe your data to AI: "I need to store product ratings from 1 to 5. Should I use TINYINT UNSIGNED or INT in MySQL?" AI will explain that TINYINT UNSIGNED stores 0 to 255 in one byte, which is perfect for a 1-to-5 scale and saves three bytes per row compared to INT. On a table with ten million rows, that is 30 megabytes of storage saved just from one correct data type choice.

4. Use AI to plan multi-table schemas before writing any SQL.
Before creating a single table, describe your entire application to AI: "I am building a library management system. I need to track books, borrowers, loans, and fines. What tables should I create, what columns should they have, and how should they relate to each other?" AI will suggest a normalized schema with primary keys, foreign keys, and junction tables where needed. Use this as a starting blueprint, then refine it based on your own understanding of normalization and business rules. The value is not in blindly copying the AI output. It is in starting from a solid structure instead of a blank page.

5. Verify everything AI generates before executing it on your database.
AI can hallucinate data types that do not exist, suggest constraints that are syntactically invalid for your MySQL version, or forget to include NOT NULL on critical columns. Always read the generated SQL carefully. Run DESCRIBE after creating the table to confirm the structure matches your intent. Test with a few INSERT statements to make sure the constraints behave as expected. AI accelerates your workflow, but the final responsibility for a correct schema is always yours.

A habit worth building from this lesson onward: whenever you need to create a new table, sketch the schema on paper or in a note first, then use AI to generate the SQL, then audit the output line by line before executing. This workflow, plan, generate, audit, execute, is how professional developers use AI-assisted coding tools. It is faster than writing from scratch and safer than copying blindly.

Next lesson: inserting data, basic SELECT queries, and the WHERE clause.