Dbms Quiz
Dbms Quiz
Dbms Quiz
==========================================================================
Answer :
DBMS stands for Database Management System. A DBMS receives requests from
applications and translates those requests into actions on a specific database. A
DBMS processes SQL statements or uses other functionality to create process and
administer databases.
Answer :
Answer :
Answer :
Answer :
Answer :
Answer :
This language is to specify the internal schema. This language may specify the
mapping between two schemas.
Answer :
The storage structures and access methods used by database system are specified by
a set of definition in a special type of DDL called data storage-definition
language.
Answer :
1. Procedural DML or Low level: DML requires a user to specify what data are needed
and how to get those data.
2. Non-Procedural DML or High level: DML requires a user to specify what data are
needed without specifying how to get those data.
Answer :
Answer :
Answer :
Answer :
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.
Answer :
The High level or Non-procedural DML can specify and retrieve many records in a
single DML statement. This retrieve of a record is said to be Set-at-a-time or Set-
oriented.
Question 15. What Is Relational Algebra?
Answer :
Answer :
Question 17. How Does Tuple-oriented Relational Calculus Differ From Domain-
oriented Relational Calculus?
Answer :
1. The tuple-oriented calculus uses a tuple variables i.e., variable whose only
permitted values are tuples of that relation. E.g. QUEL
2. The domain-oriented calculus has domain variables i.e., variables that range
over the underlying domains instead of over relation. E.g. ILL, DEDUCE.
Answer :
(1)Minimizing redundancy,
(2)Minimizing insertion, deletion and update anomalies.
Answer :
Answer :
It guarantees that the spurious tuple generation does not occur with respect to
relation schemas after decomposition.
Answer :
The domain of attribute must include only atomic (simple, indivisible) values.
Answer :
Answer :
Answer :
Answer :
Answer :
Answer :
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 1.) Ri = R for some i.
Answer :
Question 29. What Is System Catalog Or Catalog Relation? How Is Better Known As?
Answer :
A RDBMS maintains a description of all the data that it contains, information about
every relation and index that it contains. This information is stored in a
collection of relations maintained by the system called metadata. It is also called
data dictionary.
Answer :
The phase that identifies an efficient execution plan for evaluating a query that
has the least estimated cost is referred to as query optimization.
Answer :
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.
Answer :
1. 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.
2. 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.
Answer :
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.
Answer :
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.
Answer :
Answer :
Answer :
Answer :
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.
Answer :
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.
Example:
Select� From CUST Where �10/03/1990� IN (Select ODATE From ORDER Where CUST.CNUM =
ORDER.CNUM)
Answer :
Question 41. Are The Resulting Relations Of Product And Join Operation The Same?
Answer :
No.
PRODUCT: Concatenation of every row in one relation with every row in another.
JOIN: Concatenation of rows from one relation and related rows from another.
Answer :
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.
Answer :
Question 44. Which Part Of The Rdbms Takes Care Of The Data Dictionary? How?
Answer :
Data dictionary is a set of tables and database objects that is stored in a special
area of the database and maintained exclusively by the kernel.
Answer :
The information in the data dictionary validates the existence of the objects,
provides access to them, and maps the actual physical storage location.
Question 46. How Do You Communicate With An Rdbms?
Answer :
Question 47. Define Sql And State The Differences Between Sql And Other
Conventional Programming Languages?
Answer :
Question 48. Name The Three Major Set Of Files On Disk That Compose A Database In
Oracle?
Answer :
There are three major sets of files on disk that compose a database. All the files
are binary. These are
1.) Database files
2.) Control files
3.) Redo logs
The most important of these are the database files where the actual data resides.
The control files and the redo logs support the functioning of the architecture
itself. All three sets of files must be present, open, and available to Oracle for
any data on the database to be useable. Without these files, you cannot access the
database, and the database administrator might have to recover some or all of the
database using a backup, if there is one.
Answer :
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 PL/SQL.
Question 50. What Are Stored-procedures? And What Are The Advantages Of Using Them?
Answer :
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.
Answer :
It is a program module that provides the interface between the low-level data
stored in database, application programs and queries submitted to the system.
Answer :
It is a program module, which is responsible for fetching data from disk storage
into main memory and deciding what data to be cache in memory.
Answer :
Answer :
It is a program module, which manages the allocation of space on disk storage and
data structure used to represent information stored on a disk.
Answer :
It is the program module, which tests for the satisfaction of integrity constraint
and checks the authority of user to access data.
Answer :
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.
Answer :
PL/SQL uses cursors for all database information accesses statements. The language
supports the use two types of cursors
1.) Implicit
2.) Explicit
Question 58. What Is Cold Backup And Hot Backup (in Case Of Oracle)?
Answer :
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 flies from the latest
backup. All work performed on the database since the last backup is lost.
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 available option. What is meant by Proactive, Retroactive and Simultaneous
Update. Proactive Update: The updates that are applied to database before it
becomes effective in real world. Retroactive Update: The updates that are applied
to database after it becomes effective in real world. Simultaneous Update: The
updates that are applied to database at the same time when it becomes effective in
real world.
Question 59. What Are Data And Information, And How Are They Related In A Database?
Answer :
Data is recorded facts and figures, and information is knowledge derived from data.
A database stores data in such a way that information can be created.
Question 60. What Is Enterprise Resource Planning (erp), And What Kind Of A
Database Is Used In An Erp Application?
Answer :
Answer :
Question 62. Who Is E.f. Codd, And Why Is He Significant In The Development Of
Modern Database Systems?
Answer :
While working at IBM, E.F. Codd created the relational database model. A paper he
published in 1970 presented his ideas to the world at large. His work is the
foundation for most of the DBMSs currently in use, and thus forms the basis for
database systems as we know and use them today.
Answer :
SQL stands for Structured Query Language, and is the most important data processing
language in use today. It is not a complete programming language like Java or C#,
but a data sublanguage used for creating and processing database data and metadata.
All DBMS products today use SQL.
Question 64. Write An Sql Select Statement To Display All The Columns Of The
Student Table But Only Those Rows Where The Grade Column Is Greater Than Or Equal
To 90?
Answer :
Answer :
COUNT: computes the number of rows in a table. SUM: totals numeric columns. AVG:
computes the average value. MAX: obtains the maximum value of a column in a table.
MIN: obtains the minimum value of a column in a table.
Question 66. Write An Sql Select Statement To Count The Number Of Rows In Student
Table And Display The Result With The Label Num Students?
Answer :
Answer :
An SQL subquery is a means of querying two or more tables at the same time. The
subquery itself is an SQL SELECT statement contained within the WHERE clause of
another SQL SELECT statement, and separated by being enclosed in parenthesis. Some
subqueries have equivalent SQL join structures, but correlated subqueries cannot be
duplicated by a join..
Question 68. Discuss The Alternative Terminology That Is Used In The Relational
Model?
Answer :
Relations are also called tables, and sometimes by the older data processing term
files. A row is known as a tuple in the relational model, but may also be referred
to as a record. Finally, relational model attributes are known as table columns and
sometimes as fields.
Answer :
Equations deal with numerical relationships. A functional dependency deals with the
existence of a determinant relationship between attributes, regardless of whether
or not there is a numerical relationship between them. Thus, if we know that there
is no hot water every Wednesday, No-Hot-Water is functionally dependent on
Wednesday. So, if we know it is Wednesday, then we know we will have No-Hot-Water.
This is a functional dependency, but not an equation.
Answer :
A deletion anomaly occurs when, by deleting the facts about one entity, we in
advertently delete facts about another entity; with one deletion, we lose facts
about two entities. For example, if we delete the tuple for Student 001289 from a
table, we may lose not only the fact that Student 001289 is in Pierce Hall, but
also the fact that he has $200 left in his security deposit. An insertion anomaly
happens when we encounter the restriction that we cannot insert a fact about one
entity until we have an additional fact about another entity. For example, we want
to store the fact that the security deposit for Pierce Hall is $300, but we cannot
enter this data into the Student relation until a student registers for Pierce
Hall.
Question 72. You Have Been Given A Set Of Tables With Data And Asked To Create A
New Database To Store Them. When You Examine The Data Values In The Tables, What
Are You Looking For?
Answer :
Multivalued dependencies,
Functional dependencies,
Candidate keys,
Primary keys and
Foreign keys.
Question 73. Why Do Normalized Tables Require More Complex Sql When Sql Statements
Are Used In Application Programs?
Answer :
Tables that are normalized contain data that has been distributed among the tables,
but which may need to be recombined to answer queries from an application. To
recombine the data, the programmer will have to use subqueries and/or joins. These
SQL structures are more complex to write than a simple SELECT statement.
Question 74. What Is The Inconsistent Values Problem? Include An Example Not Used
In The Text?
Answer :
The inconsistent values problem occurs when different users or data sources use
slightly different forms of the same data value. One example is where automobiles
are specified as �Ford, 2-door, Red� in one cell and �Red Ford 2-door� in another.
Question 75. Explain The Relationship Between Entity, Entity Class, And Entity
Instance?
Answer :
Answer :
Entities have attributes. Attributes are properties that describe the entity�s
characteristics. Entity instances have identifiers. Identifiers are attributes that
name, or identify, entity instances.
Answer :
Question 78. What Are The Steps For Transforming An Entity Into A Table?
Answer :
Answer :
The ideal primary key is short, numeric and fixed. A surrogate key is a unique,
DBMS-supplied identifier intended to be used as the primary key of a table.
Further, the DBMS will not allow the value of a surrogate key to be changed. The
values of a surrogate key have no meaning to the users and are usually hidden on
forms and reports. By design, they are short, numeric and fixed and thus meet the
definition of the ideal primary key.
Answer :
Data constraints on a column are the limits put on the values the data can have.
domain constraints, which define a limited set of values for the column,
range constraints, which specify that the values must fall within a certain range,
intrarelation constraints, which define what values the column can have based on
values of other columns in the same table, and
interrelation constraints, which define values the column can have based on values
of columns in other tables.