MIS_Week_2
Management Information Systems MySQL Workbench Tutorial
Page 1: Overview
Course Title: Management Information Systems
Week 2 Focus: MySQL Workbench Tutorial
Instructor: Yuyang Ye
Date: January 27, 2024
Class: Management Information Systems (Sp 2025)
Page 2: Software Installation
Required Software:
MySQL Server
MySQL Workbench
Installation Notes:
Both can be installed simultaneously on Windows.
Ensure compatibility between MySQL Server (8.0.x) and MySQL Workbench.
Follow video instructions on Canvas for installation steps (for Windows and Mac).
Page 3: Installation Components
Additional Installations:
MySQL Documentation: Aids in usage and troubleshooting.
Samples and Examples: Provides example databases and queries for practice.
Important: Remember the MySQL root account password. Recovery can be difficult if lost.
Page 4: Database Architecture
Client-Server Model:
Database Server handles data storage and management.
Clients interact with the server to query and update the database.
Preferences:
Large companies typically run the database on a separate computer.
Smaller setups may run both on a single machine for convenience.
Page 5: MySQL Workbench
GUI Overview:
MySQL Workbench provides a graphical interface to connect to the database server.
Uses include:
Database and table management
Data viewing and addition
Writing SQL queries
Page 6: Connecting to Database
Connection Steps:
Click on "Local Instance MySQL80" in the MySQL Connections list.
Page 7: Creating a New Connection
If not visible:
Click the "+" icon next to "MySQL Connections" to create a new connection.
Click "Ok" to proceed with connection setup.
Page 8: Database Creation
After connection, to create a database:
Select the "Schemas" tab to view local databases.
Page 9: Viewing Schemas and Tables
Example Schemas:
Expand the schema and tables sections to view installed tables.
If steps followed correctly, example schemas will populate.
Page 10: Viewing Table Rows
To view table rows:
Right-click table name (e.g., "city") and click "Select rows - limit 1000".
A simple SELECT SQL query will display the first 1000 rows in the top window.
Page 11: Modifying Table Definitions
Right-click on a table name and select "Alter Table" for modifications.
Options include viewing and changing the following:
Table name
Columns
Constraints
Indexes
Foreign keys
Page 12: Types of Data
Data Representation:
Numeric Data: Sales figures, temperatures, stock prices.
Discrete (e.g., products sold) vs. Continuous data (e.g., temperatures)
Textual Data: Customer reviews, social media posts, emails.
Visual Data: Images, videos, graphs.
Audio Data: Sound recordings, music.
Page 13: Common MySQL Data Types
Overview of data types to be discussed in subsequent pages.
Page 14: INTEGER Data Type
Definition: Whole numbers without fractional parts, stored in 4 bytes.
Range: -2,147,483,648 to 2,147,483,647.
Variants exist for different ranges (see MySQL Manual).
Page 15: DOUBLE PRECISION Data Type
Definition: Numbers stored as a base and exponent, using 8 bytes.
Exponent range: -1022 to 1023.
Less precision than INTEGER or DECIMAL.
Example: 1/3 produces a repeating fraction.
Page 16: DECIMAL(N,D) Data Type
Stores exact numbers with a fractional part.
Format: DECIMAL(N,D) means N digits total with D after the decimal.
Example: DECIMAL(4,2) can store 65.99 but not 165.1.
Page 17: DATETIME Data Type
Format: YYYY-MM-DD hh:mm:ss
Example: "2024-05-15 09:39:22"
Page 18: DATE Data Type
Format: YYYY-MM-DD
Example: "2024-05-15"
Page 19: VARCHAR(N) Data Type
Definition: String containing variable characters, up to N.
Example: VARCHAR(5) can store "hello" but not "goodbye".
Page 20: TEXT Data Type
Definition: String of varying lengths, not usable for keys.
Example: "hello and goodbye"
Page 21: BOOLEAN Data Type
Definition: Logical values TRUE or FALSE, stored as 1 or 0 (1 byte).
Example: TRUE
Page 22: Data Type Abbreviations
Guidance on MySQL data types, noting differences in how they may be displayed.
Page 23-25: Abbreviations and Documentation
Focus on understanding abbreviations like INT for INTEGER.
Reference MySQL Official Documentation and examples in MySQL Workbench.
Page 26: Table Information
Introduction to examples of data types in MySQL Workbench.
Page 27: Effective Use of ChatGPT
Tips for obtaining better responses:
Ask clear, specific questions.
Ensure understanding of responses.
Verify accuracy through multiple sources.
Page 28: Good vs. Bad Questions
Examples of good questions for effective outcomes.
Good: Asking for definitions and examples of data types.
Bad: Asking generic or vague questions.
Page 29: Numeric Data Types Overview
Overview of numeric data types such as INT, DECIMAL, FLOAT, and DOUBLE.
Page 30: String Data Types Overview
Overview of string data types including CHAR, VARCHAR, and TEXT.
Page 31: Date and Time Data Types Overview
Overview of date and time data types: DATE, TIME, DATETIME, and TIMESTAMP.
Page 32: Binary Data Types Overview
Introduction to BLOB and its variants for binary data storage.
Page 33: Miscellaneous Data Types
Overview of BOOLEAN, ENUM, and SET data types.
Page 34: Customizing Data Types
Explanation of customizing MySQL data types with options such as UNSIGNED and ZEROFILL.
Page 35: Adding Data to Tables
Steps for adding data to existing tables:
Open Table View and select a table.
Edit data directly in the view.
Save changes and confirm SQL commands.
Page 36: Removing Data from Tables
Steps to remove data:
Select the row to remove and confirm deletion.
Option to revert changes if necessary.
Page 37: Running an SQL Query
Steps to write and execute SQL queries in MySQL Workbench:
Open a new query tab and select target database.
Execute query with the yellow bolt icon.
Page 38: Example of SQL Query
Example SQL query structure:
SELECT All columns (*) from "world(database).city(table)".