Oracle-Relational Database

• Database in which all data is stored in Relations (Tables) with     rows and columns. Each table is composed of records (called Tuples) and each record is identified by a field (attribute) containing a unique value. Every table shares at least one field with another table in 'one to one,' 'one to many,' or 'many to many' relationships. These relationships allow the database user to access the data in almost an unlimited number of ways, and to combine the tables as building blocks to create complex and very large databases.
• The oracle database houses everything There is 1 database which has multiple tablespaces, schemas, Datafiles and segments.

What is Oracle Schema ??

•In simple terms a schema in an Oracle database is another name for a user. So a schema and a user are the same thing.

•SCHEMA = USER

•The purpose of a schema in Oracle is to house database objects. The objects could be like tables and indexes, or object definitions like views, packages, triggers, etc. Hopefully image will help you to understand the relationship between a database, a schema, the segments in a schema and the tablespace in which the segments reside.

More about Schema !!!

•A schema is owned by a database user and has the same name as that user. Each user owns a single schema. Schema objects can be created and manipulated with SQL and include the following types of links

•Database triggers

•Dimensions

•External procedure libraries

•Indexes and index types

•Java classes

•Materialized views and materialized view logs

•Object tables, object types, and object views

•Operators

•Sequences

•Stored functions, procedures, and packages

•Synonyms

•Tables and index-organized tables

•Views

Logical Storage Structures

Oracle Database uses a logical storage structure for fine-grained control of disk space usage. The following are logical storage structures in an Oracle Database:

Data blocks: a data block corresponds to a number of bytes on the disk. Oracle stores data in data blocks. Data blocks are also referred to as logical blocks, Oracle blocks or pages.

Extents: An extent is a specific number of logically contiguous data blocks used to store the particular type of information.

Segments: a segment is a set of extents allocated for storing database objects, e.g., a table or an index.

Tablespaces: a database is divided into logical storage units called tablespaces. A tablespace is a logical container for a segment. Each tablespace consists of at least one data file.

     CONSTRAINTS IN ORACLE

Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.

Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table.

The following constraints are commonly used in SQL:

• NOT NULL - Ensures that a column cannot have a NULL value
• UNIQUE - Ensures that all values in a column are different
• PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
• FOREIGN KEY - Uniquely identifies a row/record in another table
• CHECK - Ensures that all values in a column satisfies a specific condition
• DEFAULT - Sets a default value for a column when no value is specified
• INDEX - Used to create and retrieve data from the database very quickly

Data Definition Language (DDL)statements are used to define the database structure or schema. Some examples:

• CREATE - to create objects in the database
• ALTER - alters the structure of the database
• DROP - delete objects from the database
• TRUNCATE - remove all records from a table, including all spaces allocated for the records are remove
• RENAME - rename an object

Data Manipulation Language (DML)statements are used for managing data within schema objects. Some examples:

• SELECT - retrieve data from the a database
• INSERT - insert data into a table
• UPDATE - updates existing data within a table
• DELETE - deletes all records from a table, the space for the records remain
• MERGE - UPSERT operation (insert or update)EXEC - call a PL/SQL or Java subprogram
• EXPLAIN PLAN - explain access path to data

Data Control Language (DCL)Some examples:

• GRANT - gives user's access privileges to database
• REVOKE - withdraw access privileges given with the GRANT command