What is a Database system?

The database and DBMS software together is called as Database system.

Advantages of DBMS?

• Redundancy is controlled.
• Unauthorised access is restricted.
• Providing multiple user interfaces.
• Enforcing integrity constraints.
• Providing backup and recovery.

Disadvantage in File Processing System?

• Data redundancy & inconsistency.
• Difficult in accessing data.
• Data isolation.
• Data integrity.
• Concurrent access is not possible.
• Security Problems.

Describe the three levels of data abstraction?

The are three levels of abstraction:

Define the “integrity rules”

There are two Integrity rules.

What is Data Independence?

Data independence means that “the application is independent of the storage structure and access strategy of data”.

In other words, The ability to modify the schema definition in one level should not affect the schema

definition in the next higher level.
Two types of Data Independence:

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.

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 Object Oriented model?

This model is based on collection of objects. An object contains values stored in instance variables with in the object.

An object also contains bodies of code that operate on the object. These bodies of code are called methods.

Objects that contain same types of values and the same methods are grouped together into classes.

What is an Entity?

It is a ‘thing’ in the real world with an independent existence.

It is a collection (set) of entities that have same attributes.

It is a collection of all entities of particular entity type in the database.

An entity set may not have sufficient attributes to form a primary key, and its primary key compromises of its partial key

and primary key of its parent entity, then it is said to be Weak Entity set.

It is a particular property, which describes the entity.

A relation Schema denoted by R(A1, A2, …, An) is made up of the relation name R and the list of attributes Ai that it contains.

A relation is defined as a set of tuples. Let r be the relation which contains set tuples (t1, t2, t3, …, tn).

Each tuple is an ordered list of n-values t=(v1,v2, …, vn).

It is the number of attribute of its relation schema.

It is an association among two or more entities.

The collection (or set) of similar relationships.

Relationship type defines a set of associations or a relationship set among a given set of entity types.

A data base schema is specifies by a set of definitions expressed by a special language called DDL.

It specifies user views and their mappings to the conceptual schema.

This language is to specify the internal schema. This language may specify the mapping between two schemas.

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.

This language that enable user to access or manipulate data as organised by appropriate data model.

It interprets DDL statements and record them in tables containing metadata.

It is a process of analysing the given relation schemas based on their Functional Dependencies (FDs)

and primary key to achieve the properties

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.

Multivalued dependency denoted by X Y specified on relation schema R, where X and Y are both subsets of R,

specifies the following constraint on any relation r of R: if two tuples t1 and t2 exist in r such that t1[X] = t2[X]

then t3 and t4 should also exist in r with the following properties

where [Z = (R-(X U Y)) ]

It guarantees that the spurious tuple generation does not occur with respect to relation schemas after decomposition.

The domain of attribute must include only atomic (simple, indivisible) values.

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.

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.

A relation schema R is in 3NF if it is in 2NF and for every FD X A either of the following is true

In other words, if every non prime attribute is non-transitively dependent on primary key.

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.

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 true

• X is subset or equal to (or) XY = R.
• X is a super key.

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.

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.

Indexing is a technique for determining how quickly specific data can be found.
Types:

The phase that identifies an efficient execution plan for evaluating a query that has the least

estimated cost is referred to as query optimization.

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.

It is one, which keeps its Physical Structure hidden from user.

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.

AB -> B
AC -> C
C -> B
a) is in 1NF
b) is in 2NF
c) is in 3NF
d) is in BCNF

(a) is in 1NF since (AC)+ = { A, B, C} hence AC is the primary key. Since C B is a FD given, where neither C is a Key

nor B is a prime attribute, this it is not in 3NF. Further B is not functionally dependent on key AC thus it is not in 2NF.

Thus the given FDs is in 1NF.

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.

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.

It is a program module, which ensures that database, remains in a consistent state despite system failures

and concurrent transaction execution proceeds without conflicting.

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.

PL/SQL uses cursors for all database information accesses statements. The language supports the use two types of cursors

How to get the second Max sal from emp table in Oracle?

Answer :
select max(salary)from emp where salary<(select max(salary)from emp)

Que : How many types of joins are exists ? explain with example?

Answer:
There are three basic types of joins, Cross, Inner & Outer join
1. Cross Join – A cross join merges two tables on every record in a geometric fashion, every record of
one table is combined with every record from the other table. Two tables of 10 records each in a cross join will
create a table of 100 (10 X 10) records.
2. Inner Join – An Inner join is used to match two tables based on values of a common field.
It can be thought of as the Intersection of the two sets of data. Inner join is the records that appear in both tables.
3. Outer Join – The Outer join is a type of join that returns all of the members of one set,
and any matching members of the other set. Outer join can be Left Outer join or Right Outer join.
For explaining this we will take two tables Students and Courses
a. Left Outer join – This join will give a list of Students who are not enrolled in any course.
b. Right Outer join – This join will give a list of courses which don’t have any Students enrolled.