Latest CSE Info: Database Management System Important Questions 2

DESCRIPTIVES

Q.15

Explain (a) Heap file (b) Sorted file. Also discuss their advantages and disadvantages.

Ans:

Heap File is an unordered set of records, stored on a set of pages. This class

provides basic support for inserting, selecting, updating, and deleting records. Temporar y

heap files are used for external sorting and in other relational operators. A sequential scan

of a heap file (via the Scan class) is the most basic access method.

Sorted file

The

sort

utility shall perform one of the following functions:

Sort lines of all the named files togeth er and write the result to the specified output.

Merge lines of all the named (presorted) files together and write the result to the specified

output.

Check that a single input file is correctly presorted.

Comparisons shall be based on one or more sort keys extracted f rom each line of input

(or, if no sort keys are specified, the entire line up to, but not including, the terminating

), and shall be performed using the collating sequence of the current locale

Q.16

Describe a method for direct search? Explain how data is stored in a file so that direct

searchin g can be p erformed.

Ans:

For a file of unordered fixed length records using unspanned blocks and

contiguous allocation, it is straight forward to access any record by its position in the file.

If the file records are numbered 0,1,2,---,r-1 and the records in each block are numbered

0,1,---bf r-1; where bfr is the blocking factor, then ith record of the file is located in block

[(i/bfr)] and is the (I mod bfr)

record in that block. Such a file is often called a relative or

direct file because records can easily be accessed directly b y their relative positions.

Accessing a record based on a search condition; however, it facilitates the construction of

access paths on the file, such as the index es.

Q1 7

Explain the integrity constraints: Not Null, Unique, Primary Key with an example each.

Is the combination ‘Not Null, Primary Key’ a valid combination. Justify.

(7)

Ans: Not Null –

Should contain valid values and cannot be NULL.

Unique –

An attribute or a combination of two or more attributes must have a unique

value in each row. The unique key can have NULL values.

Primary Key –

It is same as unique key but cannot have NULL values. A table can have

at most one primary key in it.

For ex ample:

STUDENT

Mobile

Roll No Name

City

17 Ankit Vats Delhi 9891663808

16 Vivek Rajput Meerut 9891468487

6 Vanita Punjab NULL

75 Bhavya Delhi 9810618396

Roll No is a primary key.

Name is defined with NOT NULL, means each student must have a name.

Mobile is unique

‘

Not Null, Primary Key

’ is a valid combination. Primary key constraint alread y includes

‘Not Null’ constraint in it but we can also add ‘Not Null’ constraint with it. The use of

‘Not Null’ with ‘Primary Key’ will not have any effect. It is same as if we are using just

‘Primary Key’.

Q.18

Explain the followings :

(i)

Nested Queries.

(ii)

Cursors in SQL.

(iii)

RDBMS.

(iv)

View

(v)

Application Programming Interface

(14)

Ans: (i) Nested Queries –

A SELECT query can have subquer y(s) in it. When a

SELECT query having another SELECT query in it, is called as nested query. Some

operations cannot be performed with single SELECT command or with join operation.

There are some operations which can be performed with the help of nested queries (also

referred to as subqueries). For example, we want to compute the second highest salary:

SELECT MAX(SAL) FROM EMP WHERE SAL < (SELECT MAX(SAL) FROM EMP)

Some operations can be performed both by Join and subqueries. The Join operation is

costlier in terms of time and space. Therefore, the solution based on subqueries is

preferred.

(ii) Cursors in SQL

– An object used to store the output of a query for row-by-row

processing by the application programs. Cursors are constructs that enable the user to

name a private memory area to hold a specific statement for access at a later time.

Cursors are used to process multi-row result sets one row at a time. Additionally, cursors

keep track of which row is currently being accessed, which allows for interactive

processing of the active set.

(iii) RDBMS –

RDBMS is a database management system (DBMS) that stores data in

the form of relations. R elational databases are powerful because they require f ew

assumptions about how data is related o r how it will be extracted f rom the database. As a

result, the same database can be viewed in many different ways. An important feature of

relational system is that a single database can be spread across several tables. This differs

from flat-file databases, in which each database is self-contained in a single table.

(iv) View –

A view is a relation (virtual rather than base) and can be used in query

expressions, that is, queries can be written using the view as a relation. In other words, a

view is a named table that is represented, not b y its own physically separate stored data,

but by its definition in terms of other named tables (base tables or views). The base

relations on which a view is based are sometimes called the existing relations. The

definition of a view in a create view statement is stored in the system catalog. The syntax

to create a view is:C REATE [ OR R EPLACE] VIEW [( )] AS

WITH {READ ONLY|CHECK OPTION [CONSTRAINT

]};

(v) Application Programming Interface –

Commercial SQL implementations take

one of the two basic techniques for includin g SQL in a programming lan guage –

embedded SQL and ap plication program interface (API). In the application program

interface approach, the program communicates with the RDBMS using a set of functions

called the Application Program Interface ( API). The program passes the SQL statements

to the RDBMS usin g API calls and uses API calls to retrieve the results. In this method,

the precompiler is not r equired.

Q.19

Consider the following relational schema:

(7)

PERSON (SS#, NAME, ADDRESS)

CAR (REGISTRATION_NUMBER, YEAR , MODEL)

ACCIDENT (DATE, DRIVER, CAR_REG_NO)

OWNS (SS#, LICENSE)

Construct the following r elational algebra queries:

(i)

Find the names of persons who are involved in an accident.

(ii)

Find the registration number of cars which were n ot involved in any accident.

Ans

(i)

(PERSON)

(ACCIDENT)

NAME

DRIVER

(ii)

(CAR) –

(ACCIDENT)

REGISTRATION_NUMBER

CAR_REG_NO

Q.20

What is a key? Explain Candidate Key, Alternate Key and Foreign Key.

(7)

Ans:

Key –

A single attribute or a combination of two or more attributes of an entity set that is

used to identify one or more instances (rows) o f the set (table) is called as key.

Candidate Key –

A candidate key is a minimal superkey, which can be used to uniquely

identify a tuple in the relation.

Alternate Key –

All the

candidate keys except primary key

ar e called as alternate keys.

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

in the relation

and

referenced key

in the relation

. The relation

is also called as

parent table

and relation

is also called as

child table

Q.21

What is data independence? Explain the difference between ph ysical and logical data

independence.

(7)

Ans:

Data independence is the capacity to change the schema at one level of a database

system without having to change the schema at the next level. The three-schema

architecture allows the feature of d ata independence. Data independence occurs

because when the schema is changed at some level, the schema at the next level

remains unchanged; only the

mapping

between the two levels is changed. Types of

data independ ence are:

Physical Data Independence –

It is capacity to change the internal schema without

having to change conceptual schema. Hen ce, the external schemas need not be changed

as well. Changes to the internal schema may be needed because some physical files had

to be reorganized to improve the performance of retrieval or update. If the same data as

before remains in the database, the conceptual schema needs not be changed.

Logical Data Independence -

It is the capacity to change the conceptual schema

without having to change external schemas or application programs. The conceptual

schema may be changed to expand the database (by adding a record type or data item),

to change constraints, or to reduce the database (b y removin g a reco rd type or data

item). Only the view definition and the mappings need be changed in a DBMS that

supports logical data independence. Chan ges to constraints can be applied to the

conceptual sch ema without affecting the external schemas or application programs.

Q. 2 2

Write short notes on:

(i)

Weak and strong entity sets.

(ii)

Typ es of attributes.

(iii)

Oracle Instance.

(iv)

Mid square method of hashing.

(4 x 4 = 16 )

Ans: (i)

Weak and Strong entity sets:

A strong entity set has a primary key. All

tuples in the set are distinguishable by that key. A weak entity set has no primary key

unless attributes of the strong entity set on which it depends are included. Tuples in a

weak entity set are partitioned according to their relationship with tuples in a strong entity

set. Tuples within each partition are distinguishable by a discriminator, which is a set of

attributes. A strong entity set has a primary key. All tuples in the set are distinguishable

by that key. A weak entity set has no primary key unless attributes of the strong entity set

on which it depends are included. Tuples in a weak entity set are partitioned according to

their relationship with tuples in a strong entity set. Tuples within each partition are

distinguishable by a discriminator, which is a set of attributes.

(ii) T ypes of attributes:

An attribute's type determines the kind of values that are allowed

in the attribute. For example, the value

version 1

is not valid for an attribute defined as an

integer, but the value

is valid. Numeric types (such as integer or real) can also be

limited to a predefined r ange by th eir attribute definition.

Choice :

An attribute with a list of predefined values.

ID Reference:

An attribute with a value that is a Unique ID value from another element.

It is typically used for element-based cross-references.

ID References:

An attribute with a value of one or more Unique ID values from

another element.

Integer:

An attribute with a whole number value (no decimal parts). Examples of valid

integers are 22, -22, and +322. An integer can be defined to fall within a range.

Integers:

An attribute with a value of one or more integers. Enter each number on a

separate line in the Attribute Value text box.

Real An attribute with a real number value, with or without a decimal part (the value

can also be expressed in scientific notation). Examples of valid real numbers are 2, 22.4, -

-1

0.22, and 2.3e

. A real number can be defined to fall within a range.

Reals:

An attribute with a value of one or more real numbers. Enter each number on a

separate line in the Attribute Value text box.

String:

An attribute with a value of a series of characters (text).

Strings:

An attribute with a value of one or more strings. Enter each string on a

separate line in the Attribute Value text box.

Unique ID:

An attribute with a value of a unique text string. An element can have only

one ID attribute (which can be of type Unique ID or Unique IDs). All ID values must be

unique in the document or book. An element with a Unique ID attribute can be the source

for an element-based cro ss-reference.

Unique IDs:

An attribute with a value of one or more unique text strings. Enter each

string on a separ ate line in the Attribute Value text box.

(iii)

Oracle Instances:

An instance is the (executed) Oracle software and the memor y

they use. It is the instance that manipulates the data stored in the database. It can be

started independ ent of an y database. It consists of:

A shared memo ry area that provides the communication between various p rocesses.

Upto five background processes which handled various tasks.

Whenever an oracle instance starts, the file ‘INIT.OR A’ is ex ecuted.

(iv)

Mid square method of hashing:

In midsquare hashing, the key is squared and the

address selected from the middle of the squared nu mber.

Mid square method

* Square K.

* Strip predetermined digits from front and rear.

* e.g., use thousands and ten thousands places.

Q.23

Consider the following relational schemas:

EMPLOYEE (EMPLOYEE_NAME, STREET, CITY)

WORKS (EMPLOYEE_NAME, COMPANYNAME, SALARY)

COMPANY (COMPANY_NAME, CITY)

Specify th e table definitions in SQL.

(5)

Ans:

CREATE TABLE EMPLOYEE

( EMPLOYEE_NAME VARCHAR2(20) PRIMARY KEY,

STREET VARCHAR2(20),

CITY VARCHAR2(15));

CREATE TABLE COMPANY

( COMPANY_NAME VARCHAR2(50) PRIMARY KEY,

CITY VARCHAR2(15));

CREATE TABLE WORKS

( EMPLOYEE_NAME VARCHAR2(20)

REFERENCES EMPLOYEE(EMPLOYEE_NAME,

COMPANYNAME VARCHAR2(50)

REFERENCES COMPANY(COMPANY_NAME,

SALARY NUMBER(6),

CONSTRAINT WORKS_PK PRIMARY KEY(EMPLOYEE_NAME,

COMPANY_NAME));

Q.24

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

(9)

(i)

Find the names of all employees who work f or first Bank Corporation.

(ii)

Find the names and company names of all employees sorted in ascendin g order of

company name and descending order of employee names of that compan y.

(iii)

Change th e city of First Bank Corporation to ‘New Delhi’

Ans:

(i)

SELECT EMPLOYEE_ NAME

FROM WORKS

WHERE COMPANYNAME = ‘First Bank Corporation’;

(ii)

SELECT EMPLOYEE_ NAME, COMPANYNAME

FROM WORKS

ORDER BY COMPANYNAME, EMPLOYEE_NAME DESC;

(iii)

UPDATE COMPANY

SET CITY = ‘New Delhi’

WHERE COMPANY_NAME = ‘First Bank Corporation’;

2 5

Discuss the correspond ence between the E-R model construct and the relation model construct.

Show how each E-R model construct can be mapped to the relational model using the

suitable example?

Ans:

An entity-relationship model (ERM):

An entity-relationship model (ERM) is

an abstract conceptual representation of structured data. Entity-relationship modeling is a

relational schema database modeling method, used in software engineering to produce a

type of conceptual data model (or semantic data model) of a system, often a relational

database, and its requirements in a top-down fashion. Diagrams created using this process

are called

entity-relationship diagrams

, or

ER diagrams

ERDs

for sho rt.

ER-to-Rela tional Mapping Algorithm:

Step 1: Mapping of regular entity types:

For each strong entity typ e E, create a

relation T that includes all the simple attributes of a composite attribute.

Step2: Mapping of weak entity types:

For each weak entity type W with owner entity

type E, create relation R and include all simple attributes (or simple components of

composite attributes) of W as attributed of R. In addition, include as foreign key attributes

of R, the primar y key attribute (s) of relation(s) that correspond to the owner(s) and the

partial key of the weak entity type W, if any.

Mapping of relationship types:

form a relation R, for relationship with primary keys

of participating relations A and B as foreign keys in R. In addition to this, any attributes

of relationship become an attribute of R also.

4) Mapping of multivalued attributes:

For each multilvalued attribute A, create a new

relation R. This relation R will include an attribute corresponding to A, plus primary key

attribute K-as a foreign key in R-o f the relation that represents the entity type or

relationship type that has A as an attribute.

Q.26

Explain the concepts of relational data model. Also discuss its advantages and

disadvantages.

(7)

Ans:

Relational Data Model –

The relational model was first introduced b y Prof. E.F. Codd of

the IBM Resear ch in 1970 and attracted immediate attention due to its simplicity and

2 9

DC 1 0

DA T AB A S E MA N AGE ME N T S Y ST E MS

mathematical foundation. The model uses the concept of a math ematical relation (like a

table of values) as its basic building block, and h as its theoretical basis in set theor y and

first-order predicate lo gic. The relational model represents the database as a

collection of

relations

. The relational model like all other models consists of three basic

components:

a set of domains and a set of relations

operation on relations

integrity rules

Advantages

•

Ease of use –

The revision of any information as tables consisting of rows and

columns is quite natural and therefore even first time users find it attractive.

•

Flexibility –

Different tables from which information has to be linked and extracted

can be easily manipulated by operators such as project and join to give information in

the form in which it is desired.

•

Security –

Security control and authorization can also be implemented more easily b y

moving sensitive attributes in a given table into a separate relation with its own

authorization controls. If authorization requirement permits, a particular attribute

could be joined back with others to enable full inf ormation retrieval.

•

Data Independence –

Data independence is achieved more easily with normalization

structure used in a relational database than in the more complicated tree or network

structure. It also frees the users from details of storage structure and access methods.

•

Data Manipulation Language –

The possibility of responding to ad-hoc quer y b y

means of a language based on relational algebra and relational calculus is easy in the

relational database approach. Provides simplicity in the data organization and the

availability of reasonably simple to very powerful query languages.

Disadvantages

•

Perf ormance

– If the number of tables between which relationships to be established

are large and the tables themselves are voluminous, the performance in responding to

queries is definitely degr aded.

•

Unsuitable f or Hierarchies

– While the relational database approach is a logically

attractive, commercially feasible approach, but if the data is for example naturally

organized in a hierarchical manner and stored as such, the hierarchical ap proach may

give better r esults.

Q.27

Consider the following relational schema:

(14)

Doctor(DName,Reg_no)

Patient(Pname, Disease)

Assigned_To (Pname,Dname)

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

(i)

Get the names of patients who are assigned to more than one doctor.

(ii)

Get the names of doctors who are treatin g patients with ‘Polio’.

Ans:

(i)Tuple Calculus:

{p[PName] | p

PATIENT

ASSIGNED_TO

p[PName] = a

[PName]

[PName] = a

[PName]

[DName]

[DName])}

Domain Calculus:

{p |

, d

, p

, d

( s>

PATIENT

, d

ASSIGNED_TO

, d

ASSIGNED_TO

= p

)}

(ii)Tuple Calculus:

{u[Dname] | u

ASSIGNED_TO

t (t

PATIENT

t[Disease] = ‘Polio’

t[PName] = u[PName])}

Domain Calculus:

{d |

, p

, s

(

, d>

ASSIGNED_TO

, s

PATIENT

= p

= ‘Polio’)}

Q.28

What are the features of embedded SQL? Explain.

(7)

Ans: Embedded SQL

– SQL can be 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

query language or Embedded SQL. Although similar capabilities are supported for a

variety of host languages, the syntax sometimes varies. Some of the features 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.

Q.29

What is the purpose of tables, private synonyms and public synon yms? If there are

multiple objects of same name on an Oracle database, which order are they accessed in?

Ans:

The purpose of table is to store data. If we use the PUBLIC keyword (or no

keyword at all), anyone who has access to the database can use our synonym. If the

database is not ANSI-compliant, a user does not need to know the name of the owner of a

public synon ym. Any synonym in a database that is not ANSI-compliant

and

was created

in an Informix database server is a public synonym. In an ANSI-compliant database, all

synonyms ar e private. If you use the PUBLIC or PR IVATE keywords, the database server

issues a syntax error. If you use the PRIVATE keyword to declare a synonym in a

database that is not ANSI-compliant, the unqualified synonym can be used by its owner.

Other users must qualify the synonym with the name of the owner

Q.30

Explain the followings:

(14)

(i)

Temporary Tables

(ii)

Integrity Constraints.

Ans:

(i) Temporary Tables –

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 permanent table, a temporary table does not automatically allocate space 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 temporar y

table in Oracle.

CREATE GLOBAL TEMPORARY TABLE

(

)

ON COMMIT {PRESERVE|DELETE} ROWS;

(ii)Integrity Constraints –

A database is only as good as the information stored in it, and

a DBMS must therefore help prevent the entry of incorrect info rmation. An

integrity

constraint

is a condition specified on a database schema and restricts the data that can be

stored in an instance of the database. If a d atabase instance satisfies all the integrity

constraints specified on the database schema, it is a legal instance. A DBMS enforces

integrity constraints, in that it permits only legal instances to be stored in the database.

Integrity constraints are specified and enfor ced at different times:

When the DBA or end user defines a database schema, he or she specifies the

integrity constr aints that must hold on any instance of this database.

When a database application is run, the DBMS checks for violations and disallows

changes to the data that violate the specified integrity constraints.

Many kinds of integrity constraints can be specified in the relational model, such as,

Not Null, Check, Unique, Primary Key, etc.

Q.31

Explain different types of failures that occur in Oracle database.

(7)

Ans

Types of Failures –

In Oracle database following types of failures can occurred:

Statement Failure

•

Bad data type

•

Insufficient space

Insufficient Privileges (e.g., object privileges to a r ole)

User Process Failur e

•

User performed an abnormal disconnect

•

User's session was abnormally terminated

•

User's program raised an address exception

User Er ror

•

User drops a table

•

User damages data by modification

Instance Failure

Media Failure

•

User drops a table

•

User damages data by modification

Alert Logs

•

Records informational an d error messages

•

All Instance startups and shutdowns are recorded in the log

•

Every Create, Alter, or Drop operation on a rollback segment, tablespace,

or database is reco rd in the log

Recover y Views

DB Verify

•

Used to insure that a datafile is valid before a resto re

Q.32

What is ODBC? What are the uses of ODBC? Under what circumstances we use this

technology?

(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). In contrast to Embedded SQL, ODBC allows a single executable to

access different DBMSs without recompilation. Thus, while Embedded SQL is DBMS-

independent only at the source code level, applications using ODBC are DBMS-

independent at the source code level and at the level of the executable.

All direct interaction with a specific DBMS happens through a DBMS-specific driver. A

driver is a software program 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 driver translates the SQL commands from th e application into equivalent commands

that the DBMS understands. An application that interacts with a data source through

ODBC selects a data source, dynamically loads the correspondin g driver, and establishes

a connection with the data source. ODBC achieves portability at the level of the

executable by introducing an extra level of indirection. In addition, using ODBC, an

application can access not just one DBMS but several different ones simultaneously.

Q.33

List an y two significant differences b etween a file processing system and a DBMS.

(4)

Ans:

File Processing System vs. DBMS

Data Independence

- Data independence is the capacity to change the schema at one

level of a database system without having to change the schema at the next level. In file

processing systems the data and applications are generally interdependent, but DBMS

provides the feature of data independence.

Data Redundancy –

Data redundancy means unnecessary duplication of data. In file

processing systems there is redundancy of data, but in DBMS we can reduce data

redundancy b y means of normalization process without affecting the original data. If we

do so in file processing system, it becomes too complex.

Q.34

Differentiate between various levels of data abstr action.

(5)

Ans: Data Abstraction –

Abstraction is the process to hide the irrelevant things from the

users and r epresent the relevant things to the user. Database systems are often used b y

non-computer prof essionals so that the complexity must be hidden from database system

users. This is done by defining levels of abstract as which the database may be viewed,

there are logical view or external view, conceptual view and internal view or ph ysical

view.

External View

– This is the highest level of abstraction as seen b y a user. It

describes only the part of entire database, which is relevant to a particular user.

Conceptual View –

This is the nex t higher level of abstraction which is the sum

total of Database Management System user's views. It describes what data are

actually stored in the database. It contains information about entire database in

terms of a small number of relatively simple structure.

Internal View –

This is the lowest level of abstraction. It describes how the data are

physically stored

Latest CSE Info

Saturday, March 31, 2012

Database Management System Important Questions 2

No comments:

Post a Comment