DBMS PART 4
41. Explain
the difference between an exclusive lock and a shared lock.
An exclusive
lock prohibits other users from reading the locked resource; a shared lock
allows
other users
to read the locked resource, but they cannot update it.
42. 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.
43. What is
deadlock? How can it be avoided? How can it be resolved once it occurs?
Deadlock
occurs when two transactions are each waiting on a resource that the other
transaction
holds. Deadlock can be prevented by requiring transactions to acquire all locks
at
the same
time; once it occurs, the only way to cure it is to abort one of the
transactions and
back out of
partially completed work.
44. What are
the major functions of the database administrator?
Managing
database structure, controlling concurrent processing, managing processing
rights
and
responsibilities, developing database security, providing for database
recovery, managing
the DBMS and
maintaining the data repository.
45. Explain
what we mean by an ACID transaction.
An ACID
transaction is one that is atomic, consistent, isolated, and durable. Durable
means
that
database changes are permanent. Consistency can mean either statement level or
transaction
level consistency. With transaction level consistency, a transaction may not
see its
own changes.
There are four transaction isolation levels: read committed, read uncommitted,
repeatable
read and serialization. Atomic means it is performed as a unit.
46. What are
the ways in which an Oracle database can be created?
There are
three (3) ways to create an Oracle database. You can create a database using
the
Database
Configuration Assistant, using the Oracle-supplied database creation procedures
or
using the
SQL CREATE DATABASE command.
47. What are
sequences, and what are the possible problems when using them to
create
surrogate keys?
A sequence
is an object that generates a sequential series of unique numbers. Sequences
are
most often
used to provide values for surrogate keys. However, there are three problems
with
using
sequences. First, a developer can use a defined sequence for any purpose; and
if a
sequence is
used for purposes other than the surrogate key, some values will be missing. A
second
problem is that there is nothing in the schema that prevents someone from
issuing an
INSERT
statement that adds data to the table but that does not use the sequence. Finally,
it is
possible to
use the wrong sequence when putting data into a table.
48. Under
what conditions should indexes be used?
Indexes can
be created to enforce uniqueness, to facilitate sorting, and to enable fast
retrieval
by column
values. A good candidate for an index is a column that is frequently used with
equal
conditions
in WHERE clauses.
49. Explain
the three levels of transaction isolation supported by Oracle.
Oracle
supports read committed, serializable, and read-only transaction isolation
levels.
Because of
the way Oracle System Change Number (SCN) values are processed, Oracle never
reads dirty
data. Serializable isolation is possible, but the application program must be
written
to process
the "Cannot serialize" exception. Applications can place locks
explicitly using
SELECT FOR
UPDATE commands but this is not recommended.
50. What are
the types of files used in Oracle recovery?
Datafiles,
control files and two types of ReDo log files: OnLine Redo and Offline ReDo
(which is
also known
as Archive ReDo).
51. What is
the difference between SQL Server 2000 complete and differential
backups?
A complete
backup makes a copy of the entire database. A differential backup makes a copy
of
the changes
that have been made to the database since the last complete backup. A complete
backup must
be made before the first differential backup. Because differential backups are
faster, they
can be taken more frequently and the chance of data loss is reduced. Complete
backups take
longer but are slightly simpler to use for recovery.
52. Explain
the meaning of each of the transaction levels supported by SQL Server.
The
strictest isolation level is SERIALIZABLE. With it, SQL Server places a range
lock on the
rows that
have been read. This level is the most expensive to use and should only be used
when
absolutely required. The next most restrictive level is REPEATABLE READ, which
means
SQL Server
places and holds locks on all rows that are read. It is possible to make dirty
reads
by setting
the isolation level to READ UNCOMMITTED, which is the least restrictive level.
READ
COMMITTED is
the default isolation level.
53. Explain
the difference between the SQL Server 2000 simple, full, and bulk-logged
recovery
models.
With the
simple recovery model, no logging is done. The only way to recover a database
is to
restore the
database to the last backup. With full recovery, all database changes are
logged.
With
bulk-logged database recovery, all changes are logged except those that cause
large log
entries.
54. What is
the difference between SQL Server 2000 clustered and nonclustered
indexes?
With a
clustered index, the data are stored in the bottom level of the index and in
the same
order as
that index. With a nonclustered index, the bottom level of an index does not
contain
data; it
contains pointers to the data. For data retrieval, clustered indexes are faster
than
nonclustered
indexes.
55. What
triggers does SQL Server 2000 support?
SQL Server
2000 supports INSTEAD OF and AFTER triggers only. There is no SQL Server
support for
BEFORE triggers. A table may have one or more AFTER triggers for insert, update
and delete
actions; AFTER triggers may not be assigned to views. A view or table may have
at
most one
INSTEAD OF trigger for each triggering action of insert, update or delete.
56. What is
the relationship of ODBC, OLE DB, and ADO?
Developed
first, the ODBC standard is for relational databases; while the OLE DB standard
provides
functionality for both relational and other databases. Finally, ADO was
developed to
provide
easier access to OLE DB data for the non-object-oriented programmer.
57. What are
the three types of data sources used with ODBC?
An ODBC file
data source is a file that can be shared among database users. A ODBC system
data source
is one that is local to a single computer. A ODBC user data source is only
available
to the user
who created it.
58. What
disadvantage of ODBC does OLE DB overcome?
By breaking
the features and the functions of a DBMS into COM objects, OLE DB
characteristic
overcomes a
major disadvantage of ODBC. With ODBC, a vendor must create an ODBC driver
for almost
all DBMS features and functions in order to participate in ODBC at all. This is
a large
task that
requires a substantial initial investment. With OLE DB, however, a DBMS vendor
can
implement
portions of their product.
59. What are
to goals of OLE DB?
The major
goals of OLE DB are to: (1) Create object interfaces for DBMS functionality
pieces;
(2) Increase
flexibility for developers and users; (3) provide an object interface over any
type
of data; and
(4) do not force data to be converted or moved from where it is.
60. In OLE
DB, what is the difference between an interface and an implementation?
An OLE DB
interface is specified by a set of objects, and the properties and methods that
they
expose, and
OLE DB defines standardized interfaces. An object need not expose all of its
properties
and methods in a given interface. An OLE DB implementation defines how the
object
supports the interface. The implementation is completely hidden from the user.
Thus
developers
of an object are free to change the implementation whenever they want, but they
should not
change the interface without consulting their users.
61. Why is XML
a better markup language than HTML?
No comments:
Post a Comment