DATABASE MANAGEMENT SYSTEMS 2018-20121 B.C.A SEMESTER 2 SEC INTERNAL MARCH 2019

SAINTGITS COLLEGE OF APPLIED SCIENCES

Second Internal Assessment Examination, March 2019

Department of BCA    , Semester II

Database Management Systems (Core)

ANSWER SHEME

SECTION A

1.    Logical data independence:-capacity to change conceptual schema without changing external schema

2.    A data dictionary is a file or a set of files that contains a database's metadata.

3.    Any user operation performed on a db.

Atomicity,Consistency ,Isolation and durability

4.    Entity is an object that has physical or logical existence Eg: a particular student, an entity type defines a collection of entities that have same attributes eg:Student

5.    Entity type donot have any key attributes of their own are called weak entity type Eg: Dependent

Entity type that  has a key attributes of their own are called strong entity type Eg: Employee.

6.    A virtual table

7.     The Database Engine enforces data uniqueness by automatically, when we set primary key

8.    Natural join doesnot use ant comparison operators. We can perform a natural join only if there exist atleast one common attributes between relations.

An Equi join is a join with a join condition containing an equality operator.

9.    For successful completion of concurrent execution of different transactions.

10.  Super key:-is a set of one or more attributes which can uniquely identify a row in a table.

Candidate key:-minimal set of attributes which can uniquely identify a tuple

11.  RDBMS stores data in tables.

12.  Description of a data base is known as schema

Data in a database at a particular moment is known as instances

SECTION B

13.  Description of a data base is known as schema

Schema defined in three levels

1.     Internal level:-Describe physical storage structure

2.     Conceptual level:-describe structure of a database in terms of entity, attributes etc.

3.     External level:-describe parts of the db

14.  Data model:- collection of concepts that can be used to describe structure of a database.

a.     High level datamodel:-It uses concept such as entity,attribute and relationship for describing structure of a database

b.     Logical datamodel-

c.     Lowlevel datamodel- physicat structure such as access path, indexing etc.

15.  Cardinality ratio ----explain it’s types

Participation constraints----- explain it’s types

16.  Sum(),Avg(),min(),max(),count()

17.  UNION,INTERSECT

EXCEPT

18.  Capacity to change the schema at one level without changing higher lavel

1.     Logical data independence:-capacity to change conceptual schema without changing external schema

2.     Physical data independence:- capacity to change physical schema without changing conceptual schema

19.  Draw E-R diagram

20.   

Concurrent execution of transaction may cause problems like

a.     Lost update problem

b.     Dirty read

c.     Incorrect summary problems

21.  U stock(itemno,name,quantity,unitprice).

Create table stock(itemno int,name varchar(20),quantity int,unitprice int);

Select * from stock;

Select itemno,name from stock where unitprice>100;

SECTION C

22.  Draw picture(5 marks)

Explain different user interfaces

DBA uses DDL commands to define structure of the database and DDL compiler compiles the DDL statements and store result in system catalogue. DBA can also communicate with database using privileged commands.

Casual users communicate database using dml statements, DML compiler convert entered information in to an internal form, precompiler eliminate redundancy and convert the code in to an executable form and give it to runtime database processor.

Runtime database processor works with stored data manager which in turn uses the basic operating system services for carrying out low level input output operations between disk and main memory.

23.  Indexing is a data structure based technique for accessing records in a file.  Indexes are auxiliary access structures which are used to speed up the retrieval of records in response to certain search conditions.  A main file of records can be supplemented by one or more indexes.

Index structures provide secondary access paths, which provide alternative ways of accessing the records without affecting the physical placement of records on the disk. Indexes may be part of the main file or be separate files and may be created and destroyed as required without affecting the main file.

Indexes allow for efficient access to records based on the indexing fields that are used to construct the index.   Any field of the file can be used to create an index, and multiple indexes on different fields can be constructed on the same file.

Ordered access structures are similar to indexes in textbooks.  The indexes or tables of contents list the important terms in alphabetical order, along with page numbers where the information can be found.  

               

You can search an index to find the address (page numbers in this case),  and locate the term by searching the appropriate page.  The alternative is to complete a linear search of the textbook, looking through each page one at a time.

For a file consisting of several fields, and index access structure is usually defined on a single field, called an indexing field.  An index usually is composed of two components,  the index field value and a list of pointers to all disk blocks that contain records with that field value.  The values in the index are ordered, so we can do a binary search on the index.  The index file is much smaller than the data file, so the binary search is much faster.

There are different types of indexes, these include:

-          Primary Indexes

-          Clustering index

-          Secondary index

A primary index is an ordered file whose records are of fixed length with two fields.  The first field is the primary key of the main data file, and the second is a pointer to a disk block.  There is one index entry in the index file for each block in the data file.  Each index entry has the value of the primary key field for the first record in the block, and a pointer to the block.

Figure

A clustering index is an ordered file with two fields, the first is of the same type as the clustering field of the data file, and the second is a block pointer.  There is one entry in the clustering index for each distinct value of the clustering field containing the value and a pointer to the first block in the data file that has a record with that value. For its clustering field.

Secondary Indexes

A secondary index provides a secondary means of accessing a file for which some primary access already exists.  The secondary index may be on a field which is a candidate key and has a unique value in every record, or a nonkey with duplicate values. 

The index is an ordered file with two fields.  The first field is of the same data type as the nonordering field of the data file that is an indexing field.  The second is either a block pointer or a record pointer.  There can be many secondary indexes for the same file.

Figue

24.  Normalization is the process of reducing redundancy on database

1NF-relation does not contain any composite or multivalued attributeEg:

2NF-in 1NF and no nonprime attribute in R is Fully functionally dependant on the primary key of R Eg:

3NF:-in 2NF and it does not contain any transitive dpendancy Eg:

BCNF: A relation schema R is in BCNF if whenever  a non-trivial FD XàA holds in R then X is a super key

25.   Self-describing nature of a database

b. Insulation between programs and data

c. Support of multiple views

d. Sharing of data and multiuser transaction processing

e. Controlling redundancy

f. provide persistant storage for program object

g. Restricting unauthorized access

h.providing backup and recovery

i. Providing multiple userinterface

j. Representing complex relationship among data