Three Levels of Data Abstraction

Question :: Three Levels of Data Abstraction
Answer:
Data abstraction is a process of representing the essential features without
including implementation details.
many database-systems users are not computer trained, developers hide the
complexity from users through several levels of abstraction, to simplify users’
interactions with the system:

1) Physical level.
The lowest level of abstraction describes how the data are
actually stored. The physical level describes complex low-level data
structures in detail.

2) Logical level. 
The next-higher level of abstraction describes what data are
stored in the database, and what relationships exist among those data. The
logical level thus describes the entire database in terms of a small number of
relatively simple structures.

3) View level.

 The highest level of abstraction describes only part of the entire

database. The variety of information stored in a large database. Many users

of the database system do not need all this information; instead, they need

to access only a part of the database. The view level of abstraction exists to

simplify their interaction with the system.

Composite Vs. Multi Valued Attributes

Question :: Composite Vs. Multi Valued Attributes
Answer:
Composite :
Composite attributes can be divided into subparts. For example, an attribute name
could be structured as a composite attribute consisting of first-name, middle-initial,
and last-name.

Multivalued:
There may be instances where an attribute has a set of values for a specific
entity. Consider an employee entity set with the attribute phone-number. An
employee may have zero, one, or several phone numbers, and different employees
may have different numbers of phones. This type of attribute is said to be

Composite Attributes : Attribute Divided into sub parts. Eg. Name (First name,
Middle Name, last name)

Multivalued Attributes : Attribute having more than one values. Eg. Phone
Number.

Foreign Key vs Primary Key

Question :: Difference Foreign Key and Primary Key ?
Answer:

ACID Property

Question  :: Explain ACID Property and Importance of each ?

Answer:

ACID Means:

A -Atomicity

C - Consistency

I - Isolation

D - Durability

Atomicity: 

Either all operations of the transaction are reflected properly in the

Database, or none are.

Let Ti be a transaction that transfers $50 from account A to account B. This

transaction can be defined as

Ti: read(A);

A := A − 50;

write(A);

read(B);

B := B + 50;

write(B)

Suppose that, just before the execution of transaction Ti the values of

accounts A and B are $1000 and $2000, respectively. Now suppose that, during the

execution of transaction Ti, a failure occurs that prevents Ti from completing its

execution successfully. Examples of such failures include power failures, hardware

failures, and software errors. Further, suppose that the failure happened after the

write(A) operation but before the write(B) operation. In this case, the values of

accounts A and B reflected in the database are $950 and $2000. The system

destroyed $50 as a result of this failure. In particular, we note that the sum A + B

is no longer preserved.

The basic idea behind ensuring atomicity is this: The database system keeps

track (on disk) of the old values of any data on which a transaction performs a

write, and, if the transaction does not complete its execution, the database system

restores the old values to make it appear as though the transaction never executed.

Consistency: 

If single transaction or more than two transactions executing concurrently then it should preserve the consistency of the database, i.e. Transaction must be in consistent states. The consistency requirement here is that the sum of A and B be unchanged by the execution of the transaction. Without the consistency requirement, money could be created or destroyed by the transaction! It can be verified easily that, if the database is consistent before an execution of the transaction, the database remains consistent after the execution of the transaction.

Isolation:

Even if the consistency and atomicity properties are ensured for each

transaction, if several transactions are executed concurrently, their operations may

interleave in some undesirable way, resulting in an inconsistent state. Even though

multiple transactions may execute concurrently, the system guarantees that, for

every pair of transactions Ti and Tj , it appears to Ti that either Tj finished

execution before Ti started, or Tj started execution after Ti finished. Thus, each

transaction is unaware of other transactions executing concurrently in the system.

Durability: 

After a transaction completes successfully, the changes it has made to the database persist, even if there are system failures.Once the execution of the transaction completes successfully, and the user who initiated the transaction has been notified that the transfer of funds has taken place, it must be the case that no system failure will result in a loss of data corresponding to this transfer of funds.The durability property guarantees that, once a transaction completes successfully, all the updates that it carried out on the database persist, even if there is a system failure after the transaction completes execution.

Responsibilities of DBA

Question :: What are the Responsibilities of DBA ?
Answer:
One of the main reasons for using DBMSs is to have central control of both the data
and the programs that access those data. A person who has such central control
over the system is called a database administrator (DBA). The functions of a
DBA include:
• Schema definition. The DBA creates the original database schema by executing
a set of data definition statements in the DDL.
• Storage structure and access-method definition.
• Schema and physical-organization modification. The DBA carries out
changes to the schema and physical organization to reflect the changing needs of
the organization, or to alter the physical organization to improve performance.
• Granting of authorization for data access. By granting different types of
authorization, the database administrator can regulate which parts of the database
various users can access. The authorization information is kept in a special system
structure that the database system consults whenever someone attempts to access
the data in the system.
• Routine maintenance. Examples of the database administrator’s routine
maintenance activities are:

• Periodically backing up the database, either onto tapes or onto remoteservers, to prevent loss of data in case of disasters such as flooding.

•  Ensuring that enough free disk space is available for normal operations, andupgrading disk space as required.

•  Monitoring jobs running on the database and ensuring that performance isnot degraded by very expensive tasks submitted by some users.

Aggregation

Question  :: What do you mean by aggregation ?Explain How Aggregation used to remove Redundant Relationship ?
Answer :
Aggregation (Definition): One limitation of the E-R model is that it cannot
express relationships among relationships. Aggregation is an abstraction through
which relationships are treated as higher-level entities. Aggregation is an
abstraction through which we can express relationships among two relationships.

Let’s consider manger wants to manage Employee, branch in which employee
working and jobs. Using the basic E-R modeling constructs, we obtain the E-R
diagram as shown in Figure 1. It appears that the relationship sets works-on and
manages can be combined into one single relationship set. We should not combine
them into a single relationship, since some employee, branch, job combinations
many not have a manager.

Figure : E-R diagram with redundant relationships

As shown in Figure 1, Every Employee, Job and Branch combined with
manager, also combined with works-on. Therefore we can say this diagram have
many redundant relationships. If the manager were a value rather than a manager
entity, we could instead make manager a multivalued attribute
of the relationship works-on and we can avoid redundant relationships. But doing it
is more difficult task because manager is entity.
The best way to model such a situation is to use aggregation. Aggregation
is an abstraction through which relationships are treated as higher-level entities.
Thus, for our example, we can take works-on(relating the entity sets employee,
branch, and job) as higher level entity set .Then we can create binary relationship
with manages that react as lower level entity sets.

As shown in Figure 2, Works-on relationships set acting as higher level entity

set with combination with Employee, Job and branch. This higher level entity set

creates binary relationship with manages, a lower level entity set i.e. relationship

between two relationship sets and redundant relationships can be reduced.

DBMS Vs File Processing

Question  :: Explain the Advantages of DBMS over File Processing System.

Answer ::

This typical file-processing system is supported by a conventional operating

system. The system stores permanent records in various files, and it needs

different application programs to extract records from, and add records to, the

appropriate files. Before database management systems (DBMSs) came along,

organizations usually stored information in such systems.

Keeping organizational information in a file-processing system has a number of

major disadvantages:

1) Data redundancy and inconsistency. Since different programmers create

the files and application programs over a long period, the various files are

likely to have different formats and the programs may be written in several

programming languages. Moreover, the same information may be duplicated

in several places (files). For example, the address and telephone number of a

particular customer may appear in a file that consists of savings-account

records and in a file that consists of checking-account records. This

redundancy leads to higher storage and access cost. In addition, it may lead

to data inconsistency; that is, the various copies of the same data may no

longer agree. For example, a changed customer address may be reflected in

savings-account records but not elsewhere in the system.

2) Difficulty in accessing data : Conventional file-processing environments do

not allow Needed data to be retrieved in a convenient and efficient manner.

More Responsive data-retrieval systems are required for general use.This

problem can be easily accommodate by Databast management system.

3) Data isolation :: Because data are scattered in various files, and files may

be in different formats, writing new application programs to retrieve the

appropriate data is difficult.

4) Integrity problems. The data values stored in the database must satisfy

certain types of consistency constraints. For example, the balance of a

bank account may never fall below a prescribed amount (say, $25).

Developers enforce these constraints in the system by adding appropriate

code in the various application programs. However, when new constraints are

added, it is difficult to change the programs to enforce them. The problem is

harder when constraints involve several data items from different files.

5) Atomicity problems. A computer system, like any other mechanical or

electrical device, is subject to failure. In many applications, it is crucial that,

if a failure occurs, the data be restored to the consistent state that existed

prior to the failure. Consider a program to transfer $50 from account A to

account B.

If a system failure occurs during the execution of the program, it is

possible that the $50 was removed from account A but was not credited to

account B, resulting in an inconsistent database state. This inconsistent state

must be removed. The fund transfer must be atomic that is if failure occurs

whatever updates performed must be reversible. It is difficult to ensure

atomicity in a conventional file-processing system.

6) Concurrent-access anomalies :: For the sake of overall performance of the

system and faster response, many systems allow multiple users to update

the data simultaneously. In such an environment, interaction of concurrent

updates may result in inconsistent data. Consider bank account A, containing

$500. If two customers withdraw funds (say $50 and $100 respectively) from

account A at about the same time, the result of the concurrent executions

may leave the account in an incorrect (or inconsistent) state.

To guard against this possibility, the system must maintain some form

of supervision. But supervision is difficult to provide because data may be

accessed by many different application programs that have not been

coordinated.

DBMS provides locking mechanism to guard such a anomalies.

7) Security Problem:: Not every user of the database system should be able

to access all the data. Based on user’s role or privilege users are restricted to

access all data.