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)".