Supporting the Sample Application | Microsoft SQL Server 2005 Notification Services

As mentioned earlier, the application we start to build in this chapter is a notification service for an online digital music store. Our application enables the store's customers to subscribe for notifications about new music added to the store's catalog.

Many online digital music stores are in business todayMSN Music (http://music.msn.com) is one example. The kind of application we're going to build over the next few chapters could be targeted for any of the existing music stores. But because we don't have access to the internals of any of these real online music stores, our application will be built against a fictional music store that we create. We'll have complete control over our fictional music store's data and processing, which we'll need during the testing of our notification application.

This fictional music store we create in this chapter is a simple model of a real music store; it captures only the key elements necessary to build our notification application. Specifically, the model consists of a database that represents the store's catalog of songs and a mechanism to add songs to this catalog. Other parts of a typical music store, such as the business logic and the infrastructure for conducting real e-commerce transactions, are absent from our simple model because they are not relevant to the notification application.

This section describes the design of the model music store database and the steps you'll need to follow to create it on your system. Technically, this isn't part of the normal notification application development process (if you were a developer building a notification application for a real online music store, this infrastructure would already exist). You just need to perform these steps here to support the sample application. After you complete these steps, you'll be ready to start designing and prototyping the notification application in the next section.

The Music Store Database Schema

The music store database used in this chapter is simple. I've designed a basic schema that models the entities and relationships that compose the music store's catalog, as shown in Figure 5.3. Of course, this schema is not nearly sophisticated enough to be used in a real music store, but for the purposes of illustration in this chapter, it is sufficient.

Figure 5.3. Schema for the music store catalog.

As Figure 5.3 shows, the fundamental entity is the song, which has a title field, as well as the date it was added. Songs are related to albums, which also have title and date added fields. Albums are related to artists and genres, which are stored in separate tables.

This simple, normalized schema works well for our purposes, but it has some obvious limitations. For example, it is not possible to model an album that contains songs by different artists. Nor is it possible to model a song that has more than one artist. However, because the goal of this chapter is not to build the best database design for a music store, but rather to show how a notification application can be built against one, we'll ignore these limitations and proceed with this simple schema.

Creating the Music Store Database

I've provided a set of batch files and SQL scripts that you can use to create the music store database on your system and populate it with data. Follow the instructions in this section to set up the music store database on your system. You need to complete these instructions before building the notification application in the next section.

Note

All the instructions in this chapter assume that you have set up your development environment as described in Chapter 2, "Getting Set Up." If you have not already done this, go back to Chapter 2 and follow the instructions for setting up your system before continuing here.

As in previous chapters, I assume that you installed this book's source code in the C:\SQL-NS directory. If you installed the source code in a different directory on your system, replace C:\SQL-NS with the name of that directory in all the following instructions.

If you're using SQL Server Authentication, many of the scripts in this chapter's instructions prompt you for the SQL password of the development or test account set up in Chapter 2.

From a Notification Services Command Prompt on your development machine, navigate to the scripts directory for this chapter's sample by typing the following command:

 cd /d C:\SQL-NS\Samples\MusicStore\Scripts

Run create_music_store_database.cmd. If you receive an error message when running this batch file, refer to the caution box in this section for possible remedies.

This batch file calls sqlcmd to run two SQL scripts, located in the sample's root directory (C:\SQL-NS\Samples\MusicStore):

CreateMusicStore.sql Creates the music store database and the tables, views, and stored procedures in it
PopulateMusicStore.sql Inserts some sample data into the music store

Caution

If the C:\SQL-NS\Samples\MusicStore\CreateMusicStore.sql file appears to be missing on your system (running create_music_store_database.cmd results in an error message that reads Sqlcmd: 'C:\SQL-NS\Samples\MusicStore\CreateMusicStore.sql' : Invalid filename), you probably have not completed all the setup steps given in Chapter 2. If this is the case, return to Chapter 2 and make sure you complete the steps in the "Customizing the Source Files for Your Environment" section (p. 39).

If your SQL Server is remote (not on your development machine), running create_music_store_database.cmd may fail with an error message that reads CREATE DATABASE failed. Some file names listed could not be created. Check related errors. This occurs if the paths specified in CreateMusicStore.sql for the music store database's physical files do not exist on the remote SQL Server machine. If you see this error message on your system, you will need to edit C:\SQL-NS\Samples\MusicStore\CreateMusicStore.sql. Open the file in a text editor and find the CREATE DATABASE statement near the top. Within the CREATE DATABASE statement, several file paths are specified. Notice that these all refer to directories on your development machine. Edit these paths so that they refer to valid locations for creating data and log files on your SQL Server machine. Save C:\SQL-NS\Samples\MusicStore\CreateMusicStore.sql and then run create_music_store_database.cmd again.

You can examine these SQL scripts if you're curious about the internal structure of the music store database. Notice that CreateMusicStore.sql creates all the database objects for the music store's catalog (those shown in Figure 5.3, as well as the supporting stored procedures and views) in a schema called Catalog.

As described in the previous section, the data about each song is normalized into several tables. Although this makes sense from a schema design perspective, it is inconvenient to work with from our notification application. To get all the properties of each song, we would have to join all these tables. Because it will be common to query all the song properties together, the music store database provides a view that shows all the song details in a single place. This view effectively denormalizes the song data so that it's easier to work with. Let's take a look at this view:

1.	Open Management Studio and log in to your SQL Server.
2.	Find the `MusicStore` database under the Databases folder in the Object Explorer and click the plus sign to expand its node.
3.	Expand the `Tables` node under the `MusicStore` database. You'll see four tables corresponding to the entities illustrated in Figure 5.3 listed (`Albums, Artists, Genres`, and `Songs`). These table names are prefaced with the schema name `Catalog` to isolate them from other objects we'll put in the database later.
4.	Expand the `Views` node under the `MusicStore` database and you see the `SongDetails` view listed (like the other objects, it's also created in the `Catalog` schema). This is the view that the notification application operates against.
5.	Open a new query window and issue the following query: USE MusicStore GO SELECT * FROM [Catalog].[SongDetails] This returns a set of rows showing various songs in the database. The data you see was inserted by the `PopulateMusicStore.sql` script you saw earlier.

Although the notification application we're going to build uses the SongDetails view, I encourage you to take a look at the other tables and stored procedures in the music store database to become familiar with its structure.