MySQL Database Administration: Beginner SQL Database Design

Explore how SQL evolved from the 1970s, its universal standard, and how MySQL fits into the broader family of SQL flavors, with editors like MySQL Workbench, phpMyAdmin, and others.

Relational databases organize data into related tables linked by primary and foreign keys. Databases and schemas are treated interchangeably, and you start by creating and modifying tables.

Explore relational database management systems, focusing on schemas with related tables and relationships, and using MySQL Workbench to manage data, permissions, and table constraints.

Install MySQL community server and MySQL Workbench to host and manage schemas, connect MySQL Workbench to the server, review the interface, and create databases for the course.

Discover how to install MySQL community server on macOS by downloading the macOS DMG, running the installer, setting the root password, and verifying in system preferences before using MySQL Workbench.

Learn how to download and install MySQL Workbench on macOS, choose the DMG version from dev.mysql.com, install to applications, and open the app with a ready or blank connection.

Learn how to install MySQL community server and MySQL workbench on windows, including choosing the right installer, configuring the server, and setting a root password.

Create a new MySQL Workbench connection to the server using the plus icon, with the Root username. Enter the password when prompted and save it for future sessions.

Navigate the MySQL Workbench interface across Mac and PC, explore schemas, query editor, and administration tools to manage servers, databases, and SQL tasks efficiently.

Run the provided sql file in Workbench to create and seed course schemas with create schema and create table statements, then verify the new schemas under the schemas tab.

Create a new schema in MySQL Workbench using the UI, recognizing schema and database as interchangeable. Name the schema, set UTF-8, apply the SQL, and verify its creation.

Create schemas in MySQL Workbench using a simple SQL create statement, matching the UI options. Explore defaults like utf8 and utf8mb4 and observe the charset warnings as schemas are created.

Create tables in a chosen schema via UI tools or SQL code, define an auto-incrementing primary key and a non-null created at date time, and run queries.

Create a data table with SQL by writing a create table statement, selecting a schema, defining columns and data types, and applying primary key constraints.

Explore MySQL data types, from tiny int to big int, and decimal for monetary values, then review characters and varying characters, tiny text, text, date, date time, timestamp, and enum.

Create a schema Tom's marketing stuff and add publisher's table and a publisher spend table, with publisher id integer, publisher name up to 65 characters, month date, and spend decimal.

Create a new schema named Tom's marketing stuff and design two tables, publishers and publisher spend, with a primary key and required columns, using UTF eight and SQL practices.

learn to add and drop columns in mysql workbench using the editor ui, alter tables in thrift shop schema, and manage date time columns with not null and default null.

Alter tables to add or drop columns, specify data types, including decimal(10,2), and position new columns with after or at the end.

Drop the hourly wage column and add a new average customer rating decimal column with one decimal place on the employees table, using alter table.

Apply alter table to remove the hourly wage from employees and add an average customer rating as decimal(10,1) after position, with a hands-on walkthrough of schema inspection and change application.

Learn to drop schemas and tables in MySQL Workbench with careful permissions, using both UI and SQL commands. Understand confirmation prompts and the potential impacts on back-end processes and reporting.

Drop the candy products table from the candy store schema and remove the candy store old schema. Exercise caution to avoid dropping any preexisting schemas.

Drop the candy products table and the candy store old schema in MySQL, using Workbench or code, and heed the warning to drop carefully due to potential data loss.

Insert new records into MySQL tables using insert into, managing single or batch inserts, specifying columns or using defaults, illustrated with the thrift shop inventory example.

Update existing records in MySQL with a set clause and a where clause to target specific rows, preferably using primary keys to avoid bulk updates.

Insert two new employees into the employees table and update average ratings, adding Charles Munger and William Gates as clerks with hire date March 15, 2020 and 5.0 ratings.

Insert new records for employees seven and eight, Charles Munger and William Gates. Update the average customer rating values and demonstrate optional case statements for bulk updates.

Delete specific records using delete from with a where clause, the primary key, as shown in the thrift shop inventory table; control changes with autocommit, commit, and rollback.

Use truncate table to remove all records while preserving column definitions and constraints. Truncate is data definition language and cannot be rolled back, unlike delete which can.

Practice deleting records by removing Margaret Simpson from the employees table and emptying the customer reviews table while preserving structure, with guidance on autocommit, rollback, and commit.

Master MySQL deletions by removing a single employee record from the candy store employees table and deleting all customer reviews, using auto commit control, rollback, commit, and truncate as needed.

Explore how primary keys and foreign keys connect tables to form one-to-many relationships, enabling efficient joins and structured data storage across a film inventory example.

Demonstrate primary keys and foreign keys with a live MySQL Workbench demo on the Maven Fuzzy Factory Mini dataset, linking orders, order items, refunds, and website sessions.

Explore table relationships in the online learning school schema by identifying primary keys and foreign keys across three tables, and explain their relationships and types.

Demonstrates how the courses, course ratings, and course rating summaries tables relate, identifies primary and foreign keys, and explains the one-to-many relationship from courses to ratings.

Learn how database normalization structures tables to minimize redundancy and preserve data integrity. Split data into related tables linked by IDs to film and address tables, enabling efficient scaling to millions of records.

In this Maven Fuzzy Factory demo, normalize a non-normalized website page views table into two related tables with primary and foreign keys to remove redundancy.

Assess and improve Emily's online school's database by identifying normalization gaps, explaining changes, and optionally executing normalization by creating or altering tables to reduce duplication.

Discover how to normalize a MySQL database by removing redundant course name and instructor fields from the course_ratings table, using course_id to join to courses, and creating efficient summaries.

Learn to design enhanced entity-relationship (EER) diagrams in MySQL, mapping tables, primary keys, foreign keys, data types, and constraints to visualize a one to many relationship.

Map the online learning school schema with an EER diagram using the EER diagram editor in MySQL Workbench, visualizing table relationships to help onboarding and scaling.

Demonstrates building an EER diagram for the online learning school schema in MySQL Workbench, creating courses, course_ratings, and course_rating_summaries with primary and foreign keys.

Apply sql skills to restructure the Maven movie's non-normalized dvd rental database, design a new scalable schema, and populate it, explaining why the current system is not optimized for scale.

demonstrates normalizing the Maven movies database by splitting inventory into inventory, film, and store tables, adding primary and foreign keys, and populating from the legacy data.

learn how indexes speed up select queries by indexing columns used in where conditions and joins, with a practical walkthrough of creating an index in workbench on employee ID.

Learn to enforce unique values with a MySQL unique constraint using workbench or SQL code, preventing duplicate item names and preserving data integrity in your inventory.

Apply the non null constraint to ensure every record has a value, enforcing data integrity, as shown with the inventory table's number in stock and preventing inserts without that value.

Implement and optimize keys, indexes, and constraints in the Sloppy Joe's restaurant database to protect data quality and support scalable growth across customers, orders, menu items, and staff.

Apply primary keys, foreign keys, not null constraints, and indexes in the Sloppy Joe's restaurant database to ensure data integrity and scalable performance in MySQL workbench.

Harness stored procedures on the MySQL server to share and reuse complex queries. The lesson uses a simple inventory example to create, call, and drop a procedure.

Design and implement a simple stored procedure, SP_staff_orders_served, that returns each staff member's all-time total orders using a count and group by, enabling a one-line query for managers.

Embed a query in a stored procedure using the Sloppy Joe's schema in MySQL Workbench, applying delimiter changes to return staff IDs and total orders served.

Learn how to create MySQL triggers that keep related tables in sync by updating inventory after an insert on the customer purchases table, using for each row and inventory_id.

Create a trigger in the MySQL database to auto-update the staff orders served total when a customer order is inserted, updating employee 1 to 11 and employee 2 to 13.

Create a trigger after insert on the customer orders table to update the staff table's orders served count for the corresponding staff member, incrementing only the matching staff ID.

Monitor the MySQL server status in MySQL Workbench's administration tab and use the startup and shutdown tool to start or stop the database instance.

Check the server status, restart if needed, and verify authentication details for a simulated Joe's Restaurant database to practice practical server management in MySQL.

Watch this solution video demonstrating how to check server status in MySQL Workbench, stop and restart the server, enter administrator and root passwords, and verify schema access.

Learn to manage MySQL users and privileges in Workbench, creating read-only and DBA roles, customizing global privileges, setting account limits, and granting access to specific schemas.

Create two database users with privileges: a DBA with full privileges and an analyst with select-only access, each with passwords and a reminder to change them, restricting access to what’s needed.

Add two users in MySQL Workbench: data guru with DBA privileges and analyst with select privileges, set their passwords to change me, and apply privileges.

Design a MySQL database from scratch to capture customers, purchases, products, and employees, then tackle six questions with timestamps and review the solution video to solidify MySQL database administration skills.

Explore a practical final course project solution in MySQL database design, building customers, employees, products, and purchases tables with keys, constraints, and schema setup, plus sample data and user privileges.