Managing Databases with SQL Server 2012, Oracle Database 11g Release 2, and MySQL 5.6 - Database Processing: Fundamentals, Design, and Implementation - page 431

In each online chapter for the specific DBMS product, we tell you exactly what in that
DBMS product constitutes what we have been calling a database and the steps to create and
name it. In each case, we end up with a usable “database” named VRG for the View Ridge
Gallery database project.

Creating and Running SQL Scripts

Now that we have created the VRG database, we need to create the table and relationship struc-
ture of the database and then populate the tables with data. We prefer to do this with SQL scripts,
as we have discussed in Chapter 2 on SQL queries and in Chapter 7 on SQL DDL. Therefore, we
discuss how to create, store, retrieve, and run SQL scripts using one of the DBMS utilities:

For Microsoft SQL Server 2012, we use the Microsoft SQL Server 2012 Management
Studio.

●

For Oracle Database 11 g Release 2, we use Oracle SQL Developer.

●

For MySQL 5.6, we use the MySQL Workbench.

●

Further, each DBMS product has its own variant of SQL and SQL/Persistent Stored
Modules (SQL/PSM):

For Microsoft SQL Server 2012, we have Transact-SQL (T-SQL) .

●

For Oracle Database 11 g Release 2, we have Procedural Language/SQL (PL/SQL) . ●

For MySQL 5.6, we do not have a separate variant name and just use SQL and SQL/PSM. ●

We discuss each of these in the context of its parent DBMS product in the separate online
chapters.

Reviewing the Database Structure in the DBMS GUI Utility

Besides giving us a good SQL editor to create and run SQL scripts, each DBMS product has
also built its GUI utilities with the capability to work with database objects such as tables in
a GUI mode (similar to what we do in Microsoft Access 2013). We discuss how to use specific
GUI utilities to do this:

For Microsoft SQL Server 2012, we use the Microsoft SQL Server 2012 Management
Studio.

●

For Oracle Database 11 g Release 2, we use Oracle SQL Developer.

●

For MySQL 5.6, we use the MySQL Workbench.

●

Creating and Populating the View Ridge Gallery Database Tables

Having created the VRG database and knowing how to use SQL scripts, we turn to actually
creating the VRG tables, referential integrity constraints, and indexes that form the basic
structure of the database itself. As you might expect, each DBMS product has its own variant
on exactly how this should be done. A good example is how each DBMS product handles sur-
rogate keys:

In Microsoft SQL Server 2012, we use the T-SQL IDENTITY property . ●

For Oracle Database 11 g Release 2, we use the PL/SQL SEQUENCE object .

●

For MySQL 5.6, we use the MySQL AUTO_INCREMENT property .

●

Once the database structure is created, we discuss how to populate the tables with data.
Because the VRG data as provided in Figure 7-16 contains non-continuous surrogate key val-
ues, we discuss how to handle this situation when inputting data into tables.