Home

	Expand Search

Introducing Sybase Workplace SQL Server

Chapter 2: Terms and Concepts

Database Object

Database Object

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.

Row and Column

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

Key

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.

Default

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.

View

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.

Index

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.

Rule

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.

Stored Procedure

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.

Trigger

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.