Archive for November, 2016


Microsoft SQL Server 2000 – Database Design and Implementation…

November 15, 2016


Just got this book from PNM ( Perpustakaan Negara Malaysia ) – National Library. It’s quite an interesting book to read about using and managing SQL Server 2000. The book tells us how to do database design and how to implement it using Microsoft SQL Server 2000 Enterprise Management.

System Integration- SQL Server 2000 works with other products to form a stable and secure data store for Internet and intranet systems:
■ SQL Server 2000 works with Windows 2000 Server and Windows NT Server security and encryption facilities to implement secure data storage.
■ SQL Server 2000 forms a high-performance data storage service for Web applications running under Microsoft Internet Information Services.
■ SQL Server 2000 can be used with Site Server to build and maintain large, sophisticated e-commerce Web sites.
■ The SQL Server 2000 TCP/IP Sockets communications support can be integrated with Microsoft Proxy Server to implement secure Internet and intranet communications.
SQL Server 2000 is scalable to levels of performance capable of handling extremely large Internet sites. In addition, the SQL Server 2000 database engine includes native support for XML, and the Web Assistant Wizard helps you to generate Hypertext Markup Language (HTML) pages from SQL Server 2000 data and to post SQL Server 2000 data to Hypertext Transport Protocol (HTTP) and File Transfer Protocol (FTP) locations.

SQL Server 2000 is an RDBMS that is made up of a number of components. The  database engine is a modern, highly scalable engine that stores data in tables. SQL Server 2000 replication helps sites to maintain multiple copies of data on different computers in order to improve overall system performance while making sure that the different copies of data are kept synchronized. DTS helps you to build data warehouses and data marts in SQL Server by importing and transferring data from multiple heterogeneous sources interactively or automatically on a regularly scheduled basis. Analysis Services provides tools for analyzing the data stored in data warehouses and data marts. SQL Server 2000 English Query helps you to build applications that can customize themselves to ad hoc user questions. SQL Server 2000 Meta Data Services provides a way to store and manage metadata relating to information systems and applications. SQL Server Books Online is the online documentation provided with SQL Server 2000. SQL Server 2000 includes many
graphical and command-prompt utilities that help users, programmers,and administrators perform a variety of tasks

Table and Index Architecture – SQL Server 2000 supports indexes on views. The first index allowed on a view is a clustered index. At the time a CREATE INDEX statement is executed on a view, the result set for the view materializes and is stored in the database with the same structure as a table that has a clustered index. The data rows for each table or indexed view are stored in a collection of 8 KB data pages. Each data page has a 96-byte header containing system information, such as the identifier of the table that owns the page. The page header also includes pointers to the next and previous pages that are used if the pages are linked in a list. A row offset table is at the end of the page. Data rows fill the rest of the page, as shown in Figure 1.5.
Organization of data pages. SQL Server 2000 tables use one of two methods to organize their data pages—clustered tables and heaps:
■ Clustered tables. Clustered tables are tables that have a clustered index. The data rows are stored in order based on the clustered index key. The index is implemented as a B-tree structure that supports the fast retrieval of the rows based on their clustered index key values. The pages in each level of the index, including the data pages in the leaf level, are linked in a doubly linked list, but navigation from one level to another is done using key values.
■ Heaps. Heaps are tables that have no clustered index. The data rows are not stored in any particular order, and there is no particular order to the sequence of the data pages. The data pages are not linked in a linked list. Indexed views have the same storage structure as clustered tables.

SQL Server also supports up to 249 non-clustered indexes on each table or indexed view. The non-clustered indexes also have a B-tree structure but utilize it differently than clustered indexes. The difference is that non-clustered indexes have no effect on the order of the data rows. Clustered tables and indexed views keep their data rows in order based on the clustered index key. The collection of data pages for a heap is not affected if non-clustered indexes are defined for the table. The data pages remain in a heap unless a clustered index is defined.

Transact-SQL Debugger Window- SQL Query Analyzer comes equipped with a Transact-SQL debugger that enables you to control and monitor the execution of stored procedures. The debugger supports traditional functions, such as setting breakpoints, defining watch expressions, and single-stepping through procedures. The Transact-SQL debugger in SQL Query Analyzer supports debugging against SQL Server 2000, SQL Server 7.0, and SQL Server 6.5 Service Pack 2.

You can run Transact-SQL Debugger only from within SQL Query Analyzer.  started, the debugging interface occupies a window within that application, as shown . Transact-SQL Debugger window showing the result of debugging the Cust-OrderHist stored procedure in the Northwind database. When the Transact-SQL Debugger starts, a dialog box appears prompting you to set the values of input parameter variables. It is not mandatory for these values to be set at this time. You will have the opportunity to make modifications once the Transact-SQL Debugger window appears. In the dialog box, click Execute to continue with your session.

Due to connection constraints, it is not possible to create a new query while the debugger window is in the foreground. To create a new query, either bring an existing
query window to the foreground or open a new connection to the database. The Transact-SQL Debugger window consists of a toolbar, a status bar, and a series of window panes. Many of these components have dual purposes, serving as both control and monitoring mechanisms.

Only limited functionality might be available from some of these components after
a procedure has been completed or aborted. For example, you cannot set breakpoints
or scroll between entries in either of the variable windows when the procedure
is not running.

p/s:-  Some of the articles above is taken from the excerpt – Microsoft SQL Server 2000 – Database Design and Implementation. The book is written and published by Microsoft Press year 2003.

Practical Reporting with Ruby and Rails.


Finally, got this book from National Library – PNM ( Perpustakaan Negara Malaysia ) about Ruby and Rails. Most of the examples in this book use Active Record as a database access library. Active Record is a simple way to access databases and database tables in Ruby. It is a powerful object-relational mapping (ORM) library that lets you easily model databases using an object-oriented interface. Besides being a stand-alone ORM package for Ruby, Active Record will also be familiar to web application developers as the model part of the web application framework Ruby on Rails (see
Active Record has a number of advantages over traditional ORM packages. Like the rest of the Rails stack, it emphasizes configuration by convention. This means that Active Record assumes that your tables and fields follow certain conventions unless you explicitly tell it otherwise. For example, it assumes that all tables have an artificial primary key named id (if you have a different primary key, you can override it, of course). It also assumes that the name of each table is a pluralized version of the model (that is, class) name; so if you have a model named Item, it assumes that your database table will be named items.

Active Record lets you define one or more models, each of which represents a single
database table. Class instances are represented by rows in the appropriate database table. The fields of the tables, which will become your object’s attributes, are automatically read from the database, so unlike other ORM libraries, you won’t need to repeat your schema in two places or tinker with XML files to dictate the mapping. However, the relationships between models in Active Record aren’t automatically read from the database, so you’ll need to place code that represents those relationships in your models.Creating a model in Active Record gives you quite a few features for free. You can automatically add, delete, find, and update records using methods, and those methods can make simple data tasks very trivial.

Grouping refers to a way to reduce a table into a subset, where each row in the subset
represents the set of records having a particular grouped value or values. For example, if
you were tracking automobile accidents, and you had a table of persons, with their age and number of accidents, you could group by age and retrieve every distinct age in he database. In other words, you would get a list of the age of every person, with the duplicates removed.

If you were using an Active Record model named Person with an age column, you
could find all of the distinct ages of the people involved, as follows:
ages = Person.find(:all, :group=>’age’).

However, to perform useful work on grouped queries, you’ll typically use aggregate functions. For example, you’ll need to use aggregate functions to retrieve the average accidents per age group or the count of the people in each age group. You’ve probably encountered a number of aggregate functions already. Some common ones are MAX and MIN, which give you the maximum and minimum value; AVG, which gives you the average value; SUM, which returns the sum of the values; and COUNT, which returns the total number of values. Each database engine may define different statistical functions, but nearly all provide those just mentioned. Continuing with the Active Record model named Person with an age column, you could find the highest age from your table as follows:
oldest_age = Person.calculate(:max, :age)
Note that calculate takes the max function’s name, as a symbol, as its first argument,but Active Record also has a number of convenience functions named after their respective
purposes: count, sum, minimum, maximum, and average. For example, the following two
lines are identical:
average_accident_count = Person.calculate(:avg, :accident_count)
average_accident_count = Person.average(:accident_count)

You have many choices for creating charts with Ruby. For example, you can do simple
charting in straight Hypertext Markup Language (HTML) and Cascading Style Sheets (CSS). Chapter 7 shows you how to use Markaby, a templating language for Ruby, to create your own HTML bar charts. Chapter 11 demonstrates how to use CSS helpers to create charts in Rails. Here, we’ll look at the Gruff and Scruffy graphing libraries, and then use Gruff in a couple of examples. Gruff ( provides a simple, Ruby-based interface to enter data and display details. After writing the code, you call a simple command to render the graph to a file. For example, if you had a collection of vintage guitars and wanted to display a simple bar chart with their values.

Generally, clients love spreadsheets. Often, they don’t have the expertise to manipulate data using SQL or a programming language like Ruby, but they do know how to perform calculations and analyze data using Microsoft Excel or a similar tool. If their data is directly delivered in their format of choice, they can skip a step and save time. (In fact,some less computer-savvy users may not realize that they can copy and paste data from a web page, so exporting to an Excel-compatible format may enable them to act on data in ways they could not before.)

p/s:- The article above is taken from the excerpt from the book Practical Reporting with Ruby and Rails , written by David Berube and publish by Apress year 2008.