Schema & its objects

Schema & its objects

What is Schema?

• It is a collection of logical structure, units of data or schema objects.
• Each user owns a single schema.
• These schema objects can be created and manipulated by SQL.

Types of Schemas:
1. Table
2. View
3. Sequence
4. Index
5. Synonymous
6. Cluster

1. Table:

• It is basic unit of data storage.
• It has number of rows and columns.
• It can be defined with table name and set of columns.
• Example:
• Student (rollno, name, address, mobile)
• Each column defined with column name, column type and size.
• Example:
• rollno number(3)
• We can applied different kinds of constraints on table with column level or table level.

2. View:

• It is a presentation of the data contains in one or more table or views.
• It is treats as table.
• A base table is a table which help us to create view.
• A view is not allocated any storage space.
• It does not contain any data.
• View and Table have many similarities.
• Just like table, we can also apply queries to view.
• If any operation  performed on view, it is automatically performed with base table.
• For Example:
• When we insert any records in view - it automatically insert records into base table.
• When we delete any records in view - it automatically delete records from base table.

3. Sequence

• Sequence is a sequential list of unique numbers.
• It is useful for the table's column without maximum I/O operations.
• for Example:
• Assume two users are simultaneously insert new employee rows in employee table.
• So, the sequence automatically generates the correct value for each other.

4. Index

• It is used to create indexes in tables.
• Indexes are used to retrieve data from the database more quickly. 
• The users cannot see the indexes.
• They are just used to speed up searches/queries.
• Duplicate values are not allowed.
• Syntax:
• CREATE INDEX index_name ON table_name (column1, column2, ...);
• Example:
• create index idx_rollno on Student(rollno);
• CREATE INDEX idx_pname ON Persons (LastName, FirstName);
• DROP INDEX Statement
• The DROP INDEX statement is used to delete an index in a table.
• Syntax:
• DROP INDEX index_name;
• Example:
• Drop index idx_rollno;
• drop index idx_pname;

5. Synonymous

• It is an alias for any table, view, sequence, function, package.
• It is simply an alias.
• It requires no storage other than its defintion.
• It can be either public or private.
• Syntax:
• create [public] synonym for schema;
• Example:
• create public synonym stud for Student
• Syntax:
• drop synonym
• Example:
• drop synonym stud

6. Cluster

• Cluster is a group of tables that share the same data blocks.
• They can share common columns and used together.
• For Example:
• We have two table: Employee (empid, empname, dept_id), Department (dept_id,dname)
• The common column name is dept_id.
• cluster store related rows of different table together.
• It also improves access time for joining records.
• Syntax:
• create cluster (column datatype, column2 datatype...)
• Example:
• create cluster studfees (rollno number)
• create table student(rollno number(3), sname varchar2(20)) cluster studfees (rollno);