Database System

_____ 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

Which of the following is true?

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?

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

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

Date base is generally

The restriction placed on data is said to be

An object in databases is equal to _____ + relationships.

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.

_____ 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

Which of the following is true?

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?

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

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

Date base is generally

The restriction placed on data is said to be

An object in databases is equal to _____ + relationships.

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.

A description of data in terms of a data model is called a _____

Information about the conceptual, external, and physical schemas is stored in the _____

The weak entity set must have participation in the _____ identifying relationship set.

The _____ also called arity, of a relation is the number of fields.

In relational algebra renaming operation is denoted by _____ operator.

SQL queries are optimized by decomposing them into a collection of smaller units, called _____

The set of all functional dependencies (FDs) implied by a given set F of FDs is called the _____ F, denoted by _____

X→→Y is a trivial MVD if _____ or _____

_____ allows us to identify a point in a transaction and selectively roll back operations carried out after this point.

Design a generalization-specialization hierarchy for a motor-vehicle sales company. The company sells motor-cycles, passenger cars, vans, and buses. Justify your placement of attributes at each level of the hierarchy.

Explain the difference between condition-defined and user-defined constraints. Which of these constraints can the system check automatically? Explain your answer.

List two reasons why we may choose to define a view.

List two major problems with processing update operations expressed in terms of views.

Passengers (Name, Address, Age)
Reservations (Name, FlightNum, Seat)
Flights (FlightNum, DepartCity, DestinationCity, DepartureTime, ArrivalTime, MinutesLate)
Write the following queries in relational algebra:
(a) Get the names of passengers who had a reservation on a flight that was more than 30 minutes late.
(b) Get the names of passengers who had reservations on all flights that were more than 60 minutes late.

What is the dependency closure $ F^+ $ of a set F of FDs? How can you compute the closure of a given set F of FDs?

What is the attribute closure $ X^+ $ of a set of attributes X with respect to a set of FDs F? Give the algorithm for computing the attribute closure of a set X of attributes.

When is the decomposition of a relation schema R into two relation schemas X and Y said to be a lossless-join decomposition? Why is the property so important? Give a necessary and sufficient condition to test whether a decomposition is lossless-join.

Describe how a query block is translated into extended relational algebra. Describe and motivate the extensions to relational algebra. Why are $ \sigma\pi x $ expressions the focus of an optimizer?

When do two actions on the same data object conflict? Explain with example the anomalies that can be caused by conflicting actions.

What transaction characteristics can a programmer control in SQL? Explain in brief the different access modes and isolation levels in particular. How can the isolation level and access mode be set in the transaction? Show with example.

A description of data in terms of a data model is called a _____

Information about the conceptual, external, and physical schemas is stored in the _____

The weak entity set must have participation in the _____ identifying relationship set.

The _____ also called arity, of a relation is the number of fields.

In relational algebra renaming operation is denoted by _____ operator.

SQL queries are optimized by decomposing them into a collection of smaller units, called _____

The set of all functional dependencies (FDs) implied by a given set F of FDs is called the _____ F, denoted by _____

X→→Y is a trivial MVD if _____ or _____

_____ allows us to identify a point in a transaction and selectively roll back operations carried out after this point.

Design a generalization-specialization hierarchy for a motor-vehicle sales company. The company sells motor-cycles, passenger cars, vans, and buses. Justify your placement of attributes at each level of the hierarchy.

Explain the difference between condition-defined and user-defined constraints. Which of these constraints can the system check automatically? Explain your answer.

List two reasons why we may choose to define a view.

List two major problems with processing update operations expressed in terms of views.

Passengers (Name, Address, Age)
Reservations (Name, FlightNum, Seat)
Flights (FlightNum, DepartCity, DestinationCity, DepartureTime, ArrivalTime, MinutesLate)
Write the following queries in relational algebra:
(a) Get the names of passengers who had a reservation on a flight that was more than 30 minutes late.
(b) Get the names of passengers who had reservations on all flights that were more than 60 minutes late.

What is the dependency closure $ F^+ $ of a set F of FDs? How can you compute the closure of a given set F of FDs?

What is the attribute closure $ X^+ $ of a set of attributes X with respect to a set of FDs F? Give the algorithm for computing the attribute closure of a set X of attributes.

When is the decomposition of a relation schema R into two relation schemas X and Y said to be a lossless-join decomposition? Why is the property so important? Give a necessary and sufficient condition to test whether a decomposition is lossless-join.

Describe how a query block is translated into extended relational algebra. Describe and motivate the extensions to relational algebra. Why are $ \sigma\pi x $ expressions the focus of an optimizer?

When do two actions on the same data object conflict? Explain with example the anomalies that can be caused by conflicting actions.

What transaction characteristics can a programmer control in SQL? Explain in brief the different access modes and isolation levels in particular. How can the isolation level and access mode be set in the transaction? Show with example.