Creating a Database Schema

Although creating a list of tables and fields is a good way to nail down the structure of the database, you may also want to be able to look at the tables and fields in a graphical format. That will let you see not only which tables and fields are available to you, but also how they relate to each other. You do so by creating a schema.

A schema is a road map to your database. A schema comprises diagrams of all the tables, fields, and relationships in your database. Including a database schema as a part of your software design is important because it gives you a quick way to see what's going on in your database.

Schemas also are important long after the database design process has been completed. You'll need the schema to perform multitable queries on the data. A good graphical schema answers questions such as: Which tables do I need to join to list all the orders greater than $50.00 that came in from customers in Minnesota in the last 24 hours? (For more information on how to create queries based on more than one table, see Chapter 2.)

There is no one standard way to create database schemas, although there are many tools that you can use to create them. The drawing tool Visio is flexible, fast, and easy to use and it integrates well with other Windows applications, particularly Microsoft Office. Visio is now shipped as part of Visual Studio.NET Enterprise Architect edition; it's also available separately.

You're not limited to using Visio when creating a graphical database schema. You can use what ever drawing tool you're comfortable with. Microsoft Windows Paint is a workable option, as are Microsoft Word's drawing features.

Using Visual Studio to Create a Database

There are a number of ways to create a database in SQL Server. It has its own set of tools, known as SQL Enterprise Manager, which enables you to create databases and tables graphically or programmatically (using SQL commands). Available are a number of external tools that enable you to work with database structures (one of which, Visio, is described later in this chapter).

Visual Studio.NET has an outstanding facility for working with a SQL Server database. This facility is contained in Server Explorer, a new Visual Studio feature that lets you work with all kinds of server software in an integrated way. To use Server Explorer to create a SQL Server database do the following.

  1. Launch VS.NET.

  2. From the left side of the VS.NET window, select the Server Explorer tab. The Server Explorer window appears. (Note that tabs to select Server Explorer may be vertical or horizontal.)

  3. Expand the outline so that you can see your server from the Servers node. Beneath your server name should be a SQL Servers node. Expand it to see the instance of SQL Server running on your machine, as shown in Figure 1.1.

    Figure 1.1. The Server Explorer window in Visual Studio.NET. In this window, you can manage server processes, such as SQL Server.

    graphics/01fig01.jpg

  4. To create a new database, right-click on the name of the SQL Server running on your machine (in Figure 1.1, the name of the computer is ROCKO; yours, of course, will be different). From the pop-up menu, select Create Database.

  5. The Create Database dialog appears. Type the name of the database (Novelty) and click on OK.

  6. The new database should appear in the Server Explorer window.

Expanding the outline view for the database that you just created reveals five categories of database objects available from VS.NET:

  • Database Diagrams

  • Tables

  • Views

  • Stored Procedures

  • Functions

As you can't do much in a database without a table, first create one by doing the following.

  1. In Server Explorer, right-click on the Tables node beneath the Novelty database. From the pop-up menu, choose New Table.

  2. A table design window appears. Create tblCustomer having the following fields and definitions for those fields.

    Column Name

    Data Type

    Length

    Allow Nulls

    ID

    int[*]

    4

    No

    FirstName

    varchar

    20

    Yes

    LastName

    varchar

    30

    Yes

    Company

    varchar

    50

    Yes

    Address

    varchar

    50

    Yes

    City

    varchar

    30

    Yes

    State

    char

    2

    Yes

    PostalCode

    varchar

    9

    Yes

    Phone

    varchar

    15

    Yes

    Fax

    varchar

    15

    Yes

    E-mail

    varchar

    100

    Yes

    [*] Note that the ID field will be the identity column; that is, it will contain a unique number (integer) for each row that contains fields in the table.

  3. The information inserted in the table yields the result shown in Figure 1.2.

    Figure 1.2. Creating a table definition by using Visual Studio.NET's Server Explorer

    graphics/01fig02.jpg

  4. Click on the ID field. From the Diagram menu, select Set Primary Key. Doing so will ensure that no two customers in your database can have the same ID number. (We present more information on primary keys in the next section.)

  5. Next you'll need to make the ID field an identity column. That will cause SQL Server automatically to generate an ID number for each of your customers. To do so, right-click on the table definition window. From the pop-up menu, choose Indexes/Keys.

  6. A property page for the table definition appears. Click on the Tables tab at the top of the page.

  7. In the field Table Identity Column, choose ID.

  8. Click on Close.

  9. From the File menu, choose Save Table1. The Choose Name dialog appears, asking you to specify a better name for your table. Type the name tblCustomer and click on OK. The table is saved; note that it has been added to the list of tables in this database in Server Explorer.

Designating Indexes and the Primary Key

Now that you've created the basic table, one thing remains to be done: You must designate indexes. An index is an attribute that you can assign to a field, making it easier for the database engine to retrieve data based on information stored in that field. For example, if you have a database that tracks employees, your application will probably tend to look up employees by last name, department, and individual ID number. It makes sense then to create indexes on each of these fields, to make the process of retrieving records based on these fields faster.

Once you've realized the benefits of indexes in database design, you might ask yourself the question: If indexes make lookups faster, why not place an index on every field in every table? The answer is that there's a diminishing return with indexes. Indexes make your database physically larger, so if you have too many indexes, they will consume far too much memory and disk space, making your computer run more slowly. In addition a lot of maintenance is involved in updating indexes, which obviously nullifies the benefit of having an index in the first place. There's no hard and fast rule for how many indexes each table should have, but in general, you should create indexes for the fields that you envision will be queried most often. (For more information on how to use the information in a field as a query criterion to retrieve sets of records, see Chapter 2.)

A primary key is a special type of index. A field that is designated as a table's primary key uniquely identifies the record. So, unlike other types of indexes, no two records in the same table may have the same value in its primary key field. Also, when you designate a field as a primary key, no record may contain an empty, or null, value in that field. When you designate a field in a table as that table's primary key, you can create relationships between that table and other tables in your database.

Every table you create should at least have a primary key, and it should also be indexed on those fields you expect to be queried the most. In the case of tblCustomer, as with many database tables, the primary key will be the ID field. (You should have made this field the primary key earlier when you created tblCustomer.) The secondary indexes will be the LastName and FirstName fields.

Now you can create two more indexes, for the FirstName and LastName fields, by doing the following.

  1. Right-click on the Server Explorer table design window for tblCustomer. From the pop-up menu, choose Indexes/Keys.

  2. A property page appears with a list of existing indexes. The primary key index (called PK_tblCustomer) should already be there. Click on the New button to create a new index for the FirstName field.

  3. In the list of column names, choose FirstName (as shown in Figure 1.3), then click on Close.

    Figure 1.3. The Table Structure dialog box, after all the fields and indexes have been designated

    graphics/01fig03.jpg

  4. Repeat this process for the LastName field.

    Caution

    At the bottom of the property page there's an option labeled "Create UNIQUE". Don't check it! If you do, you won't be able to add two people with the same first name to the database. Create unique indexes only when you want to ensure that two records with the same value in a given field can't be created.

  5. To save your changes to the database, choose the menu command File, Save tblCustomer. You may close the table design window in VS.NET after saving the changes successfully.

Now that you've created the data structure of the table, you may want to enter data into it. Server Explorer makes it easy; to work with data in the table, simply right-click on it in the Server Explorer window and choose Retrieve Data From Table from the pop-up menu. A data-entry grid appears, as shown in Figure 1.4

Figure 1.4. Entering data into a newly created table, using the Retrieve Data From Table feature of Server Explorer

graphics/01fig04.jpg

You can enter data into this grid by typing; when you move off a row the data you enter is automatically saved in the database. Don't bother entering data into the ID field. Remember, because you designated it as an identity column when you created the table, the database engine will fill in an ID for you automatically when the new record is created.

Now that you've gone through the steps to create a single table in your database, you should be able to use Visual Studio.NET Server Explorer to model virtually any kind of database you require. However, at this stage there is one thing that can get in your way: the ability to model complicated relationships between multiple tables in a complex data design. You can use a database diagram to simplify that task.

Creating Database Diagrams

A database diagram is a visual representation of the tables in a database. You can use the diagramming features provided by SQL Server to create tables and the relationships between them visually. To create a database diagram in Visual Studio.NET's Server Explorer, do the following.

  1. Under the Novelty database node in Server Explorer, right-click on the Database Diagrams node. From the pop-up menu, choose New Diagram.

  2. The Add Table dialog box appears. It shows you a list of the tables that currently exist in the database; if you created tblCustomer earlier, it should appear here. Select it, click on Add, and then click on Close.

  3. A visual representation of the structure of tblCustomer is added to the diagram, as shown in Figure 1.5.

    Figure 1.5. Diagram for the Novelty database. The tables that you choose are automatically represented in the diagram.

    graphics/01fig05.jpg

  4. To add a second table to this diagram, right-click in the white space around tblCustomer and select New Table from the pop-up menu.

  5. The Choose Name dialog appears. Give this new table the name tblOrder.

  6. A table definition sheet appears in the diagram. Create fields for tblOrder, as shown in Figure 1.6.

    Figure 1.6. Field definitions for the new tblOrder table, created in the database diagram

    graphics/01fig06.jpg

  7. From the File menu, choose Save. A confirmation dialog appears, asking you if you want to save the table to the database. Choose Yes. The table should appear in your database definition in Server Explorer.

Now that you have tables for customers and orders, it makes sense to document the relationship that exists between them. Specifically, whenever an order is created, the ID of the customer will always be copied from the customer record's ID field to the CustomerID field of tblOrder. To reflect this action in the database diagram do the following.

  1. Click on the ID field in tblCustomer and drag to the CustomerID field in tblOrder.

  2. The Create Relationship dialog appears. The settings in this dialog box denote the properties of a relationship constraint that is being created between the two tables. Once this constraint has been created, you won't be able, for example, to create orders for customer IDs that don't exist in the database. This constraint is generally a good thing, so click on OK to confirm its creation.

  3. The database diagram is updated to reflect the new relationship, as shown in Figure 1.7.

    Figure 1.7. The database diagram for the Novelty database, denoting a relationship between tblCustomer and tblOrder

    graphics/01fig07.jpg

To finish your work here, choose File, Save DatabaseDiagram1 from the menu. In the Save Database Diagram dialog box, give the diagram the name Relationships. You should receive a warning dialog indicating that tables are about to be created in the database; answer Yes so that VS.NET can create tblOrder.

It's particularly useful that SQL Server creates and stores the database diagram within the database itself. Thus you can always get to the diagram, even from different tools. (You can manipulate database diagrams in SQL Enterprise Manager, as well as in VS.NET.)

Using Microsoft Visio to View and Alter a Database Schema

You may find it useful to use a graphical tool other than VS.NET to create, inspect, and modify database schemas. The diagramming tool Microsoft Visio has the capability to diagram database structures automatically; it can also easily reverse engineer nearly any kind of existing database structure. This capability makes this tool particularly useful for documenting and working with the database schemas of databases that were designed in the mists of time by programmers unknown.

Note

It isn't strictly necessary for you to know how to use Visio to set up a SQL Server database. It's just a different way of rolling database design and documentation tasks into a single set of operations. If you feel comfortable using Visual Studio's Server Explorer (or SQL Server's own Enterprise Manager tools), or if you don't have access to the version of Visio that comes with Visual Studio Enterprise Architect, you can safely skip this section.


Reverse engineering a database inspects an existing database schema and creates an entity relationship diagram (ERD) from it. An entity relationship diagram is a type of symbolic database design that focuses on the broad categories of data known as entities (typically stored in the database as tables).

To reverse engineer a database schema using Visio, follow these steps.

  1. Start Visio 2002 for Enterprise Architects. The Choose Drawing Type panel appears; select the Database category.

  2. From the Template panel, select Database Model Diagram. The basic Visio drawing window appears, as shown in Figure 1.8.

    Figure 1.8. The basic Visio drawing window. The drawing template appears on the left, and the drawing area is on the right. You create drawings by dragging items from the template onto your drawing.

    graphics/01fig08.jpg

  3. From the Database window, select Reverse Engineer. The Visio Reverse Engineer Wizard launches.

  4. From the drop-down list of Visio drivers, select Microsoft SQL Server.

  5. You'll next need to define a data source that will enable you to access the Novelty database. To do so, click on the New button.

  6. The Create New Data Source dialog box appears. It first asks you to specify which kind of data source to create. Select System data source and then click on Next.

  7. The next screen asks you to select a database driver (again). Choose SQL Server (again). You'll probably have to scroll down the list to get to the SQL Server driver. Click on Next and then click on Finish.

  8. Another dialog box, Create a New Data Source to SQL Server, appears. Enter the name Novelty for the data source. In the drop-down list labeled "Which SQL Server do you want to connect to?" choose (local). Then click on Next.

  9. Specify the authentication mode you use on your SQL Server which you should have specified when you installed SQL Server. (For more information on this topic, see the discussion of SQL Server authentication modes in Chapter 3.) Then click on Next.

  10. In the next screen, check the box labeled "Change the default database to:". From the drop-down list, choose the Novelty database. Click on Next and then click on Finish.

  11. The final dialog box, ODBC Microsoft SQL Server Setup, appears. It gives you the ability to test the connection to your data source by using the information you just provided. Click on the Test Data Source button to do run the test; it's always a good idea to be sure that it works, as a lot of information is required to create a connection to a data source. Once the connection has been verified, click on OK.

  12. You should be back at the Reverse Engineer Wizard, and the Novelty data source should have been automatically selected. Double-click on Next.

  13. When the wizard asks you to select the tables you want to reverse engineer, check both tblCustomer and tblOrder. Then click on Finish. Visio creates a diagram of your database, including the relationship between tblCustomer and tblOrder that you defined previously. This diagram is shown in Figure 1.9.

    Figure 1.9. The diagram generated by Visio's Reverse Engineer Wizard, showing the two tables in the Novelty database and the relationship between them

    graphics/01fig09.jpg

At this point you may be asking yourself, Why was that process so tedious and painful? The reason is that the Reverse Engineer Wizard kicked off a second wizard that created something called an ODBC data source. ODBC is an old Microsoft technology for providing interoperability between relational databases for application developers. (It's described in more depth in the VB6 edition of this book; it's not used extensively in VS.NET, so we're not repeating that discussion here.)

The important thing to know about ODBC is that, once you've created a named ODBC data source using the steps in this section, you don't have to do it again. The next time you need to work with the Novelty database on your computer, you can simply use the ODBC data source that you just defined.

You may now want to add another table to the database through Visio. Recall that Brad Jones's cocktail-napkin design for this database included the ability to divide customers into regions. Thus you'll need a table of regions, which you can add in Visio as follows.

  1. From the Entity Relationship template on the left side of the Visio window, click on the Entity shape and drag it onto the drawing area. A new entity (table) is created, initially labeled "Table1".

  2. Right-click on the entity shape that you just created and then select Database Properties from the pop-up menu. A Database Properties sheet appears at the bottom of the Visio window.

  3. Type the name of the table, tblRegion, into the Physical Name field.

  4. In the list of Categories in the Database Properties sheet, click on Columns. Create the three fields in the table definition by typing them into the grid. Note that, to denote the length of the char and varchar fields in the table, you must select the field and click on the Edit button on the right side of the property sheet.

When you're done, the graphic should look like Figure 1.10.

Figure 1.10. The Visio ERD diagram containing the new definition for tblRegion

graphics/01fig10.jpg

Note

The preceding method is a very simple way to create a database schema; however, more involved methods might suit your purposes better. In fact, Visio has a number of specialized templates for creating database diagrams.


There is a relationship between the new tblRegion and the existing tblCustomer (through the State fields that exist in both tables), which your diagram should reflect. You can create a relationship between two tables in Visio by using the Relationship shape, as follows.

  1. Click on and drag a Relationship shape onto your drawing. This shape is represented as a line with an arrow on one end. You should be able to see green squares (called handles) on each end of the line.

  2. Click on and drag one of the green handles onto the entity shape for tblRegion. The handle should turn red to indicate that it's not yet complete.

  3. Click on and drag the other green handle onto the entity shape for tblCustomer.

  4. In the property sheet at the bottom of the Visio window, select the State fields in both tables and then click on the Associate button. Your diagram should look like that in Figure 1.11. Note that the button located between the two listboxes showing you the column names of the two tables being associated will either appear as disabled or show Disconnect or Associate. The button is enabled and the text reads "Associate" when you have selected one column from each listbox.

    Figure 1.11. The Visio ERD diagram displaying the relationship between tblOrder and tblCustomer

    graphics/01fig11.jpg

Now that you've drawn the diagram for a new table in your database, you can use Visio to create the table in the database. To do so, select the Visio menu command Database, Update. The Visio Database Update Wizard will launch, asking you how you want to perform the update. You may want Visio simply to generate a Data Definition Language (DDL) script that will perform the necessary changes to your database; this decision will also have the side benefit of documenting the changes in case you need to replicate them later. (For more information on how DDL commands work, see Chapter 2.) Or you may simply want Visio to make the changes to the database easily. You have the option to perform either or both operations with Visio's Update Database Wizard.

Often, creating a graphical database schema will reveal flaws in your design. For example, the database design that you have so far enables the business to store information on customers and orders. But orders consist of items taken from the company's inventory and sold to the customer. With your current design, there's no way to see what the customer actually ordered.

The solution to this problem is to create a new table for items associated with an order. The design of this new table looks like the following.

tblOrderItem

ID

OrderID

ItemID

Quantity

Cost

There is a one-to-many relationship, then, between the tblOrder table and the tblOrderItem table. The database schema now should look like that in Figure 1.12.

Figure 1.12. The evolved database schema, including relationships among four tables in the database

graphics/01fig12.jpg

The complete Visio file is included in the downloadable source code for this book from the Addison-Wesley Web site, www.awprofessional.com.

Note

Don't confuse the process of developing a database schema with a software design methodology. Most successful software development organizations have a design methodology in place that dictates what business problems the software is supposed to solve, how the software application will look, how it will be built, and the like. You should consider all these issues before you design a database.




Database Access with Visual Basic. NET
Database Access with Visual Basic .NET (3rd Edition)
ISBN: 0672323435
EAN: 2147483647
Year: 2003
Pages: 97

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