MySQL Workbench

What is MySQL?

MySQL is an open source relational database.
MySQL is cross platform which means it runs on a number of different platforms such as Windows, Linux, and Mac OS etc.
In this tutorial, you will learn-

• What is MySQL?
• Why use MySQL?
• Introducing MySQL Workbench
• MySQL workbench- Modeling and Design tool
• MySQL workbench - SQL development tool
• MySQL workbench - Administration tool
• Install MySQL workbench Guide

Why use MySQL?

There are a number of relational database management systems on the market.
Examples of relational databases include Microsoft SQL Server, Microsoft Access, Oracle, DB2 etc.
One may ask why we would choose MySQL over the other database management systems.
The answer to this question depends on a number of factors. 
Let's look at the strengths of MySQL compared to over relational databases such as SQL Server-

• MySQL supports multiple storage engines each with its own specifications while other systems like SQL server only support a single storage engine. In order to appreciate this statement, let's look at two of the storage engines supported by MySQL.
• InnoDB: - its default storage engine provided with MySQL as of version 5.5. InnoDB supports foreign keys for referential integrity and also supports ACID-standard transactions.
• MyISAM: - it was the default storage engine for MySQL prior to version 5.5. MyISAM lacks support for transactions. Its advantages over InnoDB include simplicity and high performance.
• MySQL has high performance compared to other relation database systems. This is due to its simplicity in design and support for multiple-storage engines.
• Cost effective, it's relatively cheaper in terms of cost when compared to other relational databases. In fact, the community edition is free. The commercial edition has a licensing fee which is also cost effective compared to licensing fees for products such as Microsoft SQL Server.
• Cross platform - MySQL works on many platforms which means it can be deployed on most machines. Other systems such as MS SQL Server only run on the windows platform.

In order to interact with MySQL, you will need a server access tool that can communicate with MySQL server. MySQL supports multiple user connections.

 

Introducing MySQL Workbench

MySQLWorkbench is a Visual database designing and modeling access tool for MySQL server relational database. It facilitates creation of new physical data models and modification of existing MySQL databases with reverse/forward engineering and change management functions.

Getting Started MySQL workbench- Modeling and Design tool

• Models are at the core of most valid and high performance databases. MySQLworkbench has tools that allow developers and database administrators visually create physical database design models that can be easily translated into MySQL databases using forward engineering.
• MySQL workbench supports creation of multiple models in the same environment.
• It supports all objects such as tables, views, stored procedures, triggers,  etc. that make up a database.
• MySQL workbench has a built in model validating utility that reports any issues that might be found to the data modeler.
• It also allows for different modeling notations and can be extended by using LUA a scripting language.

The figure shown below shows the modeling window for MySQLWorkbench.

MySQL workbench - SQL development tool

Structured Query Language (SQL) allows us to manipulate our relational databases. SQL is at the heart of all relational databases.

• MySQLworkbench, has built in SQL visual editor.
• The Visual SQL editor allows developers to build, edit and run queries against MySQL server databases. It has utilities for viewing data and exporting it.
• Its syntax color highlighters help developers easily write and debug SQL statements.
• Multiple queries can be run and results automatically displayed in different tabs.
• The queries are also saved in the history panel for later retrieval and running.

The figure shown below shows the SQL development window for MySQL Workbench.

MySQL workbench - Administration tool

Server administration plays a critical role in securing the data of the company. The major issues concerning server administration are users' management, server configuration, server logs and many more. Workbench MySQL has the following features that simplify the process of MySQL server administration;

• User administration - visual utility for managing users that lets database administrators easily add new and remove existing users if need arises, grant and drop privileges and view user profiles.
• Server configuration - allows for advanced configuration of the server and fine tuning for optimal performance.
• Database backup and restorations - visual tool for exporting/importing MySQL dump files. MySQL dump files contain SQL scripts for creating databases, tables, views, stored procedures and insertion of data.
• Server logs - visual tool for viewing MySQL server logs. The logs include error logs, binary logs and InnodDB logs. These logs come in handy when performing diagnosis on the server. The figure shown below shows the modeling window for MySQL Workbench.

The figure shown below shows  the Admin panel for Workbench MySQL .

Install MySQL workbench Guide(for Windows)

This is a 2 step process
1)      Install MySQL Community Server
2)      Install MySQL workbench. - You can install the workbench using a zip file or an msi installer (recommended)
Note: You will require Administrator or Power User Privileges to perform installation.
Getting Started
Once you have finished installing above you need to set up MySQL Workbench as shown below -

1.    First step is launching the Workbench MySQL . What you see is called Home Window

 

2. Next you need to create your MySQL Server Connection which contains details about target database server including how to connect to it. Click " +  " in MySQL Workbench Home Window. This will open Setup New Connection . Wizard

3. As a beginner you can create a connection for a locally installed server. Click Configure Server Management button in  Setup New Connection window to check the cofiguration of the MySQL server.

4. A new window opens named Configure Local Management . Click Next button to continue.

5. Next the Wizard will test connections to database. If test fails, go back and correct database connection parameters.5. Next it will open a pop up window asking your root password to test your connection with the local mysql server instance. The password is the one you set during installation of MySQL Workbench. Enter your password and press OK

6. Next the Wizard will test connections to database. If test fails, go back and correct database connection parameters. Else if all tests are sucessful click Next to continue.

7. After that a new wizard will open about Local Service Management - It lets you switch between multiple mysql severs installed on one machines. As a beginner you can bypass this and click Next  to continue.

8. The Wizard will then check ability to access MySQL Server Configuration File, and test start/stop commands.

9. Next you can review current configurations. After reviewing the configurations, Click Finish to finsh server cofiguration

10. Next Step is to setup a connection, which can be used to connect to server. If you have not created a connection already, you can use the default values given. Click on Test Connection [ 2 ] after entering the Connection Name [ 1 ].

11. A new dialog box will open asking you password to root/selected user. If your MySQL root user has a password, you can enter that using Store in Vault feature. Click OK.

If the entered password for the user is correct then the following screen will show. Click on both OK buttons and you will be good to go.

A new instance is shown in the homepage

Double click and start querying.

Summary

• MySQL is an open source relational database that is cross platform.
• MySQL supports multiple storage engines which greatly improve the server performance tuning and flexibility. Prior to version 5.5, the default storage engine was MyISAM which lacked support for transactions, as of version 5.5; the default storage engine is InnoDB which supports transactions and foreign keys.
• MySQL server can be administered using a number of server access mysql tools which include both commercial and open source products. Popular examples include;
• phpMyAdmin - cross platform web based open source server access tool
• SQLYog - targeted at the windows platform, desktop commercial server access tool
• MySQL workbench - cross platform open source server access tool.
• MySQL workbench is an integrated development environment for MySQL server. It has utilities for database modeling and designing, SQL development and server administration. 

Database Design Tutorial: Learn Data Modeling

What is Database Design?

Database Design is a collection of processes that facilitate the designing, development, implementation and maintenance of enterprise data management systems

It helps produce  database systems

1. That meet the requirements of the users
2. Have high performance.

The main objectives of database designing are to produce logical and physical designs models of the proposed database system.
 The logical model concentrates on the data requirements and the data to be stored independent of physical considerations. It does not concern itself with how the data will be stored or where it will be stored physically.
 The physical data design model involves translating the logical design of the database onto physical media using hardware resources and software systems such as database management systems (DBMS).
In this tutorial, you will learn-

• What is Database Design?
• Why Database Design is Important ?
• Database development life cycle
• Requirements analysis
• Database designing
• Implementation
• Types of Database Techniques

Why Database Design is Important ?

Database designing is crucial to high performance database system.
  Apart from improving the performance, properly designed database are easy to maintain, improve data consistency and are cost effective in terms of disk storage space.
Note , the genius of a database is in its design . Data operations using SQL is relatively simple

 

Database development life cycle

 
The database development life cycle has a number of stages that are followed when developing database systems.
The steps in the development life cycle do not necessary have to be followed religiously in a sequential manner.
On small database systems, the database system development life cycle is usually very simple and does not involve a lot of steps.
In order to fully appreciate the above diagram, let's look at the individual components listed in each step.

Requirements analysis

• Planning - This stages concerns with planning of entire Database Development Life Cycle  It  takes into consideration the Information Systems strategy of the organization.
• System definition - This stage defines the scope and boundaries of the proposed database system.

Database designing

• Logical model - This stage is concerned with developing a database model based on requirements. The entire design is on paper without any physical implementations or specific DBMS considerations.
• Physical model - This stage implements the logical model of the database taking into account the DBMS and physical implementation factors.

Implementation

• Data conversion and loading - this stage is concerned with importing and converting data from the old system into the new database.
• Testing - this stage is concerned with the identification of errors  in the newly implemented system .It checks the database against requirement specifications.

Two Types of Database Techniques

1.     Normalization
2.     ER Modeling

Let's study them one by one 

What is Normalization? 1NF, 2NF, 3NF & BCNF with Examples

What is Normalization?

Normalization is a database design technique which organizes tables in a manner that reduces redundancy and dependency of data.
It divides larger tables to smaller tables and links them using relationships.
In this tutorial, you will learn-

• Database Normal Forms
• 1NF Rules
• What is a KEY?
• What is Composite Key
• 2NF Rules
• Database - Foreign Key
• What are transitive functional dependencies?
• 3NF Rules
• Boyce-Codd Normal Form (BCNF)

The inventor of the relational model Edgar Codd proposed the theory of normalization with the introduction of First Normal Form, and he continued to extend theory with Second and Third Normal Form. Later he joined with Raymond F. Boyce to develop the theory of Boyce-Codd Normal Form. 
Theory of Data Normalization in SQL is still being developed further. For example, there are discussions even on 6^th Normal Form. However, in most practical applications, normalization achieves its best in 3^rd Normal Form. The evolution of Normalization theories is illustrated below-

Database Normalization Examples -

Assume a video library maintains a database of movies rented out. Without any normalization, all information is stored in one table as shown below.

Table 1

Here you see Movies Rented column has multiple values.

Database Normal Forms

Now let's move into 1^st Normal Forms

1NF (First Normal Form) Rules

• Each table cell should contain a single value.
• Each record needs to be unique.

The above table in 1NF-

1NF Example

Table 1: In 1NF Form

Before we proceed let's understand a few things --

What is a KEY?

A KEY is a value used to identify a record in a table uniquely. A KEY could be a single column or combination of multiple columns
Note: Columns in a table that are NOT used to identify a record uniquely are called non-key columns.
What is a Primary Key?

A primary is a single column value used to identify a database record uniquely. It has following attributes A primary key cannot be NULL A primary key value must be unique The primary key values cannot be changed The primary key must be given a value when a new record is inserted.

What is Composite Key?

A composite key is a primary key composed of multiple columns used to identify a record uniquely
In our database, we have two people with the same name Robert Phil, but they live in different places.

Hence, we require both Full Name and Address to identify a record uniquely. That is a composite key.
Let's move into second normal form 2NF

2NF (Second Normal Form) Rules

• Rule 1- Be in 1NF
• Rule 2- Single Column Primary Key

It is clear that we can't move forward to make our simple database in 2^nd Normalization form unless we partition the table above.

Table 1

Table 2

We have divided our 1NF table into two tables viz. Table 1 and Table2. Table 1 contains member information. Table 2 contains information on movies rented.
We have introduced a new column called Membership_id which is the primary key for table 1. Records can be uniquely identified in Table 1 using membership id

Database - Foreign Key

In Table 2, Membership_ID is the Foreign Key

Foreign Key references the primary key of another Table! It helps connect your Tables A foreign key can have a different name from its primary key It ensures rows in one table have corresponding rows in another Unlike the Primary key, they do not have to be unique. Most often they aren't Foreign keys can be null even though primary keys can not

 

Why do you need a foreign key?
Suppose an idiot inserts a record in Table B such as
You will only be able to insert values into your foreign key that exist in the unique key in the parent table. This helps in referential integrity. 

The above problem can be overcome by declaring membership id  from Table2  as foreign key of membership id from Table1
Now, if somebody tries to insert a value in the membership id field that does not exist in the parent table, an error will be shown!

What are transitive functional dependencies?

A transitive functional dependency is when changing a non-key column, might cause any of the other non-key columns to change
Consider the table 1. Changing the non-key column Full Name may change Salutation.

Let's move into 3NF

3NF (Third Normal Form) Rules

• Rule 1- Be in 2NF
• Rule 2- Has no transitive functional dependencies

To move our 2NF table into 3NF, we again need to again divide our table.

3NF Example

TABLE 1

Table 2

Table 3

We have again divided our tables and created a new table which stores Salutations. 
There are no transitive functional dependencies, and hence our table is in 3NF
In Table 3 Salutation ID is primary key, and in Table 1 Salutation ID is foreign to primary key in Table 3

Now our little example is at a level that cannot further be decomposed to attain higher forms of normalization. In fact, it is already in higher normalization forms. Separate efforts for moving into next levels of normalizing data are normally needed in complex databases.  However, we will be discussing next levels of normalizations in brief in the following.

Boyce-Codd Normal Form (BCNF)

Even when a database is in 3^rd Normal Form, still there would be anomalies resulted if it has more than one Candidate Key.
Sometimes is BCNF is also referred as 3.5 Normal Form.

4NF (Fourth Normal Form) Rules

If no database table instance contains two or more, independent and multivalued data describing the relevant entity, then it is in 4^th Normal Form.

5NF (Fifth Normal Form) Rules

A table is in 5^th Normal Form only if it is in 4NF and it cannot be decomposed into any number of smaller tables without loss of data.

6NF (Sixth Normal Form) Proposed

6^th Normal Form is not standardized, yet however, it is being discussed by database experts for some time. Hopefully, we would have a clear & standardized definition for 6^th Normal Form in the near future...

That's all to Normalization!!!

Summary

• Database designing is critical to the successful implementation of a database management system that meets the data requirements of an enterprise system.
• Normalization helps produce database systems that are cost-effective and have better security models.
• Functional dependencies are a very important component of the normalize data process
• Most database systems are normalized database up to the third normal forms.
• A primary uniquely identifies are record in a Table and cannot be null
• A foreign key helps connect table and references a primary key

What is ER Modeling? Learn with Example

What is ER Modeling?

Entity Relationship Modeling (ER Modeling) is a graphical approach to database design. It uses Entity/Relationship to represent real world objects.
An Entity is a thing or object in real world that is distinguishable from surrounding environment. For example each employee of an organization is a separate entity. Following are some of major characteristics of entities.

• An entity has a set of properties.
• Entity properties can  have values. 

In this tutorial, you will learn-

• What is ER Modeling?
• Enhanced Entity Relationship (EER) Model
• Why use ER Model?
• Entities in the "MyFlix" library
• Defining the relationships among entities

Let's consider our first example again. An employee of an organization is an entity. If "Peter" is a programmer (an employee) at Microsoft, he can have attributes (properties) like name, age, weight, height, etc. It is obvious that those do hold values relevant to him.

Each attribute can have Values. In most cases single attribute have one value. But it is possible for attributes have multiple values also.  For example Peter's age has a single value. But his "phone numbers" property can have multiple values.
Entities can have relationships with each other. Let's consider a simplest example. Assume that each Microsoft Programmer is given a Computer. It is clear that that Peter's Computer is also an entity. Peter is using that computer and the same computer is used by Peter. In other words there is a mutual relationship among Peter and his computer.
In Entity Relationship Modeling, we model entities, their attributes and relationships among entities.

 

Enhanced Entity Relationship (EER) Model

Enhanced Entity Relationship (EER) Model is a high level data model which provides extensions to original Entity Relationship (ER) model. EER Models supports more details design. EER Modeling emerged as a solution for modeling highly complex databases.
EER uses UML notation. UML is the acronym for Unified Modeling Language; it is a general purpose modeling language used when designing object oriented systems. Entities are represented as class diagrams. Relationships are represented as associations between entities. The diagram shown below illustrates an ER diagram using the UML notation.
 

 

Why use ER Model?

Now you may think why use ER modeling when we can simply create the database and all of its objects without ER modeling? One of the challenges faced when designing database is the fact that designers, developers and end-users tend to view data and its usage differently. If this situation is left unchecked, we can end up producing a database system that does not meet the requirements of the users.
Communication tools understood by all stakeholders(technical as well non-technical users) are critical in producing database systems that meet the requirements of the users. ER models are examples of such tools.
ER diagrams also increase user productivity as they can be easily translated into relational tables.
Case Study: ER diagram for "MyFlix" Video Library
Let's now work with the MyFlix Video Library database system to help understand the concept of ER diagrams. We will using this database for all hand-on in the remainder of this tutorials
MyFlix is a business entity that rents out movies to its members. MyFlix has been storing its records manually.  The management now wants to move to a DBMS
Let's look at the steps to develop EER diagram for this database-

1.  Identify the entities and determine the relationships that exist among them. 
2.  Each entity, attribute and relationship, should have appropriate names that can be easily understood by the non-technical people as well. 
3. Relationships should not be connected directly to eachother. Relationships should connect entities.   
4. Each attribute in a given entity should have a unique name.

Entities in the "MyFlix" library

The entities to be included in our ER diagram are;

• Members - this entity will hold member information.
• Movies - this entity will hold information regarding movies
• Categories - this entity will hold information that places movies into different categories such as "Drama", "Action", and "Epic" etc.
• Movie Rentals - this entity will hold information that about movies rented out to members.
• Payments - this entity will hold information about the payments made by members.

Defining the relationships among entities

Members and movies
The following holds true regarding the interactions between the two entities.

• A member can rent a more than movie in a given period.
• A movie can be rented by more than one member in a given period.

From the above scenario, we can see that the nature of the relationship is many-to-many. Relational databases do not support many-to-many relationships. We need to introduce a junction entity. This is the role that the MovieRentals entity plays. It has a one-to-many relationship with the members table and another one-to-many relationship with movies table.
Movies and categories entities
The following holds true about movies and categories.

• A movie can only belong to one category but a category can have more than one movie.

We can deduce from this that the nature of the relation between categories and movies table is one-to-many.
Members and payments entities
The following holds true about members and payments

• A member can only have one account but can make a number of payments.

We can deduce from this that the nature of the relationship between members and payments entities is one-to-many.
Now lets create EER model using MySQL Workbench
In the MySQL workbench , Click - "+" Button

 
Double click on Add Diagram button to open the workspace for ER diagrams.

Following window appears

Let's look at the two objects that we will work with.

• The table object allows us to create entities and define the attributes associated with the particular entity.
• The place relationship button allows us to define relationships between entities.

The members' entity will have the following attributes

• Membership number
• Full names
• Gender
• Date of birth
• Physical address
• Postal address

Let's now create the members table
1.Drag the table object from the tools panel
2.Drop it in the workspace area. An entity named table 1 appears
3.Double click on it. The properties window shown below appears

 
Next ,

1. Change table 1 to Members
2. Edit the default idtable1 to membership_number
3. Click on the next line to add the next field
4. Do the same for all the attributes identified in members' entity.

Your properties window should now look like this.

Repeat the above steps for all the identified entities.

Your diagram workspace should now look like the one shown below.

Lets create relationship between Members and Movie Rentals

1. Select the place relationship using existing columns too
2. Click on membership_number in the Members table
3. Click on reference_number in the MovieRentals table

 
Repeat above steps for other relationships. Your ER diagram should now look like this -

Summary

• ER Diagrams play a very important role in the database designing process. They serve as a non-technical communication tool for technical and non-technical people.
• Entities represent real world things; they can be conceptual as a sales order or physical such as a customer.
• All entities must be given unique names.
• ER models also allow the database designers to identify and define the relations that exist among entities.

The entire ER Model is attached below. You can simple import it in MySQL Workbench.





Download ER Model

MySQL Create Database, Tables, Data Types

 Steps for Create Database Mysql

Create Database in two ways
1) By executing a simple SQL query
2) By using forward engineering in MySQL Workbench
In this tutorial, you will learn-

• Create Database
• Creating Tables MySQL
• Data types
• MySQL workbench ER diagram forward Engineering

As SQL beginner , let's look into the query method first.

Create Database

CREATE DATABASE is the SQL command for creating a database. 
Imagine you need to create a database with name "movies". You can do it by executing following SQL command.

CREATE DATABASE movies; 

Note: you can also use the command CREATE SCHEMA instead of CREATE DATABASE
Now let's improve our SQL query adding more parameters and specifications.

IF NOT EXISTS

A single MySQL server could have multiple databases. If you are not the only one accessing the same MySQL server or if you have to deal with multiple databases there is a probability of attempting to create a new database with name of an existing database .  IF NOT EXISTS let you to instruct MySQL server to check the existence of a database with a similar name prior to creating database.
When IF NOT EXISTS is used database is created only if given name does not conflict with an existing database's name. Without the use of IF NOT EXISTS MySQL throws an error.

CREATE DATABASEIF NOT EXISTS movies;

Collation and Character Set

Collation is set of rules used in comparison. Many people use MySQL to store data other than English. Data is stored in MySQL using a specific character set. The character set can be defined at different levels viz, server , database , table and columns.
You need to select the rules of collation which in turn depend on the character set chosen.
For instance, the Latine1 character set uses the latin1_swedish_ci collation which is the Swedish case insensitive order.

CREATE DATABASE IF NOT EXISTS movies CHARACTER SET latin1 COLLATE latin1_swedish_ci

The best practice while using local languages like Arabic , Chinese etc is to select Unicode (utf-8) character set which has several collations or just stick to default collation utf8-general-ci.
You can find the list of all collations and character sets here here
You can see list of existing databases by running following SQL command.

SHOW DATABASES

Creating Tables MySQL

Tables can be created using CREATE TABLE statement and it actually has the following syntax.

CREATE  TABLE [IF NOT EXISTS] `TableName` (`fieldname` dataType [optional parameters]) ENGINE = storage Engine; 

HERE

• "CREATE TABLE" is the one responsible for the creation of the table in the database.
• "[IF NOT EXISTS]" is optional and only create the table if no matching table name is found.
• "`fieldName`" is the name of the field and "data Type" defines the nature of the data to be stored in the field.
• "[optional parameters]" additional information about a field such as "  AUTO_INCREMENT" , NOT NULL etc
  
  

  Create Table Example:-

  
  

  CREATE  TABLE IF NOT EXISTS `MyFlixDB`.`Members` (
    `membership_number` INT  AUTOINCREMENT ,
    `full_names` VARCHAR(150) NOT NULL ,
    `gender` VARCHAR(6) ,
    `date_of_birth` DATE ,
    `physical_address` VARCHAR(255) ,
    `postal_address` VARCHAR(255) ,
    `contact_number` VARCHAR(75) ,
    `email` VARCHAR(255) ,
    PRIMARY KEY (`membership_number`) )
  ENGINE = InnoDB;

Now let's see what the MySQL's data types are. You can use any of them depending on your need. You should always try to not to underestimate or overestimate potential range of data when creating a database.

DATA TYPES

Data types define the nature of the data that can be stored in a particular column of a table
MySQL has 3 main categories of data types namely

1.   Numeric,
2.   Text
3.   Date/time.

Numeric Data types

Numeric data types are used to store numeric values. It is very important to make sure range of your data is between lower and upper boundaries of numeric data types.

TINYINT( )	-128 to 127 normal 0 to 255 UNSIGNED.
SMALLINT( )	-32768 to 32767 normal 0 to 65535 UNSIGNED.
MEDIUMINT( )	-8388608 to 8388607 normal 0 to 16777215 UNSIGNED.
INT( )	-2147483648 to 2147483647 normal 0 to 4294967295 UNSIGNED.
BIGINT( )	-9223372036854775808 to 9223372036854775807 normal 0 to 18446744073709551615 UNSIGNED.
FLOAT	A small approximate number with a floating decimal point.
DOUBLE( , )	A large number with a floating decimal point.
DECIMAL( , )	A DOUBLE stored as a string , allowing for a fixed decimal point. Choice for storing currency values.

Text Data Types

As data type category name implies these are used to store text values. Always make sure you length of your textual data do not exceed maximum lengths.

CHAR( )	A fixed section from 0 to 255 characters long.
VARCHAR( )	A variable section from 0 to 255 characters long.
TINYTEXT	A string with a maximum length of 255 characters.
TEXT	A string with a maximum length of 65535 characters.
BLOB	A string with a maximum length of 65535 characters.
MEDIUMTEXT	A string with a maximum length of 16777215 characters.
MEDIUMBLOB	A string with a maximum length of 16777215 characters.
LONGTEXT	A string with a maximum length of 4294967295 characters.
LONGBLOB	A string with a maximum length of 4294967295 characters.

 
Date / Time

DATE	YYYY-MM-DD
DATETIME	YYYY-MM-DD HH:MM:SS
TIMESTAMP	YYYYMMDDHHMMSS
TIME	HH:MM:SS

 
Apart from above there are some other data types in MySQL.

ENUM	To store text value chosen from a list of predefined text values
SET	This is also used for storing text values chosen from a list of predefined text values. It can have multiple values.
BOOL	Synonym for TINYINT(1), used to store Boolean values
BINARY	Similar to CHAR, difference is texts are stored in binary format.
VARBINARY	Similar to VARCHAR, difference is texts are stored in binary format.

 
Now let's see a sample SQL query for creating a table which has data of all data types. Study it and identify how each data type is defined.

CREATE TABLE`all_data_types` (
    `varchar` VARCHAR( 20 )  ,
    `tinyint` TINYINT  ,
    `text` TEXT  ,
    `date` DATE  ,
    `smallint` SMALLINT  ,
    `mediumint` MEDIUMINT  ,
    `int` INT  ,
    `bigint` BIGINT  ,
    `float` FLOAT( 10, 2 )  ,
    `double` DOUBLE  ,
    `decimal` DECIMAL( 10, 2 )  ,
    `datetime` DATETIME  ,
    `timestamp` TIMESTAMP  ,
    `time` TIME  ,
    `year` YEAR  ,
    `char` CHAR( 10 )  ,
    `tinyblob` TINYBLOB  ,
    `tinytext` TINYTEXT  ,
    `blob` BLOB  ,
    `mediumblob` MEDIUMBLOB  ,
    `mediumtext` MEDIUMTEXT  ,
    `longblob` LONGBLOB  ,
    `longtext` LONGTEXT  ,
    `enum` ENUM( '1', '2', '3' )  ,
    `set` SET( '1', '2', '3' )  ,
    `bool` BOOL  ,
    `binary` BINARY( 20 )  ,
    `varbinary` VARBINARY( 20 )
) ENGINE= MYISAM ;

 

Best practices

• Use upper case letters for SQL keywords i.e. "DROP SCHEMA IF EXISTS `MyFlixDB`;"
• End all your SQL commands using semi colons.
• Avoid using spaces in schema, table and field names. Use underscores instead to separate schema, table or field names.

MySQL workbench ER diagram forward engineering

MySQL workbench has utilities that support forward engineering. Forward engineering is a technical term is to describe the process of translating a logical model into a physical implement automatically.
We created an ER diagram on our ER modeling tutorial. We will now use that ER model to generate the SQL scripts that will create our database.
Creating the MyFlix database from the MyFlix ER model
1.       Open the ER model of MyFlix database that you created in earlier tutorial.
2.       Click on the database menu. Select forward engineer

 
3.       The next window,  allows you to connect to an instance of MySQL server. Click on the stored connection drop down list and select local host. Click Execute

 
4.       Select the options shown below in the wizard that appears. Click next

 

5.       The next screen shows the summary of objects in our EER diagram. Our MyFlix DB has 5 tables.  Keep the selections default and click Next.

6..       The window shown below appears. This window allows you to preview the SQL script to create our database. We can save the scripts to a *.sql" file or copy the scripts to the clipboard. Click on next button

 

 
7. The window shown below appears after successfully creating the database on the selected MySQL server instance.

Summary

• Creating a database involves translating the logical database design model into the physical database.
• MySQL supports a number of data types for numeric, dates and strings values.
• CREATE DATABSE command is used to create a database
• CREATE TABLE command is used to create tables in a database
• MySQL workbench supports forward engineering which involves automatically generating SQL scripts from the logical database model that can be executed to create the physical database

The Database along with Dummy Data is attached. We will be using this DB for all our further tutorials. Simple import the DB in MySQL Workbench to get started.


Download MyFlixDb