Step 2: Create a Data Model

A data model is a detailed description of the various pieces of data required by an application. Data modeling is the process of identifying, documenting, and implementing the data required by an application and usually involves:

• Identifying each piece of data required to perform the business operations that the application is being designed to automate.
  
  
• Defining the type, size, and default value of each piece of data.
  
  
• Defining relationships between the various pieces of data. (For example, the customer ID is used to relate customer orders to customer accounts that are maintained in a separate table.)
  
  
• Defining the integrity constraints required by each piece of data. (For example, the ZIP code must contain exactly five alpha characters, there can only be one student with an ID value of 12345, and so on.)
  
  
• Organizing the data to avoid redundancy and inconsistency while maintaining optimal overall performance—a process commonly known as normalization.
  
  
• Defining the operational processes required to effectively maintain the data (for example, scheduled backups, security audits, and so on).
  
  
• Choosing a data storage technology to actually implement the model (for example, a Relational Database Management System (RDBMS), a hierarchical database system, an Indexed Sequential Access Method (ISAM) database system, and so on).

The fundamental design goal of the DNA PurchaseOrder application is to allow users to purchase books by submitting purchase order requests, which means the application must have a detailed description of the various pieces of data that ultimately constitute a single purchase order. The data used to describe each individual purchase order is kept in the PurchaseOrders table (see Table 1).

Table 1. The PurchaseOrders Table

Notice that the PurchaseOrders table (Table 1) doesn't contain a single reference to any line item data, which is data that describes the quantity, description, price, and so on, of each item purchased as part of a particular purchase order. That's because line item data is kept in a separate LineItems table (see Table 2). The line items are related to their respective purchase orders using a one-to-many relationship between the PurchaseOrders table and the LineItems table. This means that for every row in the PurchaseOrders table there may be many rows in the LineItems table. The one-to-many relationship between the PurchaseOrders table and the LineItems table allows each purchase order to accommodate an arbitrarily different number of line items.

Table 2. The LineItems Table

In order for the DNA PurchaseOrder application to achieve its fundamental design goal—to allow users to purchase books by submitting purchase order requests—the application must maintain information regarding the various books that are for sale. The Inventory table (see Table 3) maintains this information. The Inventory table is consulted when the DNA PurchaseOrder application presents its users with the list of books that are available for purchase. The Inventory table is also consulted when the DNA PurchaseOrder application processes purchase orders to ensure that each book being purchased is actually "in stock." If a particular book is not in stock, the entire order is aborted.

Table 3. The Inventory Table

When users purchase books using the DNA PurchaseOrder application, the total sale price (including any tax and shipping charges) is charged against their store account. The Accounts table (see Table 4) maintains information regarding each customer account. The Accounts table is consulted when the DNA PurchaseOrder application processes purchase orders to ensure the customer has enough available credit to facilitate the purchase. If the customer does not, the entire order is aborted.

Table 4. The Accounts Table

Each of the previous tables has a primary key (IDPK). However, none of the primary keys are identity columns. An identity column is a special column containing a unique value that is automatically incremented by the database whenever a row is inserted into the table. To understand why none of the primary keys are identity columns, recall that the purchase order ID is used to relate a purchase order with one or more line items. This means that before a line item can be inserted into the LineItems table, the purchase order to which it belongs must already exist in the PurchaseOrders table. However, part of the process of inserting a purchase order is inserting each of its corresponding line items and, unfortunately, there is no way to obtain the identity column value for a row before the row is actually inserted into the table. Therefore, the primary key values for each row are generated by the application itself. The DNA PurchaseOrder application generates primary key values in batches of 100 by simply incrementing a numeric value that is cached and managed by the Microsoft Transaction Server (MTS) Shared Property Manager (SPM) and ultimately persisted in the UniqueIDs table.

Table 5. The UniqueIDs Table

Figure 13. An illustration of the DNA PurchaseOrder application data model

To implement the data model for the DNA PurchaseOrder application

1. Start the SQL Server Enterprise Manager if it's not already running.
   
   
2. Select the Databases node in the left-hand pane.
   
   
3. To create a new database, right-click the Databases node and select New Database.
   
   
4. Type DNADesign for the name of the new database, and click OK.
   
   
5. From the SQL Server Enterprise Manager Tools menu, select SQL Server Query Analyzer.
   
   
6. Select the DNADesign database in the DB drop-down list box.
   
   
7. Execute the SQL scripts in Listings 1-5 sequentially to create the Accounts, Inventory, PurchaseOrders, LineItems, and UniqueIDs tables.

If everything was successful, you should be able to select the Databases\DNADesign\Tables node in the left-hand pane of the SQL Server Enterprise Manager and see the Accounts, Inventory, PurchaseOrders, LineItems, and UniqueIDs tables in the right-hand pane.

To access the DNADesign database, the DNA PurchaseOrder application must obtain the necessary connection information. One of the easiest ways to provide data source connection information is to create a system data source name (DSN).

To create a system DSN

1. From Control Panel, select ODBC Data Sources.
   
   
2. From the System DSN tab, click Add to create a new System DSN.
   
   
3. Select the SQL Server driver and click Finish.
   
   
4. Type DNADesign for the name of the new system DSN, select the (local) SQL Server, and click Next.
   
   
5. Select the SQL Server authentication option (radio) button, provide the appropriate authentication information (Login ID = sa, Password = ), and click Next.
   
   
6. Select DNADesign as the default database and click Next.
   
   
7. Click Finish to accept the defaults for the remaining configuration wizard options.
   
   
8. Click Test Data Source to validate the newly created DNADesign system DSN.