CS-10337 – Applied Database Technologies� Lecture 1

By Prof. Rafael Orta

Wayground

Last class we covered

• Introduction
• Syllabus
• Office Hours
• Week-by-week calendar
• Instructure / Canvas
• My Website
• The Academic Desktop / Elvis Database
• Late assignments and extra credit policy

​

​

Agenda

• Data vs Information
• Introduction & Types of Databases
• Getting started with MySQL
• MySQL Shell
• MySQL Workbench

Data vs. Information

Data vs. Information

Introduction to Databases

What is DBMS?

​

A Database Management System (DBMS) is a Software system designed to handle, store, retrieve, and work with data in databases. It ensures effective data management by serving as an interface between databases and end users. A DBMS provides users/developers with the ability to create, edit, and remove databases, as well as organize the environment in which data is stored in tables, records, and fields.

​

​

Introduction to Databases

If DBMS stands for Database Management System, what does RDBMS stand for?

A/ Relational Database Management System

Introduction to Databases

Why Did We Need Databases?

​

Pre‑computer era: Like pilots using paper flight logs and charts—slow to update, easy to misplace, and hard to share. Information was stored in ledgers, notebooks, and index cards.

�Analogy: Think of a public library’s card catalog—you had to flip through thousands of cards just to find one book.

​

Early Computer Storage (Pre-Database Era)

Punch Cards & Paper Tape (1940s–1950s): Early computers stored data using physical media with holes or marks.�

Magnetic Tape (1950s): Introduced sequential digital storage—fast for writing large amounts but slow for retrieving a specific record.�

​

​

Introduction to Databases

Hard Disk Drives (1956 onward): IBM’s RAMAC made random access possible—jump directly to the data you need.�

Analogy: Like flipping instantly to page 85 of a book instead of scrolling through the whole text.

​

File Systems: A Step Forward, But Limited. With the advent of hard drives came the concept of files and directories. Programs stored data in flat files (text, CSV, binary).

​

• Limitations:
• Poor search capabilities—finding data meant scanning the entire file.
• Redundancy—same data repeated in many files.
• Inconsistency—updating one copy didn’t update others.
• Concurrency issues—two users editing at the same time caused corruption.

​

​

Introduction to Databases

First Databases: Navigational Models (1960s)

​

Hierarchical Model: Data stored in tree structures (parent → child). IBM IMS is a classic.�Analogy: Like folders on your computer—open a parent folder to get to the child folder.

​

Network Model: Allowed more flexible connections using pointers. CODASYL was the pioneer.�Analogy: Like a subway system map—you follow specific tracks to get from one station to another.

​

Problem: Applications were tightly coupled to the data structure. Any change in data requires rewriting the program.

​

Introduction to Databases

The Relational Revolution (1970s)

​

E. F. Codd’s Relational Model (1970): Store data in tables (rows and columns).

​

Key breakthrough: You can query by content rather than by following a path. SQL emerged as the language to interact with these databases.

​

Examples: Oracle (1979), Ingres, IBM DB2.

​

Analogy: Like searching a library’s digital catalog by author, title, or subject, instead of memorizing the shelf layout.

​

Introduction to Databases

Objects Meet Databases (1980s–1990s)

​

Object-Oriented Databases: Store complex data as “objects” closer to how programming languages like Java represent them.

​

Object-Relational Databases: Hybrid models (e.g., PostgreSQL) allowed both relational querying and richer data types.

​

Analogy: Like a recipe card that doesn’t just list ingredients but also contains instructions that can execute themselves.

​

Introduction to Databases

The NoSQL Movement (2000s)

As the internet grew, relational systems struggled with massive, unstructured, and distributed data.

NoSQL offered flexibility:

• Key-Value Stores (Redis)
• Document Stores (MongoDB)
• Column Stores (Cassandra)
• Graph Databases (Neo4j)

​

Trade-off: Faster and more scalable, but sometimes weaker guarantees for transactions.

​

Analogy: Like different types of warehouses—some designed for neatly stacked boxes (tables), others for free-form crates (documents), or complex shipping routes (graphs).

Introduction to Databases

NewSQL & Distributed SQL (2010s–Today)

​

Goal: Global scalability without losing relational integrity.

Systems like CockroachDB, Google Spanner, and Yugabyte provide both SQL querying and ACID guarantees.

​

Analogy: Like a global package delivery service (UPS, FedEx) that ensures no matter where you ship from or to, the tracking, timing, and delivery standards are consistent.

Supplemental Video(s)

The fascinating history of databases by Awesome

Supplemental Reading

The History of DBMS by GeekforGeeks

Knowledge Check

Which statements correctly describe the evolution from files to databases?

​

A. Flat files often led to redundancy, inconsistency, and inefficiency.�B. Hierarchical databases store data in tree-like structures, requiring rigid navigation.�C. The relational model introduced tables and SQL, making queries based on content rather than fixed paths.�D. NoSQL databases were designed to handle internet-scale, unstructured, and distributed data.�E. NewSQL systems attempt to combine global scalability with relational integrity.

​

Correct answers: A, B, C, D, E.

​

Getting Started with MySQL

Introduction to MySQL

​

What is MySQL?

An open-source relational database management system (RDBMS).

Initially developed by MySQL AB in 1995, it was later acquired by Sun Microsystems (2008) and then by Oracle Corporation (2010).

Still widely used today—especially for web applications (Facebook, WordPress, Wikipedia).

​

​

​

Getting Started with MySQL

Why popular?

​

• Free & open-source.
• Cross-platform.
• Strong community support.
• Easy to learn, yet powerful enough for large applications.

​

Analogy: MySQL is like a Swiss Army knife for data—simple enough to slice an apple, but with enough tools to build a treehouse.

​

​

​

Getting Started with MySQL

Getting Started with MySQL

MySQL Architecture Overview: At a high level, MySQL can be thought of as a layered system:

​

Client Layer: Applications or users send SQL statements (e.g., SELECT, INSERT).

​

Tools: MySQL Workbench, command-line client, web apps.

​

Server Layer: SQL Parser: Breaks down statements into understandable parts.

​

• Optimizer: Figures out the fastest way to execute a query.
• Query Cache: May store results of frequent queries (older versions).

​

​

​

​

Storage Engine Layer

Responsible for how data is stored/retrieved physically.

​

MySQL is pluggable, with different engines available (MyISAM, InnoDB, MEMORY, etc.).

​

We will focus on InnoDB, the default since MySQL 5.5.

​

Getting Started with MySQL

InnoDB: The Default Storage Engine

​

• Supports ACID transactions (Atomicity, Consistency, Isolation, Durability).
• Uses row-level locking (better for concurrency).
• Supports foreign keys and referential integrity.
• Stores data in tablespaces for efficient organization.

​

Analogy: Think of InnoDB as a modern warehouse with barcode scanning (transactions), secure locks (integrity), and well-organized aisles (tablespaces).

​

​

​

Getting Started with MySQL

Key MySQL Concepts for Beginners

​

These terms will be used throughout the course:

​

- Database: A container for data, usually representing a project, system, or application.

​

- Schema: In MySQL, “schema” and “database” are often used interchangeably. Technically, a schema is the logical structure (tables, views, and indexes).

​

- Tablespace The physical storage area where InnoDB keeps its tables and indexes. Can be a shared space or per-table.

​

- Data File: The actual file on disk where data is stored (.ibd for InnoDB). MySQL automatically manages them, but it’s essential to be aware of their existence.

​

- Table: The core structure of rows and columns where data lives.

​

- Users: MySQL requires accounts to establish a connection.

​

Each user has a username, host, and password.

Example: rafael@localhost.

​

Getting Started with MySQL

• Permissions / Roles: MySQL controls access via privileges:
• SELECT, INSERT, UPDATE, DELETE → control actions on data.
• CREATE, DROP → control schema changes.

​

• Roles are collections of privileges (introduced in MySQL 8).

​

How MySQL Stores Data

​

• When you create a database, MySQL makes a folder in its data directory.
• Inside, tables map to files (with extensions like .frm, .ibd).

​

​

• InnoDB uses a tablespace system that may span multiple files.
• System tables (user accounts, permissions, etc.) are kept in a special MySQL database.

​

Getting Started with MySQL

Why MySQL Matters

​

• Powers millions of web applications.
• A stepping stone: knowledge here applies to Oracle, PostgreSQL, and SQL Server.
• Helps students understand both theory (RDBMS design) and practice (SQL coding, storage, permissions).

​

Supplemental Video(s)

What is MySQL Explained by Hosting Academy

Supplemental Reading

MySQL, what it is, and how it is used by Oracle

Knowledge Check

Which statements about MySQL are correct?

​

A. A schema in MySQL is essentially the same as a database.�B. InnoDB is the default storage engine and supports transactions and foreign keys.�C. Tablespaces are physical storage areas where InnoDB organizes data.�D. Permissions in MySQL allow fine-grained control, such as allowing SELECT but not INSERT.�E. A MySQL user account consists only of a username and does not include host information.

​

Correct Answers: A, B, C, D

​

MySQL Shell

What is MySQL Shell?

​

• MySQL Shell (a.k.a. mysqlsh) is a modern client and scripting environment for MySQL.
• It was introduced as part of MySQL 8.0 to replace the older, more limited MySQL Command-Line Client (mysql).
• Unlike the classic client, MySQL Shell supports multiple languages, better scripting, and advanced features.

​

​

​

​

Key Features

​

Multiple Modes:

​

• SQL Mode (\sql) → Run SQL queries (similar to the classic client).
• JavaScript Mode (\js) → Use JavaScript to script database tasks.
• Python Mode (\py) → Use Python to automate database operations.

​

Scripting and Automation

• Allows writing full scripts (in Python or JS) to manage databases.
• Useful for DevOps and DBAs who want repeatable automation.

​

MySQL Shell

​

Administration Utilities

​

• Built-in tools for backups, monitoring, and replication setup.
• Includes AdminAPI to configure and manage InnoDB Cluster for high availability.

​

JSON Support

• Since MySQL 8 handles JSON as a native data type, MySQL Shell has commands that make working with JSON documents easier.

​

Improved Output

• Query results can be displayed in different formats (table, JSON, tabbed, vertical).
• Much easier to read compared to the classic MySQL client.

​

MySQL Shell

Why Use MySQL Shell?

​

• More powerful than the old client.
• Scripting makes it easier to automate repetitive tasks (like creating users or checking replication status).
• Essential for working with MySQL InnoDB Cluster and Group Replication.
• Flexible: You don’t need to leave the shell if you want to switch between SQL queries and scripting.

​

Analogy: The old MySQL client is like a basic calculator—you can do one operation at a time.�MySQL Shell is more like a scientific calculator with programming functions—it handles simple tasks but also lets you write entire formulas.

​

Supplemental Video(s)

Running SQL Scripts with MySQL Shell by MySQL

Supplemental Reading

MySQL Shell by MySQL

Knowledge Check

Which of the following statements about MySQL Shell (mysqlsh) are correct?

​

A. MySQL Shell only supports SQL mode, just like the classic MySQL command-line client.�B. MySQL Shell supports SQL, JavaScript, and Python modes for queries and scripting.�C. MySQL Shell includes administration utilities such as AdminAPI for managing InnoDB Cluster.�D. MySQL Shell allows query results to be shown in different formats (e.g., table, JSON, vertical).�E. MySQL Shell cannot be used for automation; it is intended only for manual SQL queries.�F. MySQL Shell has built-in support for working with JSON documents.

​

Correct Answers: B, C, D, F

​

Attendance

MySQL Workbench

What is MySQL Workbench?

​

MySQL Workbench is a free, official graphical user interface (GUI) tool for MySQL.

Developed by Oracle, it combines database design, SQL development, administration, and visualization into one application.

​

It runs on Windows, macOS, and Linux.

​

Key Features

​

SQL Development

• Query editor to run SQL statements.
• Syntax highlighting and autocomplete for easier learning.
• Multiple tabs for running queries against different databases.

​

Data Modeling (ER Diagrams)

• Create Entity-Relationship (ER) diagrams visually.
• Forward-engineer: Turn diagrams into physical databases.
• Reverse-engineer: Generate diagrams from an existing database.

​

MySQL Workbench

Database Administration

• Manage users and roles.
• Configure server settings.
• Backup and restore databases.
• Monitor server health (connections, status, performance).

​

Visual Tools

• Query results displayed in grids, charts, or JSON.
• Schema and object browsers on the side panel.

​

Why Use MySQL Workbench?

​

• Ideal for beginners: No need to memorize all SQL commands to get started.
• Useful for visual learners who want to “see” the database structure.
• Great for professionals too: ER diagrams and modeling help in real projects.
• Complements MySQL Shell: Shell is powerful for scripting/automation, while Workbench is strong for visual design and management.

MySQL Workbench

Basic Workflow in MySQL Workbench

​

Connect to a MySQL Server

• Use the connection wizard (host, port, username, password).

Create a Database (Schema)

• Right-click in the schema panel → “Create Schema.”

Create Tables

• Define columns, data types, and constraints in the GUI.

Run Queries

• Use the SQL editor to run SELECT, INSERT, etc.

Manage Users

• Administration tab → create new users and assign privileges.

​

Supplemental Video(s)

Introduction to MySQL Workbench by Caleb Curry

Supplemental Reading

MySQL Workbench Tutorial

Knowledge Check

Which of the following statements about MySQL Workbench are correct?

​

A. MySQL Workbench is a graphical user interface (GUI) tool for MySQL.�B. MySQL Workbench can be used to create and visualize ER diagrams.�C. MySQL Workbench can manage users, roles, and server administration tasks.�D. MySQL Workbench only runs on Windows systems.�E. MySQL Workbench includes an SQL editor with syntax highlighting and autocomplete.

​

Correct Answers: A, B, C, E

​

Reference Material used in this presentation

• Murach’s MySQL 3^rd Edition.
• Fundamentals of Database Systems, 7^th Edition by Elmasnri
• Proprietary Material by the author.
• Material from Professor Jack Mayers.
• Material from Professor Phillip Quinn.