Latest CSE Info: Database Management System Important Questions 1

DESCRIPTIVES

Q.1

What is a database? Describe the advantages and disadvantages of using of DBMS.

(7)

Ans: Database

– A database is a collection of related data and/or information stored so

that it is available to many users for dif ferent purp oses.

Advantages Of DBMS

Centralized Management and Control

- One of the main advantages of using a

database system is that the organization can exert, via the DBA, centralized

management and control over the data.

Reduction of Redundancies and Inconsistencies -

Centralized control avoids

unnecessary duplication of data and effectively reduces the total amount of data

storage required. Removing redundancy eliminates inconsistencies.

Data Sharing

- A database allows the sharing of data under its control by an y

number of application programs or users.

Data Integrity

- Data integrity means that the data contained in the database is both

accur ate and consistent. Centralized control can also ensure that adequate checks are

incorporated in the DBMS to provide data integrity.

Data Security

- Data is of vital importance to an organization and may be

confidential. Such confidential data must not be accessed by unauthorized persons.

The DBA who has the u ltimate responsibility for the data in the DBMS can ensure

that proper access procedures are followed. Different levels of secu rity could be

implemented for various types of data and operations.

Data Independence

- Data independence is the capacity to chan ge the schema at

one level of a database system without having to change the schema at the next

level. It is usually considered from two points of view: physical data independence

and logical data independence. Physical data independence is the capacity to change

the internal schema without having to change conceptual schema. Logical data

independence is the capacity to chan ge the conceptual schema without having to

change external schemas or application programs.

Providing Storage Structures for Efficient Query Processing -

Database systems

provide capabilities for efficiently ex ecuting queries and updates. Auxiliary files

called

indexes

ar e used for this purpose.

Backup and Recovery -

These facilities are provided to recover databases from

hardware and/or so ftware failures.

Some o ther advantages are:

Reduced Application Development Time

Flexibility

Availability of up-to-date Information

Disadvantages Of DBMS

Cost of Software/Hardware and Migration

- A significant disadvantage of the

DBMS system is cost.

Reduced Response and T hroughput

- The processing overhead introduced by the

DBMS to implement security, integrity, and sharing of the data causes a degradation

of the response and throughput times.

Problem with Centralization

- Centr alization also means that the data is accessible

from a single source namely the database. This increases the potential of security

breach es and disruption of the operation of the organization because of downtimes

and failures.

Q.2

Explain five duties of Database Administrator.

(7)

Ans:

1. DBA administers the three lev els of the database and, in consultation with the

overall user community, sets up the definition of the global view or conceptual level

of the database.

2. Mappings between the internal and the conceptual levels, as well as between the

conceptual and external levels, ar e also defined by the DBA.

3. DBA ensur es that appropriate measures are in place to maintain the integrity of the

database and that the database is not accessible to unauthorized users.

4. DBA is responsible for granting permission to the users of the database and stores

the profile of each user in the database.

5. DBA is responsible for defining procedu res to recover the database from failures

with minimal loss of data.

Q.3

Explain the terms primary key, candidate key and foreign key. Give an example for

each.

(7)

Ans: Primary Key

– Primary key is one of the candidate keys that uniquely

identifies each row in the relation.

Candidate Key

– A candidate key of an entity set is a minimal superkey, that uniquely

identifies each row in the relation.

Foreign Key

– Let there are two relations (tables)

and

. An y candidate key of the

relation

which is referred in the relation

is called the

foreign key

the relation

and

referenced key

in the relation

The relation

also called as

parent table

and relation

is also called as

child table

For ex ample:

STUDENT

Enrl No

Mobile

Roll No Name

City

11 17 Ankit Vats Delhi 9891663808

15 16 Vivek Rajput Meerut 9891468487

6 6 Vanita Punjab

33 75 Bhavya Delhi 9810618396

GRADE

Grade

Roll No Course

6 C A

17 VB C

75 VB A

6 DBMS B

16 C B

Roll No is the primary key in the relation STUDENT and Roll No + Course is the

primary key of the relation GRADE.

Enrl No and Roll No are the candidate keys of the relation STUDENT.

Roll No in the relation GRADE is a foreign key whose values must be one of those

of the relation STUDENT.

Q.4

Differentiate between logical database design and ph ysical database design. Show how

this separation leads to data independence.

(7)

Ans:

Basis Logical Database Design Physical Database Design

Task Maps or transforms the conceptual

The specifications for the stored

schema (or an ER schema) from the

database in terms of physical storage

high-lev el data model into a

structures, record placement, and

relational database schema.

indexes are designed.

Choice of

The mapping can proceed in two

The following criteria are often used

criteria

stages:

to guide the choice of physical

database design options:

System-independent mapping

but data model-dependent

Response Time

Tailoring the schemas to a

Space Utilization

specific DBMS

Transaction Throughput

Result DDL statements in the language of

An initial determination of storage

the chosen DBMS that specify the

structures and the access paths for

conceptual and ex ternal level

the database files. This corresponds

schemas of the datab ase system. But

to defining the internal schema in

if the DDL statements include some

terms of Data Storage Definition

physical design parameters, a

Language.

complete DDL specification must

wait until after the physical

database design phase is completed.

The database design is divided into several phases. The logical database design and

physical database design are two of them. This separation is generally based on the

concept of three-level architecture of DBMS, which provides the data independence.

Therefo re, we can say that this separation leads to data independence because the output

of the logical database design is the conceptual and external level schemas of the database

system which is independent from the output of the ph ysical database design that is

internal schema.

Q.5

Consider the following relation schemes:

7=14)

Project (Project#, Project_name, chief_architect)

Employee (Emp#, Empname)

Assigned_To (Project#, Emp#)

Give expression in Tuple calculus and Domain calculus for each of the queries below:

(i)

Get the employee numb ers of employees who work on all projects.

(ii)

Get the employee numbers of employees who do not work on the COMP123

project.

Ans:

( i) T up le Ca l cu lu s:

{t[Emp#] | t

ASSIGNED_TO

p (p

PROJECT

u (u

ASSIGNED_TO

p[Project#] = u[Project#]

t[Emp#] = u[Emp#]))}

Domain Calculus:

{e |

p (

ASSIGNED_TO

(

, n

, c

PROJECT

, e>

ASSIGNED_TO))}

(ii) Tuple Calculus

{t[Emp#] | t

ASSIGNED_TO

u (u

ASSIGNED_TO

u [ P ro j ect # ] = ‘ C OM P 1 23 ’

t[ Em p # ] = u [ Emp # ] ) }

Domain Calculus:

{e |

p (

ASSIGNED_TO

, e

(

, e

ASSIGNED_TO

‘COMP123’

e))}

Q.6

What is ODBC? How does Or acle act as ODBC and give examples of front end uses with

ODBC.

(7)

Ans:

ODBC

– Open DataBase Connectivity (ODBC) enable the integration of SQL with a

general-purpose programming language. ODBC expose database capabilities in a

standardized way to the application programmer through an application programming

interface (API). Using ODBC, an application can access not just one DBMS but several

different ones simultaneously.

ODBC achieve portability at the level of the executable by introducing an extra level of

indirection. All direct interaction with a specific DBMS happens through a DBMS-

specific driver. A driver is a software pro gram that translates the ODBC calls into DBMS-

specific calls. Drivers are loaded dynamically on demand since the DBMSs the

application is going to access are known only at run-time. Available drivers are registered

with a driver manager. The Oracle database driver translates the SQL commands from the

application into equivalent commands that the Oracle DBMS understands and takes the

result from the DBMS and translate

into equivalen t form for the application.

Example: Let there b e a DSN named EMPLOYEE through, which we want to access the

Oracle d atabase in Visual Basic.

Dim CN As New ADODB.Connection

Dim RS As New ADODB.Recordset

CN.Open “DSN=employee”, “scott”, “tiger”

RS.Open “Select * From Emp”, CN

Q.7

Define the five basic operators of relational algebra with an ex ample each.

(7)

Ans: Five basic operators of relational algebra are:

1. Union

(

)

Selects tuples that are in either P or Q or in both of them.

The

duplicate tuples are eliminated

R = P

2. Minus (–) -

Removes common tuples from the first relation.

R = P – Q

3. Cartesian Product or Cross Product (

)

The cartesian product of two

relations is the concatenation of tuples belonging to the two relations and consisting of

all possible combination of the tuples

R = P

For Ex ample:

ID Name

Name

101 Jones

100 John

103 Smith

104 Lalonde

R = P

Q R = P – Q

ID Name

100 John

101 Jones

103 Smith

104 Lalonde

R = P

P.ID P.Name Q.ID Q.Name

101 Jones 100 John

101 Jones 104 Lalonde

103 Smith 100 John

103 Smith 104 Lalonde

104 Lalonde 100 John

104 Lalonde 104 Lalonde

4. Projection (

) -

The projection of a relation is defined as a projection of all its tuples

over some set of attributes, i.e., it yields a

vertical subset

of the relation. It is used to

either

reduce

the number of attributes (d egree) in the resultant relation or to

reorder

attributes. The projection of a relation T on the attribute A is denoted by

(T).

5. Selection (

) -

Selects only some of the tuples, those satisf y given criteria, from the

relation. It yields a

horizontal subset

of a given relation, i.e., the action is d efined over a

complete set of attribute names but only a subset of the tuples are included in the result.

R =

(P)

For Ex ample:

EMPLOYEE:

Id Name Name

101 Jones Jones

Smith

103 Smith

Lalonde

104 Lalonde

106 Byron B yron

Projection of relation EMPLOYE E over attribute Name

EMPLOYEE: Result of Selection

Id Name Id Name

101 Jones 104 Lalonde

106 B yron

103 Smith

104 Lalond e

106 Byron

Result of Selection over

EMPLOYE E

for

ID > 103

Q.8

Explain entity integrity and ref erential integrity rules in relational model. Show how

these ar e realized in SQL.

(7)

Ans:

Entity Integrity Rule –

No primar y key value can be null.

Referential Integrity Rule –

In referential integrity, it is ensured that a value that appears

in one relation for a giv en set of attributes also appears for a certain set of attributes in

another r elation.

In SQL, entity integrity and referential integrity rules are implemented as constraints on

the relation called as primary key constraint and reference key constraint respectively.

These constraints can be specified with relation at the time of creation of the relations or

after the creation of the r elations by alterin g the definition of the relations. For example:

CREATE TABLE DEPT

(DEPTNO NUMBER PRIMARY KEY,

DNAME VARCHAR2(15));

CREATE TABLE EMP

(EMPNO NUMBER PRIMARY KEY,

2 0

ENAME VARCHAR2(15),

JOB VARCHAR2(10),

DEPTNO NUMBER REFERENCES DEPT(DEPTNO));

Q.9

What are the advantages of embedded query language? Give an ex ample of a embedded

SQL query.

(7)

Ans:

Embedded query language –

SQL can b e implemented in two ways. It can be used

interactively or embedded in a host language or by using API. The use of SQL commands

within a host language (e.g., C, Java, etc.) program is called embedded quer y language or

Embedded SQL. Although similar capabilities are supported for a variety of host

languages, the syntax sometimes varies. Some of the advantages of embedded SQL are:

SQL statements can be used wherever a statement in the host language is allowed.

It combines the strengths of two programming environments, the procedural features of

host languages and non-procedural features of SQL.

SQL statements can refer to variables (must be prefixed by a colon in SQL statements)

defined in the host pro gr am.

Special program variables (called null indicators) are used to assign and retriev e the

NULL values to and from the database.

The facilities available through the interactive query language are also automatically

available to the host programs.

Embedded SQL alon g with host languages can be used to accomplish ver y complex and

complicated data access and manipulation tasks.

Example: The following Embedded SQL statemen t in C inserts a row, whose column

values ar e based on the values of the host language variables contained in it

EXEC SQL

INSERT INTO Sailors VALUES (:c_sname, :c_sid, :c_rating, :c_age);

Q.10

Consider the following relations:

(3.5 x 2=7)

S (S#, SNAME, STATUS, C ITY)

SP (S#, P#, QTY)

P (P#, PNAME, COLOR, WEIGHT, CITY)

Give an ex pression in SQL fo r each of qu eries below:

(i) Get supplier names for supplier who supply at least one red part

(ii) Get supplier names for supplier who do not supply part P2.

Ans:(i)

SELECT SNAME FROM S

WHERE S# IN (SELECT S# FROM SP

WHERE P# IN (SELECT P# FROM P

WHERE COLOR = RED’))

(ii)

SELECT SNAME FROM S

WHERE S# NOT IN (SELEC T S# FROM SP WHERE P# = ‘P2’)

Q.11

Define a view and a trigger. Construct a view for the above relations which has the

information about supp liers and the parts they supply. The view contains the S#,

SNAME, P# , PNAME renamed as SNO, NAME, PNO, PNAME.

(7)

2 1

Ans:

View –

A view is a virtual table which is based on the one or more physical tables and/or

views. In other words, a view is a named table that is represented, not by its own

physically separate stored data, but by its definition in terms of other named tables (base

tables or views).

Trigger –

A trigger is a procedure that is automatically invoked by the DBMS in the

response to specified ch anges to the database. Triggers may be used to supplement

declarative referential integrity, to enforce complex business rules or to audit changes to

data.

Command:

CREATE VIEW SUP_PART (SNO, NAME, PNO, PNAME) AS

SELECT S.S#, SNAME, P.P#, PNAME

FROM S, SP, P

WHERE S.S# = SP.S# AND P.P# = SP.P#

Q.12

Differentiate between the following:

(10)

(i) Theta Join. (ii) Equi Join. (iii) Natural Join

(iv) Outer Join.

Ans:(i) Theta Join –

The theta join operation is an extension to the natural-join

operation that allows us to combine selection and a Cartesian product into a single

operation. Consider relations

r(R)

and

s(S)

, and let be a predicate on attributes in the

schema

. The theta join operation

is defined as follows:

s =

(r x s)

(ii)

Equi Join –

It produces all the combinations of tuples from two relations that

satisfy a join condition with only equality comparison (=).

(iii)

Natural Join -

Same as equi-join except that the join attributes (having same

names) are not included in the resulting relation. Only one sets of domain compatible

attributes involved in the natural join are present.

(iv)

Outer Join -

If there ar e any values in on e table that do not h ave corresponding

value(s) in the other, in an equi-join that will not be selected. Such rows can be forcefully

selected by using the outer join. The corresponding columns for that row will have

NULLs. There are actually three forms of the outer-join operation: left outer join (

right outer join (

) and full outer join (

Q.13

What are temporary tables? When are they useful? Justify with an example.

(4)

Ans:

Temporary tables exists solely for a particular session, or whose data persists for the

duration of the transaction. The temporary tables are generally used to support specialized

rollups or specific application processing requirements. Unlike a p ermanent table, a space

is not allocated to a temporary table when it is created. Space will be dynamically

allocated for the table as rows are inserted. The CREATE GLOBAL TEMPORARY

TABLE command is used to create a temporary table in Oracle.

CREATE GLOBAL TEMPORARY TABLE (

) ON COMMIT {PRESERVE|DELETE} ROWS;

Q.14

Draw and explain the three level architecture of the database system.

(7)

Ans:

A DBMS provides three levels of data is said to follow three-level architecture. The goal

of the three-schema architecture is to separate the user applications and the physical

database. The view at each of these levels is described by a schema. The processes of

transforming requests and results between lev els are called

mappings

. In this architecture,

schemas can be defined at the following three levels:

External Level or Subschema –

It is the highest level of database abstraction where

only those portions of the database of concern to a user or application program are

included. Any number o f user views (some of which may be identical) may exist for a

given global or conceptual view. Each external view is described b y means of a schema

called an

external schema

subschema

Conceptual Level or Conceptual Schema -

At th is level of database abstraction all the

database entities and the relationships among them are included. One conceptual view

represents the entire database. This conceptual view is defined by the

conceptual

schema

. There is only one conceptual schema per database. The description of data at

this level is in a format independent of its physical repr esentation. It also includes

featur es that specify the checks to retain data consistency and integrity.

Internal Level or Physical Schema –

It is closest to the physical storage method used.

It indicates how the d ata will be stored and describes the data structures and access

methods to be used by the database. The internal view is expressed by the

internal schema

Latest CSE Info

Saturday, March 31, 2012

Database Management System Important Questions 1

No comments:

Post a Comment