Altova DatabaseSpy 2024 Professional Edition

"ZooDB" Database

Home Prev Top Next

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:

dbs_create_db_visual_studio

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\DatabaseSpy2024\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.

 

File

Purpose

create_ZooDB_tables.sql

Generates multiple tables in the "ZooDB" tutorial database.

create_ZooDB_relationships.sql

Creates foreign key relationships between database tables.

tblAnimalTypes_data.sql

Contains INSERT queries that populate two of the tables with data.

animalType_queries.sql

Contains SELECT queries that retrieve data from two tables.

tblZookeepers.txt

Contains data in CSV format that can be used to populate one of the database tables with data.

dbo.tblFeedSuppliers.xml

Contains data in XML format that can be used to populate one of the database tables with data.

 

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).

© 2017-2023 Altova GmbH