Introduction to Database

Other Useful Information
Introduction to Oracle
What is 11.2.0.2 in Oracle
What Is Mean By export ORACLE_SID
Why We set Kernal Parameter?

==============================================================

1) What is a database?

Database offer a single point of mechanism for storing and retrieving information with the help of tables.

• Table is made up of columns and rows where each column stores specific attribute and each row displays a value for the corresponding attribute.

• It is a structure that stores information about the attributes of the entities and relationships among them.

• It also stores data types for attributes and indexes.

• Well known DBMS include Oracle, ibm db2, Microsoft sql server, Microsoft access, mysql and sqlLite.

• Table is made up of columns and rows where each column stores specific attribute and each row displays a value for the corresponding attribute.

• It is a structure that stores information about the attributes of the entities and relationships among them.

• It also stores data types for attributes and indexes.

• Well known DBMS include Oracle, ibm db2, Microsoft sql server, Microsoft access, mysql and sqlLite.

2) What are the different types of storage systems available and which one is used by Oracle?

Two types of storage systems are available. Relational Database Management System (RDBMS) and Hierarchical Storage Management System (HSM)

• Most databases use RDBMS model, Oracle also uses RDBMS model.

• Hierarchical Storage Management System (HSM)

• Information Management System (IMS) from IBM.

• Integrated Database Management System (IDMS) from CA.

3) What are the components of logical data model and list some differences between logical and physical data model?

Components of logical data model are

• Entity – Entity refers to an object that we use to store information. It has its own table.

• Attribute – It represents 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 – It refers to a collection of all the properties associated with an entity for one specific condition, represented as row in a table.

• Domain – It is the set of all the possible values for a particular attribute.

• Relation – Represents a relation between two entities. Difference between Logical and Physical data model

• Logical data model represents database in terms of logical objects, such as entities and relationships.

• Physical data model represents database in terms of physical objects, such as tables and constraints.

4) What is normalization? What are the different forms of normalization?

• Normalization is a process of organizing the fields and tables of a relational database to minimize redundancy and dependency.

• It saves storage space and ensures consistency of our data.

There are six different normal forms

• First Normal Form – If all underlying domains contain atomic values only.

• Second Normal Form – If it is in first normal form and every non key attribute is fully functionally dependent on primary key.

• Third Normal Form - If it is in 2nd normal form and every non key attribute is non transitively dependent on the primary key.

• Boyce Codd Normal Form - A relation R is in BCNF if and only every determinant is a candidate key.

• Fourth Normal Form• Fifth Normal Form

5) Differentiate between a database and Instance and explain relation between them?

• Database is a collection of three important files which include data files, control files and redo log files which physically exist on a disk

• Whereas instance is a combination of oracle background process (SMON, PMON, DBWR, LGWR) and memory structure (SGA, PGA).

• Oracle background processes running on a computer share same memory area.

• An instance can mount and open only a single database, ever.

• A database may be mounted and opened by one or more instances (using RAC).

6) What are the components of SGA?

• SGA is used to store shared information across all database users.

• It mainly includes Library cache, Data Dictionary cache, Database Buffer Cache, Redo log Buffer cache, Shared Pool.

• Library cache – It is used to store Oracle statements.

• Data Dictionary Cache – It contains the definition of Database objects and privileges granted to users.

• Data Base buffer cache – It holds copies of data blocks which are frequently accessed, so that they can be retrieved faster for any future requests.

• Redo log buffer cache – It records all changes made to the data files.