Reviewing an Operational and Database Schema


The best way to see the difference between an operational/transactional database and a decision-support data warehouse database is to diagram a selection of tables. The following exercise will demonstrate some of the structural differences that have been discussed in this chapter. First, you'll create a database diagram of the is2005sbs database, a simplified version of the Adventure Works Cycles operational database. After that, you will create a database diagram of the is2005sbs database, a data warehouse schema based on the same set of sample corporate data.

Using each of these two databases, at the conclusion of this exercise, the objective is to run a query showing the total sales for each store in a specific state or province for a product subcategory and calendar year. Your users want to answer the question, "What were the total Mountain Bike sales for stores in the State of Washington during calendar year 2003?" After diagramming the tables, you will write a query for each database to obtain this information.

Creating a Database Diagram

SQL Server Management Studio includes a database diagramming tool that can be used to view existing tables and their relationships in the form of an Entity Relation Diagram (ERD). Optionally, changes made in a diagram can be captured as Data Definition Language scripts used to persist changes to the actual database schema. The first time a diagram is created, the diagramming tool components are installed.

Create a New Diagram for the is2005sbs Database
  1. Using the Object Browser in SSMS, expand Databases and the is2005sbs database.

  2. Right-click Database Diagrams and choose New Database Diagram from the menu. If this is the first time you have created a diagram, a dialog box might appear, asking if you want to create diagramming objects in the database. If this dialog box is presented, click Yes.

    image from book

  3. The Add Table dialog box displays all of the tables in the is2005sbs database. Select the following tables from this dialog box and add them to the diagram:

    • Address

    • Customer

    • CustomerAddress

    • Product

    • ProductSubCategory

    • SalesOrderDetail

    • SalesOrderHeader

    • StateProvince

    • Store

      image from book

      There are three ways to add tables from the Add Table dialog box window:

    • Click a table name, and then click Add.

    • Double-click a table name.

    • Press Ctrl, and then click each table name. Click Add.

  4. Close the Add Table dialog box when the tables have been added.

Organize the Diagram
  1. Resize and rearrange the tables in the diagram to make it more compact and easier to read.

  2. To organize these objects automatically, right-click the background of the diagram and choose Arrange Tables from the menu.

    It isn't necessary for all of the field names to be visible in a table window. The arrangement of tables is a matter of personal preference.

Tip 

Some of the table objects will likely be outside the designer's visible window. To navigate the entire design surface easily, click the compass points icon in the lower-right intersection of the scroll bars. Click, hold, and drag the mouse to move the visible window area to another location within the diagram.

You can rearrange and resize the tables manually to make them easier to read and to understand the diagram.

image from book

Note the relationship lines between each table. These were added to the diagram because of existing primary and foreign key constraints. Be sure not to delete or change these.

Note 

Note that all nine tables are required for the query: The store Name is in the Store table. A store is a type of Customer. The CustomerAddress and Address tables are necessary links to the StateProvince table. The Product table joins SalesOrderDetail (individual sales transactions) to ProductSubCategory, where you will find Mountain Bikes. The SalesOrderHeader contains the OrderDate, which will be used to derive the calendar year, and the SalesOrderDetail contains the LineTotal, which will be used to calculate the total sales amount.

Save the Diagram
  1. From the File menu, click Save Diagram_1.

  2. When prompted, name the diagram ResellerSales.

Query the is2005sbs Database
  1. To open a new query window, right-click the is2005sbs database in the Object Browser and choose New Query.

  2. On the File menu, click File, Open, and then File.

  3. Navigate to the C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005sbs\Chap12\ folder and open image from book Chap12 - is2005sbs Reseller Sales query.sql.

  4. This Transact-SQL query references all of the tables you added to the database diagram. Review the syntax of this query:

     USE is2005sbs SELECT   Store.Name AS StoreName        , SUM(SalesOrderDetail.LineTotal) AS SalesAmount FROM     Product INNER JOIN          ProductSubCategory          ON Product.ProductSubCategoryID =             ProductSubCategory.ProductSubCategoryID          INNER JOIN SalesOrderDetail          ON Product.ProductID = SalesOrderDetail.ProductID          INNER JOIN Customer          INNER JOIN SalesOrderHeader          ON Customer.CustomerID = SalesOrderHeader.CustomerID          INNER JOIN Store          ON Customer.CustomerID = Store.CustomerID          ON SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID          INNER JOIN CustomerAddress          ON Customer.CustomerID = CustomerAddress.CustomerID          INNER JOIN Address          ON CustomerAddress.AddressID = Address.AddressID          INNER JOIN StateProvince          ON Address.StateProvinceID = StateProvince.StateProvinceID WHERE    YEAR(SalesOrderHeader.OrderDate) = 2003          AND ProductSubCategory.Name = 'Mountain Bike'          AND StateProvince.Name = 'Washington' GROUP BY Store.Name, StateProvince.Name ORDER BY Store.Name 

  5. Execute the query by clicking Execute on the SQL Editor toolbar and make note of the time it takes for the query to complete.

    This is displayed in the status bar below the query window. The query returns 15 rows showing the stores and the total Mountain Bike sales in Washington State for calendar year 2003.




Microsoft SQL Server 2005 Integration Services Step by Step
MicrosoftВ® SQL Server(TM) 2005 Integration Services Step by Step
ISBN: 0735624054
EAN: 2147483647
Year: 2007
Pages: 152

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