SQL

What is Database ?

SQL Server uses a type of database called a relational database.  In relational databases, data is organized into tables.  Tables are organized by grouping data about the same subject and contain columns and rows of information.  The tables are then related back to each other by the database engine when requested.  Tables are closely related back to each other by the database engine when requested.   Tables are closely related to something called a relation, or entity in proper theory books, but we’re trying to be practical over here.

You can generally think of a database as a collection of related data.  In some earlier database products, a database was usually just a file-something like employee.dbf, which contains a single table of data.  Inside the employee.dbf file were columns relating to employee data, such as salary, hire date, name, Social Security number, and so on.  The file contained a row for each person in the company, which corresponding values in the appropriate columns.  Indexes, used to speed data access, were in a separate file, as was any security-related item.

In SQL Server 2000, a database isn’t necessarily tied to a single file; it’s more of a logical concept based on a collection of related objects.  For example, a database in SQL Server contains not only the raw data, but it also contains the structure of the database, any indexes, the security of the database, and perhaps other objects such as views of stored procedures related to that particular database.

Relational Database Objects :

As you just saw, a relational database is composed of different types of objects.  These objects are all described in more detail in the particular day’s lesson that applies to them.  The following are some of the more common objects;

v     Tables are the objects that contains the data types and actual raw data. 

v     Columns are the parts of the table holding the data.  Columns must be assigned a data type and unique name.

v     Data types are the base storage type of your data.  You can choose from various data types, such as character, numeric, or date.  A single data type is assigned to each column within a table.

v     Stored Procedures are like macros in the Transact-SQL code that can be written and stored under a name.  By executing the stored procedure, you actually run the T-SQL Code within the procedure.  One use would be to take the T-SQL Code that runs a weekly report, save it as a stored procedure, and from then on just run the stored procedure to generate the report.  You can also use stored procedures as security mechanisms.

v     User-defined functions are Transact-SQL code that’s very similar to stored procedures.  However, functions can be called in your database queries either to modify a column of data you want to view or to act as tables, even though they’re built programmatically and dynamically.  An example might be that you could write your own date functions to modify columns of the datetime data type.

v     Triggers are stored procedures that activate either before or after data is added, modified, or deleted from the database.  They ensure that business rules or other data integrity rules are enforced in the database.  For example, a trigger can ensure that every book in a bookstore has a valid publisher assigned to it.

v     Views are basically queries stored in the database that can reference one or many tables.  You can create and save them so that you can use them easily in the future.  Views usually either exclude certain columns from a table or link two or more tables.  You can also use them as security mechanisms.

v     Indexes can help organize data so that queries run faster.

v     Primary Key ,  although not objects per se, are essential to relational databases.  They enforce uniqueness among rows, providing a way to uniquely identify every item you want to store.

v     Foreign keys  are one or more columns that reference the primary keys or unique constraints of other tables.  SQL Server uses primary and foreign keys to relate the data back together from separate tables when queries are performed.

v     Constraints are server-based, system-implemented data-integrity enforcement mechanism.

v     Rules are assigned to columns so that data being entered must conform to standard you set.  For example, you can use rules to make sure that person’s phone number contains only numbers.  Rules have been functionally replaced by CHECK constraints in SQL Server 2000.

v     Defaults  can be set on fields so that if no data is entered during an Insert operation, default values are used.  An example is setting the area code for the area when most of your customers come from, which saves you from entering the area code for local customers.  Defaults have been functionally replaced by DEFAULT constraints in sQL Server 2000

Designing Relational Databases

As a SQL server administrator, you will likely be given a relational database that has been designed by someone else; using such a database doesn’t mean you can be clueless when it comes to designing a relational database.  Knowing some do’s and don’ts about designing databases and knowing about normalization can only help you in your job.

Although the process of designing a good relational database could fill a book by itself, the following are some basic steps to consider:

v     Analyze the situation to gather information about the proposed database.

v     Decide on columns, data types, and lengths of data.

v     Normalize the data into tables.

v     Create the database and tables.

When you organize related data into relational tables, you are following normalization rules.

The design process should start with a good look at the business situation and what the customer is trying to accomplish.  Brainstorming about different variables and how they all fit together into tables is the next step.  The process then moves to designing reports and queries that will benefit the users, as well as other pieces of the design, including access to web pages.

The following do’s and don’ts will help you during the design process.  Remember, in the end you’re building a solution to solve a business problem, so you need to remain focused on the problem you’re solving and not get too worried about using the perfect technical terms to describe it.

DO	Don’t
DO ask the users what they need.	Don’t ignore the users (also known as customers)
DO create a list of objects.	Don’t create objects you will never use.
Do keep object names short yet descriptive	Don’t use complex names, names with spaces, or names with unusual characters because they are harder to type.
Do Organize properties of objects into correct grouping.	Don’t have a column that contains more than one value.
Do create identically named columns in different tables to relate them back together.  These columns become your primary and foreign key	Don’t create tables with a huge number of columns.
Do test your design with some sample data.	Don’t assume that because your design works well with 5 rows, it will perform well with 500,000 rows
Do create at least one index for tables that will be queried.	Don’t create a lot of indexes(more than five) per table.
Do design your tables with security in mind.	Don’t forget to set up security on your data.
Do document table names, column names, and primary and foreign keys.	Don’t lose your documentation.
Do follow  a standardized naming convention for your database objects. Following this convention can greatly simplify working with your objects.  We like to use prefixes.  For example, use tblEmployee for a table object named Employees and idxLastName for an index based on last name.

What Kinds of SQL Servers are Available ?

A great first question to ask yourself is, “Which SQL Server do I need?”  Microsoft is simultaneously releasing six editions of SQL Server 2000.  After you examine their requirements or needs, it should be obvious which one to use.  However, the most important point to remember is that, regardless of the edition of SQL Server you choose, they are all built on a common code base, so that same rules, conditions, and administrations apply.

Standard Edition

It is what most people mean when they refer to SQL Server 2000.  This version of the product supplies full functionality and is intended to run on windows NT Server 4.0(SP5) or later, as well as a Windows 2000 Server computer.  It also runs on the Enterprise Edition of both Windows NT 4.0 and Windows 2000.  This version supports upto four central processing units(CPU) and up to 2 GB of Random Access Memory.

Enterprise Edition

Enterprise Edition of SQL is for very high-end installation, or installations that requires the best performance from SQL Server.  It runs on Windows NT Server 4.0(SP5), or Windows 2000 Server, Advanced Server, or Data Central Server, and provides features such as large memory support(Up to 64 GB or RAM), Microsoft Clustering Support(high availability support for up to four cluster nodes), and support up to 32 CPUs.

Personal Edition

The personal edition runs on Windows 98  and Windows ME, Windows 9x henceforth-and Windows NT Workstation 4.0 or Windows 2000 Professional.  It is meant as a development and remote SQL Server installation to support a central server.

Developer Edition

The developer edition of SQL Server 2000 is the Enterprise Edition.  Therefore, if you use the developer version of the product, you are actually using the Enterprise Edition.

Evaluation Edition.

It is also Enterprise Edition but for limited time period.

Microsoft SQL Server 2000 Desktop Edition (MSDE)

It is handicapped version of the full product.  It’s built from the same code base, but restrictions have been placed on the product, such as replication restrictions.