Database Design: MYSQL CLOUD SERVICE 2018 [1Z0-320]

Continuation of my previous Blog Post for MYSQL CLOUD SERVICE 2018 [1Z0-320] exam preparation, In this blog will discuss Database Design part for MySQL. Rather than rewriting given topics I have provided already exists good reference links for few topics.

Database Design

  • Describe Datatypes in a MySQL database
  • Create databases and tables
  • Create basic SQL queries
  • Maintain Databases, Tables, and Columns
  • Configure Indexes and Constraints
  • Join Tables
  • Partition MySQL Tables

Describe Datatypes in a MySQL database

Reference Link:

Link1: MySQL Data Types

Link2: MySQL Data Types

Create databases and tables

Create database:

CREATE DATABASE IF NOT EXISTS project1  CHARACTER SET utf8 COLLATE utf8_general_ci;

[On Filesystem level, this will create project1 directory inside mysql datadir with db.opt file in it.]

USE project1;

SHOW DATABASES 

CREATE TABLE IF NOT EXISTS dept ( deptid INT PRIMARY KEY, dname CHAR(50), location text ) ;

IF NOT EXISTS (optional): Prevents an error from occurring if the table exists with the same name. It will not compare table structure.

Create a table with INDEX and Foreign key:

CREATE TABLE emp (empid INT AUTO_INCREMENT PRIMARY KEY, fname CHAR(50), deptid INT , doj DATETIME , FOREIGN KEY fk_cat(deptid) REFERENCES dept(deptid)
ON UPDATE CASCADE
ON DELETE RESTRICT
) ENGINE=InnoDB;

Create TEMPORARY TABLE: 

Available only within the current session and removed automatically after a session end.

CREATE TEMPORARY TABLE tmptbl (id int, c2 varchar(40));

Cloning or Copying a Table:

CREATE TABLE emp1 LIKE emp;

CREATE TABLE dept1 AS select * from dept;

Reference Links:

CREATE DATABASE

CREATE TABLE

MySQL Basic Operations:

  • Create basic SQL queries
  • Configure Indexes and Constraints
  • Join Tables
  • Subqueries
Types Query Commands / Details
READ DATA SELECT [ DISTINCT ,COUNT, SUM ,MAX, MIN, AVG, AS]
DATA FILTERS WHERE [ AND,OR, IN,BETWEEN,LIKE,LIMIT,IS NULL
SORT DATA ORDER BY
GROUP DATA GROUP BY, HAVING
SUBQUERY [Inner query, outer query]
MODIFY DATA INSERT [IGNORE]

UPDATE

DELETE [ ON DELETE CASCADE]

REPLACE

PREPARE Statements

JOINS CROSS, INNER, LEFT, RIGHT, self-join
INDEX PRIMARY KEY

UNIQUE, INDEX

FULLTEXT

All above are stored in B-trees.

Indexes on spatial data types use R-trees.

MEMORY tables also support hash indexes.

InnoDB uses inverted lists for FULLTEXT indexes.

CONSTRAINTS NOT NULL

PRIMARY KEY

FOREIGN KEY

UNIQUE

CHECK

Reference Links :

MySQL Basic Operations [ SQL Queries, Indexes and Constraints, Join, Subquery ]

MySQL INDEX

MySQL JOINS

MySQL Subquery 

Maintain Databases, Tables, and Columns

General Options for DB Objects Management:

Operation Commands and details
Database CREATE DATABASE

SHOW DATABASES

USE  [db_name]

DROP DATABASE

Table CREATE TABLE

CREATE TEMPORARY TABLE

ALTER TABLE

RENAME TABLE

DROP TABLE

TRUNCATE TABLE

Column ALTER TABLE ADD COLUMN

ALTER TABLE MODIFY COLUMN

ALTER TABLE DROP COLUMN

Metadata Information: [Information_schema]
Information_schema database will keep metadata information about all DB objects.

Databases:

SHOW DATABASES;

Select Database:

USE [db_name];

Tables in Database:

SHOW TABLES;

SELECT TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA = 'db_name';

ROUTINES:

select * from ROUTINES where ROUTINE_SCHEMA='db_name';

INDEX:

SELECT TABLE_NAME,INDEX_NAME,COLUMN_NAME,INDEX_TYPE  FROM INFORMATION_SCHEMA.STATISTICS where TABLE_SCHEMA = '<database name>';

View:

select * from information_schema.VIEWS where TABLE_SCHEMA = 'db_name';

TABLE Maintenance:

Analyze table: Update key-index stats for a table. 

Optimize table: Avoid defragmenting problem.

Check table: Check the integrity of database tables.

Repair table: Repair table structure. 

Reference links:

Maintaining MySQL Database Tables

Table Maintenance Statements

 

Will write about MySQL Partitioning in next blog post.

Advertisements

Exam Preparation part 1: MySQL Cloud Service 2018 [1Z0-320]

Recently, Oracle added new certification for MySQL,

MySQL Cloud Service 2018 Implementation Essentials

This exam is all about MySQL Enterprise Edition with MySQL Cloud Service. Best study resource for this exam is “MySQL official documentation”. Along with MySQL Enterprise topics given in exam topics, this exam also includes one major part of MySQL Cloud Service.

Exam Topics: 

Will write more about these exam topics in our next blog post.

Feel free to share your ideas and preparation tips for this exam.


I started reading these topics and following are my notes for few of them,

Oracle MySQL Enterprise Product Suite

  • Describe the difference between MySQL Enterprise Edition and the Community/Standard Edition

https://www.mysql.com/products/

  • Explain the products and features of MySQL Enterprise Products

https://www.mysql.com/products/enterprise/

  • Identity the Oracle products that are certified to work with MySQL Enterprise Edition

MySQL Enterprise Edition makes managing MySQL easier in these environments by certifying and supporting the use of the MySQL Database in conjunction with many Oracle products. These include:

  • Oracle Linux
  • Oracle VM
  • Oracle Fusion Middleware
  • Oracle Secure Backup
  • Oracle Golden Gate
  • Oracle Audit Vault and Database Firewall
  • Oracle Enterprise Manager
  • Oracle OpenStack for Oracle Linux
  • Oracle Clusterware

Installation and Architecture

  • Explain the MySQL infrastructure architecture

https://lalitvc.wordpress.com/2016/11/03/mysql-architecture-and-components/

  • Install a MySQL Enterprise Server

https://dev.mysql.com/doc/refman/5.6/en/installing.html

  • Configure settings for a MySQL Enterprise Server

https://dev.mysql.com/doc/refman/5.6/en/server-configuration.html

  • Access MySQL databases with MySQL Workbench
  • Manage MySQL Database users with MySQL Workbench

https://dev.mysql.com/doc/workbench/en/

 

Good luck with your studies 🙂