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.
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.
Using the Object Browser in SSMS, expand Databases and the is2005sbs database.
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.
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
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.
Close the Add Table dialog box when the tables have been added.
Resize and rearrange the tables in the diagram to make it more compact and easier to read.
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.
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. |
From the File menu, click Save Diagram_1.
When prompted, name the diagram ResellerSales.
To open a new query window, right-click the is2005sbs database in the Object Browser and choose New Query.
On the File menu, click File, Open, and then File.
Navigate to the C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005sbs\Chap12\ folder and open Chap12 - is2005sbs Reseller Sales query.sql.
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
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.