Database Designer

The Database Designer allows you to create, edit, or delete database objects. You can also create database diagrams to visualize the structure of your database tables and their relationships.

Creating and Modifying Database Diagrams

Database Diagrams graphically represent the tables in your database. They also show the relationships between the tables and their indexes and constraints. To create a Database Diagram, you right-click the Database Diagrams folder in Data View and then choose New Diagram. Figure 12-2 shows a Database Diagram within Visual InterDev.

click to view at full size.

Figure 12-2. A Database Diagram showing six tables and their relationships.

You can use Database Diagrams to:

  • Manipulate database objects without having to write SQL code
  • Visualize the structure of your database tables and their relationships
  • Provide different visualizations of complex databases
  • Experiment with database changes without modifying the underlying database
  • Create new tables, indexes, relationships, and other constraints
  • Alter the structure of your database

You can place tables onto your Database Diagram by dragging them from the Data View. You can also create new tables by right-clicking within the Database Diagram and choosing New Table from the context menu. Tables can be dragged around within the Database Diagram to help you better visualize your database schema.

The Database Diagram has extensive context menus for working with tables, allowing you to perform a variety of tasks. Any changes you make to your tables are not saved to the database until you save the diagram. Figure 12-3 shows the context menu that appears if you right-click a particular table.

Figure 12-3. The Database Diagram context menu that appears when a table is selected.

In Figure 12-3, the top six menu items allow you to set and modify your view of the tables within the Database Diagram. You have the following choices:

  • Column Properties
  • Column Names
  • Keys
  • Name Only
  • Custom
  • Modify Custom View

If you choose Column Properties, you'll see the tables with all their column properties displayed. For SQL Server, this includes Column Name, Datatype, Length, Precision, Scale, Allow Nulls, Default Value, Identity, Identity Seed, and Identity Increment. If you choose Column Names, you'll see only the column names associated with each table, as shown in Figure 12-2. If you choose Keys, you'll see only those columns associated with a primary or foreign key. If you choose Name Only, only the table name is displayed. If you choose Custom, you can view a custom set of table properties. The default custom view shows all column names with their associated data types and nullability, as shown in Figure 12-4.

Figure 12-4. A custom view of a table within the Database Diagram showing column names, data types, and nullability.

Finally, you can choose Modify Custom View from the context menu to create your own custom view for the tables within the Database Diagram. Figure 12-5 below shows the resulting dialog box. Here you can choose from a set of available columns. You can also sort the columns in any order you choose and save your changes as the default custom view.

Other menu items on the context menu shown in Figure 12-3 allow you to define primary keys, insert or delete columns, remove tables from the diagram, delete tables from the database, arrange the size and location of the tables on the diagram, and view table property pages.

Figure 12-5. The Column Selection dialog box for modifying a custom view of a table.

By right-clicking outside of any table in the Database Diagram, you can pull up another context menu that allows you to manipulate your diagram. For example, you can zoom the diagram from 10 percent to 200 percent, arrange the layout of tables on the diagram, view page breaks (useful when printing), and create text annotations to place on the diagram.

Printing the Database Diagram is useful when you are developing your applications since it gives you a handy reference to the column names and data types within each of your tables.

Creating and Modifying Database Objects

For SQL Server and Oracle databases, you can use the Database Designer to exercise complete control over your database objects. You can create tables, relationships, indexes, keys, and constraints.

Tables

To create a new table, follow these steps:

  1. Expand the Tables folder in Data View.
  2. Right-click a table name, or right-click the Tables folder.
  3. Choose New Table from the context menu.
  4. Enter the table name in the Choose Name dialog box. The new table definition appears in a window, as shown in Figure 12-6.
  5. Define the name of a column for the new table in the Column Name field.
  6. Tab to the Datatype field, and select the column's data type.
  7. Select or deselect the Allow Nulls option, depending on your project's requirements.
  8. If desired, make the column a primary key (PK) by clicking the Primary Key button on the Table toolbar. A key symbol appears to indicate that the field is the primary key.
  9. Enter the other column information. Your table definition window should resemble the one in Figure 12-7 below.
  10. Choose Save from the File menu or click the Save button on the toolbar to save your new table and update the database.

click to view at full size.

Figure 12-6. The new table definition window is shown in the right pane.

click to view at full size.

Figure 12-7. The table definition window for the Account_Balance table.

You can also easily modify any existing table using the Database Designer. Take the following steps:

  1. Right-click a table name, or right-click the Tables folder.
  2. Choose Design from the context menu to display the table definition window, like the one shown in Figure 12-7 above.
  3. Modify the table definition as desired.
  4. Save the table definition by clicking the Save button on the toolbar.

You can also create a new table by right-clicking an open space on the Database Designer and then choosing New Table from the context menu.

Now that you have a new table, you can easily create related tables. You can copy the table definition to the Clipboard to copy information from one table to another. This is useful for creating other tables with related columns.

The Clipboard is extremely useful when you need to create a one-to-many relationship between two tables—such as a Customer table that must be linked to an Account_Balance table. Create the Customer and Account_Balance tables, and then copy the primary key from the Customer table and paste it into the new Account_Balance table. Copy and paste assures that you have no misspelled words in the Account_Balance table's new foreign key.

To use the Clipboard in this manner, simply select the row that defines the column you want to copy. Choose Copy from the Edit menu, and then select the other table and choose Paste from the Edit menu to paste the column definition into the other table.

Relationships

You can create relationships between tables within a Database Diagram. A typical relationship between two tables is a one-to-many relationship: one record in a table can have one or many related records in another table.

The relationship lines indicate the current relationships defined in the database. You can edit a relationship by right-clicking the line and choosing the Property Pages command from the context menu or by right-clicking the relevant table and choosing the Property Pages command from its context menu. Figure 12-8 shows the Relationships tab on the Property Pages dialog box.

Figure 12-8. The Relationships tab on the Property Pages dialog box showing a relationship between the Account_Balance and Account_Code tables.

You can reposition the relationship lines between tables by moving line segments with the mouse. You can also add segments by grabbing the corner of a line and moving it diagonally.

You can create a reflexive relationship on a table by dragging a column and then dropping it back onto the same table. For instance, if you have an Employees table with a Manager ID field that also represents an Employee ID, you can create a reflexive relationship. First make Employee ID the primary key, and then drag a relationship out from Manager ID and drop it back onto the Employees table.

When you drag relationships from one table to another, the Database Designer keeps track of which columns you drag and which you drop on. For instance, if you drag a nonprimary key column into another table, the Database Designer assumes that you want to link on that table's PK column. If you drag a PK column to another table, it assumes that the column you drop on is the foreign key (FK) column for a one-to-many relationship.

You can also change the keys on a table as well as change the data type across FK relationships. This feature lets you change either the data type or the length of a PK or FK column and then change all the related tables. The Database Designer monitors your changes, alerting you before applying them to the database.

Indexes and keys

The primary key for a table is a column or combination of columns that uniquely identifies a row in a table. It cannot allow null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables. You can add a primary key to a table within a Database Diagram by right-clicking the appropriate column in the table and choosing Set Primary Key from the context menu or by using the Indexes/Keys tab in the Property Pages dialog box.

Figure 12-9 shows the Indexes/Keys tab for the Account_Balance table. You can see that the index is named PK_Account_Balance and that it is a primary key. The key is based upon the account_no column in the Account_Balance table.

Figure 12-9. The Indexes/Keys tab on the Property Pages dialog box showing the primary key for the Account_Balance table.

From this Indexes/Keys tab, you can create additional indexes by clicking the New button. You can then select one or more columns to include in the index and specify various properties of the index, such as whether it is unique and whether it is clustered. A unique index can uniquely identify a row in a table. In a clustered index, the physical order of the rows in the table is the same as the logical (indexed) order of the key values. Clustered indexes are useful for several purposes, including speeding up UPDATE and DELETE SQL statements and for queries that return large resultsets.

Constraints

Constraints are business rules that you can apply to the data within your tables. In SQL Server, there are two types of constraints: check constraints and unique constraints. Check constraints apply certain business rules to your data, such as ensuring the data is within a certain range of values. You can apply check constraints to multiple columns and you can apply multiple check constraints to a single column. Unique constraints ensure that the data entered is unique for that specific column in a table. NULL values are allowed in a unique constraint as long as rows are unique in the table.

You define unique constraints using the Indexes/Keys tab in the Property Pages dialog box, as shown in Figure 12-9.

To create a unique constraint:

  1. Select the appropriate table from the Database Diagram, and access its Property Pages dialog box.
  2. Select the Indexes/Keys tab.
  3. Click the New button.
  4. Select the column name for the constraint.
  5. Check the Create Unique check box.
  6. Ensure the Constraint radio button is selected.
  7. Click the Close button to save your new constraint but not immediately apply it to the database, or choose File|Save to save your new constraint.

You define check constraints using the Tables tab in the Property Pages dialog box, as shown in Figure 12-10.

To create a check constraint:

  1. Select the appropriate table from the Database Diagram, and access its Property Pages dialog box.
  2. Select the Tables tab.
  3. Click the New button.
  4. Enter the constraint expression.
  5. Enter the name for the constraint.
  6. Click the Close button to save your new constraint but not immediately apply it to the database, or choose File|Save to save your new constraint.

Figure 12-10. The Tables tab showing a check constraint for the password column in the Login table.

In Figure 12-10, you'll also notice the check boxes at the bottom of the dialog box that allow you to specify whether the constraint should check existing data upon creation and whether it should be enabled for INSERTs, UPDATEs, and replication. Once a constraint has been created (assuming the constraint is enabled for INSERTs), if any new data that is entered into the table violates the constraint you'll see an error message similar to the one shown in Figure 12-11.

click to view at full size.

Figure 12-11. A sample error message that is displayed when a check constraint is violated upon an INSERT statement.

Creating Scripts

Whenever you make changes to your database schema within the Database Diagram, the changes are not applied to the database until you choose the Save command from the File menu. At this point, you'll be prompted with the Save dialog box, which will inform you which tables will be saved to the database and give you the option either to continue or to cancel the save operation. If you choose Yes to save your changes to the database, you'll see a Save Change Script dialog box, as shown in Figure 12-12.

This dialog box gives you the option to view your change script and to save it to a text file. The change script is a SQL script (for your SQL Server or Oracle database) that contains all the Data Definition Language (DDL) syntax necessary to apply your changes to the database. These changes can include creation, modification, or deletion of tables, columns, constraints, relationships, indexes, and so on. The change scripts are useful for a number of purposes, including:

  • Keeping a record of changes that you've made to your database for version control
  • Passing along to a database administrator for review/approval and for changes to the production database
  • Applying to other databases such as test databases so that the database schemas are kept in sync

Another way to save your change script is to click the Save Change Script button on the Database Diagram toolbar. If you don't see the Database Diagram toolbar on your screen, you can access it by choosing View|Toolbars|Database Diagram from the menu.

When you save your change script the name of the file is DbDgmN.sql, where N is the number of the change script you generate. N starts at 1 and increments each time you save a script. You can execute the script against a database at any time using the Query Designer within Visual InterDev or a tool such as ISQL/W, which comes as part of SQL Server. Oracle databases provide similar tools for applying SQL scripts to the database.

click to view at full size.

Figure 12-12. The Save Change Script dialog box allows you to view and save your database changes as a SQL script for later use.



Programming Microsoft Visual InterDev 6. 0
Programming Microsoft Visual InterDev 6.0
ISBN: 1572318147
EAN: 2147483647
Year: 2005
Pages: 143

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net