Dbms Notes

(1)

KINGS

COLLEGE OF ENGINEERING COLLEGE OF ENGINEERING

DEPARTMENT OF COMPUTER SCIENCE & ENGINEERING

QUESTION BANK

Subject: Database Management System Year/Sem : II / IV

UNIT I

INTRODUCTION AND CONCEPTUAL MODELLING

PART-A ( 2 Marks)

1.

1. Define Entity and entity sets.

Define Entity and entity sets.

•

An entity

An

entity

is an object that exists and

is an object that exists and is distinguishable from other objects. For instance, John

is distinguishable from other objects. For instance, John

Harris with S.I.N. 890-12-3456 is an

Harris with S.I.N. 890-12-3456 is an entity, as he can be

entity, as he can be uniquely identified as one particular

uniquely identified as one particular

person in the universe.

•

An entity may be

An entity may be concrete

concrete

(a person or a book, for example) or

(a person or a book, for example) or abstract

abstract

(like a holiday or a

concept).

•

An

An entity set

entity set

is a set of entities of the same type (e.g., all persons having an account at a bank).

•

Entity sets

Entity sets need not be disjoint

need not be disjoint. For example, the entity set

. For example, the entity set employee

employee (all employees of a bank)

(all employees of a bank)

and the entity set

and the entity set customer

customer (all customers of the bank)

(all customers of the bank) may have members in common.

may have members in common.

•

An entity is represented by a set of

An entity is represented by a set of attributes

attributes..

○

E.g. name, S.I.N., street, city

E.g. name, S.I.N., street, city for ``customer'' entity.

for ``customer'' entity.

○

The

The domain

domain

of the attribute is the set of permitted values (e.g. the

of the attribute is the

set of permitted values (e.g. the telephone number must

telephone number must

be seven positive integers).

•

Formally, an attribute is a

Formally, an attribute is a function

function

which maps an entity set into a domain.

○

Every entity is described by a set of (attribute, data value) pairs.

○

There is one pair for each attribute of the entity set.

○

E.g. a particular

E.g. a particular customer

customer entity is described by the set

entity is described by the set {(name, Harris), (S.I.N., 890-123-

{(name, Harris), (S.I.N.,

890-123-456), (street, North), (city, Georgetown)}.

456), (street, North), (city, Georgetown)}.

An analogy can be

An analogy can be made with the programming language notion of type

made with the programming language notion of type definition.

definition.

•

The concept of an

The concept of an entity set

entity set

corresponds to the programming language

corresponds to the programming language type definition

type definition..

•

A variable of a given type has a particular value at a

A variable of a given type has a particular value at a point in time.

point in time.

•

Thus, a programming language variable corresponds to

Thus, a programming language variable corresponds to an

an entity

entity

in the E-R model.

Figure 2-1 shows two entity sets.

We will be dealing with five

We will be dealing with five entity sets in this section:

entity sets in this section:

•

branch

branch, the set of all branches of a particular bank. Each branch is

, the set of all branches of a particular bank. Each branch is described by the

described by the

attributes

attributes branch-name

branch-name,, branch-city

branch-city and

and assets

assets..

•

customer

customer , the set of all people having an account at the bank. Attributes are

, the set of all people having an account at the bank. Attributes are

customer-

customer-name

name,, S.I.N.

S.I.N.,, street

street and

and

customer-city..

customer-city

•

(2)

•

account

account , the set of all accounts created and maintained in the bank. Attributes are

, the set of all accounts created and maintained in the bank. Attributes are

account-

account-number

number and

and balance

balance..

•

transaction

transaction, the set of all account transactions executed in the bank. Attributes are

, the set of all account transactions executed in the bank. Attributes are

transaction-

transaction-number

number ,, date

date and

and

amount

amount ..

2. Define role in Database Administrator.

A Database Adminstrator, Database Analyst or Database Developer is the

A Database Adminstrator, Database Analyst or Database Developer is the person responsible for managing theperson responsible for managing the information within an organization. As most companies continue

information within an organization. As most companies continue to experience inevitable growth of their databases,to experience inevitable growth of their databases, these positions are probably the most solid within the IT industry. In most cases,

these positions are probably the most solid within the IT industry. In most cases, it is not an area that is targeted it is not an area that is targeted for for layoffs or downsizing. On the downside, however,

layoffs or downsizing. On the downside, however, most database departments are often understaffed, requiringmost database departments are often understaffed, requiring adminstrators to perform a multitude of tasks.

adminstrators to perform a multitude of tasks.

Depending on the company and the department, this role can either be highly specialized or incredibly diverse. The Depending on the company and the department, this role can either be highly specialized or incredibly diverse. The primary role of the Database Administrator is to adminster, develop, maintain and

primary role of the Database Administrator is to adminster, develop, maintain and implement the policies andimplement the policies and procedures necessary to ensure the security and integrity of the co

procedures necessary to ensure the security and integrity of the co rporate database. Sub roles within the Databaserporate database. Sub roles within the Database Administrator classification may include security, architecture, warehousing

Administrator classification may include security, architecture, warehousing and/or business analysis. Other primaryand/or business analysis. Other primary roles will include:

roles will include:

•

• Implementation of data modelsImplementation of data models •

• Database designDatabase design •

• Database accessibilityDatabase accessibility •

• Performance issuesPerformance issues •

• Capacity issuesCapacity issues •

• Data replicationData replication

•

• Table MaintainenceTable Maintainence

Database Administrators are often on-call and required to work

Database Administrators are often on-call and required to work as needed. This position carries an enoas needed. This position carries an enormous amountrmous amount of responsibility.

of responsibility.

3. Define DBMS.what are the advantages of DBMS?

A

ADatabase Management SystemDatabase Management System((DBMSDBMS) is a set of ) is a set of computer programscomputer programsthat controls the creation, maintenance,that controls the creation, maintenance, and the use of the

and the use of the databasedatabase in a computer platform or of an in a computer platform or of an organization and itsorganization and its end usersend users.. It allows organizations toIt allows organizations to place control of organization-wide database development in the hands of

place control of organization-wide database development in the hands of database administratorsdatabase administrators(DBAs) and other (DBAs) and other specialists.

specialists.

A DBMS is a system software package that helps the

A DBMS is a system software package that helps the use of integrated collection of data use of integrated collection of data records and files known asrecords and files known as databases. It allows different user application programs to easily access the same

databases. It allows different user application programs to easily access the same database. DBMSs may use any of adatabase. DBMSs may use any of a variety of

variety of database modelsdatabase models,, such as thesuch as thenetnetworwork k modmodelelor or relational modelrelational model. In large systems, a DBMS allows users and. In large systems, a DBMS allows users and other software to store and retrieve data in a

other software to store and retrieve data in astructuredstructuredway. Instead of having to write computer programs to extractway. Instead of having to write computer programs to extract information, user can ask simple questions in a

information, user can ask simple questions in a query languagequery language. Thus, many DBMS packages provide. Thus, many DBMS packages provide Fourth-

Fourth-generation programming language

generation programming language(4GLs) and other application development (4GLs) and other application development features. It helps to specify the logicalfeatures. It helps to specify the logical organization for a database and

organization for a database and access and use the information within a access and use the information within a database. It provides facilities for database. It provides facilities for controlling

controlling data accessdata access,, enforcingenforcing data integritydata integrity, managing concurrency controlled, restoring database., managing concurrency controlled, restoring database.

Using a DBMS to manage data has many advantages:

Data independence:

Data independence:Application programs should be as independent as possible from details of data representationApplication programs should be as independent as possible from details of data representation and storage. The DBMS can

and storage. The DBMS can provide an abstract view of the provide an abstract view of the data to insulate application code from such data to insulate application code from such details.details.

Efficient data access:

Efficient data access:A DBMS utilizes a variety of sophisticated techniques to store and A DBMS utilizes a variety of sophisticated techniques to store and retrieve data eficiently. Thisretrieve data eficiently. This feature is especially important if the data is stored on ex

feature is especially important if the data is stored on ex ternal storage devices.ternal storage devices.

Data integrity and security:

Data integrity and security:If data is always accessed through the DBMS, the If data is always accessed through the DBMS, the DBMS can enforce integrityDBMS can enforce integrity constraints on the data. For example,

constraints on the data. For example, before inserting salary information for an employee, the DBMS can check before inserting salary information for an employee, the DBMS can check thatthat the department budget is not e

the department budget is not exceeded. Also, the DBMS can xceeded. Also, the DBMS can enforce access controls that govern what data enforce access controls that govern what data is visible tois visible to different classes of users.

(3)

Data administration:

Data administration: When several users share the data, cenWhen several users share the data, centralizing the administration of data can offer significanttralizing the administration of data can offer significant improvements. Experienced professionals who understand the nature of the data being managed, and how di

improvements. Experienced professionals who understand the nature of the data being managed, and how di

erent groups of users use it, can be responsible for organizing the data representation to minimize redundancy and for erent groups of users use it, can be responsible for organizing the data representation to minimize redundancy and for retuning the storage of the data

retuning the storage of the data to make retrieval efficient.to make retrieval efficient.

Concurrent access and crash recovery:

Concurrent access and crash recovery:A DBMS schedules concurrent accesses to the data in such a manner thatA DBMS schedules concurrent accesses to the data in such a manner that users can think of the data

users can think of the data as being accessed by only one as being accessed by only one user at a time. Further, the DBMS protects users from theuser at a time. Further, the DBMS protects users from the effects of system failures.

effects of system failures.

Reduced application development time:

Reduced application development time:Clearly, the DBMS supports many important functions that are common toClearly, the DBMS supports many important functions that are common to many applications accessing data stored in the

many applications accessing data stored in the DBMS. This, in conjunction with the high-level interface to DBMS. This, in conjunction with the high-level interface to the data,the data, facilitates quick development of applications. Such applications are

facilitates quick development of applications. Such applications are also likely to be more robust than applicationsalso likely to be more robust than applications developed from scratch because many

developed from scratch because many important tasks are handled by the DBMS instead of important tasks are handled by the DBMS instead of being implemented by thebeing implemented by the application.

application.

Given all these advantages, is there

Given all these advantages, is there ever a reason not to use ever a reason not to use a DBMS? A DBMS is a complex a DBMS? A DBMS is a complex piece of software,piece of software, optimized for certain kinds of workloads (e.g., an

optimized for certain kinds of workloads (e.g., an swering complex queries or handling many concurrent swering complex queries or handling many concurrent requests), andrequests), and its performance may not be adequate

its performance may not be adequate for certain specialized applications. Examples include applications for certain specialized applications. Examples include applications with tightwith tight real-time constraints or applications with just a few well-defined critical operations for which eficient custom code real-time constraints or applications with just a few well-defined critical operations for which eficient custom code must be written. Another reason for not using

must be written. Another reason for not using a DBMS is that an application may need a DBMS is that an application may need to manipulate the data in wato manipulate the data in waysys not supported by the query language.

not supported by the query language. In such a situation, the abstract view of In such a situation, the abstract view of the data presented by the DBMS does the data presented by the DBMS does notnot match the application’s needs, and

match the application’s needs, and actually gets in the way. As an example, actually gets in the way. As an example, relational databases do not support flexiblerelational databases do not support flexible analysis of text data (although vendors are now extending their products in this direction). If specialized performance analysis of text data (although vendors are now extending their products in this direction). If specialized performance or data manipulation requirements are central to a

or data manipulation requirements are central to a n application, the application may chon application, the application may choose not to use a DBMS,ose not to use a DBMS, especially if the added benefits’ of a DBMS (e.g.,

especially if the added benefits’ of a DBMS (e.g., flexible querying, security, concurrent access, and crash recovery)flexible querying, security, concurrent access, and crash recovery) are not required. In most situations calling for large-scale data management, however, DBMSs have become an are not required. In most situations calling for large-scale data management, however, DBMSs have become an indispensable tool.

indispensable tool.

4. What is meant by E-R model? Why?

In

In software engineeringsoftware engineering, an, an Entity-Relationship ModelEntity-Relationship Model(ERM) is an abstract and conceptual (ERM) is an abstract and conceptual representationrepresentation of

of datadata. Entity-relationship modeling is a. Entity-relationship modeling is a database modelingdatabase modelingmethod, used to produce a type of method, used to produce a type of conceptualconceptual

schema

schema or or semantic data modelsemantic data modelof a system, often a relational databaseof a system, often arelational database, and its requirements in a, and its requirements in a top-downtop-downfashion.fashion. Diagrams created using this process are called

Diagrams created using this process are called entity-relationship diagramsentity-relationship diagrams, or , or ER diagrams ER diagrams or or ERDs ERDs for short.for short. The definitive reference for entity relationship modelling is generally given as

The definitive reference for entity relationship modelling is generally given as Peter ChenPeter Chen''s 1976 paper s 1976 paper [1][1]_{. However,}_{. However,} variants of the idea existed previously (see for example

variants of the idea existed previously (see for example A.P.G. BrownA.P.G. Brown[2][2]_{) and have been devised subsequently.}_{) and have been devised subsequently.} The first stage of

The first stage of information systeminformation systemdesign uses these models during thedesign uses these models during the requirements analysisrequirements analysis to describeto describe information needs or the type of

information needs or the type of informationinformationthat is to be stored in athat is to be stored in a databasedatabase. The. The data modelingdata modelingtechnique can betechnique can be used to describe any

used to describe any ontologyontology(i.e. an overview and classifications of used terms and (i.e. an overview and classifications of used terms and their relationships) for atheir relationships) for a certain

certain area of interestarea of interest. In the case of . In the case of the design of an information system that is based on the design of an information system that is based on a database, thea database, the conceptualconceptual

data model

data modelis, at a later stage (usually called logical design), mapped is, at a later stage (usually called logical design), mapped to ato a logical data modellogical data model, such as the relational, such as therelational

model

model; this in turn is mapped to a ; this in turn is mapped to a physical model during physical design. Note that sometimes, both physical model during physical design. Note that sometimes, both of these phasesof these phases are referred to as "physical design".

are referred to as "physical design". There are a number of

There are a number of conventions for entity-relationship diagrams (ERDs). The classical notation mainly relatesconventions for entity-relationship diagrams (ERDs). The classical notation mainly relates to

to conceptual modelingconceptual modeling. There are a range of notations employed in logical and physical. There are a range of notations employed in logical and physical database designdatabase design, such, such as

as IDEF1XIDEF1X..

The building blocks: entities, relationships, and

The building blocks: entities, relationships, and attributes

attributes

Two related entities Two related entities

An

(4)

Primary key

An

An entityentitymay be defined as a thing which is recognized as being capable of an independent existence and which canmay be defined as a thing which is recognized as being capable of an independent existence and which can be uniquely identified. An entity is an abstraction from the

be uniquely identified. An entity is an abstraction from the complexities of some domain. When we speak complexities of some domain. When we speak of an entityof an entity we normally speak of some aspect of

we normally speak of some aspect of the real world which can be the real world which can be distinguished from other aspects of the real world.distinguished from other aspects of the real world.[3][3] An entity may be a physical object such as a house or a car, an event such as a house sale or a car service, or a concept An entity may be a physical object such as a house or a car, an event such as a house sale or a car service, or a concept such as a customer transaction or order.

such as a customer transaction or order. Although the term entity is the one most commonly used, Although the term entity is the one most commonly used, following Chen wefollowing Chen we should really distinguish between an entity and an

should really distinguish between an entity and an entity-type. An entity-type is a category. An entity, strictlyentity-type. An entity-type is a category. An entity, strictly speaking, is an instance of a

speaking, is an instance of a given entity-type. There are usually many instances of an engiven entity-type. There are usually many instances of an en tity-type. Because the termtity-type. Because the term entity-type is somewhat cumbersome, most people tend to use

entity-type is somewhat cumbersome, most people tend to use the term entity as a synonym for this term.the term entity as a synonym for this term. Entities can be thought of as

Entities can be thought of as nounsnouns. Examples: a computer, an . Examples: a computer, an employee, a song, a mathematical theorem. employee, a song, a mathematical theorem. Entities areEntities are represented as rectangles.

represented as rectangles.

A relationship captures how two or more entities are related

A relationship captures how two or more entities are related to one another. Relationships can be to one another. Relationships can be thought of asthought of asverbsverbs,, linking two or more nouns. Examples: an

linking two or more nouns. Examples: an ownsowns relationship between a company and a computer, arelationship between a company and a computer, a supervises

supervises relationship between an employee and a department, arelationship between an employee and a department, a performs performs relationship between an artist and a song,relationship between an artist and a song, aa proved proved relationship between a mathematician and relationship between a mathematician and a theorem. Relationships are represented as diamonds, conneca theorem. Relationships are represented as diamonds, connec tedted by lines to each of the

by lines to each of the entities in the relationship.entities in the relationship.

The model's linguistic aspect described above is utilized in the database

The model's linguistic aspect described above is utilized in the database query languagequery languageERROLERROL.. Entities and relationships can both have

Entities and relationships can both have attributes. Examples: anattributes. Examples: an employeeemployee entity might have aentity might have a Social SecuritySocial Security Number

Number (SSN) attribute; the(SSN) attribute; the proved proved relationship may have arelationship may have a datedate attribute. Attributes are represented as attribute. Attributes are represented as ellipsesellipses connected to their owning entity sets by a line.

connected to their owning entity sets by a line. Every entity (unless it is a

Every entity (unless it is a weak entityweak entity) must have a minimal set of u) must have a minimal set of uniquely identifying attributes, which is called theniquely identifying attributes, which is called the entity's

entity's p primrimarary y kekeyy..

Entity-relationship diagrams don't show single entities or single instances of relations. Rather, they show entity sets Entity-relationship diagrams don't show single entities or single instances of relations. Rather, they show entity sets and relationship sets. Example: a particular

and relationship sets. Example: a particular song song is an entity. The collection of all songs is an entity. The collection of all songs in a database is an in a database is an entity set.entity set. The

The eateneatenrelationship between a child and hrelationship between a child and her lunch is a single relationship. The set er lunch is a single relationship. The set of all such child-lunchof all such child-lunch relationships in a database is a relationship set. In o

relationships in a database is a relationship set. In o ther words, a relationship set corresponds to ather words, a relationship set corresponds to a relation inrelation in

mathematics

mathematics, while a relationship corresponds to a , while a relationship corresponds to a member of the relation.member of the relation. Certain

Certain cardinality constraintscardinality constraints on relationship sets may be indicated as well.on relationship sets may be indicated as well.

5. Give some shapes for which purpose in E-R model?

6. What is meant by Aggregation in E-R?

7. Define File system.

8. When we realize DBMS better to FS?

9. How we overcome inconsistency problem?

10. Example query for DDL and DML?

11. Compare Procedural and Non Procedural languages?

12. Define super key and candidate keys with suitable example.

13. Define instances and schemas.

(5)

14. Define data dictionary.

15. Define procedural language and non procedural language.

PART-B

1. a. Explain the architecture of DBMS (8)

b. Compare File systems with database systems . (8)

2. What is meant by Relational calculus? Query examples for touples and domain

relational calculus? (16)

3. Explain all types of data models (16)

4. Explain E-R Model concept and extended E-R model. (16)

5. Define relational algebra. With suitable example. (16)

6. Roles of data base administrator and the details of aggregate function queries (16)

7. a. With relevant examples discuss the following in SQL

i. DDL

ii. DML

iii. DCL

iv. Views (8)

b. Construct an ER diagram for a car insurance company that has a set of customers,

each of whom owns one/more cars

..

Each car has associated with it zero to any

number of recorded accidents. (8)

UNIT II

RELATIONAL MODEL

PART- A ( 2 Marks)

1

1 . Name the different types of joins supported in SQL

. Name the different types of joins supported in SQL

2. Why it is necessary to decompose a relation?

3. Define view and their syntax

4. What are the types of join operation.

5. What is meant by 1NF?

6. What is meant by 2NF?

7. Why we go for decomposition?

8. Define Domain constraints, General constraints.

9. How we provide lossless join?

10. What is meant by Functional dependency?

PART-B

1. What is normalization? Explain all Normal forms. (16)

2. a) What is meant by Selection and projection used in Relational algebra? (8)

b) How we made lossless and lossy-join in relational model? (8)

3. a) Define class Hierarchies and Aggregation? (8)

b) Write about decomposition preservation algorithm for all FD’s. (8)

4. a)Explain the strength and weakness of the Triggers mechanism (8)

and update views in stored procedure.

b)What is meant by Integrity and security? (8)

Employee(empno,name,office,age)

Books(isbn,title,authors,publisher)

Loan(empno,isbn,date)

Write the following queries in relational algebra.

a. Find the names of employees who have borrowed a book

published by McGraw-Hill. (4)

b. Find the names of employees who have borrowed all books

(6)

published by McGraw-Hill. (4)

c. Find the names of employees who have borrowed more than

five different books published by McGraw-Hill. (4)

d. For each publisher, find the names of employees who have borrowed

more than five books of that publisher. (4)

6.Explain the details about embedded and dynamic SQL (16)

7.Explain functional dependency concepts (16)

UNIT III

DATA STORAGE AND QUERY PROCESSING

PART-A ( 2 Marks)

1. What is meant by fixed and variable record length?

2. How heap files are formed as page directory?

3. What is meant by RAID?

4. How we done Buffer replacement policy?

5. List out Disk components.

6. Define Buffer size.

7. Why we not stored everything in main memory?

8. DBMS Vs. OS file system.

9. What is meant by Disk storage.

10. What is meant by redundant data.

PART-B

1. a) What is Raid? List the different levels in Raid technology and explain

its features (8)

b) Discuss about primary file storage system (8)

2. Explain static and dynamic Hashing Techniques? (16)

3. Briefly describe about B+ tree index file structure. (16)

4. What are the steps involved in Query processing? How would you (16)

estimate the cost of query.

5. Discuss selection operation techniques (16)

6. Explain structure of file indices (16)

7. Explain different properties of indexes in detail (16)

UNIT IV

TRANSACTION MANAGEMENT

PART-A ( 2 Marks)

1. Define Transaction.

2. How many states are in Transaction system?

3. Define Centralized DBMS.

4. What is a collaborating server?

5. Horizontal Fragmentation vs Vertical Fragmentation?

6. Difference between Homogeneous and Heterogeneous ?

7. How the data is distributed across sites?

8. What is meant by Synchronous and Asynchronous replication?

9. Explain three solutions of Distributed Deadlock detection? How?

10. What is meant by peer-peer communication.

PART-B

1. a) How Transactions are possible in Distributed database? Explain briefly (8)

b) what is Transaction state and its ACID properties? (8)

2. a) Discuss about two phase locking and commit protocol (8)

(7)