Database System - B.Tech 5th Semester Exam., 2019

_____ play an important role in defining and maintaining a database for an organization. (Fill in the blank)

The strong entity type and weak entity type participate in _____ relationship. (Fill in the blank)

A level that describes how a record is stored is

1. physical
2. logical
3. user
4. view

Which of the following is true?

1. Every relation in 3NF is also in BCNF
2. A relation R is in 3NF if very non-prime attribute of R is fully functionally dependent on every key of R
3. Every relation in BCNF is also in 3NF
4. No relation can be in both BCNF and 3NF

Consider the relation scheme R={E,F,G,H,I,J,K,L,M,N} and the set of functional dependencies \( \{E,F\} \rightarrow \{G\} \), \( \{F\} \rightarrow \{I,J\} \), \( \{E,H\} \rightarrow \{K, L\} \), \( \{K\} \rightarrow \{M\} \), \( \{L\} \rightarrow \{N\} \) on R. What is the key for R?

1. {E, F}
2. {E, F, H}
3. {E, F, H, K, L}
4. {E}

Given the Students' relation as shown below (StudentName, StudentAge) to be the key for this instance, the value X should not be equal to

1. 18
2. 19
3. 15
4. 20

From the instance of a relation scheme R (A, B, C) we can conclude that

1. A functionally determines B and B does not functionally determine C
2. B does not functionally determine C
3. A does not functionally determine B and B does not functionally determine C
4. A functionally determines B and B functionally determines C

Date base is generally

1. system centered
2. user centered
3. company centered
4. data centered

The restriction placed on data is said to be

1. relation
2. attribute
3. parameter
4. constraint

An object in databases is equal to _____ + relationships.

1. data
2. attribute
3. entity
4. constraint

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

Compare the traditional file-based systems and relational database management system approaches.

What is a view? Can we update a view? Justify your answer.

When we try to modify any table in database system, we encounter some side-effects if the tables are insufficiently normalized. Can you explain those side-effects with the respective examples?

List out various constraints in relational model and explain in short.

Discuss the correspondence between E-R model construct and the relation model construct. Show how each E-R model construct can be append to the relational model using the following description of an organization: An organization uses number of items of an equipment to produce goods. Each item is at one LOCATION, of one TYPE and has a DETAILED_DISCRIPTION. Faults on the equipment are identified by a unique FAULT_ID and are reported at a TIME_REPORTED. Any number of persons may be assigned to a fault and work on the fault until it is fixed. The TIME_FIXED is recorded as the TIME_SPENT by each person on a fault. Any number of parts may be used to repair a fault. The QTY_USED of each part is recorded against the fault. Each part is identified by a PART_ID and has a given weight and MAX_DIMENSION and can have any number of colors.

Write a short note on types of attributes and their representation in E-R model with neat figures.

Considering the following schema, create the appropriate tables and insert at least 5 records:
AUTHOR (author-id, name, city, country)
PUBLISHER (publisher-id, name, city, country)
CATALOG (book-id, title, author-id, publisher-id, category-id, year, price)
CATEGORY (category-id, description)
ORDER-DETAILS (order-no, book-id, quantity)
Write each of the following queries in SQL and relational algebra :
(a) Obtain the names of authors who have 2 or more books in the catalog.
(b) Find the author of the book which has maximum sales.
(c) Obtain the names of author who have maximum number of publisher.
(d) Obtain the name of the city, author, publisher where publisher and author belong to same city.
(e) Obtain the title of books which has maximum sales.
(f) Obtain the book-id, description for the author who have exactly 3 books in the catalog.
(g) Obtain the author and publisher who have published books in more than or equal to 2 categories.

You are given the following set F of functional dependencies for a relation: \( R(A,B,C,D,E,F):F=\{ABC\rightarrow D, AB \rightarrow DE, CD\rightarrow F, \) \( CDF\rightarrow B, BF \rightarrow D\} \)
(i) Find all keys of R based on these functional dependencies.
(ii) Is this relation in Boyce-Codd normal form? Is it 3NF? Explain your answer.
(iii) Can the set F be simplified (by removing functional dependencies or by removing attributes from the left-hand side of functional dependencies) without changing the closure of F (i.e. F+)?

Compute the closure of the following set F of functional dependencies for relation schema: \( R=(A,B,C,D,E).A\rightarrow BC,CD\rightarrow E,B\rightarrow D,E\rightarrow A \). List the candidate keys for R.

What is the need of normalization? How many types of normalization exist? Explain in detail with suitable examples.

What is trigger? When are they used and why? Explain.

For the following set of key values construct a B+ tree with a degree 4: 5, 10, 15, 29, 35, 46, 58, 63, 67, 89. Initially tree is empty. Values must be added in ascending order. Show the step-by-step construction.

What is multilevel indexing? Explain in detail.

What is two-phase locking protocol? Explain its working in detail. How can it guarantee serializability?

Discuss the various approaches for handling the deadlocks in dbms.