Q18.) What are the OOPS concepts in Oracle?
A18.) Oracle does implement the OOPS concepts. The best example is the Property Classes. We can categorise the properties by setting the visual attributes and then attach the property classes for the objects. OOPS supports the concepts of objects and classes and we can consider the property classes as classes and the items as objects.
Q19) What is the difference between candidate key, unique key and primary key?
A19) Candidate keys are the columns in the table that could be primary keys and the primary key is the key that has been selected to identify the rows. Unique key is also useful for identifying the distinct rows in the table.
Q20) What is concurrency?
A20) Concurrency is allowing simultaneous access of same data by different users. Locks useful for accessing the database are
a) Exclusive — The exclusive lock is useful for locking the row when an insert , update or delete is being done. This lock should not be applied when we do only select from the row.
b) Share lock — We can do the table as Share_Lock as many share_locks can be put on the same resource.
Q21) What are the Previleges and Grants?
A21) Privileges are the right to execute a particular type of SQL statements. The grant has to be given by the owner to object.
Q22) What are Table Space, Data files, parameter file, control files?
A22) Table space: The table space is useful for storing the data in the database. When a database is created two table spaces are created.
a) System Table space : This data file stores all the tables related to the system and dba tables.
b) User Table Space: This data file stores all the user related tables.
We should have separate table spaces for storing the tables and indexes so that the access is fast.
Data files : Every oracle Data base has one or more physical data files. They store the data for the database. Every datafile is associated with only one database. Once the data file is created the size can not change. To increase the size of the database to store more data we have to add data file.
Parameter file:Parameter file is needed to start an instance. A parameter file contains the list of instance configuration parameters e.g. db_block_buffers = 500
db_name = ORA7
db_domain = u.s.acme lang
Control files : Control files record the physical structure of the data files and redo log files . They contain the DB name, name and locations of dbs , data files, redo log files and time stamp.
Q23) What is physical storage of the Data?
A23) The finest level of granularity of the data base are the blocks.
Extent : Extent is the number of specific number of contigious data blocks.
Segments : Set of Extents allocated for Extents. There are three types of segments
a) Data Segment : Non clustered tables has data segment data of every table is stored in cluster data segment.
b) Index Segment : Each index has index segment that stores data.
c) Rollback Segment : Temporarily store ‘undo’ information.
Q24) What is Normalisation ? Explain 1NF, 2NF, 3NF.
Normalization is a process of eliminating data redudancy.
1NF- No repeating groups.
2NF- Eliminates the functional dependency on the partial key field.
3NF- Eliminates the functional dependency on the non-key field.