Striking up a Relationship with Your Data A

SQL Server 2005 does not have a diagramming tool, but Microsoft Visio 2000 has a technique known as reverse engineering a database to create a diagram of the related groups of tables in a database. This is the technique that I use to create the ER diagram shown in Figure 2-1. It pays to have a closer look at Figure 2-1, so here goes. The most detailed table in the diagram is SalesOrderDetail. A foreign key FK1 in this table relates to the Product table. The Product table, in turn, is related to the product subcategory (ProductSubCategory) and the product category (ProductCategory) tables. The information from these product-related tables can be combined to define a product hierarchy. A hierarchy describes how particular business terms are grouped into categories, which in turn are grouped into higher level categories. For example, the product hierarchy represents how products roll up (indicating the hierarchy of products) to subcategories and then to categories. A territory hierarchy, on the other hand, represents how salespersons roll up to territories. Each grouping within a hierarchy is a level of the hierarchy. The product hierarchy has three levels (products, subgroups, and groups), and the territory hierarchy has two levels (salesperson and sales territory). Figure 2-1: Entityrelationship diagram of the Sales Order model from the Adventure Works database. 32 Part I: Just the Basics

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Clan Web Hosting services

Striking up a Relationship with Your Data A

Striking up a Relationship with Your Data A relationship is a connection between tables in the database. As you might expect, many types of relationships can exist in a database. The one-to-one relationship, for example, means that for each row in one table, there is at most one associated row in another table. If you want to get concrete, I can tell you that for each husband, there is at most one (legal) wife, and a wife has at most one (legal) husband at least in this country. You can also have many-to-one relationships, indicating that for a given row in a table, there can be multiple related rows in another table. A woman, for example, can have zero or more children. And then there are many-to-many relationships, in which there are no restrictions on the number of rows related to the row of a specific table. As I discuss in the preceding section, a foreign key in a database table is a key from another table that is contained within the table in question. The foreign key in a specific table is most often the primary key of the related table. Foreign keys complete an association or relationship between two tables by identifying the parent entity. Foreign keys provide a method for navigating between different instances of an entity (sometimes referred to as referential integrity). Every relationship in a database must be supported by a foreign key. The rule for referential integrity is that if a relational table has a foreign key, every value of the foreign key must either be null or match the values in the relational table in which that foreign key is a primary key. As you might expect, a relational database is in fact a set of tables with relationships. The job of a DBA is to select the appropriate relationships for the database being modeled. When the DBA completes a model for a database, he or she publishes it in the form of an entity-relationship (ER) model. An ER diagram is the document that communicates how all the tables in a database are related. It is also the roadmap for how to query a database for reporting purposes. You can see an example of an entity-relationship diagram in Figure 2-1. (The sample AdventureWorks database depicted here comes with SQL Server, so it s going to serve as my handy sample database of choice in this and many other chapters of this book.) The relationships between sales order, sales detail, territory, salesperson, and customer are clearly shown in this ER diagram, so it s easy to see how tables link up. Note that the scope of this diagram is not the entire database but merely the Sales Order table relationships in the database. This diagram was created as a database diagram with the SQL Server 2005 Management Studio, which I talk more about in Chapter 12. Chapter 2: Retrieving Data from a Database 31

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Clan Web Hosting services

Chapter 2 Retrieving Data from a Database In

Tables are the backbone for any database. Each table comprises columns and rows designating attributes of the data you want to store. The best way to envision a table consisting of columns and rows is to think of a table as a grid in which the columns and rows of the grid correspond to the various attributes for that table. For example, the ORDER table might contain rows for each order and columns for the date the order was entered, who entered the order, the customer for which the order was placed, the promised ship date of the order, and so on. The specific products contained in a customer order might be in another related table called ORDER DETAILS. Each column has a data type associated with it that describes what type of information is stored in the column. If the column is to contain a number, the data type is a numeric data type. Data types can be numeric, text, decimal, currency, notes, and so on. Keying In on Your Data For any given table in the database, you have at least one attribute that uniquely identifies it. (There might be an order number column for this purpose, for example, conveniently called OrderID.) In the world of database management, you call such a column (or set of columns) whose value uniquely identifies every row in a table a primary key. An example of a primary key for a PERSON table is an American s Social Security number because this number is unique for every person in the United States. Having a primary key is great because when you later have to manipulate all the data you ve stored, you can access each record uniquely by referring to the value of its primary key. Any primary key you choose to set up for a table has to meet the following common-sense requirements: No two rows can have the same primary key value. Every row must have a primary key value assigned to it. The column containing the primary key value cannot be modified or updated. Primary key values can never be reused. In your database travels, you are likely to encounter the concept of a foreign key. Don t despair or think that you suddenly have to learn some foreign language in order to crack a secret code. A foreign key is simply a primary key for one table that is kept in another table to make it easier to join the two tables to query information from two related tables. 30 Part I: Just the Basics

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

Chapter 2 Retrieving Data from a Database In

Chapter 2 Retrieving Data from a Database In This Chapter Finding out about database concepts Looking at database diagrams Building an SQL query In Chapter 1, I start you off with tons of great info about how a cool new reporting tool Microsoft SQL Server 2005 Reporting Services can help you write better reports. (I know, because I put the info there.) What Chapter 1 doesn t cover are the basic, everyday database concepts concepts that might prove useful to anyone who, say, actually wants to work with a database. And this is why Chapter 1 inevitably leads to Chapter 2. Rest assured that after reading this chapter, you ll have enough background and knowledge to be able to handle most of what you ll ever encounter when accessing data from relational databases. Okay, you might come across some higher-end issues that a real database administrator (DBA) should deal with, but even there, I show you how to phrase a request so that your organization s DBA will be impressed by your sharp business sense and acumen. Discovering What Makes a Database A database is nothing more than a collection of data stored in some organized fashion. Databases use the concept of tables as the organizing scheme. Just like working with a filing system to organize your papers, tables are used to store structured data of a specific type. And as long as I m defining stuff, another name for a table is an entity a thing that can be distinctly identified. For example, orders and customers are examples of entities. They are things that are distinct from each other but relevant to your business. In a relational database named SALES, you d expect to find two distinct entities named ORDER and CUSTOMER.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

4. Click the Back button when you finish

4. Click the Back button when you finish viewing the report. Alternatively, you can link back to the original report folder by clicking the link on the breadcrumb trail in the top-left of the Report Manager window. Figure 1-3: Report Manager with the Product Line Sales report displayed. Chapter 1: Getting Familiar with Reporting Services 27

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Inexpensive Web Hosting services

1. Connect to the Report Manager by pointing

3. Select the report that you want to view and click it. Report Manager displays the report, as shown in Figure 1-3. Notice that the report shows all the information based on any parameter value sentered as well as on the security enforced on the report server. If this is an interactive report, you might see rows highlighted, indicating that a hyperlink exists to another report or a bookmark within the same report. Reporting Services reports can be developed to include graphics like bar and pie charts that are linked to the data shown in the same report. You can also design parameters in the report so that the user can change the parameter value and rerun the same report and see an entirely different slice of the data in the report. For example, you can select different product categories or different time ranges to see a different slice of the information. Note that there is an option on the toolbar to export to a different format. This is a way to change the reported information into a spreadsheet or a PDF for sharing and collaborating with others. Figure 1-2: Report Manager Sample Reports folder with specific reports available for viewing. 26 Part I: Just the Basics

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Clan Web Hosting services

1. Connect to the Report Manager by pointing

1. Connect to the Report Manager by pointing your favorite Web browser to http://localhost/reports. The Report Manager home page appears, as shown in Figure 1-1. You ll see a folder for Sample Reports as well as any other folders defined on your report server. You ll also see the menu options related to site settings and subscriptions. The view in the figure shows how it looks after you click the Show Details button, which then toggles to a Hide Details button (shown in the upper-right of Figure 1-1). You can see who created the folders and when they were last modified. 2. Select the folder that you want to view and click it. Report Manager displays the contents of the Sample Reports folder, as shown in Figure 1-2. The folder consists of reports, resources, and (possibly) other folders defined on the report server. Each report in the Sample Reports folder can have a description (if provided) as well as a listing of the last date modified and who modified it. It might also show the last date run if configured in this way. You can click the column headings to sort all contents in that column in either an ascending or descending order. You can edit report properties by clicking the Edit symbol the tiny-hand-over-the-report-page icon. You can also move or delete reports by marking the check box in the far-left column and clicking the Delete or Move button. Figure 1-1: The Report Manager home page is the entry point for viewing reports. Chapter 1: Getting Familiar with Reporting Services 25

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Clan Web Hosting services

Distributing a report The most popular way to

Printed reports versus online reports Reading a printed report doesn t require any technical sophistication. And with a printed report, you get a paper copy of key information that you can refer to. Paper reports, however, do not offer any interactivity where you might click on some hyperlink to further explore the underlying details of some information on the report. It is helpful to display the date and time when the report was run, as well as the time when the data was last refreshed. This allows you to budget the currency and relevance of the information displayed in the report. But there are a few limitations in having access only to a printed report: The report is not connected to a live data source. With a live connection to the data source, you can get information since the latest refresh of the database. For daily cumulative reporting, it is best to work with live data source connections if you really want to understand the current dynamics of the business. Graphical information has a single shade of gray display. This can present some difficulties when you review a bar chart with a multitude of different categories, but your printer produces only black and white. Using colors yes, that includes shades of gray improves the visibility of the dynamics of your business as viewed through the reports. You have no drill-down capability. With a live database connection on a report with drill-down capabilities, you can easily navigate to see a more detailed perspective of the information presented from a summary report. You also have a choice on what areas you want to drill down into detail. This need might change from period to period, so a live report with dynamic drill-down capability in several report variables clearly provides a much better analysis. You cannot take hyperlinks that jump from one view to another view. With hyperlinks, you can set up jumping within the report to link to a section grouping of the report, or you can set jumping outside the report to go to another report entirely or to a hyperlink on the Internet. Without the ability to hyperlink, you might have to flip through lots of reports trying to locate the data that you re trying to analyze. Viewing a report Viewing a report with Reporting Services requires that your computer at least have a connection to the server that s running the report server. If you ve installed the sample reports from the Reporting Services installation CD, you should be able to view a sample report by following these steps: 24 Part I: Just the Basics

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Inexpensive Web Hosting services

Distributing a report The most popular way to

Distributing a report The most popular way to distribute a report is to print it. The printed report is then copied for all report consumers and distributed at an appointed time. Footers usually record the time the report was run as well as the time the data source was last refreshed. Many years ago, environmentalists took the bull by the horns and recommended to corporate America that Business (with a capital B) should consider how many trees were being chopped down in the name of printed report distribution. Deforestation worries intensified the environmentalists resolve. Even recycling experts could not make a decisive argument against the environmentalists, so corporate America began to embrace the online report distribution best practice. The ubiquitous Internet came along and further popularized this trend, which means that reporting in the modern age is becoming increasingly paperless. To accomplish paperless reporting, Web services like Reporting Services are required. Using Reporting Services, you can author a report and then publish it to a folder to be accessed by Report Manager. On-demand reports are reports that do not cache temporary copies of reports when they are run. Subscriptions can be defined to push reports to users: Subscriptions can be scheduled to execute regularly to deliver rendered reports to end users. One delivery option for subscriptions is to have the subscription send an e-mail containing a hyperlink, a notification, or a rendered report attachment. Another delivery option is to have the subscription execute a file delivery to send the report to a file location that an intranet can access easily. Accessing and Looking at Reports Now you can catch your breath after that rapid tour of Reporting Service capabilities. The following chapters of this book dive much more deeply into each of these subject areas. One of the very first things that you might want to do with this new tool is to see how you can get to a report and view it. Consider this dipping your toes into the vast ocean of Reporting Services. You can follow this road with me if you have access to the sample reports provided in the Reporting Service installation software. (You ll find the sample reports in the file system folders that you name during the installation process.) Chapter 1: Getting Familiar with Reporting Services 23

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Inexpensive Web Hosting services