MySQL is a relational database management system used to store, organize, retrieve, and manage structured data. Before working with databases, tables, and SQL queries, it is important to understand how MySQL Database Server works, how to establish a connection, and how database structures can be represented using ER diagrams.
Learning Objectives
By the end of this article, you will be able to:
Understand what MySQL is
Understand the role of MySQL Database Server
Connect to a MySQL Server
Understand the purpose of ER diagrams
Identify entities, attributes, and relationships
Understand fact tables and dimensional tables
Recognize the difference between fact and dimensional tables
What is MySQL?
MySQL is a relational database management system used to organize information into tables consisting of rows and columns.
MySQL allows users to perform operations such as:
Creating databases
Creating and modifying tables
Inserting records
Retrieving information
Filtering and sorting data
Joining related tables
Updating records
Deleting records
SQL (Structured Query Language) is used to communicate with the database and perform these operations.
Understanding MySQL Database Server
The MySQL Database Server is responsible for storing databases and processing requests made by users or applications.
A typical setup includes:
MySQL Server – Stores and manages the databases.
MySQL Workbench – Provides a graphical interface for connecting to the server and working with databases.
SQL – The language used to send instructions to the database server.
For example, when you execute:
SHOW DATABASES;
MySQL Server processes the request and returns the available databases.
Connecting to MySQL
After installing and configuring MySQL, you need to establish a connection before working with databases.
A local MySQL connection commonly uses:
Hostname: localhost
Port: 3306
Username: root
Password: Your configured password
Open MySQL Workbench and select your local MySQL connection.
Enter your password when prompted.
After connecting successfully, the SQL Editor will open.
Verify the Connection
Run:
SELECT VERSION();
This displays the version of MySQL Server currently running.
You can also execute:
SHOW DATABASES;
Example Result
information_schema
mysql
performance_schema
sys
If the query executes successfully, your connection to MySQL Server is working.
What is an ER Diagram?
An Entity Relationship (ER) Diagram is a visual representation of a database structure. It shows the entities within a database and the relationships between them.
ER diagrams are useful when planning a database before creating its tables.
For example, consider a database containing:
Customer
Order
Product
A customer can place an order, and an order can contain products. An ER diagram visually represents these relationships.
Main Components of an ER Diagram
Entity
An entity represents a real-world object or concept that needs to be stored in the database.
Examples:
Customer
Employee
Product
Order
Entities are typically represented as tables when the database is created.
Attribute
An attribute represents information about an entity.
For example, a Customer entity may contain:
Customer_ID
Customer_Name
City
These attributes generally become columns in the database table.
Relationship
A relationship describes how entities are connected.
For example:
Customer → Places → Order
The relationship indicates that a customer can place an order.
Simple Database Example
Consider an order management database containing three tables.
Customers
customer_id
customer_name
city
Products
product_id
product_name
price
Orders
order_id
customer_id
product_id
quantity
order_date
The customer_id and product_id fields can be used to establish relationships between these tables.
What is a Fact Table?
A fact table stores measurable information related to business activities or events.
For example, a sales fact table could contain:
sale_id | product_id | customer_id | quantity | sales_amount |
1001 | 201 | 301 | 2 | 500 |
1002 | 202 | 302 | 1 | 300 |
Here, values such as quantity and sales_amount represent measurable information.
Fact tables commonly contain:
Transaction records
Numeric measurements
References to related dimension data
What is a Dimensional Table?
A dimensional table stores descriptive information that provides context for data in a fact table.
For example, a product dimension could contain:
product_id | product_name | category |
201 | Laptop | Electronics |
202 | Desk | Furniture |
Instead of storing all product information repeatedly in the fact table, product_id can be used to associate each transaction with its product information.
Other examples of dimensions include:
Customer
Product
Location
Date
Category
Fact Table vs Dimensional Table
Feature | Fact Table | Dimensional Table |
Purpose | Stores measurable business information | Stores descriptive information |
Typical Data | Quantity, amount, transaction data | Product, customer, location details |
Example | Sales transactions | Product information |
Records | Often contains many transaction rows | Usually contains descriptive records |
Relationship | References dimensions | Provides context for facts |
FactSales stores transaction information, while DimCustomer and DimProduct provide descriptive information about customers and products.
This structure makes it easier to organize and analyze business information across related tables.
Still need help?
Contact us