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
ORACLE DATABASE
1. What are the components of Physical databasestructure of Oracle Database?.
2. What are the components of Logical database structure of ORACLE database?
3. What is a Tablespace?
4. What is SYSTEM tablespace and When is it Created?
5. Explain the relationship among Database, Tablespace and Data file.
Answers
1.
ORACLE database is comprised of three types of files. One or more Data files, two are more Redo Log files, and one
or more Control files.
2.
Tablespaces and the Database's Schema Objects.
3.
A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical
structures together.
4.
Every ORACLE database contains a tablespace named SYSTEM, which is automatically created when the database is
created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.
5.
Each databases logically divided into one or more tablespaces One or more data files are explicitly created for each
tablespace.
1. What is schema?
A Schema is a collection of database objects owned by a database user.
2. What are Schema Objects ?
Some Schema objects are : tables , views, synonyms,indexes, procedures , fundtions ...
3. Can objects of the same Schema reside in different tablespaces.?
Yes they can reside.
4. Can a Tablespace hold objects from different Schemes ?
Yes it can hold.
5. What is Table ?
It is the basic informational unit of the database. Basic storage unit of database. Table data stored as Row , Column format(like excel).
6. What is a View ?
A view is a logical entity. It is a SQL statement stored in the database in the system tablespace. Data for a view is built in a table created by the database engine in the TEMP tablespace. When you query on the view the SQl associated with it is taken and executed and the result is displayed.
1. What are the advantages of Views ?
2. What is a Sequence ?
3. What is a Synonym ?
4. What are the type of Synonyms?
5. What is a Private Synonyms ?
6. What is a Public Synonyms ?
7. What are synonyms used for ?
Answers
1.
Provide an additional level of table security, by restricting access to a predetermined set of rows and columns of a table.
Hide data complexity.
Simplify commands for the user.
Present the data in a different perpecetive from that of the base table.
Store complex queries.
2.
A sequence generates a serial list of unique numbers for numerical columns of a database's tables.
3.
A synonym is an alias for a table, view, sequence or program unit.
4.
There are two types of Synonyms Private and Public.
5.
A Private Synonyms can be accessed only by the owner.
6.
A Public synonyms can be accessed by any user on the database.
7.
Synonyms are used to : Mask the real name and owner of an object.
Provide public access to an object
Provide location transparency for tables,views or program units of a remote database.
Simplify the SQL statements for database users.
1. What is an Index ?
2. How are Indexes Update ?
3. What are Clusters ?
4. What is cluster Key ?
5. What is Index Cluster ?
6. What is Hash Cluster ?
7. When can Hash Cluster used ?
Answers
1.
An Index is an optional structure associated with a table to have direct access to rows,which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.
2.
Indexes are automatically maintained and used by ORACLE. Changes to table data are automatically incorporated into all relevant indexes.
3.
Clusters are groups of one or more tables physically stores together to share common columns and are often used together.
4.
The related columns of the tables in a cluster is called the Cluster Key.
5.
A Cluster with an index on the Cluster Key.
6.
A row is stored in a hash cluster based on the result of applying a hash function to the row's cluster key value. All rows with the same hash key value are stores together on disk.
7.Hash clusters are better choice when a table is often queried with equality queries. For such queries the specified cluster key value is hashed. The resulting hash key value points directly to the area on disk that stores the specified rows.
2. What are the components of Logical database structure of ORACLE database?
3. What is a Tablespace?
4. What is SYSTEM tablespace and When is it Created?
5. Explain the relationship among Database, Tablespace and Data file.
Answers
1.
ORACLE database is comprised of three types of files. One or more Data files, two are more Redo Log files, and one
or more Control files.
2.
Tablespaces and the Database's Schema Objects.
3.
A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical
structures together.
4.
Every ORACLE database contains a tablespace named SYSTEM, which is automatically created when the database is
created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.
5.
Each databases logically divided into one or more tablespaces One or more data files are explicitly created for each
tablespace.
1. What is schema?
A Schema is a collection of database objects owned by a database user.
2. What are Schema Objects ?
Some Schema objects are : tables , views, synonyms,indexes, procedures , fundtions ...
3. Can objects of the same Schema reside in different tablespaces.?
Yes they can reside.
4. Can a Tablespace hold objects from different Schemes ?
Yes it can hold.
5. What is Table ?
It is the basic informational unit of the database. Basic storage unit of database. Table data stored as Row , Column format(like excel).
6. What is a View ?
A view is a logical entity. It is a SQL statement stored in the database in the system tablespace. Data for a view is built in a table created by the database engine in the TEMP tablespace. When you query on the view the SQl associated with it is taken and executed and the result is displayed.
1. What are the advantages of Views ?
2. What is a Sequence ?
3. What is a Synonym ?
4. What are the type of Synonyms?
5. What is a Private Synonyms ?
6. What is a Public Synonyms ?
7. What are synonyms used for ?
Answers
1.
Provide an additional level of table security, by restricting access to a predetermined set of rows and columns of a table.
Hide data complexity.
Simplify commands for the user.
Present the data in a different perpecetive from that of the base table.
Store complex queries.
2.
A sequence generates a serial list of unique numbers for numerical columns of a database's tables.
3.
A synonym is an alias for a table, view, sequence or program unit.
4.
There are two types of Synonyms Private and Public.
5.
A Private Synonyms can be accessed only by the owner.
6.
A Public synonyms can be accessed by any user on the database.
7.
Synonyms are used to : Mask the real name and owner of an object.
Provide public access to an object
Provide location transparency for tables,views or program units of a remote database.
Simplify the SQL statements for database users.
1. What is an Index ?
2. How are Indexes Update ?
3. What are Clusters ?
4. What is cluster Key ?
5. What is Index Cluster ?
6. What is Hash Cluster ?
7. When can Hash Cluster used ?
Answers
1.
An Index is an optional structure associated with a table to have direct access to rows,which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.
2.
Indexes are automatically maintained and used by ORACLE. Changes to table data are automatically incorporated into all relevant indexes.
3.
Clusters are groups of one or more tables physically stores together to share common columns and are often used together.
4.
The related columns of the tables in a cluster is called the Cluster Key.
5.
A Cluster with an index on the Cluster Key.
6.
A row is stored in a hash cluster based on the result of applying a hash function to the row's cluster key value. All rows with the same hash key value are stores together on disk.
7.Hash clusters are better choice when a table is often queried with equality queries. For such queries the specified cluster key value is hashed. The resulting hash key value points directly to the area on disk that stores the specified rows.
Subscribe to:
Posts (Atom)