2019 question paper
Database Management System
26 questions
Q1a. ____ play an important role in defining and maintaining a database for an organization. *(Fill in the blank)*20192m
Module 1: Database System Architecture
View this question on its own page →____ play an important role in defining and maintaining a database for an organization.
(Fill in the blank)Q1b. The strong entity type and weak entity type participate in ____ relationship. *(Fill in the blank)*20192m
Module 1: Database System Architecture
View this question on its own page →The strong entity type and weak entity type participate in ____ relationship.
(Fill in the blank)Q1c. A level that describes how a record is stored is: (i) physical (ii) logical (iii) user (iv) view20192m
Module 1: Database System Architecture
View this question on its own page →A level that describes how a record is stored is:
(i) physical
(ii) logical
(iii) user
(iv) viewQ1d. Which of the following is true? (i) Every relation in 3NF is also in BCNF (ii) A relation R is in 3NF if very non-prime attribute of R is fully functionally dependent on every key of R (iii) Every relation in BCNF is also in 3NF (iv) No relation can be in both BCNF and 3NF20192m
Module 2: Relational Query Languages & Design
View this question on its own page →Which of the following is true?
(i) Every relation in 3NF is also in BCNF
(ii) A relationRis in 3NF if very non-prime attribute ofRis fully functionally dependent on every key ofR
(iii) Every relation in BCNF is also in 3NF
(iv) No relation can be in both BCNF and 3NFQ1e. Consider the relation scheme R = {E, F, G, H, I, J, K, L, M, N} and the set of functional dependencies {{E, F} → {G}, {F} → {I, J}, {E, H} → {K, L}, K → {M}, L → {N}} on R. What is the key for R? (i) {E, F} (ii) {E, F, H} (iii) {E, F, H, K, L} (iv) {E}20192m
Module 2: Relational Query Languages & Design
View this question on its own page →Consider the relation scheme R = {E, F, G, H, I, J, K, L, M, N} and the set of functional dependencies {{E, F} → {G}, {F} → {I, J}, {E, H} → {K, L}, K → {M}, L → {N}} on
R. What is the key for R?
(i) {E, F}
(ii) {E, F, H}
(iii) {E, F, H, K, L}
(iv) {E}Q1f. Given the Students' relation as shown below: | StudentID | StudentName | StudentE-mail | StudentAge | CPI | |---|---|---|---|---| | 2345 | Shankar | shankar@math | X | 9.4 | | 1287 | Swati | swati@ee | 19 | 9.5 | | 7853 | Shankar | shankar@cse | 19 | 9.4 | | 9876 | Swati | swati@mech | 18 | 9.3 | | 8765 | Ganesh | ganesh@civil | 19 | 8.7 | For (StudentName, StudentAge) to be the key for this instance, the value X should not be equal to: (i) 18 (ii) 19 (iii) 15 (iv) 2020192m
Module 2: Relational Query Languages & Design
View this question on its own page →Given the Students' relation as shown below:
StudentID StudentName StudentE-mail StudentAge CPI 2345 Shankar shankar@math X 9.4 1287 Swati swati@ee 19 9.5 7853 Shankar shankar@cse 19 9.4 9876 Swati swati@mech 18 9.3 8765 Ganesh ganesh@civil 19 8.7 For
(StudentName, StudentAge)to be the key for this instance, the valueXshould not be equal to:
(i) 18
(ii) 19
(iii) 15
(iv) 20Q1g. From the instance of a relation scheme R (A, B, C) | A | B | C | |---|---|---| | 1 | 1 | 1 | | 1 | 1 | 0 | | 2 | 3 | 2 | | 2 | 3 | 2 | we can conclude that: (i) A functionally determines B and B functionally determines C (ii) A functionally determines B and B does not functionally determine C (iii) B does not functionally determine C (iv) A does not functionally determine B and B does not functionally determine C20192m
Module 2: Relational Query Languages & Design
View this question on its own page →From the instance of a relation scheme
R (A, B, C)A B C 1 1 1 1 1 0 2 3 2 2 3 2 we can conclude that:
(i) A functionally determines B and B functionally determines C
(ii) A functionally determines B and B does not functionally determine C
(iii) B does not functionally determine C
(iv) A does not functionally determine B and B does not functionally determine CQ1h. Database is generally: (i) system centered (ii) user centered (iii) company centered (iv) data centered20192m
Module 1: Database System Architecture
View this question on its own page →Database is generally:
(i) system centered
(ii) user centered
(iii) company centered
(iv) data centeredQ1i. The restriction placed on data is said to be: (i) relation (ii) attribute (iii) parameter (iv) constraint20192m
Module 1: Database System Architecture
View this question on its own page →The restriction placed on data is said to be:
(i) relation
(ii) attribute
(iii) parameter
(iv) constraintQ1j. An object in databases is equal to ____ + relationships. (i) data (ii) attribute (iii) entity (iv) constraint20192m
Module 1: Database System Architecture
View this question on its own page →An object in databases is equal to ____ + relationships.
(i) data
(ii) attribute
(iii) entity
(iv) constraintQ2a. Draw and explain the three-level architecture of the database system.20197m
Module 1: Database System Architecture
View this question on its own page →Draw and explain the three-level architecture of the database system.
Q2b. Compare the traditional file-based systems and relational database management system approaches.20197m
Module 1: Database System Architecture
View this question on its own page →Compare the traditional file-based systems and relational database management system approaches.
Q3a. What is a view? Can we update a view? Justify your answer.20194m
Module 2: Relational Query Languages & Design
View this question on its own page →What is a view? Can we update a view? Justify your answer.
Q3b. When we try to modify any table in a database system, we encounter some side-effects if the tables are insufficiently normalized. Can you explain those side-effects with the respective examples?20195m
Module 2: Relational Query Languages & Design
View this question on its own page →When we try to modify any table in a database system, we encounter some side-effects if the tables are insufficiently normalized. Can you explain those side-effects with the respective examples?
Q3c. List out various constraints in relational model and explain in short.20195m
Module 1: Database System Architecture
View this question on its own page →List out various constraints in relational model and explain in short.
Q4a. 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.201910m
Module 1: Database System Architecture
View this question on its own page →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.
Q4b. Write a short note on types of attributes and their representation in E-R model with neat figures.20194m
Module 1: Database System Architecture
View this question on its own page →Write a short note on types of attributes and their representation in E-R model with neat figures.
Q5. 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.201914m
Module 2: Relational Query Languages & Design
View this question on its own page →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.Q6a. You are given the following set F of functional dependencies for a relation: R(A, B, C, D, E, F): F = {ABC → D, ABD → E, CD → F, CDF → B, BF → 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+)?20199m
Module 2: Relational Query Languages & Design
View this question on its own page →You are given the following set F of functional dependencies for a relation:
R(A, B, C, D, E, F): F = {ABC → D, ABD → E, CD → F, CDF → B, BF → 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+)?Q6b. Compute the closure of the following set F of functional dependencies for relation schema: R = (A, B, C, D, E). A → BC, CD → E, B → D, E → A List the candidate keys for R.20195m
Module 2: Relational Query Languages & Design
View this question on its own page →Compute the closure of the following set F of functional dependencies for relation schema:
R = (A, B, C, D, E). A → BC, CD → E, B → D, E → AList the candidate keys for R.
Q7a. What is the need of normalization? How many types of normalization exist? Explain in detail with suitable examples.20198m
Module 2: Relational Query Languages & Design
View this question on its own page →What is the need of normalization? How many types of normalization exist? Explain in detail with suitable examples.
Q7b. What is trigger? When are they used and why? Explain.20196m
Module 2: Relational Query Languages & Design
View this question on its own page →What is trigger? When are they used and why? Explain.
Q8a. 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.201910m
Module 3: Storage Strategies
View this question on its own page →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.Q8b. What is multilevel indexing? Explain in detail.20194m
Module 3: Storage Strategies
View this question on its own page →What is multilevel indexing? Explain in detail.
Q9a. What is two-phase locking protocol? Explain its working in detail. How can it guarantee serializability?20197m
Module 4: Transaction Processing
View this question on its own page →What is two-phase locking protocol? Explain its working in detail. How can it guarantee serializability?
Q9b. Discuss the various approaches for handling the deadlocks in dbms.20197m
Module 4: Transaction Processing
View this question on its own page →Discuss the various approaches for handling the deadlocks in dbms.