Friday, September 28, 2007

Database Terminalogy

A collection of columns and rows representing a single entity (e.g., customers, orders, employees, etc.).

A single attribute of an entity stored in a table. A column has a name and a datatype. A table may have, and typically does have, more than one column as part of its definition.

A single instance of an entity in a table, including all columns. For example, a student row will store all information about a single student, such as that student’s ID, name, and address.

The term cell is used to refer to the intersection of a single column in a single row. For example, the CompanyName column for CustomerID 10002 in our example would be a cell holding that data—Bradley Systems.

A database object that is used to enforce simple business rules and database integrity. Examples of constraints are PRIMARY KEY, FOREIGN KEY, NOT NULL, and CHECK.

A view is a logical projection of data from one or more tables as represented by a SQL statement stored in the database. Views are used to simplify complex and repetitive SQL statements by assigning those statements a name in the database.

An index is a database object that helps speed up retrieval of data by storing logical pointers to specific key values. By scanning the index, which is organized in either ascending or descending order according to the key value, you are able to retrieve a row quicker than by scanning all rows in a table.

Indexorganized table
A table whose physical storage is organized like an index. Unlike a regular table, where rows are inserted in no particular order and querying all rows will retrieve the data in random order, index-organized tables store data organized according to the primary key defined on the table. The difference between a table (referred to as storing data on a heap) and an indexorganized table is like the difference between storing all of your receipts in a shoebox (i.e., in no specific order) and storing it chronologically according to the date the expense was incurred. Taking the receipts out of the shoebox will result in no specific logic in their retrieval, while doing the same when the receipts are organized chronologically will allow you to predict that the June 2 receipt will appear before the August 1 receipt.

Tables in Oracle 10g can be cut into pieces for more efficient physical storage. A partition (or subpartition) holds a subset of the table’s data, typically on a separate physical disk, so that data retrieval is quicker either by allowing reads from more than one physical disk simultaneously (multipartition parallel reads) or by not reading a partition’s data at all if it is not required to satisfy the query (partition elimination).

A storage mechanism object that allows rows from more than one table to be physically stored together for quicker retrieval. For example, if you store the Order information (customer, payment info, delivery details, etc.) in one table and the line items (item, cost, sale price, quantity, etc.) in a different table, you will need to perform at least two reads to retrieve information about an order: one for the order info and the second for line item info. Creating both tables on the cluster organized by the order ID will allow Oracle to place the order and line item data for the same order ID on the same physical block, thereby reducing retrieval of that order’s information to a single read. The downside of clusters is that they force you to preallocate a certain portion or all of the disk space they require when rows are added or the cluster is created.

A sequence allows you to creat and increment a counter that can be used to generate numerical values to be used as primary key values for a table.

As in the English language, a synonym is another name for an existing object. Synonyms are used in Oracle as shorthand for objects with long names, or to make it easier to remember a specific object.

Stored procedure
A stored procedure is a collection of SQL and PL/SQL statements that perform a specific task, such as to insert a row into a table or to update data.

A trigger is a special kind of stored procedure that cannot be invoked manually but rather is automatically invoked whenever an action is performed on a table. Triggers can be associated with a table and a corresponding action such as INSERT, UPDATE, or DELETE as well as system events such as user logon and logoff, or database STARTUP and SHUTDOWN.

A function is a stored program that must return a value. Unlike stored procedures, which can have parameters passed to them and do not need to return any value as output, a function must return a value.

A package is a collection of stored procedures and functions grouped under a common name. This allows you to logically group all program elements for a particular part of the database under a single name for maintenance and performance reasons.

User-defined datatype
A user-defined datatype is a database object that can be used in any table or another object definition. Using user-defined datatypes allows you to ensure consistency between tables and also lets you apply methods (i.e., actions that can be performed by the object) as part of the definition.

A BLOB is a binary large object used to store video, images, and large amounts of text. BLOBs are defined as a column in a table and can be one of several datatypes: BLOB, CLOB, NCLOB, or BFILE.

No comments: