What are database objects?

Are you preparing for IT certification? With practice questions, study notes, interactive quizzes, tips and technical articles, uCertify PrepKits ensure that you get a solid grasp of core technical concepts to ace your certification exam in first attempt.

What are database objects?

Rating:

Database objects are data structures that are stored in an Oracle database. They are used to store data, access stored data, restrict data access, and speed up data access. Database objects can be created either by Oracle Server or a database user. An Oracle database can contain multiple database objects. Following are the primary database objects:

Table: A table is the basic unit of data storage in an Oracle database. It is composed of columns and rows. A column is vertical space in a database table, which represents a particular domain of data. A row is a collection of data in columns corresponding to a single record. A table can have a minimum of one and a maximum of 1000 columns.

A table can be created in a database at any time, even while users are using the database. While creating a table, its size need not be specified. It is ultimately determined by the amount of space allocated to the database as a whole.

View: A view is a logical representation of data from one or more tables. It is stored as a query (SELECT statement) in the data dictionary of Oracle. A view does not contain data of its own but derives (or dynamically displays) data from other tables or views on the basis of the query specified for the view. The tables from which a view derives data are known as base tables. Operations on a view affect its base tables. A view can have a minimum of one and a maximum of 1000 columns.

Sequence: A sequence is a user-created database object that generates sequential numbers for numeric columns of a table. Sequences are often used to provide values for the primary key column of a table. The same sequence can be used by multiple tables.

Values are extracted from a sequence using the NEXTVAL pseudocolumn. A sequence returns a unique value each time it is referenced using the NEXTVAL pseudocolumn. The CURRVAL pseudocolumn is used to obtain the current sequence value from a sequence.

Synonym: A synonym is an alternative name for a database object such as a table, view, sequence etc. Synonyms are of two types, public and private. A public synonym can be accessed by all users, while a private synonym is available only to the owner or the users to whom the owner grants privileges.

Index: An index is a data structure that improves the performance of queries issued against a table. An index can be created for one or more columns of a table. Once created, an index is automatically updated and used by the database. It is logically and physically independent of data. The table or other indexes remain unaffected when an index is created or deleted.

Two types of indexes can be created. They are as follows:

1. Unique index: Oracle automatically creates this index when a column in a table is defined as a PRIMARY KEY or UNIQUE constraint.


2. Non-unique index: This index can be created by a database user.

A large number of indexes on a table affects the performance of the INSERT, UPDATE, and DELETE statements because all indexes must be updated as data in the table changes. Therefore, an index should be created only if the data in the indexed columns is queried frequently.

Constraint: A constraint enforces the integrity of a database. It defines rules regarding the values allowed in columns of a table. It is the standard mechanism for enforcing integrity. Using constraints is preferred to using triggers, rules, and defaults. Most of the RDBMS databases support the following five types of constraints:

1. NOT NULL constraint: It specifies that the column does not accept NULL values.


2. CHECK constraint: It enforces domain integrity by limiting the values that can be placed in a column.


3. UNIQUE constraint: It enforces the uniqueness of values in a set of columns.


4. PRIMARY KEY constraint: It identifies the column or set of columns whose values uniquely identify a row in a table.


5. FOREIGN KEY constraint: It establishes a foreign key relationship between the columns of the same table or different tables.

Rating: