Altova DatabaseSpy 2023 Enterprise Edition

In this tutorial, you will use DatabaseSpy to interact with a SQL Server database that models a zoo. Step-by-step instructions, SQL scripts and other files are provided to help you complete this task.

The tutorial is organized into the following parts, which must be followed sequentially.

•Setting up the "ZooDB" Project - Shows you how to create the project that groups together all the required script files and the database connection.

•Adding Tables to the Database - Illustrates various ways to create or design the database structure.

•Defining Constraints - Provides instructions for creating the following constraint kinds: unique, check, default, and foreign key constraints.

•Adding Data to the Database - In this tutorial section, you learn how to populate the database with data. You can do this by importing data from XML or CSV files, by running an INSERT script, or by entering data manually.

•Querying the Database - This tutorial section illustrates various ways to query the "ZooDB" database in order to view, update, or delete data. This includes running SQL scripts and generating on demand SQL statements. In addition, you can always select, update, or delete data manually.

•Exporting Database Data - Shows you how to export data to other formats such as XML.

Prerequisites

Before you can start with this tutorial, a new sandbox database must be created in Microsoft SQL Server. You can accomplish this task either with Microsoft SQL Server Management Studio, or from Visual Studio. To do this, connect to a database server running Microsoft SQL Server, and follow the instructions specific to your SQL Server database administration tool to create the database. For example, the following image illustrates creating a new database on a network SQL Server from Visual Studio 2015:

Notes:

•In this tutorial, the database name is "ZooDB".

•Your SQL Server user account requires "dbcreator" privileges to create a database. Contact your database server administrator if you do not have the respective rights.

•The new "ZooDB" database must be empty. All tables, relationships, and data will be added to this database in the subsequent steps of this tutorial.

Tutorial example files

The table below lists the files used in this tutorial. These files are available at the following path: C:\Users\<username>\Documents\Altova\DatabaseSpy2023\DatabaseSpyExamples\Tutorial. Most of them are scripts in SQL Server grammar that create the database structure and then populate some of the tables with data. Note that the files are listed in the order in which they are meant to be run against the database, as you will discover by following the tutorial.

Several other CSV and XML files are available if you want to optionally populate all of the database tables with data, see Populating Other Tables (Optional).