1. What is VDL (View Definition Language)?
It specifies user views and their mappings to the conceptual schema.
2. What is SDL (Storage Definition Language)?
This language is to specify the internal schema. This language may specify the mapping
between two schemas.
3. What is DML Compiler?
It translates DML statements in a query language into low-level instruction that the
query evaluation engine can understand.
4. What is Query evaluation engine?
It executes low-level instruction generated by compiler.
5. What is DDL Interpreter?
It interprets DDL statements and record them in tables containing metadata.
6. 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.
7. 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.
8. What is Lossless join property?
It guarantees that the spurious tuples generation does not occur with respect to relation
schemas after decomposition.
9. What are partial, alternate,, artificial, compound and natural 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.
All Candidate Keys excluding the Primary Key are known as Alternate Keys.
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.
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.
When one of the data elements stored within a construct is utilized as the primary key,
then it is called the natural key.
10. What is a Phantom Deadlock?
In distributed deadlock detection, the delay in propagating local information might cause
the deadlock detection algorithms to identify deadlocks that do not really exist. Such
situations are called phantom deadlocks and they lead to unnecessary aborts.
11. 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
12. What do you mean by flat file database?
It is a database in which there are no programs or user access languages. It has no
cross-file capabilities but is user-friendly and provides user-interface management.
13. What is "transparent DBMS"?
It is one, which keeps its Physical Structure hidden from user.
14. What is the job of the information stored in data-dictionary?
The information in the data dictionary validates the existence of the objects, provides
access to them, and maps the actual physical storage location.
15. What is database Trigger?
A database trigger is a PL/SQL block that can defined to automatically execute for
insert, update, and delete statements against a table. The trigger can e defined to
execute once for the entire statement or once for every row that is inserted, updated, or
deleted. For any one table, there are twelve events for which you can define database
triggers. A database trigger can call database procedures that are also written in
16. What are stored-procedures? And what are the advantages of using them.
Stored procedures are database objects that perform a user defined operation. A stored
procedure can have a set of compound SQL statements. A stored procedure executes
the SQL commands and returns the result to the client. Stored procedures are used to
reduce network traffic.
17. How are exceptions handled in PL/SQL? Give some of the internal exceptions' name
PL/SQL exception handling is a mechanism for dealing with run-time errors
encountered during procedure execution. Use of this mechanism enables execution to
continue if the error is not severe enough to cause procedure termination.
The exception handler must be defined within a subprogram specification. Errors cause
the program to raise an exception with a transfer of control to the exception-handler
block. After the exception handler executes, control returns to the block in which the
handler was defined. If there are no more executable statements in the block, control
returns to the caller.
PL/SQL enables the user to define exception handlers in the declarations area of
18. Does PL/SQL support "overloading"? Explain
The concept of overloading in PL/SQL relates to the idea that you can define
procedures and functions with the same name. PL/SQL does not look only at the
referenced name, however, to resolve a procedure or function call. The count and data
types of formal parameters are also considered.
PL/SQL also attempts to resolve any procedure or function calls in locally defined
packages before looking at globally defined packages or internal functions. To further
ensure calling the proper procedure, you can use the dot notation. Prefacing a
procedure or function name with the package name fully qualifies any procedure or
19. What is cold backup and hot backup (in case of Oracle)?
1. Cold Backup: It is copying the three sets of files (database files, redo logs, and control
file) when the instance is shut down. This is a straight file copy, usually from the disk
directly to tape. You must shut down the instance to guarantee a consistent copy. If a cold
backup is performed, the only option available in the event of data file loss is restoring all
the files from the latest backup. All work performed on the database since the last backup
2. Hot Backup: Some sites (such as worldwide airline reservations systems) cannot shut
down the database while making a backup copy of the files. The cold backup is not an
18. What is meant by Proactive, Retroactive and Simultaneous Update.
1. Proactive Update: The updates that are applied to database before it becomes effective
in real world.
2. Retroactive Update: The updates that are applied to database after it becomes effective
in real world.
3. Simulatneous Update: The updates that are applied to database at the same time when it
becomes effective in real world.
19. What are stand-alone procedures?
Procedures that are not part of a package are known as stand-alone because they
independently defined. A good example of a stand-alone procedure is one written in a
SQL*Forms application. These types of procedures are not available for reference from other
Oracle tools. Another limitation of stand-alone procedures is that they are compiled at run
time, which slows execution.
20. What are cursors give different types of cursors?
PL/SQL uses cursors for all database information accesses statements. The language supports the
use two types of cursors
21. What is extension and intension?
1. Extension: It is the number of tuples present in a table at any instance.
This is time dependent.
2. Intension: It is a constant value that gives the name, structure of table and
the constraints laid on it.
22. What is a view? How it is related to data independence?
A view may be thought of as a virtual table, that is, a table that does not
really exist in its own right but is instead derived from one or more
underlying base table. In other words, there is no stored file that direct
represents the view instead a definition of view is stored in data dictionary.
Growth and restructuring of base tables is not reflected in views. Thus the
view can insulate users from the effects of restructuring and growth in the
database. Hence accounts for logical data independence.
23. What are the different phases of transaction?
Different phases are
1.) Analysis phase,
2.) Redo Phase,
3.) Undo phase.
24. What is a cascading update?
Referential integrity constraints require that foreign key values in one table
correspond to primary key values in another. If the value of the primary key
is changed -- that is, updated -- the value of the foreign key must
immediately be changed to match it. Cascading updates will set this change
to be done automatically by the DBMS whenever necessary.
25. Name four applications for triggers.
(1) providing default values, (2) enforcing data constraints, (3) updating
views and (4) enforcing referential integrity
26. Explain how to add a NOT NULL column to a table.
First, add the column as NULL. Then use UPDATE to add data to every row.
Finally use an ALTER
TABLE . . . ALTER COLUMN statement to change the column constraint to
27. Explain the difference between optimistic locking and pessimistic locking.
Optimistic locking assumes no transaction conflict will occur and deals with
the consequences if it does. Pessimistic locking assumes that conflict will
occur and so prevents it ahead of time with locks. In general, optimistic
locking is preferred for the Internet and for many intranet applications.
28. How are surrogate keys and metadata handled in MySQL?
MySQL uses integer data types combined with the property
AUTO_INCREMENT to create surrogate keys. This creates a sequence that
starts at one (1) and increases by one (1) for each new record.
MySql maintain its metadata in a database named mysql. For example, this
database maintains two tables named user and db.
29. Explain the differences between structured data and unstructured data.
Structured data are facts concerning objects and events. The most important
structured data are numeric, character, and dates. Structured data are
stored in tabular form. Unstructured data are multimedia data such as
documents, photographs, maps, images, sound, and video clips.
Unstructured data are most commonly found on Web servers and Web-
30. Briefly describe the six database activities that occur during the systems development
The enterprise modeling that analyzes the current data processing.
Conceptual data modeling that identifies entities, relationships, and
attributes. The logical database design that identifies data integrity and
security requirements. The physical database design and definition that
defines the database to a DBMS. The database implementation that installs
and converts data from prior systems. Database maintenance that fixes
errors in the database and database applications.
31. Explain minimum and maximum cardinality.
Minimum cardinality is the minimum number of instances of an entity that
can be associated with each instance of another entity. Maximum cardinality
is the maximum number of instances of an entity that can be associated with
each instance of another entity.