What is the difference among "dropping a table", "truncating a table" and "deleting all records" from a table.
Dropping : (Table structure + Data are deleted), Invalidates the dependent objects ,Drops the indexes Truncating: (Data alone deleted), Performs an automatic commit, Faster than delete
Delete : (Data alone deleted), Doesn’t perform automatic commit
Difference between a "where" clause and a "having" clause.
Having clause is used only with group functions whereas Where is not used with.
What's the difference between a primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.
What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors?
Cursors allow row-by-row prcessing of the resultsets.
Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See books online for more information.
Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one rowundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Furthere, there are restrictions on the SELECT statements that can be used with some types of cursors.
Most of the times, set based operations can be used instead of cursors.
What is a self join?
Self join is just like any other join, except that two instances of the same table will be joined in the query.
What is 5NF?
A Relation schema R is said to be 5NF if for every join dependency {R1, R2, …, Rn} that holds R, one the following is true Ri = R for some i.The join dependency is implied by the set of FD, over R in which the left side is key of R.
What is 4NF?
A relation schema R is said to be in 4NF if for every Multivalued dependency X Y that holds over R, one of following is trueX is subset or equal to (or) XY = R.X is a super key.
What is BCNF (Boyce-Codd Normal Form)?
A relation schema R is in BCNF if it is in 3NF and satisfies an additional constraint that for every FD X A, X must be a candidate key.
What is 3NF?
A relation schema R is in 3NF if it is in 2NF and for every FD X A either of the following is true
X is a Super-key of R.
A is a prime attribute of R.
In other words, if every non prime attribute is non-transitively dependent on primary key.
What is 2NF?
A relation schema R is in 2NF if it is in 1NF and every non-prime attribute A in R is fully functionally dependent on primary key.
What is Fully Functional dependency?
It is based on concept of full functional dependency. A functional dependency X Y is full functional dependency if removal of any attribute A from X means that the dependency does not hold any more.
What is 1 NF (Normal Form)?
The domain of attribute must include only atomic (simple, indivisible) values.
What is DML Compiler?
It translates DML statements in a query language into low-level instruction that the query evaluation engine can understand.
What is Query evaluation engine?
It executes low-level instruction generated by compiler.
What is DDL Interpreter?
It interprets DDL statements and record them in tables containing metadata.
What is Record-at-a-time?
The Low level or Procedural DML can specify and retrieve each record from a set of records. This retrieve of a record is said to be Record-at-a-time.
What is Relational Calculus?
It is an applied predicate calculus specifically tailored for relational databases proposed by E.F. Codd. E.g. of languages based on it are DSL ALPHA, QUEL.
What is Relational Algebra?
It is procedural query language. It consists of a set of operations that take one or two relations as input and produce a new relation.
What is degree of a Relation?
It is the number of attribute of its relation schema.
What is Relationship set?
The collection (or set) of similar relationships.
What is Relationship type?
Relationship type defines a set of associations or a relationship set among a given set of entity types.
What is degree of Relationship type?
It is the number of entity type participating.
What is DDL (Data Definition Language)?
A data base schema is specifies by a set of definitions expressed by a special language called DDL.
DDL are the one which defines the schema.
What is VDL (View Definition Language)?
It specifies user views and their mappings to the conceptual schema.
What is SDL (Storage Definition Language)?
This language is to specify the internal schema. This language may specify the mapping between two schemas.
What is the use of FILE option in EXP command ?
To give the export file name.
What is Data Model?
A collection of conceptual tools for describing data, data relationships data semantics and constraints.
What is E-R model?
This data model is based on real world that consists of basic objects called entities and of relationship among these objects. Entities are described in a database by a set of attributes.
What is an Entity?
It is a ‘thing’ in the real world with an independent existence.
What is an Entity type?
It is a collection (set) of entities that have same attributes.
What are partial, alternate,, artificial, compound and natural key?
Partial Key:It is a set of attributes that can uniquely identify weak entities and that are related to same owner entity. It is sometime called as Discriminator.
Alternate Key:All Candidate Keys excluding the Primary Key are known as Alternate Keys.
Artificial Key:If no obvious key, either stand alone or compound is available, then the last resort is to simply create a key, by assigning a unique number to each record or occurrence. Then this is known as developing an artificial key.
Compound Key:If no single data element uniquely identifies occurrences within a construct, then combining multiple elements to create a unique identifier for the construct is known as creating a compound key.
Natural Key:When one of the data elements stored within a construct is utilized as the primary key, then it is called the natural key.
When is a functional dependency F said to be minimal?
Every dependency in F has a single attribute for its right hand side.
We cannot replace any dependency X A in F with a dependency Y A where Y is a proper subset of X and still have a set of dependency that is equivalent to F.We cannot remove any dependency from F and still have set of dependency that is equivalent to F.
What is Functional Dependency?
A Functional dependency is denoted by X Y between two sets of attributes X and Y that are subsets of R specifies a constraint on the possible tuple that can form a relation state r of R. The constraint is for any two tuples t1 and t2 in r if t1[X] = t2[X] then they have t1[Y] = t2[Y]. This means the value of X component of a tuple uniquely determines the value of component Y.
What is tablespace?
Any database contain one or more Table spaces. Each Table space is devided into pages of size 4kb or 32kb. Each table psace can have one or more tables. TABLE SPACE SPACE IS UNIT OF RECOVERY OR REORGANIZATION.
There are 3 types of Table Spaces
a) Simple
b) Segmented
c) Partitioned
What is the use of COMPRESS option in EXP command ?
Flag to indicate whether export should compress fragmented segments into single extents.
What is a checkpoint and When does it occur?
A Checkpoint is like a snapshot of the DBMS state. By taking checkpoints, the DBMS can reduce the amount of work to be done during restart in the event of subsequent crashes.
What do you mean by atomicity and aggregation?
Atomicity:Either all actions are carried out or none are. Users should not have to worry about the effect of incomplete transactions. DBMS ensures this by undoing the actions of incomplete transactions.
Aggregation:A concept which is used to model a relationship between a collection of entities and relationships. It is used when we need to express a relationship among relationships.
What is durability in DBMS?
Once the DBMS informs the user that a transaction has successfully completed, its effects should persist even if the system crashes before all its changes are reflected on disk. This property is called durability.
What is join dependency and inclusion dependency?
Join Dependency:A Join dependency is generalization of Multivalued dependency.A JD {R1, R2, …, Rn} is said to hold over a relation R if R1, R2, R3, …, Rn is a lossless-join decomposition of R . There is no set of sound and complete inference rules for JD.
Inclusion Dependency:An Inclusion Dependency is a statement of the form that some columns of a relation are contained in other columns. A foreign key constraint is an example of inclusion dependency.
What is indexing and what are the different kinds of indexing?
Indexing is a technique for determining how quickly specific data can be found.
Types: Binary search style indexingB-Tree indexingInverted list indexingMemory resident tableTable indexing.
What is an Oracle Instance?
The Oracle system processes, also known as Oracle background processes, provide functions for the user processes—functions that would otherwise be done by the user processes themselves
Oracle database-wide system memory is known as the SGA, the system global area or shared global area. The data and control structures in the SGA are shareable, and all the Oracle background processes and user processes can use them.
The combination of the SGA and the Oracle background processes is known as an Oracle instance
Define SQL and state the differences between SQL and other conventional programming Languages
SQL is a nonprocedural language that is designed specifically for data access operations on normalized relational database structures. The primary difference between SQL and other conventional programming languages is that SQL statements specify what data operations should be performed rather than how to perform them.
What is RDBMS KERNEL?
Two important pieces of RDBMS architecture are the kernel, which is the software, and the data dictionary, which consists of the system-level data structures used by the kernel to manage the database
You might think of an RDBMS as an operating system (or set of subsystems), designed specifically for controlling data access; its primary functions are storing, retrieving, and securing data. An RDBMS maintains its own list of authorized users and their associated privileges; manages memory caches and paging; controls locking for concurrent resource usage; dispatches and schedules user requests; and manages space usage within its table-space structures
What do you mean by Correlated subquery?
Subqueries, or nested queries, are used to bring back a set of rows to be used by the parent query. Depending on how the subquery is written, it can be executed once for the parent query or it can be executed once for each row returned by the parent query. If the subquery is executed for each row of the parent, this is called a correlated subquery.
A correlated subquery can be easily identified if it contains any references to the parent subquery columns in its WHERE clause. Columns from the subquery cannot be referenced anywhere else in the parent query. The following example demonstrates a non-correlated subquery.
What is a query?
A query with respect to DBMS relates to user commands that are used to interact with a data base. The query language can be classified into data definition language and data manipulation language.