Managing Your Database
The proper administration and management of objects in your database is not a
rudimentary exercise. YesterChapter, you discovered some of the Visual Data Tools that
facilitate interaction with the database. ToChapter's lesson demonstrates how you can
create, organize, and maintain all of the components within your database. Visual
InterDev, again, is true to its name by providing visual tools that remove the difficulty
from database administration.
The first part of toChapter's lesson introduces you to the type of objects that are
contained in a database. Once you understand the different types of database objects,
the lesson teaches you how to use the Database Designer to manage your database components.
You will receive a detailed tour of the Database Designer features and learn how
to apply these features to your database environment. Next, you will discover how
to create and maintain database objects such as fields and tables. You also will
learn how to create diagrams of your database. Visual InterDev provides a tool that
helps you create a visual picture of your database. These diagrams help you understand
how the various objects relate to each other.
Toward the end of the Chapter, the lesson focuses on the creation and maintenance
of stored procedures and triggers. YesterChapter's lesson taught you how to execute a
stored procedure. ToChapter, you will learn how to create your own stored procedures
for use in your applications. The lesson also teaches you how to save SQL scripts
for creating your database objects.
You may be thinking that this lesson is targeted at database administrators (DBAs)
and not developers. Actually, the lesson is targeted at both. As a developer, you
need to understand the principles of database administration so you can communicate
your application needs to your DBA.
You may be a part of a development team that does not have the luxury of allocating
a dedicated person to assume the role of DBA. This chapter reveals how the Visual
Data Tools enable you to properly manage and administrate your database. You do not
have to be a DBA to use these tools. The Database Designer is very intuitive and
powerful. For DBAs, the Database Designer provides yet another set of tools to add
to your toolbox. You may find that you like them much better than the typical database
administration tools. Whether you are a developer or a DBA, this chapter is very
important. The lesson helps you understand how you can use Visual InterDev to create
and maintain the right type of database for your application.
Using the Database Designer to Manage
The Database Designer enables you to manage and administrate your database objects.
Using the Database Designer, you can build SQL databases without having a detailed
knowledge of SQL or database administration. Visual InterDev provides a very intuitive
and easy-to-use interface to manage your database components. You also can create
diagrams that visually depict your database and the relationships between the objects.
The first part of the lesson defines the types of objects that you can use with the
NOTE: The Database Designer only supports
the use of MS SQL Server 6.5 and higher. Most of the lesson toChapter is based on the
use of MS SQL Server as your database. Later in the Chapter, you will learn about the
creation and maintenance of stored procedures. The stored procedure editor supports
the use of MS SQL Server 6.0 and higher and Oracle 7.0 and higher.
In future versions of Visual InterDev, Microsoft may support the use of other databases
with the Database Designer.
Introduction to Database Objects
A database is composed of objects that define its behavior and use. You may be
familiar with a lot of the terms in this section. By using the Database Designer,
you can create and manage the following objects:
Tables are the basic object contained in a database. You use tables to store your
information in the database. Tables are composed of columns that help further define
the attributes of the data. Each table must contain at least one column to be saved
in the Database Designer. When you define a column for a table, you must specify
the column name, length, and data type.
After you construct your database tables, you must define how they will interact,
or their relationship to each other. Relationships help avoid redundant data by enabling
you to relate two tables together instead of storing the same information in both
tables. For example, you can create an order header table that contains basic order
information and then relate an order detail table that contains multiple line items
for each order header. Each order detail line item represents an item that has been
ordered. By relating these two tables, you avoid having to store the order header
information for each detail line item. For each order detail line item, you only
need to store the order number from the order header table.
Relationships also help to enforce referential integrity. The data contained in
the database must be accurate and correct. Referential integrity means that everywhere
the data is referenced in the database, the integrity of that data is maintained.
Referring back to the order header and order detail example, an order detail line
cannot exist without having an order header row. Conversely, an order header row
that references order detail lines cannot be deleted without first deleting the order
You define a relationship through the use of keys. A primary key is a column or
set of columns that uniquely identifies a row in the table. For example, the order
number is the primary key for the order header table in the preceding example. Likewise,
the combination of the order number and order detail line number serves as the primary
key combination for the order detail table. A foreign key is a column or set of columns
that matches the primary key of another table. The order number that resides in the
order detail table is referred to as a foreign key, because its value matches the
value of a column in the order header table.
There are three basic types of table relationships: one-to-many, many-to-many,
One-to-many is the most common type of relationship. A one-to-many relationship
consists of a table with one row that relates to many rows in another table. Each
order header row can consist of many order detail lines. Figure 10.1 conceptually
depicts this relationship.
A one-to-many relationship.
Many-to-many is the second type of relationship. A many-to-many relationship consists
of many rows of one table that relate to many rows in another table. This association
is achieved through the use of a junction table, which helps to relate the two tables.
An example of this relationship can be found in the sample Publishers database that
was referenced in yesterChapter's lesson.
The Titles table has a many-to-many relationship with the Authors table. A title
can have multiple authors, and an author can write multiple titles. The junction
table for the Titles and Authors tables is the TitleAuthor table. This table primary
key contains the primary key from the Titles table as well as the primary key from
the Authors table. Figure 10.2 shows a diagram of these three tables.
A many-to-many relationship.
The third type of relationship is one-to-one. In a one-to-one relationship, a
row from one table relates to a single row of another table. This relationship really
defeats the purpose of the relational model and isn't used very often.
You will want to enforce certain rules concerning the columns within your database.
Constraints enable you to define the rules for the values of the columns in your
tables. Table 10.1 displays the five types of constraints provided by MS SQL Server
and their descriptions.
Table 10.1. MS SQL Server constraints.
||Enforces valid data values for one or more columns
||Provides a default value for a column
||Avoids duplicate or null values
||Enforces referential integrity foreign key relationships
||Ensures a unique value for a column or set of columns
Indexes provide fast access to rows in your database. A database index is very
similar to the index in this guide. You use the guide index to find the page for a
certain topic and then turn to that page number to read about the topic. The database
index works in much the same way by storing a pointer to certain data in your database.
The index consists of a column or set of columns within the table. You should only
establish indexes for data that the user will access frequently. While indexes provide
fast access to data, they absorb disk space and slow the speed of inserts, changes,
and deletions into the database.
Getting Started with the Database
The Database Designer provides a very flexible and intuitive environment for working
with your database objects. Database diagrams provide the main interface for creating
and maintaining your database objects. A database diagram visually depicts the columns,
tables, and the relationships of the tables within your database. I will talk about
diagrams in the next section, "Visualizing Your Database."
I prefer to use Visual InterDev to create a database project for the specific
purposes of administering my database. In this way, you can separate the development
tasks from the database administration tasks. You can determine what is best for
your project based on personal preference.
To create a database project, select New from the File menu. The New tabbed dialog
window is then displayed. Choose the Projects tab and select Database Project from
the list. Type a name for the new database project and click OK. You will be prompted
to add or select a database connection similar to the database connection that you
added during Chapter 8, "Communicating with a Database." For purposes of this
example, I selected the Publishers datasource that I established earlier. This data
connection creates a live connection to your database that you can then use to manage
NOTE: You can choose to add the database
connection after you have created the project by choosing Add Data Connection from
the shortcut menu.
Figure 10.3 shows the database project for the Publishers database.
A sample database project.
Once you have created a database project, you are ready to get started. The next
section provides an overview of database diagrams and their relevance to managing
Visualizing Your Database
The Database Designer uses diagrams to graphically depict the objects in your
database including tables, columns, constraints, and indexes. The database diagram
will also show the relationship between tables in your database. You can make modifications
to these objects as well as the table relationships by using the database diagram.
Your changes won't affect the database until you save them, enabling you to create
what-if scenarios for the database.
When you're finished with your modifications, you can choose to either update
the database, save the changes to execute later, or cancel the changes. If you choose
to save the changes for later use, the modifications will be saved in a Transact-SQL
script. I will provide more detail on the use of these scripts at the end of toChapter's
lesson, in the section "Utilizing SQL Scripts."
Exploring a Database Diagram
Database diagrams are saved in the Database Diagram folder within your project.
You can expand this folder in the Data View to see all of the database diagrams for
your database. A database diagram will typically contain one or more tables. Figure
10.4 displays a diagram for the Authors and TitleAuthor tables.
A database diagram.
As you can see from the picture, the Authors table has a one-to-many relationship
with the TitleAuthor table. You learned about the relationship symbols during yesterChapter's
lesson. Each table is represented by a grid that contains its columns. A key symbol
in the box next to a column designates the keys for each table. In the Authors table,
there is a key beside the au_id (Author ID) column. The key for the TitleAuthor table
is a combination of the au_id and the title_id columns.
Table 10.2 defines each of the columns within the database diagram grid.
Table 10.2. Database column properties.
||Name of the column
||Type of data to be stored
||Length of the column (determined by data type)
||Maximum number of digits used or maximum length of column for alphanumeric columns
||Maximum number of digits to the right of the decimal
||Specifies whether null values can be allowed
||Sets a default value for the column
||Column that will contain a system-generated value
||Initial value for the system-generated column
||Increment for the system-generated value
NOTE: The length is defined automatically
when you assign a data type for a column. You can change the length of some fields,
including binary, char, varbinary, and varchar.
Understanding Database Diagram
You can access and change the properties for the tables, indexes, and relationships
for your diagrams by using the Properties dialog window. This window is a tabbed
display dialog window that contains the properties for each of these objects. The
following section explains the property fields for each of these database objects.
You can access the properties for your tables by selecting the table and clicking
the right mouse button to display the shortcut menu. Choose Properties from the list
of menu items. Figure 10.5 demonstrates the available properties for a sample table.
Setting the table properties.
This window enables you to view and change the properties for a particular table.
The Selected table drop-down listbox enables you to choose another table from the
list and view its properties. This listbox only displays tables that are included
in the current database diagram that you are working in. The Table name field enables
you to view and change the name of the selected table. The bottom half of this window
enables you to see the selected check constraints for the table and its columns.
The Selected constraint drop-down listbox enables you to choose a column that contains
a check constraint. See Table 10.3 for details.
Table 10.3. The Selected constraint drop-down listbox.
|New push button
||Enables you to create a new check constraint for the table
|Delete push button
||Deletes the currently selected check constraint from the database
|Constraint expression field
||Use it to enter the Transact-SQL syntax for the check constraint
||Enables you to view and change the name of the check constraint
|Check existing data on creation
||Applies the constraint to existing data in the database if the checkbox is enabled
You can check the Enable constraint for INSERT or UPDATE to apply the constraint
to all insertions and updates into the database. The Enable constraint for replication
enables you to use the constraint for replicating the table to a different database.
The Relationship Properties dialog window enables you to change the properties
of the relationships of the tables contained in your database diagrams. Figure 10.6
shows the fields that are contained on this window.
Setting the relationship properties.
The first field on this page displays the Selected relationship. You can choose
another relationship from the drop-down listbox.
The Relationship name field enables you to change the name of the currently selected
The Primary key table shows the name of the primary key table in the relationship
and the columns that make up the primary key.
The Foreign key table displays the name of the foreign key table in the relationship
and the columns that make up the foreign key.
The next three checkboxes are similar in meaning to the checkboxes contained on
the Table Properties window. These checkboxes apply to the foreign key in the table
relationship. If the Check existing data on creation is enabled, the constraint is
applied to existing data in the database when the relationship is added to the Foreign
key table. You can check the Enable constraint for INSERT or UPDATE to apply the
constraint to all insertions and updates into the Foreign key table. Enabling this
checkbox also prevents a deletion of a row in the Primary table if a related row
in the Foreign key table exists. The Enable constraint for replication enables you
to use the constraint for replicating the Foreign key table to a different database.
You can use this dialog window to view and change the keys and indexes for the
tables within your database diagrams. Figure 10.7 shows the options that are available
in this dialog window.
Setting the properties for the indexes and keys.
As you can see from Figure 10.7, the Selected index field displays the indexes
and keys for the selected table. The Type display box located below the Selected
index field denotes whether you're viewing a primary key, unique key, or index for
the selected table. The Column name grid displays the column names that are included
in the index or key. You can add, change, or delete columns from the list by using
the New and Delete push buttons. The Index name enables you to establish a name for
The Create UNIQUE checkbox enables you to create a unique constraint or index
for the table. If you create a unique index, you can choose to ignore duplicate keys.
You can use the Fill Factor field to specify how full to make the index page within
the database. This field is used by database administrators to fine-tune performance
of the database. The Create as CLUSTERED field enables you to create a clustered
index. A clustered index provides faster access to data than non-clustered indexes.
New Term: A clustered index is a type
of index in which the logical order of the key values of the index is the same as
the physical order of the rows containing the keys.
The remaining checkboxes on the Index/Keys dialog window enable you to further
specify attributes of the clustered index, and are pretty self-explanatory.
Creating and Editing SQL Server
So far, you have learned about the types of database objects that you can manipulate
as well as how database diagrams provide the main method for working with these objects.
In this section, you are guided through the process of creating and saving a database
diagram. The lesson also teaches you how to create and maintain the database objects
within your diagrams.
Creating a Database Diagram
Before you can work with the database objects, you need to create a database diagram.
You can create a diagram in one of two ways. First, you can select a table and click
the right mouse button to display the shortcut menu. Choose Design from the list
of menu items to create a database diagram with the selected table. This diagram
will be a single table diagram for the selected table.
The second method involves selecting the Insert menu and choosing Insert Database
Item from the menu items. Select Database Diagram from the list of choices, and a
blank database diagram will be created. You can then drag and drop tables from the
Data View to add additional tables into the diagram. Once you drop the table into
the diagram, the relationship between the tables will be automatically depicted.
Figure 10.8 shows a database diagram for the Titles and Sales tables in the Publishers
A sample database diagram.
Creating a New Database Table
You can click the New Table icon on the Database Diagram toolbar to insert a new
table into your database diagram. This icon is the icon furthest to the left on the
toolbar. After you click this icon, a blank table is displayed, enabling you to enter
the column names and properties into the grid, as displayed in Figure 10.9.
To enter a name for the table, select the table and click the right mouse button
to display the shortcut menu. Choose Properties from the list of menu items to display
the Properties dialog window. Select the Table tab and enter a new name for the table
in the Table name field. When you change the name of the table using this field,
the Selected name drop-down list- box changes to reflect the new name of the table
that you enter. Figure 10.10 displays the Table Properties window with a newly created
table that has been renamed.
TIP: There are several other methods for
creating a new database table. First, you can right-click the mouse and choose New
Table from the shortcut menu. Another method involves selecting the New Database
Item from the Insert menu and choosing Table from the list of database items. You
also can right-click the Tables folder in the Data View and choose Insert New Table
from the shortcut menu.
Creating a new table.
Renaming the table.
Defining the Column Properties
Once you have created the table and given it a meaningful name, you can enter
the column names and properties. You can use the Tab and arrow keys to navigate within
the fields in the grid. An arrow in the box to the left of the column name denotes
the current row that you are inserting within the grid. Once you enter a column name,
you can choose a data type from the drop-down listbox in the Datatype field for the
column. Figure 10.11 shows an example of some of the available data types using the
Setting the data type.
After you enter the columns for the table, you need to set the key values for
the table. Select the row that you want to be the primary key and click the Set Primary
Key icon on the Database Diagram toolbar. If the primary key of the table is a combination
of columns, you need to select all of the columns that are a part of the key and
then click the Set Primary Key icon. To select multiple rows in the grid, click the
mouse in the box to the left of the Column name field to highlight the first column
row. Then click the Shift+Down Arrow key combination to highlight the next column
row. Repeat this step until you have highlighted all of the column rows for the primary
key and then click the Set Primary Key icon from the Database Diagram toolbar. A
key indicator is displayed next to the columns that you designate as the primary
Saving Your Database Changes
The final step to creating your new database table involves saving the changes
to the database. You have learned over the last couple of Chapters that the database
connection that you establish is a live connection. In other words, the actions that
you perform have an immediate effect on the database. The Database Diagram gives
you the option of directly updating the database or saving the changes for later.
To save a newly created table, you have several available options. First, you can
choose Save from the File menu. You will be prompted to enter a name for the diagram
you used to create the new tables. Once you enter the name and click OK, the Database
Designer inserts the new table or tables into the database.
NOTE: The Save All command from the File
menu has the same effect as the Save option. Both of these menu commands will perform
immediate updates against the database.
You also can choose Save Change Script from the File menu, which saves the SQL
script to a text file that you can execute against the database at a later time.
Figure 10.12 shows the results of saving the Orders table against the Publishers
Saving the new table.
Working with Diagrams and Tables
You can use the Database Designer to view the relationships of the tables in your
database. You also can use the Database Designer to modify existing table relationships
and properties. To open an existing database diagram, right-click the mouse on the
selected diagram from within the Data View and choose Open from the list of menu
items. This action opens the database diagram, enabling you to work with the tables
contained in the diagram. You can make changes to the column properties as well as
the relationships between the tables. You also can add new tables to the diagram,
as explained in the previous section. Once you have made your modifications, you
need to save your changes to the database. You can either save these changes immediately
to the database or save the changes to a text file for later use.
Utilizing SQL Scripts
In the previous section, you discovered that you can execute immediate updates
against the database. You also learned that you can save these changes in a text
file to be executed at a later time. These files contain Transact-SQL commands that
perform administrative functions against the database.
SQL scripts can be useful, especially if you have to execute the same commands
repeatedly against a database. These scripts also can be useful for creating the
same databases and tables for separate development, testing, and production database
environments. You learned about the benefits and use of these environments on Chapter
3, "Design and Development Considerations." A good DBA learns the benefit
of SQL scripts very quickly in life. It only takes having to delete a table and re-create
it manually one time to see the benefit of an automated script that performs this
function for you.
Saving the SQL Script
Earlier toChapter, you learned how to create and modify tables. The last step involves
saving those changes. The previous example showed you how to immediately update the
database with the changes. To save the changes to a SQL script instead, choose Save
SQL Script from the File menu. The Save Change Script dialog window displays. Figure
10.13 shows a SQL script for a change to the column data type of an existing table.
The Save Change Script dialog window displays the actual Transact-SQL that will
be saved to the text file. Listing 10.1 shows the complete code sample for Figure
Examining the SQL Script.
Listing 10.1. Changing the data
type of a database column.
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
CREATE TABLE dbo.Tmp_Orders_4
"Order Number" int NOT NULL,
"Order Date" datetime NOT NULL,
Payterms varchar(10) NOT NULL
) ON "default"
IF EXISTS(SELECT * FROM dbo.Orders)
EXEC(`INSERT INTO dbo.Tmp_Orders_4("Order Number", "Order Date", Payterms)
SELECT "Order Number", "Order Date", CONVERT(varchar(10), Payterms)
ÂFROM dbo.Orders TABLOCKX')
DROP TABLE dbo.Orders
EXECUTE sp_rename `dbo.Tmp_Orders_4', `Orders'
ALTER TABLE dbo.Orders ADD CONSTRAINT
PK_Orders PRIMARY KEY NONCLUSTERED
) ON "default"
I'm not going to examine the entire code listing, but I did want to outline a
very robust feature of the Database Designer. You can use the Database Designer to
change the column data types of tables that contain existing data. This code listing
shows the Transact-SQL that creates a temporary table named Tmp_Orders_4. This temporary
table is used to insert any data that resides in the Orders table and convert it
to the new data type. The original Orders table with the old data type for the Payterms
column is then deleted. Finally, the Tmp_Orders_4 table that contains the order information
with the new data type is renamed to Orders. The Database Designer creates all this
logic for you, which should make DBAs appreciate the robustness of the Database Designer
After you have verified that the SQL syntax is correct, you can click Yes to save
the changes for the SQL script. A confirmation message with a default name for the
script is then displayed. The name is assigned by the Database Designer and contains
the .sql extension. You can edit this file using Visual InterDev as well as any text
editor. You also may want to rename the file to conform to any standards that you
have established for your project.
Creating and Editing Stored Procedures
The stored procedure editor provides a very intuitive tool for creating and maintaining
stored procedures for your applications. Visual InterDev supports the use of the
stored procedure editor with MS SQL Server 6.0 and higher and Oracle 7.0 and higher.
If you're using MS SQL Server 6.5 or higher, you also can also debug your stored
Creating a Stored Procedure
To create a new stored procedure, select the Stored Procedures folder and right-click
the mouse to display the shortcut menu. Choose New Stored Procedure from the list
of menu items. The stored procedure editor opens and presents a template for creating
your new stored procedure. Figure 10.14 displays a sample template for creating a
new stored procedure.
The template includes the Transact-SQL key words CREATE PROCEDURE that
signify all stored procedures. Also, the template provides a place for you to enter
the name of the new procedure. You can then enter the SQL for your procedure after
the AS key word. The stored procedure template also provides a placeholder
at the end of the procedure for capturing the return code. After you have developed
your stored procedure, you can save it by choosing Save or Save As from the File
menu. The Save As option enables you to save the file in a separate text file that
is denoted with the .tsq filename extension. Figure 10.15 shows an example of a stored
Creating a new stored procedure.
A sample stored procedure.
Executing a Stored Procedure
You learned during yesterChapter's lesson how to execute a stored procedure. Once
you have created and saved your stored procedure, you should test the procedure to
ensure that it produces the desired results. You can execute the procedure from within
the stored procedure editor by right-clicking the mouse anywhere within the stored
procedure. Choose Run from the list of menu items. If there is an error in your procedure,
the stored procedure debugger displays an error message indicating the mistake, as
shown in Figure 10.16.
An erroneous stored procedure.
If no errors are found, the result is displayed in the Output window at the bottom
of the Visual InterDev project workspace, as depicted in Figure 10.17.
You still need to make sure that the stored procedure returned the results that
you expected. A bug-free procedure does not mean that the stored procedure is accurate.
You need to test the procedure to make sure that it meets your application requirements
both now and in the future. A stored procedure is usually shared among the developers.
Develop these procedures in a manner that can be universally applied across the application.
This doesn't mean that you should have one stored procedure that meets everyone's
needs. You should, however, create procedures that are targeted to the needs of more
than a single developer.
Executing a successful stored procedure.
ToChapter's lesson concludes the last section focused solely on the database part
of your application. You should be able to apply the database principles that you
have learned over the last three Chapters toward the other lessons this week and next
week. Database administration isn't an exciting pastime, but a good DBA is worth
his or her salt. You can use the Database Designer to perform routine database administration
and management functions without becoming a full-fledged DBA. The Database Designer
provides a visual tool that removes a lot of the mundane chores of managing your
ToChapter's lesson first provided you with an introduction to the Database Designer.
You learned about the types of database objects that you can create and manipulate
using the Database Designer. Next, you learned how to use database diagrams to create
and maintain your database objects. You discovered the usefulness of database diagrams
in providing a visual picture of your database as well as a visual tool to manage
the database tables and objects.
Toward the end of the Chapter, the lesson focused on the use of SQL scripts. You learned
about how the different Save options have an impact on the database and how you can
use SQL scripts for database updates in the future. The final lesson for the Chapter
taught you to how to create and edit a stored procedure.
- Q Does the Database Designer replace my existing database administration tools?
AThe Database Designer is meant to complement your other DBA tools. The Database
Designer enables you to administrate and manage database objects once you have created
a database. You can use the Database Designer as yet another tool in your DBA toolbox.
What is the difference between an index and a primary key?
AA primary key is used to uniquely define a row in a table. A primary key can
consist of a single column or a combination of multiple columns. An index consists
of a key value and a pointer to the data contained in the table. An index provides
an additional method for accessing data in your tables.
ToChapter's workshop focuses on the use of database diagrams. Use the Publishers database
included with MS SQL Server to create some database diagrams of your own. Create
some new tables to add to the database and practice defining column properties for
these tables. Use the examples provided in this lesson as a guide to walk you through
You also should practice developing a few stored procedures for the Publishers
database. This database includes a few examples to get you started.
- 1. What is a foreign key?
2. What is the Default Value column property used for?
3. Name three types of column constraints.
- 1. A foreign key is a column or set of columns whose value matches the
primary key value of another table.
2. The Default Value property enables you to specify a default value for a
column. This property can be used in situations when you want to populate the value
of the column if the user doesn't enter a value for the field within the context
of your application.
3. Possible answers include