ORACLE DATABASE

1)What is Normalization? Need for Normalization. What are the Normal forms we have?
Normalization is the process of efficiently organizing data in a database.
Need: Eliminate redundant data (for example, storing the same data in more than one table)
Ensure data dependencies make sense (only storing related data in a table)
Normal Forms:First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)
Boyce Codd Normal Form (BCNF)
Fourth normal form (4NF)
Fifth normal form (5NF)


2)What is a Trigger?
A trigger is an important system-level component that allows you to associate a "piece of code" (usually PL/SQL or Java) with a specific system event, namely DML (SQL insert, update and delete) statements. It can also be upon login/logoff.


3)What’s the difference between DDL and DML statements?


DDL (Data Definition Language) statements are used to define the database structure or schema. DDL Statements are committed automatically. Eg CREATE,ALTER ...
DML (Data Manipulation Language) statements are used for managing data within schema objects. We have to commit the DML Statements. Eg. SELECT,INSERT,UPDATE...

4)What is a view? Can we update the data in a table from a view?


A view is a tailored presentation of the data contained in one or more tables (or other views), and takes the output of a query and treats it as a table. You can think of a view as a "stored query" or a "virtual table." You can use views in most places where a table can be used. Yes we can update the data in a table from a view.




1. What is Rollback Segment ?


A collection of extents that holds rollback data for rollback,read-consistency, or recovery.


2. What are the uses of Rollback Segment ?


Used for
– Database recovery


– Tx rollback


– Read consistency

3. What is a Temporary Segment ?


A collection of extents that holds data belonging to temporary objects.Temp segment are areas that would be used to perform temporary operation whenever required e.g sorting, ordering and distinct....etc. It can be released once transaction is completed.



4. What is a Data File ?


Every ORACLE database has one or more physical data files. A database's data files contain all the database data. The data of logical database structures such as tables and indexes is physically stored in the data files allocated for a database.



5. What are the Characteristics of Data Files ?


A datafile can only store objects for a single tablespace, but a tablespace may have more than one datafile – this happens when a disk drive device fills up and a tablespace needs to be expanded, then it is expanded to a new disk drive.

1. What is Row Chaining ?


In some circumstances, all of the data for a row in a table may not be able to fit in the same data block. When this occurs , the data for that row is stored in a chain of data block (one or more) reserved for that segment.



2. What is an Extent ?


An extent is a contiguous set of disk
blocks
Extents are identified by start block and
length
Used extents are tracked in UET$ table of
data dictionary
Free extents are tracked in FET$ table of
data dictionary
A new file starts as a single extent in FET$




3. What is a Segment ?


Each logical database object such as a
table or index is physically represented as
a segment
A segment consists of a set of extents in
one tablespace
The first block(s) of a segment contain
extent information and INTRA-segment
available space information.



4. What are the different type of Segments ?


Data Segment, Index Segment, Rollback Segment, Temporary Segment, Cache Segment(Bootstrap Segment), LOB Segment,LOB Index Segment.



5. What is a Data Segment ?


Each Non-clustered table has a data segment. All of the table's data is stored in the extents of its data segment. Each cluster has a data segment. The data of every table in the cluster is stored in the cluster's data segment.


Oracle creates data segments when you create a table with CREATE command
You can specify storage in CREATE - This determines how data segments extents are
allocated.



6. What is an Index Segment ?


Each Index has an Index segment that stores all of its data.


It is created when you do CREATE INDEX.

1. What is a Data Dictionary ?


The Oracle data dictionary is one of the most important components of the Oracle DBMS.
It contains all information about the structures and objects of the database such as tables,
columns, users, data files etc. The data stored in the data dictionary are also often called
metadata. Although it is usually the domain of database administrators (DBAs), the data
dictionary is a valuable source of information for end users and developers. The data dictionary
consists of two levels: the internal level contains all base tables that are used by the various
DBMS software components and they are normally not accessible by end users. The external
level provides numerous views on these base tables to access information about objects and
structures at different levels of detail.


2. What is an Integrity Constrains ?


Integrity constraints are used to enforce business rules on data in your tables. Business rules specify conditions and relationships that must always be true, or must always be false.When an integrity constraint applies to a table, all data in the table must conform to the corresponding rule. When you issue a SQL statement that modifies data in the table, Oracle ensures that the new data satisfies the integrity constraint, without the need to do any checking within your program.


3. Can an Integrity Constraint be enforced on a table if some existing table data does not satisfy the constraint ?


No. It will throw errors if we try to do that.


4. Describe the different type of Integrity Constraints supported by ORACLE ?


1)A NOT NULL constraint prohibits a database value from being null.
2)A unique constraint prohibits multiple rows from having the same value in the same column or combination of columnsbut allows some values to be null.
3)A primary key constraint combines a NOT NULL constraint and a unique constraint in a single declaration. That is,it prohibits multiple rows from having the same value in the same column or combination of columns and prohibitsvalues from being null.
4)A foreign key constraint requires values in one table to match values in another table.
5)A check constraint requires a value in the database to comply with a specified condition.
6)A REF column by definition references an object in another object type or in a relational table. A REF constraintlets you further describe the relationship between the REF column and the object it references