Oracle Database Objects Overview:
A database can have many schemas; one schema can contain multiple database objects like tables, views, Synonym etc. A brief explanation on each of these Oracle database objects is given below. For more detailed explanations, please refer the official website of Oracle at www.oracle.com.
Schema:
This is also known as USER and is a collection of database objects and as a data modeler one should know how to login into a particular schema and to manage these database objects.
Table:
A set of related data, arranged in the form of rows and columns.
Column:
This is also known as Field that provides the structure for organizing the rows and contains the related information.
Data type:
This is set of property associated with a column, which helps to store and identify the type of data and its length.
Null:
This is a value that indicates that the column contains no valid data.
Not Null:
This is a constraint that indicates that the column should contain data.
Primary Key Constraint:
This is a constraint imposed on the column so that all values in the column should be different from each other. This constraint can be imposed on one column or group of columns. The primary key will be always used as a parent key when adding a referential constraint by connecting it to a child table.
- Unique Constraint: Unique + Null Values
- Primary Key Constraint: Unique + Not Null Values
Foreign Key Constraint:
This is a constraint imposed on the child table. Whatever values are present in the child table, their corresponding values should be present in the parent table. This constraint can be imposed on one column or group of columns and NULL values are allowed in the child table.
Unique Constraint:
This is a constraint imposed on a column so that all NON-NULL values in the column should be different from each other. This constraint can be imposed on one column or group of columns.
Check Constraint:
This is a constraint that is imposed to validate the data within some value or range of values. This constraint can be imposed on one column or group of columns.
Index:
Index is a database object that enables faster retrieval of data. Unique Index, Bitmap Index etc., are the different types of Index.
Sequence:
This is a database object that generates unique numbers.
View:
This is a PSEUDO table that is not stored in the database and it is just a query.
Materialized Views:
Materialized Views are similar to a view but these are permanently stored in the database and often refreshed. This is used in optimization for the faster data retrieval and is useful in aggregation and summation of data.
Synonym:
This is an alias name for the object in the database created with CREATE SYNONYM command.
Procedure:
This is a program that contains set of code, which will carry out a specific action when called by other programs.
Function:
This is a program that contains set of code, which will do a specific action when called by other programs.
Package:
This is a collection of procedures, functions, PL/SQL tables, etc., that contains set of code, which will do a specific action when called by other programs.
Trigger:
This is a program that contains set of code for doing some useful action when a record is inserted or deleted or updated in a table.