oracle 10g question answer

In­tro­duc­tion to DBMS

1.     What is database?

    Database is a struc­ture that stores in­for­ma­tion about mul­ti­ple types of en­ti­ties, the at­tributes (or char­ac­ter­is­tics) of the en­ti­ties, and the re­la­tion­ships among the en­ti­ties. It also con­tains data types for at­tributes and in­dex­es.

2.     Which type of stor­age sys­tem is Or­a­cle? What other stor­age sys­tems are avail­able?

    Or­a­cle is a type of Re­la­tion­al Database Man­age­ment Sys­tem (RDBMS). An­oth­er avail­able stor­age sys­tem is Hi­er­ar­chi­cal Stor­age Man­age­ment sys­tem, such as In­for­ma­tion Man­age­ment Sys­tem (IMS) from IBM and In­te­grat­ed Database Man­age­ment Sys­tem (IDMS) from Com­put­er As­so­ci­ates.

3.     Define Database Management System (DBMS) and RDBMS.

    DBMS is a pro­gram that de­fines the rules for data stor­age and re­trieval. RDBMS is a spe­cial type of DBMS that stores the data in the re­la­tion­al for­mat as de­scribed in the re­la­tion­al the­o­ry by E.F. Codd.

4.     How is Or­a­cle dif­fer­ent from other pro­gram­ming lan­guages?

    Or­a­cle or any other DBMS is a pro­gram that han­dles user re­quests (data re­trieval, stor­age, or mod­i­fi­ca­tion re­quests) with­out any re­quire­ment to spec­i­fy the al­go­rithm to do so.

5.     Give some ex­am­ples of join meth­ods.

    Some ex­am­ples of join meth­ods are given as fol­lows:

    Merge join —Sorts both the join­ing ta­bles by using the join key and then merges the sort­ed rows.

    Nest­ed loop join —Ap­plies fil­ter con­di­tions spe­cif­ic to the outer table and gets a re­sult set. After that, it joins the inner table with the re­sult set using ei­ther an index or a full table scan.

    Hash join —Uses hash al­go­rithm for fil­ter con­di­tions on small­er table first and then per­forms the same hash­ing al­go­rithm on the other table for joined columns. After that, it re­turns the match­ing rows.

6.     Which sort­ing al­go­rithm does Or­a­cle fol­low?

    Or­a­cle used to fol­low a bal­anced bi­na­ry tree sort­ing al­go­rithm to ef­fec­tive­ly build an in-mem­o­ry index on the in­com­ing data.

Bi­na­ry tree search places a huge mem­o­ry and CPU over­head on the sys­tem for large search­es; there­fore, Or­a­cle in­tro­duced an al­go­rithm based on heap sort, which is more ef­fi­cient. How­ev­er, it also has a lim­i­ta­tion of re-or­der­ing in­com­ing rows even when the data is not ar­riv­ing out of order.

7.     What is database de­sign­ing?

    Database de­sign­ing is a pro­cess, which fol­lows the re­quire­ment anal­y­sis phase to de­ter­mine the re­quired database struc­ture to sat­is­fy ap­pli­ca­tion, prod­uct, or busi­ness re­quire­ments as per the given cri­te­ria.

8.     What is data mod­el­ing?

    A data model con­sists of the fol­low­ing two com­po­nents:

    Struc­ture —Refers to the way the sys­tem struc­tures the data (or at least how the user thinks it is struc­tured)

    Op­er­a­tions —Refers to the fa­cil­i­ties given to the users of the DBMS to ma­nip­u­late the data with­in the database

Fol­low­ing are the op­er­a­tions that can be per­formed on the database:

    Cre­ate ta­bles and queries using Data Def­i­ni­tion Lan­guage

    Add, sort, and cal­cu­late the data using Data Ma­nip­u­la­tion Lan­guage

    Re­trieve the data using Data Query Lan­guage

    Con­trol the ac­cess of data using Data Con­trol Lan­guage

9.     What is the dif­fer­ence be­tween log­i­cal data model and phys­i­cal data model?

    Phys­i­cal data model de­picts database in terms of phys­i­cal ob­jects, such as ta­bles and con­straints; where­as, log­i­cal data model de­picts database in terms of log­i­cal ob­jects, such as en­ti­ties and re­la­tion­ships, and it is rep­re­sent­ed by the en­ti­ty-re­la­tion­ship model.

10.   What are the com­po­nents of log­i­cal data model?

    Fol­low­ing are the com­po­nents of the log­i­cal data model:

    En­ti­ty —Refers to an ob­ject of in­ter­est, that is, some­thing that we want to store in­for­ma­tion about. In a re­la­tion­al database struc­ture, each en­ti­ty has its own table, that is, a struc­ture of rows and columns.

    At­tribute —Refers to the property or the information of the entity that we are interested in. It is stored as a column of the table and has specific datatype

associated with it.

    Record —Refers to a col­lec­tion of all the prop­er­ties as­so­ci­at­ed with an en­ti­ty for one spe­cif­ic con­di­tion. It is rep­re­sent­ed by a row in a table.

    Do­main —Refers to the de­scrip­tion of the le­git­i­mate val­ues for an at­tribute. It is the set of all the pos­si­ble val­ues for that at­tribute.

    Tuple —Refers to the de­sign of a record.

    Re­la­tion —Rep­re­sents a re­la­tion be­tween two en­ti­ties. There­fore, a re­la­tion­al database is a col­lec­tion of en­ti­ties and re­la­tion­ships.

    Re­la­tion­al database —Refers to a set of re­lat­ed en­ti­ties, which is used to store re­quired in­for­ma­tion as per the de­sign.

11.   De­fine re­la­tion­ship.

    A relationship is an association between entities (or tables). It is based on having common data values in the designated columns of each table. A relationship is defined by linking the common columns in the two tables. Relationships are actual

objects in the database.

Fol­low­ing are the three types of re­la­tion­ships:

    One-to-many —Im­plies that a sin­gle record from the first table can be linked to mul­ti­ple records in the sec­ond table while a sin­gle record in the sec­ond table re­lates to only a sin­gle record in the first table.

    One-to-one —Im­plies that a sin­gle record from the first table can be linked to only a sin­gle record in the sec­ond table and vice versa.

    Many-to-many —Im­plies that mul­ti­ple records from the first table can be linked to mul­ti­ple records in the sec­ond table and vice versa.

One-to-many is the most com­mon­ly used re­la­tion­ship, where a par­ent table is the table on the one side of a one-to-many re­la­tion­ship; where­as, a child table is the table on the many side of a one-to-many re­la­tion­ship.

12.   What is a pri­ma­ry key?

    A primary key is an attribute (or a collection of attributes) that uniquely identifies each row in the table. In other words, each entity instance in a table must be unique; and therefore, primary key is a way of ensuring this.

13.   What is a for­eign key?

    A for­eign key is an at­tribute in a child table that match­es the pri­ma­ry key value in the par­ent table.

14.   What is nor­mal­iza­tion? What are the dif­fer­ent forms of nor­mal­iza­tion?

    Nor­mal­iza­tion is a stan­dard to model ta­bles and re­la­tion­ships in a man­ner to avoid re­dun­dan­cy and other un­de­sir­able de­pen­den­cies or ef­fects on the data. There are six dif­fer­ent nor­mal forms, which are given as fol­lows:

    First Nor­mal Form (1NF) —A re­la­tion R is in 1NF if and only if all the un­der­ly­ing do­mains con­tain atom­ic val­ues only.

    Sec­ond Nor­mal Form (2NF) —A relation R is in 2NF if and only if it is in 1NF

and every non-key attribute is fully functionally dependent on the primary key[I1].

It means that all the columns ex­cept the pri­ma­ry key col­umn should de­pend on the pri­ma­ry key col­umn for a table to be in 2NF.

    Third Nor­mal Form (3NF) —A re­la­tion R is in 3NF if and only if it is in 2NF and every non-key at­tribute is non-tran­si­tive­ly de­pen­dent on the pri­ma­ry key.

It means that all the columns ex­cept the pri­ma­ry key col­umn should be di­rect­ly de­pen­dent on the pri­ma­ry key col­umn and not through any other col­umn.

    Boyce Codd Nor­mal Form (BCNF) —A re­la­tion R is in BCNF if and only if every de­ter­mi­nant is a can­di­date key[I2].

In any table, there ex­ists a pri­ma­ry key, which is used to de­ter­mine rest of the col­umn val­ues. If there is more than one such set of columns that can de­ter­mine rest of the col­umn val­ues, then this set of columns is called a can­di­date key or an al­ter­nate key.

    Fourth Nor­mal Form (4NF) —A relation R is in 4NF if and only if there exists multi-valued dependency in R, say A->> B, then, all the attributes of R are also

functionally dependent on A.

    Fifth Nor­mal Form (5NF) —A re­la­tion R is in 5NF or Pro­ject-Join Nor­mal Form (PJNF) if and only if every join de­pen­den­cy is im­plied by can­di­date keys.

15.   What is func­tion­al de­pen­den­cy?

    Gen­er­al­ly, in any table or re­la­tion, an at­tribute or a set of at­tributes is used to de­ter­mine the value of rest of the at­tributes in the table or re­la­tion. This is pos­si­ble as the rest of the at­tributes are the func­tions of the key col­umn. This sit­u­a­tion is called func­tion­al de­pen­den­cy and it is pos­si­ble when there ex­ists one and only value for each non-key at­tributes that cor­re­spond to the key at­tribute.

Con­sid­er an em­ploy­ee table, where em­ploy­ee_id is the key and rest of the at­tributes, such as name of the em­ploy­ee, can be de­ter­mined by em­ploy­ee_id as they are func­tion­al­ly de­pen­dent on the em­ploy­ee_id. For ex­am­ple, if there is an em­ploy­ee whose name is Ram with em­ploy­ee_id 1001, then the name cor­re­spond­ing to the em­ploy­ee_id 1001 will al­ways be Ram only. No other name can be as­so­ci­at­ed with this em­ploy­ee_id.

16.   What is trivial functional dependency?

    A triv­ial func­tion­al de­pen­den­cy is the sit­u­a­tion, which shows a re­la­tion be­tween an at­tribute with a su­per­set of it­self such that the at­tribute is de­pen­dent on the su­per­set.

Con­sid­er an at­tribute of the dress en­ti­ty, say length. If the length at­tribute is de­pen­dent on the dress code at­tribute of the dress en­ti­ty, then it is a non-triv­ial de­pen­den­cy; how­ev­er, if the length at­tribute is de­pen­dent on the color and length at­tributes of the dress en­ti­ty, then it is a triv­ial de­pen­den­cy.

17.   What is full func­tion­al de­pen­den­cy?

    Full func­tion­al de­pen­den­cy is the sit­u­a­tion when an at­tribute de­pends on a group of at­tributes com­plete­ly but not on the sub­set of the at­tributes.

For ex­am­ple, sup­pose the price of a pizza de­pends on its type and lo­ca­tion of the pizza store. Nei­ther of these alone can give the price of the pizza; there­fore, price is

fully func­tion­al­ly de­pen­dent on the pizza type and lo­ca­tion of its store.

18.   What is mul­ti-val­ued de­pen­den­cy?

    A mul­ti-val­ued de­pen­den­cy is the sit­u­a­tion when an at­tribute (as­sume B) is de­pen­dent on an­oth­er at­tribute (as­sume A). How­ev­er, there are mul­ti­ple rows in a table rep­re­sent­ing the de­pen­den­cy. This sit­u­a­tion can hap­pen when there is at least one more at­tribute which is de­pen­dent on A and it re­quires to have mul­ti­ple rows of A and B to rep­re­sent all the pos­si­ble com­bi­na­tions. A mul­ti-val­ued de­pen­den­cy is rep­re­sent­ed by A->> B.

19.   What is tran­si­tive de­pen­den­cy?

    A tran­si­tive de­pen­den­cy is the sit­u­a­tion when an at­tribute in­di­rect­ly de­pends on an­oth­er at­tribute through a third at­tribute.

20.   What is the stan­dard nor­mal form for most On­line Trans­ac­tion Pro­cess­ing (OLTP) databas­es?

    3NF is the stan­dard nor­mal form for most OLTP databas­es.

21.   What is de-nor­mal­iza­tion? Why is it rec­om­mend­ed?

    OLTP databas­es are in­tend­ed more for DML op­er­a­tions; there­fore, these databas­es are de­signed to re­duce re­dun­dan­cy; where­as, On­line An­a­lyt­i­cal Pro­cess­ing (OLAP) databas­es or ware­hous­ing ap­pli­ca­tions are pri­mar­i­ly used for re­port­ing.

OLAP databas­es are not used for day-to-day trans­ac­tions in gen­er­al and re­port­ing per­for­mance is more crit­i­cal for such databas­es; there­fore, these databas­es are de­signed to store data to sup­port easy ac­cess with­out re­quir­ing too many joins. Re­dun­dan­cy of data is not of much con­cern in these databas­es; there­fore, ta­bles are de­signed as a join of two or more ta­bles from a typ­i­cal OLTP ap­pli­ca­tion. This is called de-nor­mal­iza­tion.

22.   What is join de­pen­den­cy?

    A join dependency is a situation where a table can be created by joining two or

more tables.

2

Or­a­cle Ar­chi­tec­ture

1.     What are the com­po­nents of an Or­a­cle in­stance?

    Back­ground pro­cess­es and mem­o­ry struc­ture are the two com­po­nents of an Or­a­cle in­stance.

2.     What is the dif­fer­ence be­tween database and in­stance?

    Database is the col­lec­tion of datafiles that con­tain the in­for­ma­tion of in­ter­est; where­as, in­stance is the com­bi­na­tion of back­ground pro­cess­es and mem­o­ry struc­ture.

3.     What are the dif­fer­ent types of mem­o­ry struc­ture avail­able in Or­a­cle?

    System Global Area (SGA) and Program Global Area (PGA) are the two types of

memory structure available in Oracle.

4.     What are the com­po­nents of SGA?

    SGA is used to store shared in­for­ma­tion. It in­cludes Buffer cache, Log buffer, Shared pool, Large pool, and Java pool.

5.     Why do you need PGA?

    PGA is a mem­o­ry area used by Or­a­cle database. It is re­quired to store ses­sion spe­cif­ic in­for­ma­tion.

6.     What are the disk com­po­nents?

    Datafiles, Redo Logs, Con­trol files, Pass­word files, and Pa­ram­e­ter files are the disk com­po­nents.

7.     What hap­pens when a user re­quests for some in­for­ma­tion from RDBMS?

    The fol­low­ing steps are per­formed when a user re­quests for some in­for­ma­tion:

1.   RDBMS checks if a copy of the parsed SQL state­ment ex­ists in the li­brary cache. If parsed copy ex­ists, then steps 2 to 6 are skipped.

2.   RDBMS val­i­dates the syn­tax of the state­ment.

3.   RDBMS en­sures that all the columns and ta­bles ref­er­enced in the state­ment exist.

4.   RDBMS ac­quires parse locks on ob­jects ref­er­enced in the state­ment so that their def­i­ni­tions do not change while state­ment is parsed.

5.   RDBMS en­sures that the user has suf­fi­cient priv­i­leges.

6.   State­ment is parsed and ex­e­cu­tion plan is cre­at­ed.

7.   State­ment is ex­e­cut­ed.

8.   Val­ues are fetched.

8.     What do you mean by data consistency? How does Oracle maintain

consistency of data?

    In a mul­ti-us­er en­vi­ron­ment, there can be sit­u­a­tions when one or more users are read­ing cer­tain set of data while other is mod­i­fy­ing the same set of data. Data con­sis­ten­cy pro­vides a sta­ble or con­sis­tent data to the users through­out the ses­sion.

Data con­sis­ten­cy is main­tained through roll­back seg­ments. A roll­back seg­ment holds the data image be­fore change; there­fore, one or more ses­sions will get the same image of the data through­out the ses­sion that were read­ing the data while an­oth­er ses­sion is up­dat­ing.

9.     What hap­pens when mul­ti­ple users try to up­date the same set of data?

    Or­a­cle uses lock­ing mech­a­nism to en­sure that only one ses­sion or user can up­date a sin­gle set of data at given point in time.

10.   How long does the roll­back seg­ment hold data to main­tain con­sis­ten­cy?

    Rollback segment holds the previous version of data block until either it gets committed or the space is not available for reuse. If a session is reading specific block from rollback segment while the other session has committed the information, then more rollback space is required to maintain more rollback information. In that case, this specific block ages out of the rollback segment and the reading or selecting session gets an error.

11.   What is the pro­cess of up­dat­ing or in­sert­ing cer­tain data in the database?

    Fol­low­ing is the pro­cess to up­date or in­sert data in the database:

i.    RDBMS search­es for parsed state­ment in li­brary cache or pars­es the state­ment to gen­er­ate ex­e­cu­tion plan.

ii.   Serv­er pro­cess re­trieves rel­e­vant data from disk to the buffer cache. In case of in­sert­ing data in the database, the data block with suf­fi­cient free space will be re­trieved.

iii.   Lock is ac­quired. The data block is up­dat­ed in the buffer cache.

iv.  A lock is acquired on rollback segment as well to store old version of data in case rollback is required.

v.   User pro­cess cre­ates a redo entry in the redo log buffer to sup­port re­cov­ery.

12.   What is Sys­tem Change Num­ber (SCN)?

    SCN is an ID that Or­a­cle gen­er­ates for every trans­ac­tion. It is record­ed with the cor­re­spond­ing change in a redo entry.

13.   What is DataBase WRit­er (DBWR)?

    DBWR is a back­ground pro­cess that writes changed data blocks from buffer cache to the datafile. The changed data blocks are called dirty blocks.

14.   When does DBWR write to the datafile?

    Fol­low­ing are the sit­u­a­tions when DBWR writes to the datafile:

i.    When checkpoint occurs

ii.   When num­ber of dirty blocks reach­es a thresh­old

iii.   Every three sec­onds due to time­out

iv.  When serv­er pro­cess needs free space in buffer cache to read new blocks

15.   What is LoG WRit­er (LGWR)?

    LGWR is the back­ground pro­cess that writes redo in­for­ma­tion from redo log buffers to the log files.

16.   When does LGWR write to the log file?

    Fol­low­ing are the sit­u­a­tions when LGWR writes to the log file:

i.    When a user pro­cess com­mits a trans­ac­tion

ii.   When redo log buffer is one-third full

iii.   When more than a megabyte of changes are recorded into the redo log buffer

iv.  Be­fore DBWR writes mod­i­fied blocks to the datafiles

17.   What is the phys­i­cal struc­ture of the disk re­sources in Or­a­cle?

    Fol­low­ing is the phys­i­cal struc­ture of the disk re­sources in Or­a­cle:

     Con­trol files —Stores the sta­tus of the phys­i­cal struc­ture of the database. It con­tains dif­fer­ent types of in­for­ma­tion, such as archive log his­to­ry, datafile records, redo threads, log records, and database name.

     Datafiles —Refers to the phys­i­cal files of the op­er­at­ing sys­tem that store the data of all log­i­cal struc­tures in the database

     Redo log files —Refers to the files, which log a his­to­ry of all the changes made to the database.

18.   What is the log­i­cal struc­ture of the disk re­sources?

    Following is the logical structure of the disk resources:

     Data block —Refers to the small­est log­i­cal stor­age unit. Size of a data block is a mul­ti­ple of op­er­at­ing sys­tem block size.

     Ex­tent —Refers to the con­tigu­ous set of data blocks, which is al­lo­cat­ed as a unit to a seg­ment.

     Ta­blespace —Refers to the final log­i­cal stor­age unit. It is mapped to phys­i­cal datafile.

     Seg­ment —Al­lo­cates a log­i­cal struc­ture, such as table. It is a set of ex­tents, which are stored in the same ta­blespace.

19.   What is the dif­fer­ence be­tween a tem­po­rary ta­blespace and a per­ma­nent ta­blespace?

    A tem­po­rary ta­blespace pro­vides tem­po­rary stor­age dur­ing the pro­cess­ing of database func­tion, such as sort­ing; where­as, a per­ma­nent ta­blespace is used to store per­ma­nent database ob­jects, such as ta­bles, par­ti­tions, in­dex­es, and clus­ters.

You do not need to back­up or re­store a tem­po­rary ta­blespace.

20.   Name a ta­blespace, which is au­to­mat­i­cal­ly cre­at­ed when you cre­ate a database.

    The SYS­TEM ta­blespace is cre­at­ed au­to­mat­i­cal­ly dur­ing database cre­ation. It con­tains data dic­tio­nary ob­jects. All data stored on be­half of stored PL/SQL pro­gram units (pro­ce­dures, func­tions, pack­ages, and trig­gers) re­sides in the SYS­TEM ta­blespace, which is al­ways on­line when the database is open.

21.   Which file is ac­cessed first when you start an Or­a­cle database?

    Ini­tial­iza­tion pa­ram­e­ter file or SP­FILE is al­ways ac­cessed first when an Or­a­cle database is start­ed.

This file is used to de­ter­mine database level set­ting be­cause those val­ues are stored as pa­ram­e­ters in this file.

22.   What is the job of the System Monitor (SMON) and Process Monitor (PMON) processes?

    SMON helps in re­cov­ery at in­stance start­up. It is re­spon­si­ble for clean­ing up tem­po­rary seg­ments and co­a­lesc­ing free ex­tents. SMON also per­forms failed in­stance re­cov­ery for other failed Real Ap­pli­ca­tion Clus­ters in­stances.

PMON per­forms pro­cess re­cov­ery when a user pro­cess fails.