| |
Home |
![[Table of Contents]](/dwicons/b_toc.gif)
|
Introducing Sybase Workplace SQL Server |
![[-]](/dwicons/i_colpse.gif)
| Chapter 2: Terms and Concepts |
Before you delve too deeply into SQL Server and SQL Server documentation, familiarize yourself with some of the terminology that you will frequently encounter as you explore the Sybase family of relational database products.
Chapter 1 defined some terms, including client/server architecture, relational database,relational database management system, and table.
This chapter describes some additional terms and concepts that relate to SQL Server, including:
The term database object refers to the components of a relational database. The primary database object is the table. This section describes other database objects: rows and columns, defaults, views, indexes, rules, triggers, and stored procedures.
A table contains information about an entity. For example, a table might contain information about authors. A row, or record, describes one instance of that entityža person, a company, a sale, or some other thing. In our example, it describes one particular author. A column, or field, describes an attribute of that entity, such as name, size, color, price, etc. The example shown in Figure 2-1 shows columns that represent the author's first or last name. In early relational systems, columns were called attributes.
Figure 2-1: Rows and columns in a table
In some cases, a column does not describe a real-world attribute but instead is a key column that serves a logical function, (as introduced in "Tables"). A primary key uniquely identifies a row in a table. A foreign key relates a row in one table to a row in another table by matching its value to the value of the other table's primary key.
The sample shown in Chapter 1 and repeated in Figure 2-2 shows three key columns. The pub_id column in the publishers table is a primary key that provides a unique identifier for every row in the publishers tabležthere are no duplicated pub_id values. In the titles table, however, the pub_id column is a foreign key that relates the information about a book in the titles table to information about that book's publisher in the publishers table. In the titles table, there may be pub_ids that appear more than once, because a publisher probably publishes more than one book.
Figure 2-2: Primary and foreign key columns
You must enforce referential integrity for foreign keys; foreign key data must be kept consistent with the primary keys to which they refer. An easy way to do this is with triggers (see "Trigger"). You can also enforce referential integrity with constraints.
A default is the value that SQL Server assigns to a column when a row is added to a table specifying no particular value for that column. You have the option of specifying a default for a column when you create a table. For example, for a column that identifies the price of a published book, you can define "UNDECIDED" as the default value until a price is assigned to the book.
A view is a virtual table, composed of specific columns and/or rows from one or more tables or other views. A view is virtual because it has no physical existence in the database.
You can use a view to simplify and customize a user's perception of a database. For example, sales clerks might need to access some data about authors, but they should not have access to confidential information about an author's royalty advances. You could create a view that customizes the way that sales clerks can access the database. Figure 2-3 gives an idea of how a view can select information from a table or tables.
Figure 2-3: A view
Different views can present combinations of data from various tables in different ways, independently of the table structure in which the data is stored. In some cases data can be directly updated through a view; in other cases a view is read-only.
An index is a database object that helps SQL Server locate data. Indexes speed up data retrieval by pointing SQL Server to the location of a table column's data on disk.
Conceptually, an index in a database is like an index in a book. In a book, the index relates each indexed term to the page or pages on which that word appears. In a database, the index relates each indexed column value to the page, or physical location, at which the row of data containing the indexed value is stored. See Figure 2-4.
Figure 2-4: A simplified index schematic
Indexes are an important physical design element for high performance. The Performance and Tuning Guide explains in detail how indexes work and how to create indexes that improve performance.
A rule defines and enforces what data may be entered for a particular table column or user-defined datatype. For example, you could define a rule that requires that all the values in the title_id column in the titles table begin with two uppercase letters followed by four digits. Or you could define a rule that all values of the money datatype be less than $1,000,000.
A stored procedure is a set of one or more commands stored in a database. A stored procedure is partially processed before it is stored, so it executes faster than if you executed its constituent commands individually.
A stored procedure is invoked by its name. The caller can pass parameters to and receive results from the stored procedure. You can create and name your own stored procedures to execute specific database queries and perform other database tasks. For example, you might create a stored procedure that returns the names of all authors whose books have sold more than the number of books that you specify as a parameter at the time you call the procedure.
You can also use a set of stored procedures supplied by Sybase to help you accomplish numerous administrative tasks. These Sybase-supplied stored procedures are called system procedures and are automatically installed when you install SQL Server. The system procedures are described in the SQL Server Reference Manual.
A trigger is a special stored procedure you can create that automatically fires whenever a user updates, deletes, or inserts data, depending on how you define the trigger. You associate a trigger with a table or columns within a table.
For example, some organizations keep duplicate data to improve retrieval speed for decision support queries. They can define triggers to automatically duplicate data after an update, deletion, and insertion. Managers can query duplicated decision support data without slowing down the data entry system. Another common use of a trigger is to implement some aspects of referential integrity.
Transact-SQL(R) is Sybase's enhanced version of SQL and is the language that you, or the client application you are running, uses to communicate requests to the RDBMS.
The "Q" in "SQL" stands for query. You query or retrieve data from a database with Transact-SQL's workhorse select command. The basic query operations in a relational system are selection, projection, and join. The select command implements all of them.
The following sections present some simplified scenarios of retrieving data from a database using the select command and show the actual SQL statements you would use to accomplish the queries. The purpose of these statements is merely to familiarize you with what Transact-SQL looks like, not teach you SQL. For detailed instruction in how to use Transact-SQL, consult the Transact-SQL User's Guide, which is part of your user documentation.
A selection (also called restriction) is a subset of the rows in a table, based on some conditions. Figure 2-5 shows an example of selecting the rows for books of the type "business" from the titles table.
Figure 2-5: Selection
The actual Transact-SQL select command that retrieves the highlighted rows is:
select *
from titles
where type = "business"
A projection is a subset of the columns in a table. Figure 2-6 shows a sample projection in which you want look only at the title and price of all books in the titles table.
Figure 2-6: Projection
The actual Transact-SQL select command that retrieves the highlighted columns is:
select titles, price
from titles
A join links the rows in two or more tables by comparing the values in key columns and concatenating rows that have matching values. For example, you might want to select data from the relevant tables showing information about Berkeley publishers and the type and titles of books they published. Figure 2-7 shows the join.
Figure 2-7: A join
The actual SQL select statement that returns the highlighted data from the two tables is:
select *
from publishers, titles
where publishers.pub_id = titles.pub_id
and city = "Berkeley"
You can do more with SQL than just query, however. Transact-SQL includes other commands to create tables and views, such as create table and create view. It also includes commands to modify tables (the insert, update, and delete commands), and commands to perform many other database tasks discussed in this guide.
Transact-SQL offers extensions to the standard library of SQL commands. We have already looked at one powerful extension, in "Stored Procedure". Another is control-of-flow language, which is Transact-SQL's programming-like constructs that control the flow of execution of Transact-SQL statements. When combined with Transact-SQL's sophisticated error handling techniques, including the ability to capture a return status and get reports from global variables, they make SQL Server a fully programmable database server.
The Transact-SQL commands are described in the SQL Server Reference Manual. For in-depth discussions of how to use many of the Transact-SQL commands, see the Transact-SQL User's Guide.
There are several ways to present Transact-SQL commands to the RDBMS. One way is to use Sybase's interactive query parser, isql. You can invoke isql at the operating system command line and then enter Transact-SQL commands at the isql prompt.
End users typically do not use SQL itself but access the databases using specialized client applications. The client applications send Transact-SQL commands to the RDBMS by passing them as parameters to Open Client library routines (see "Building or Buying the Client") or to ODBC drivers that in turn called the Open Client library routines.
There are two kinds of databases: system databases and user databases. User databases are databases that you create to store and manage your enterprise's data. System databases are databases that the system creates to manage the system.
The master database is a system database that is installed with every SQL Server. It stores information about all the user databases and other system databases and their associated devices.
The sybsystemprocs database is the database that stores all the system procedures provided by Sybase.
The model database is a system database that SQL Server uses as a template for creating user databases. You can customize the default model database that Sybase provides to include your own database objects. For example, if you have created some special stored procedures that you want to be included in all the user databases that are subsequently created, you could accomplish this by creating the procedures in the model database.
The tempdb database is a system database that SQL Server uses to provide temporary working storage for objects that it creates in the process of executing queries.
The sybsecurity database is an optional system database that you can install if you want to use SQL Server's audit system. For information about the audit system and the sybsecurity database, see the Security Administration Guide.
The sybsyntax system database is an optional system database that stores the syntax for all the Transact-SQL commands, Sybase system procedures, SQL Server utilities, and some Open Client routines. Although the syntax for these facilities is available in the Sybase documentation, if you want to be able to access the syntax quickly on-line using the sp_syntax system procedure, you must install the sybsyntax database.
For more information about the system databases, see the System Administration Guide.
The pubs2 database is an optional sample user database that is used in most of the examples appearing in Sybase SQL Server documentation. If you want to try out some of the examples that appear in the SQL Server documentation, install the pubs2 database as described in the installation guide for your platform. The objects in pubs2 are described in the SQL Server Reference Supplement.
A database device stores SQL Server databases. It can be a disk or portion of a disk or, sometimes, an operating system file.
The system and user databases reside on database devices connected to the server. The master database must be installed on a device called the master device, which is automatically created at install time. The other system databases and the user databases should be created on other devices.
Part of the process of installing SQL Server consists of preparing the database devices. This procedure is explained in detail in either the installation guide or the configuration guide, depending on the platform for which you have purchased SQL Server.
Every user database contains a set of system tables, which are special tables used by the system to manage data and the system. The master database contains some special system tables not appearing in other databases that keep track of SQL Server as a whole. In other relational database products, what Sybase calls the system tables may be called the data dictionary or the system catalogs.
Workplace SQL Server includes a poster illustrating the contents of and relationships among the system tables. The SQL Server Reference Supplement also describes each system table in detail.
Although you can query the system tables directly to retrieve information about the system, such as the names of all the user databases on the server or all the user accounts on the server, it is usually simpler to use the appropriate system procedure. The system procedures are designed to retrieve the information that a user or system administrator is likely to need, and they save you the trouble of having to formulate complex queries and become familiar with the details of the system tables on your own.
A permission, sometimes called a privilege, is the authority to perform an action on a certain database object. SQL Server permissions are similar to permissions at the operating system level, but SQL Server maintains its own permissions for each user, apart from those conferred by the operating system. Examples of SQL Server permissions are permission to select data from a table, to modify data in a table, to execute a particular stored procedure, or to create databases or tables.
The SQL Server System Administrator or the owner of a particular database is responsible for setting up and maintaining permissions for various users, or groups of users, for SQL Server or a particular database.
SQL Server utility programs perform RDBMS tasks, but you start them from the operating system rather than through isql or a similar program.
isql itself is a utility. Another example of a utility is bcp, which copies bulk data from an operating system file to a database table and vice-versa. See SQL Server Utilities Programs for your platform for information on the Sybase utilities.
The transaction log is a special system table that SQL Server maintains for each database to record modifications to the data in the database tables. If you experience a system failure while data is being modified, SQL Server uses the transaction log to recover the data and restore the database to the state it was in before the failure occurred. Although the log is a part of the database, it should be stored on a separate device to reduce the likelihood of a physical disk error damaging both a database and the associated transaction log.
The term system administration refers to an assortment of tasks including, but not limited to, managing SQL Server's physical storage, creating and backing up databases, creating user accounts, granting permissions, and running diagnostic and repair functions.
The user responsible for system administration is the SQL Server System Administrator, who may or may not be the same individual or individuals who are system administrators for the server machine's operating system. For SQL Server, the System Administrator can log in to SQL Server as the special, predefined user "sa" and knows the system administrator password. The "sa" account has special privileges not held by normal users. The system administrator can grant other users permission to perform certain system administration tasks.
SQL Server Manager allows you to execute the most common system administration tasks through a graphical interface. Some tasks must be executed using Transact-SQL commands or SQL Server utilities directly. See the SQL Server Manager User's Guide for information about SQL Server Manager and the System Administration Guide and the Performance and Tuning Guide for complete information about SQL Server system administration.
|
|