A Technical Comparison: Microsoft Access 2016 vs SQL Server 2016

Posted on by Ian

Microsoft Access and SQL Server are two relational database management systems from Microsoft. Each has its own strengths and weaknesses. There are many cases where Access is the ideal tool for the job. There are other times where a more sophisticated solution like SQL Server is more appropriate.

When trying to decide which one to use, a side-by-side comparison of the technical specifications of each system can help greatly. Below is a side-by-side comparison of some of the technical limitations of each system.

Continue reading →

What is MySQL?

Posted on by Ian

MySQL is an open source relational database management system (RDBMS). It is the world’s most popular open source RDBMS and is currently ranked as the second most popular RDBMS in the world (behind Oracle Database).

MySQL is available as a free download, however, several paid editions are also available which offer additional functionality.

As the name suggests, MySQL is based on SQL. The “My” part is named after co-founder Michael Widenius’ daughter, My.

Continue reading →

What is Microsoft Access?

Posted on by Ian

Microsoft Access is a relational database management system (RDBMS) developed by Microsoft. It’s part of the Microsoft Office suite, included in the Professional and higher editions or sold separately.

Microsoft Access provides a quick and easy way to develop databases and is particularly well suited for individuals and small business. Access is the most popular desktop database on the market.

Screenshot of the Access welcome screen
The Microsoft Access welcome screen allows you to create a database from scratch. You can also search through thousands of templates for a prebuilt database that you can use to get started.

In Access, most tasks can be performed via the graphical user interface (GUI), but more advanced users can also develop applications using Visual Basic for Applications (VBA) code. Advanced users can also use SQL code to write or modify queries.

Continue reading →

What is SQL Server?

Posted on by Ian

SQL Server is a relational database management system (RDBMS) developed by Microsoft. It is Microsoft’s enterprise level RDBMS offering, and is a more sophisticated and robust system than Access, which has traditionally been a desktop system.

SQL Server’s main competitors are Oracle Database, MySQL (now owned by Oracle), PostgreSQL, and IBM’s DB2.

SQL Server is a client-server based system, which means that it operates as a server, typically containing many databases, with multiple clients accessing the databases from across a network. These clients are often other applications (such as a website or CRM system). This is in contrast to desktop systems, where the database will often (but not necessarily) reside on the user’s computer.

Continue reading →

What is SQL?

Posted on by Ian

SQL is the standard language for querying data inside a relational database management system (RDBMS). It is supported by all of the major database systems, such as Microsoft Access, SQL Server, MySQL, Oracle, PostgreSQL, DB2, etc.

SQL is a relatively easy language to learn when compared to most programming languages. It is based on SQL “statements” that, at times, can resemble natural language.

For example:

SELECT CustomerName
FROM Customers
WHERE CustomerId = 1

The above SQL statement is asking the database to:

“Select the value of the CustomerName column from the Customers table where the CustomerId column’s value equals 1“.

Continue reading →

How to Create a Relationship in MySQL Workbench

Posted on by Ian

To create a relationship in MySQL Workbench:

  1. Create a database model (either create a new model or reverse engineer an existing database)
  2. Viewing the database model, double click on the first table of the relationship
  3. The bottom pane will open with the table details. Click on the Foreign Keys tab
  4. In the left pane, select the foreign key field and referenced table
  5. In the middle pane, select the foreign column and referenced (primary key) column
  6. In the right pane, set any Update/Delete actions you’d like to occur when a primary key record is updated or deleted

The relationship is now established. Repeat steps 4 to 6 for any other foreign key columns in that table.

Continue reading →

How to Reverse Engineer a Database in MySQL Workbench

Posted on by Ian

To reverse engineer a database in MySQL Workbench:

  1. Select Database > Reverse Engineer from the top menu of MySQL Workbench
  2. Set/review parameters for connecting to the DBMS then click Continue
  3. Enter password if required, then click OK
  4. The wizard will connect to the DBMS, fetch a list of databases, and check for any issues. Click Continue
  5. Select the database/s you would like to reverse engineer, then click Continue
  6. The wizard will retrieve all objects from the selected schema/s and check the results. Click Continue
  7. Select the database objects you’d like to have reverse engineered, then click Execute
  8. The wizard will now reverse engineer all selected objects and generate the EER diagram (behind the scenes). Click Continue
  9. A summary is displayed. Click Close

The EER diagram is now displayed on the screen.

Continue reading →

How to Create a Database from a Script in MySQL

Posted on by Ian

To create a database from a script in MySQL:

  1. Open a Query tab in MySQL Workbench
  2. Run a CREATE DATABASE or CREATE SCHEMA statement to create the database (example below)

This will create a new database.

Screenshot of SQL script that creates a database in MySQL
Example of an SQL script that creates a database with tables, columns etc. In this case, the script has just run and has created the “Music” database. You can see the tables and their columns open in the left navigation pane.

Continue reading →

How to Generate a Script from a Diagram in MySQL Workbench

Posted on by Ian

To generate a script from a diagram in MySQL Workbench:

  1. Select File > Export > Forward Engineer SQL CREATE Script...
  2. Enter a location to save the file (optional) and set options to include on the script (such as DROP statements etc), then click Continue
  3. Exclude any objects by deselecting them, otherwise leave them as is, then click Continue
  4. The script is generated. You can copy it to your clipboard or click Finish to save it to a file

Continue reading →