Database Diagramming with Visual Studio 6.0 and SQL Server 7.0 

  • Article

 

Dave Mendlen
Visual Studio

November 1998

Summary: Examines the use of SQL Server 7.0 and the Visual Studio 6.0 new database diagram tool. (3 printed pages) Covers:

  • The purpose of the database diagram
  • How to create a database diagram
  • How to version your schema in Visual SourceSafe

The database diagram is a critical document. It helps developers visualize structures of tables and relationships. One of the first things developers want to do when starting to work on a new project is print the entire database schema. The print out from a complex database can cover an entire wall. Sometimes it's easier to understand a database structure if you look at it by subject area. The database diagram tools enable developers and database administrators (DBAs) to create "windows" into the entire database. For instance, there might be a diagram that consists of only the entities related to customer.

Because these documents are stored in the database, all of the developers on the team can view, edit, or create them (depending on their database permissions). Also, the SQL Server 7.0 DBA will be able to publish, share, and edit schemas with the developers from within the SQL Server 7.0 Enterprise Manager.

More and more, developers have stepped into the role of "virtual DBA." While the production DBA focuses on supporting existing databases, developers need databases to begin development against. Using a local SQL Server database and the Visual Database Tools, developers can visually author proposed database structures. From within the tools, both DBAs and developers can create new tables, indexes, relationships, and all the other expected database objects without writing a single line of Transact-SQL. You can modify existing databases, create new ones, and even experiment with changes to your database without actually modifying the underlying database. DBAs won't need to panic because these tools use SQL Server 7.0 underlying permissions—if developers don't have access to the database, they won't have access with these tools either.

It doesn't matter which language in the Visual Studio lineup you choose; you'll have these tools. When you jump over to SQL Server 7.0 Enterprise Manager, you'll notice that the database diagram is so important that it's now the "cover story" for a database.

Let's walkthrough how to use the Visual Database Tools to integrate database development the development environment. The Visual Database Tools are designed for Microsoft SQL Server 6.5, SQL Server 7.0, and Oracle 7.33+ databases. This feature is enabled in all Visual Studio development environments, but will be illustrated in the Visual InterDev and Visual J++ common development environment.

Adding a New Table and Data

  1. From the Start menu, start Visual InterDev or Visual J++™.

  2. To open a new database project, in the Start menu choose Database Project and click Open.

  3. Click the Machine Data Source tab. Select an ODBC data source to SQL Server 7.0 as the data source.

  4. Log on to the database.

  5. Right-click Database Diagrams in the Data View window, and choose New Diagram (Figure 1).

    Figure 1. Creating a new database diagram with the Visual Database Tools

  6. Click each table and drag it to the Database Diagram or select all the tables by clicking on each one while holding down the SHIFT key. Drag the tables and drop them onto the database diagram (Figure 2).

    Figure 2. Dragging SQL Server tables onto a new database diagram

  7. To arrange the tables, click on the Arrange Tables icon on the tool bar (Figure 3).

Figure 3: Automatically arranging tables in a database diagram

  1. Adjust the viewing percentage depending on how much of the database schema you would like to see.

  2. To see the relationships between tables, position the mouse over the join (Figure 4).

    Figure 4. Viewing database relationships by positioning the mouse over the links in the database diagram

  3. Right-click the Table Object and select Properties for a full examination of indexes and keys (Figure 5).

    Figure 5. Viewing key relationships with the Visual Database Tools

Creating a New Table

  1. You can easily change the database based on schema changes. To create a new table, right-click the design surface and select New Table. Name the table Limits (Figure 6).

    Figure 6. Entering a new table name

  2. You will now see a new table with all column descriptions. Fill it out, as seen in Figure 7.

    Figure 7. Viewing column properties

Printing the Diagram

  1. If you plan to print your diagram, you can insert page breaks. To do this, view the diagram at 25%.
  2. Click the View Page Breaks toolbar button to see the pagination.
  3. Close the database diagram. You will then see a confirmation message that includes a complete SQL change script that you can save for other developers to use.

Using Visual SourceSafe

  1. If your Project Explorer is not visible, click the Project Explorer button on the toolbar.

  2. Drag the Limits table from the Data View onto the project. This will create a script for the table that will enable re-creation (Figure 8). Note: if you do not see the limits table, right click on tables and select refresh.

    Figure 8. Creating a SQL script by dragging from the Data View with the Visual Database Tools

  3. Right-click the script (limits.sql) and choose the Add to Source Control option (Figure 9).

    Figure 9. Adding the database script to Visual SourceSafe

  4. Log on to Visual SourceSafe as admin (no password).

  5. To create the project in Visual SourceSafe, select the $/ folder, and click the Create button.

  6. Click OK to confirm the files you want to add to Visual SourceSafe.

  7. You can now version these database scripts in Visual SourceSafe. The scripts are available for other developers on a team to use, with full versioning capabilities.

Summary

The database diagram is a critical document that both developers and DBA's should manage. Although we didn't use these tools in the SQL Server 7.0 Enterprise Manager, they are included there as well. Because these documents are stored in the database, all of the developers on the team can view, edit, or create them (depending on their database permissions).

© Microsoft Corporation. All rights reserved.