DEV Community

Arion Dev.ed
Arion Dev.ed

Posted on

Database Systems Question Bank

#database #programming #sql #beginners

A comprehensive collection of past exam questions organized by topics.

Table of Contents

Database System Architecture

Short Answer Type Questions

  1. Question 1: Describe the three-schema architecture.

    Tags: WBUT 2006, WBUT 2013, WBUT 2015

  2. Question 2: What are the main functions of a database manager? List five major functions of Data Base Administrator.

    Tags: WBUT 2006, WBUT 2009, WBUT 2017, WBUT 2018

  3. Question 3: Write the difference between procedural and non-procedural DML.

    Tags: WBUT 2006, WBUT 2013, WBUT 2017

  4. Question 4: What is the difference between a database and a table?

    Tags: WBUT 2009, WBUT 2011

  5. Question 5: Explain DDL, DML, and DCL.

    Tags: WBUT 2012

  6. Question 6: Indicate the advantage of DBMS over conventional file system.

    Tags: WBUT 2013

  7. Question 7: Could a modern university be supported if a file processing system was used to manage enterprise information? List four major difficulties that are likely to arise if no database system was available to the university. Discuss the difficulties.

    Tags: WBUT 2014

  8. Question 8: What is Data dictionary?

    Tags: WBUT 2016

Long Answer Type Questions

  1. Question 1: Write a short note on Advantages of DBMS. Tags: WBUT 2016

Entity-Relationship Model

Short Answer Type Questions

  1. Question 1: What is the difference between logical data independence and physical data independence?

    Tags: WBUT 2006, WBUT 2015

  2. Question 2: Explain the terms Candidate key, Primary key, Foreign key, and Super key.

    Tags: WBUT 2006, WBUT 2013, WBUT 2015, WBUT 2017, WBUT 2019

  3. Question 3: Explain the difference between weak entity set and strong entity set.

    Tags: WBUT 2006, WBUT 2010, WBUT 2019

  4. Question 4:

    a) What is metadata?

    b) What do you mean by data dictionary? Why is the data dictionary required in RDBMS?

    Tags: WBUT 2007, WBUT 2010, WBUT 2015, WBUT 2017, WBUT 2019

  5. Question 5: Give an example of derived attribute.

    Tags: WBUT 2009, WBUT 2010

  6. Question 6: Explain candidate key with an example.

    Tags: WBUT 2011

  7. Question 7: Define super key, candidate key, and primary key.

    Tags: WBUT 2012

  8. Question 8: What is cardinality ratio?

    Tags: WBUT 2013, WBUT 2017

  9. Question 9: What is disjointless constraint?

    Tags: WBUT 2013

  10. Question 10: What do you mean by degree, cardinality of relationship?

    Tags: WBUT 2015

  11. Question 11: What do you mean by data abstraction? Explain three levels of data abstraction?

    Tags: WBUT 2015

  12. Question 12: What is Weak entity set? Explain with suitable example.

    Tags: WBUT 2016

  13. Question 13: Describe different types of attribute.

    Tags: WBUT 2017

  14. Question 14: "Primary keys are candidate keys but reverse is not true." – explain.

    Tags: WBUT 2018

Long Answer Type Questions

  1. Question 1: Design a Generalization-Specialization hierarchy for a motor-vehicle sales company. The company sells motor-cycles, passenger cars, vans, buses. Justify your placement of attributes at each level of the hierarchy.

    Tags: WBUT 2010, WBUT 2011

  2. Question 2:

    a) What do you mean by composite attribute and derived attribute? Give example.

    b) Some of the entries relevant to a technical university are given below: For each of them, indicate the type of relationship existing among them (e.g., one-to-one, one-to-many, or many-to-many). Draw a relationship diagram for each of them.

    • STUDENT and ENGG-BRANCH (students register for engg branches)
    • BOOK and BOOK-COPY (books have copies)
    • ENGG-BRANCH and SECTION (branches have section)
    • SECTION and CLASS-ROOM (sections are scheduled in classrooms)
    • FACULTY and ENGG-BRANCH (faculty teaches in a particular branch) c) An E-R diagram can be viewed as a graph. What do the following mean in terms of the structure of an enterprise schema?
    • The graph is disconnected
    • The graph is cyclic. Tags: WBUT 2011

  3. Question 3:

    a) What is multiple inheritance?

    b) What is attribute inheritance?

    c) Draw ER diagram showing cardinality:

    • A bill is sent to a customer. A customer may receive many bills.
    • A clerk works in a bank. A bank has many clerks.
    • Students appear for seats in college. Each student can get almost one seat. A college has many seats. A student can send many applications. d) With an example, describe specialization and generalization. Tags: WBUT 2012

  4. Question 4:

    a) Draw an ER diagram for the system given as follows: An organization has a number of faculties who are expert in one or more subjects. For each subject, a number of such experts are there, the system will store faculty and subject information and must support query on finding expertise on subjects. Students get enrolled to have training on one or more subjects. The system will keep student information also. One faculty is allotted to teach one or more subjects, and for one subject, only one faculty is assigned. The system will keep the information regarding such assignment.

    b) Describe how the entity animal (in a ZOO) can be developed into a specialization hierarchy.

    c) What is meant by a recursive relationship type? Give one example of recursive relationship type.

    Tags: WBUT 2014, WBUT 2019

  5. Question 5: Supreme products manufacture products like pressure cookers, cookwares, water purifiers, food processors, etc. The company markets its product to wholesalers all over the country, and dealers sell them to customers. The company has five regional offices. Salespersons contact dealers and explain about products, incentives offered, painting programs for wholesalers, and demonstrations for customers, etc. Dealers place orders with the salespersons attached with the regional office of their location. After receiving goods, they make payments, which may be in installments. The company would like to develop a system to monitor sales of different products, performance of salespersons, and orders from wholesalers. Do the following:

    • Identify entities, attributes, and relationships.
    • Draw an E-R diagram.
    • Convert this to relational tables. Tags: WBUT 2015

  6. Question 6: Draw the ER diagram of a hospital management system and explain.

    Tags: WBUT 2016

  7. Question 7: What is mapping constraint? Describe three-layer architecture of DBMS.

    Tags: WBUT 2019

  8. Question 8: Write short note on Data Dictionary.

    Tags: WBUT 2013

Relational Model

Short Answer Type Questions

  1. Question 1: Why do we need query optimization?

    Tags: WBUT 2008

  2. Question 2: Discuss the entity integrity and referential integrity constraints. Why is each considered important? Explain with suitable example.

    Tags: WBUT 2008, WBUT 2010, WBUT 2012

  3. Question 3: Why are entity integrity and referential integrity important in a database?

    Tags: WBUT 2009, WBUT 2011

  4. Question 4: Consider the following "sailors" and "reserves" relations: R (sid, bid, day), S (sid, sname, rating, age). Formulate the following queries using relational algebra.

    a) Find names of sailors who have reserved boat # XXX

    b) Find names and ages of sailors who have reserved a boat

    Tags: WBUT 2012, WBUT 2014

  5. Question 5:

    a) What is the disadvantage of Cartesian product and how to recover from it?

    b) Explain full outer join, left outer join, right outer join with examples.

    Tags: WBUT 2014

  6. Question 6: What do you mean by unary and binary operations in Relational algebra? Give example.

    Tags: WBUT 2015, WBUT 2016

  7. Question 7: Consider the following database with primary keys underlined: Project (P_No, P_Name, P_incharge), Employee (E_no, E_Name), Assigned-To (E_no, P_no). Write relational algebra expression for the following:

    a) List details of employees working on all projects.

    b) List E_no, E_name of employees who do not work on project with P_no = DB2003.

    Tags: WBUT 2018

  8. Question 8: What is referential integrity?

    Tags: WBUT 2019

  9. Question 9: Define the following:

    • Derived Attribute
    • Domain
    • 1 to Many relationship
    • Projection
    • Union compatibility Tags: MODEL QUESTION

  10. Question 10: Explain how a relational calculus expression can be unsafe. Illustrate your answer with an example.

    Tags: MODEL QUESTION

Long Answer Type Questions

  1. Question 1: What is query optimization? Write down the steps to process a high-level query.

    Tags: WBUT 2009

  2. Question 2: Consider the relational database as given below and write down expressions in relational algebra for the following queries.

    Material_Master (item_id, item_name, reorder_level)

    Material_Dts (item_id, Supplier_id, Pharchase_date, Qty, Utcost)

    • Select the quantities of each purchased material alphabetically.
    • Select the names of materials which have the highest total quantity.
    • Replace the material name 'power supply' with 'UPS'. Tags: WBUT 2010

  3. Question 3: Write short notes on the following:

    • Theta (θ) join
    • Database models
    • Inner join and Outer join
    • Tuple Relational Calculus
    • Degree of relationship Tags: WBUT 2009, WBUT 2016, MODEL QUESTION

SQL

Short Answer Type Questions

  1. Question 1: Explain the terms View.

    Tags: WBUT 2006, WBUT 2008, WBUT 2012

  2. Question 2: What are the recovery implications of:

    a) Forcing buffers to the database at COMMIT?

    b) Fewer physically writing buffers to the database prior to COMMIT?

    Tags: WBUT 2011

  3. Question 3: What are the differences between Embedded SQL and Dynamic SQL?

    Tags: WBUT 2012

  4. Question 4: Specify the query in SQL to declare a "Cursor" to find names & cities of residence of customers who have both an account and a loan at a particular bank branch in the same city as that customer.

    Tags: WBUT 2012

  5. Question 5: What is a trigger? How many types of triggers are there?

    Tags: WBUT 2012

  6. Question 6: Discuss the different levels of views.

    Tags: WBUT 2016

Long Answer Type Questions

  1. Question 1:

    a) What is a trigger? Instead of allowing negative a/c balances, a bank deals with overdrafts by setting the a/c balance to zero and creating a loan in the amount of the overdraft (An overdraft is an event when a customer's withdrawal amount exceeds the current a/c balance of the customer). The bank gives this loan a loan number identical to the a/c no. of the overdrawn a/c. Write the trigger in SQL for the above event.

    b) Consider the tables: Employee (Emp-code, Emp-Name, Designation, DOJ, BASIC-SAL, Dept-code), Dept (Dept-code, Dept-Name). Suppose we want that the clerk in your office should not have access to the details of the salaries, but he needs such information as Emp-Name, Designation, and Dept-Name. Create a view for this clerk.

    Tags: WBUT 2006, WBUT 2007, WBUT 2008, WBUT 2010, WBUT 2012

  2. Question 2: List two reasons why 'null' values might be introduced into the database?

    Tags: WBUT 2010

  3. Question 3:

    a) Consider the following relations and write queries in SQL:

    Flights (flno, from, to, distance, departs, arrives, price)

    Aircraft (aid, aname, cruising_range)

    Certified (eid, aid)

    Employees (eid, ename, salary)

    • Identify the flights that can be piloted by every pilot whose salary is more than $1,00,000.
    • Find the eids of employees who make the second highest salary.
    • Print the names and salary of every non-pilot whose salary is more than the average salary for pilots.
    • Find all aircraft with cruising_range over 1000 miles, find the name of the aircraft and the average salary of all pilots certified for this aircraft.
    • Find the names of pilots who can operate planes with a range greater than 3000 miles but are not certified on any Boeing aircraft. b) The salesman_master table records the salesman_no, name, rate_of_commission, qtd_sales. The commission_amount and date_of_payment along with salesman_no are calculated and recorded in the commission_payable table. Write a PL/SQL block of code such that depending upon the user-entered salesman_no, the commission amount is calculated and inserted into the commission_payable table. If the user enters a salesman_no that is not in the salesman_master table, then the PL/SQL block must display an appropriate error message back to the user. c) Consider the following relational schema: An employee can work in more than one department; the pct_time field of the works relation shows the percentage of time that a given employee works in a given department: Emp (eid, ename, age, salary) Works (eid, did, pct_time) Dept (did, budget, managerid) Write a Trigger to ensure each of the following requirements, considered independently:
    • Employees must take a minimum salary of $1000.
    • Every manager must also be an employee.
    • The total percentage of all appointments for an employee must be under 100%.
    • A manager must always have a higher salary than any employee that he or she manages. Tags: WBUT 2010, WBUT 2012

  4. Question 4: Hotel (Hotel No, Hotel Name, Address), Room (Room No, Hotel No, Type, Price_pn), Booking (Hotel No, Guest No, Date From, Date To, Room No), Guest (Guest No, Guest Name, Guest Address). Where the underlined column names are primary keys. Write down expressions in relational algebra for the following queries:

    • List all the hotels which are situated in Kolkata.
    • List all single rooms with a charge below Rs. 1000 per night.
    • List the name of all guests who are going to stay at ITC hotel from 25th December to 1st January.
    • List the price per night and type of all rooms at Grand Hotel. Tags: WBUT 2011

  5. Question 5:

    a) Consider the following tables: RESTAURANTS (rid, rname, rcity, phone, seat-capacity), DISHES (did, dname, dtype), CUSTOMER (cid, cname, ccity), SERVES (rid, did), HAS_ALLERGY (cid, did).

    • Find the names of the restaurants that serve "Burger" (dish name).
    • Find the names of dishes that customer "Roy" can eat without allergy problem.
    • List the names of dishes that appear in all restaurants in Kolkata.
    • Find the names of customers who are in the same city as restaurant "Maachan" and who can eat at least one dish at "Maachan" without allergy problem. b) What are the major problems with performing update operations through view? Briefly discuss each. c) Specify the following constraints in SQL using accurate applicable command:
    • All values of attribute mark in relation enrolment must be within 0 and 100.
    • If the value of attribute grade is 'A', then the mark must be more than or equal to 80. If the value of attribute grade is 'B', then the mark must be less than 80 but greater than or equal to 70. Tags: WBUT 2014

  6. Question 6: Consider the following employee database, primary keys are underlined. Employee(employee-name, street, city), Works(employee-name, company-name, salary), Company(company-name, city), Manages(employee-name, manager-name). Write SQLs for the queries given below:

    • Find the names of all employees who work for XYZ.
    • Find all employees in the database who live in the same cities as the companies for which they work.
    • Find all employees in the database who live in the same cities and on the same streets as do their managers.
    • Find all employees who earn more than the average salary of all employees of their company.
    • Find the company that has the smallest payroll. Tags: WBUT 2015

  7. Question 7: Consider the employee database: Employee (emp_name, street, emp_id), Works (emp_name, company_name, salary), Company (comp_name, city), Manages (emp_name, manager_name). Write the appropriate SQL statement on the basis of the above table:

    • Find the names and cities of residence of all employees who work for the UBI.
    • Find the names, street addresses, and cities of residence of all employees who work for the UBI and earn more than Rs. 50,000.
    • Find all employees in the database who do not work for UBI.
    • Find the 2nd highest salary for employees in UBI.
    • Find the company that has the most employees. Tags: WBUT 2016, WBUT 2017

  8. Question 8: Answer the following queries in SQL using given database schema: CH4 EMP (E no, E name, E add, B date, super-no), DEPT (D no, D name, mgrno), PROJECT (P no, P name, D no, P location), Work-on (E no, D no, Hours).

    • List E no, E name, E add, mgrno of all employees who work in "Research" department.
    • For all projects in "Kolkata", list p-no, controlling department name, manager name, address, and birth date.
    • List all project numbers, project name, manager name which belongs to "Product" department.
    • List all employee names who work more than 40 hours in "Research" department. Tags: WBUT 2018

Functional Dependencies and Normalization

Short Answer Type Questions

  1. Question 1: Find out closure of attribute set (AG) i.e., (AG)+ in the relational schema R and set of functional dependencies F as given below: R = (A, B, C, G, H, I), F = {A->B, A->C, CG->H, CG->I, B->H}

    Tags: WBUT 2007, WBUT 2012

  2. Question 2: Define BCNF. How does it differ from 3NF? Why is it considered a stronger form than 3NF?

    Tags: WBUT 2007, WBUT 2009, WBUT 2010, WBUT 2011, WBUT 2012, WBUT 2015, WBUT 2016, WBUT 2019

  3. Question 3: 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.

    Tags: WBUT 2008, WBUT 2011

  4. Question 4: Draw a functional dependency diagram (FD diagram) that is in 3NF but not in BCNF. Decompose that FD diagram into BCNF.

    Tags: WBUT 2009, WBUT 2011

  5. Question 5: What is lossless decomposition?

    Tags: WBUT 2011, WBUT 2013, WBUT 2014, WBUT 2019

  6. Question 6: Consider relation R (A, B, C) and a set of functional dependencies F = {A->BC, B->C, A->B, AB->C}. Compute the canonical cover for F.

    Tags: WBUT 2011

  7. Question 7: Compare between 3NF and BCNF with example.

    Tags: WBUT 2012

  8. Question 8: What do you mean by closure?

    Tags: WBUT 2013, WBUT 2019

  9. Question 9: Suppose that we decompose the schema, R = (A,B,C,D) into (A,B,C) and (A,D,E). Show that this decomposition is lossless decomposition, if the following set of FDs holds: A->BC, CD->E, B->D, E->A.

    Tags: WBUT 2013

  10. Question 10: Discuss the properties of decomposition including attribute preservation, dependency preservation, and lossless join with example.

    Tags: WBUT 2014

  11. Question 11: Consider the relation R = {A,B,C,D,E,F,G,H,I,J} and the set of functional dependencies: F = {AB->C, A->DE, B->F, F->GH, D->IJ}. Decompose R into 3NF.

    Tags: WBUT 2016

  12. Question 12: What are the advantages of normalization?

    Tags: WBUT 2016, WBUT 2018

  13. Question 13: What is closure and minimal cover? What is inclusion dependency?

    Tags: WBUT 2017

  14. Question 14: What is partial functional dependency? Explain BCNF with a suitable example.

    Tags: WBUT 2018

  15. Question 15: Explain Armstrong axiom in brief.

    Tags: WBUT 2018

Long Answer Type Questions

  1. Question 1: Discuss the "insertion anomalies", "updation anomalies", and "deletion anomalies" with respect to normal forms with suitable example and suggest a method to overcome them.

    Tags: WBUT 2006, WBUT 2008

  2. Question 2:

    a) Define MVD with suitable example.

    b) Find a lossless join decomposition of R = {PAN, PI, DI, DRUG, QTY, COST} into Boyce-Codd normal form with set of functional dependencies. F={PAN->PI, PI->DI, PI, DRUG->QTY, QTY->COST}

    Tags: WBUT 2006, WBUT 2007

  3. Question 3:

    a) Explain the following terms:

    • Normalization
    • Full functional dependency
    • Transitive dependency
    • Partial dependency b) Explain different types of Normal Form with examples (up to 5NF). c) Given R(A,B,C,D) with FD's F={A->B,A->C,C->D}. Consider the decomposition of R into R1 (A, B, C) with FD F1={A->B, A->C} and R2 (C, D) with FD F2= {C->D}. Is this decomposition lossless and dependency preserving? Explain. d) If 'D' be the set of all functional and multivalued dependencies, then write the rules to compute the 'D+ (Closure of D). Tags: WBUT 2007, WBUT 2010

  4. Question 4: Explain the following terms 'Fully functional dependency' and 'non-transitive dependency' with examples.

    Tags: WBUT 2008, WBUT 2019

  5. Question 5: What are MVDs? What do you mean by lossless (or non-additive) join property of decomposition?

    Tags: WBUT 2008, WBUT 2011, WBUT 2014

  6. Question 6: Use the definition of functional dependency to argue that each of Armstrong's axioms (reflexivity, augmentation, transitivity, union, and decomposition) is sound.

    Tags: WBUT 2008, WBUT 2010, WBUT 2012

  7. Question 7: Given a database schema named PLANE_INFO (flight_no, date, plane, airline, from, to, miles), the functional dependency diagram is given below: (diagram shows flight_no,date -> Plane; flight_no -> airline,from,to,miles; Relation(2) holds a transitive dependency). Decompose it up to Boyce-Codd Normal Form (BCNF).

    Tags: WBUT 2009, WBUT 2010

  8. Question 8: Consider the relation R (A, B, C, D, E) with the set of F = {A->C, B->C, C->D, DC->C, CE->A}. Suppose the relation has been decomposed by the relations R1 (A, D) R2 (A, B) R3 (B, E), R4 (C, D, E), R5 (A, E). Is this decomposition lossless? Justify your answer.

    Tags: WBUT 2009, WBUT 2010, WBUT 2012, WBUT 2014

  9. Question 9:

    a) For a given Relvar R={A,B,C,D,E,F} and set of functional dependencies F={AB->C,C->A,BC->D,ACD->B,BE->C,CE->FA,CF->BD, D->EF}, find the irreducible set & candidate keys.

    b) Explain the following terms 'partial functional dependency' and 'non-transitive dependency' with examples.

    c) Consider the following proposed rule for functional dependencies: If A->B and C->B, then A->C. Prove that this rule is not sound by showing a relation r that satisfies A->B and C->B, but does not satisfy A->C.

    Tags: WBUT 2010

  10. Question 10: Suppose you are given a relation R with four attributes, ABCD. For each of the following sets of FDs, assuming those are the only dependencies that hold for R, do the following:

    • Identify the candidate key(s) for R.
    • Identify the best normal form that R satisfies (1NF, 2NF, 3NF, BCNF).
    • If R is not in BCNF, decompose it into a set of BCNF relations that preserve the dependencies. Dependencies:
    • I) C->D, C->A, B->C
    • II) B->C, D->A
    • III) ABC->D, D->A
    • IV) A->B, BC->D, A->C
    • V) AB->C, AB->D, C->A, D->B Tags: WBUT 2010

  11. Question 11:

    a) Consider the relation R = {A, B, C, D, E, F, G, H, I, J} and the set of functional dependencies: F={AB->C, A->DE, B->F, F->GH, D->IJ}. Decompose R into 3NF.

    b) What do you mean by lossless and dependency-preserving decomposition?

    Tags: WBUT 2011, WBUT 2016

  12. Question 12:

    a) When do we call a relation in 3NF?

    b) Consider the relation assignment (worker_id, building_id, startdate, name, skilltype) and FDs are {worker_id->name, {worker_id, building_id}->startdate, name->skill}. Is the relation in 2NF? If not, then make it in 2NF.

    c) Describe Boyce-Codd normal form with example.

    d) What is Query Tree? Why do we need a query tree? Consider the query "SELECT EMP_NAME FROM EMPLOYEE, WORK_ON, PROJECT WHERE PROJECT_NAME='ASSEMBLY' AND PRJ_NO='P1' AND JOIN_DATE='21-12-12'. Construct a query tree for this query.

    Tags: WBUT 2013, WBUT 2018

  13. Question 13:

    a) Prove or disprove the following:

    • If AB->C, A->D, CD->EF then AB->F
    • If XW->Y, and XY->Z, then X->(Z-W) b) A table R has attributes P, Q, R, S, T, U, V, W and satisfies the following functional dependencies: PQR->S, PQ->U, Q->V, R->W
    • What are the candidate keys?
    • Is this an irreducible set of functional dependencies? c) Consider the relation R(A, B, C, D, E) with the set of F = {A->C, B->C, C->D, DC->A, CE->A}. Suppose the relation has been decomposed by the relations R1(A, D), R2(A, B), R3(B, E), R4(C, D, E), and R5(A, E). Is this decomposition lossy or lossless? Justify your answer. d) If 'α' be the set of attributes, then write the algorithm to compute 'α+'. (Closure of attribute set). Tags: WBUT 2014

  14. Question 14:

    a) Why do we normalize a relation? Describe the anomalies.

    b) Consider each order has a unique order_id for each order, the following information is stored: order_id, order_dt, customer_name, customer_address, salesman_name, salesman_address, and for each requested item, store item_code, item_name, quantity, and rate. Further assume the following functional dependencies: salesman_name->salesman_address, customer_name->customer_address, order_id->order_dt, salesman_name, customer_name, order_id, item_code->quantity, item_code->item_name, rate. Normalize the database up to 3NF, showing the steps. Indicate PRIMARY & FOREIGN KEYS.

    c) Is a table with only two columns that is in 3NF, always in BCNF? What about a table with three columns that is in 3NF? Explain your answer and give examples to support it.

    Tags: WBUT 2014

  15. Question 15:

    a) Consider the following two sets of functional dependencies: X = {A->C, AC->D, E->AD, E->H} and Y = {A->CD, E->AH}. Check whether or not they are equivalent.

    b) Consider R = (A, B, C, D, E) and set of functional dependencies F = {A->BC, CD->E, B->D, E->A} to answer the following questions:

    • List the candidate keys for R.
    • Show that the following decomposition of the schema R into (A,B,C) and (A, D, E) is lossless join decomposition if the above-given set F of functional dependencies holds.
    • Compute the canonical cover Fc.
    • What normal form is the following relation in? Discuss stuff (D, O, N, T, C, R, Y) FD's are DO->NTCRY, CR->D, D->N. Tags: WBUT 2015, WBUT 2017

  16. Question 16: Consider the relation R = {A, B, C, D, E}, and FD set FD={B->CD, D->E, B->A, E->C, AB->B}.

    a) Find out the closure of attribute set (AD) i.e., (AD)+ in the R.

    b) Find out the candidate keys for R.

    c) Consider the following two sets of FDs: F={A->C, AC->D, E->AD, E->H}, G={A->CD, E->AH}. Check whether they are equivalent. Justify your answer.

    Tags: WBUT 2017

  17. Question 17:

    a) If R=(A,B,C,D) and the FDs are {AB->CE, E->AB, C->D}. Why is R in 2NF, but not in 3NF? Explain.

    b) Show that if a relation schema is in BCNF, then it is in 3NF, but if a relation schema is in 3NF, then it is not necessarily in BCNF. Give examples.

    Tags: WBUT 2017

Storage Strategies

Short Answer Type Questions

  1. Question 1: What are the four main differences between a file-processing system and a DBMS? Tags: WBUT 2006, WBUT 2009, WBUT 2017, WBUT 2018

Long Answer Type Questions

  1. Question 1: What is the difference between Primary index, Secondary index, and Clustering index?

    Tags: WBUT 2006, WBUT 2011

  2. Question 2: What is Blocking Factor? Explain the difference between B-tree and B+-tree indexing with proper example.

    Tags: WBUT 2008, WBUT 2011

  3. Question 3:

    a) State the difference between the following in brief: Primary index versus Secondary Index.

    b) What are the causes of bucket overflow in a hash file organization?

    c) Construct a B+ tree for the following set of key values: {2, 3, 5, 7, 11, 17, 19, 23, 29, 31}. Assume that the tree is initially empty and values are added in ascending order. Construct B+ trees for the cases where the number of pointers that will fit in one node is as follows: i) Four ii) Six iii) Eight.

    Tags: WBUT 2011

  4. Question 4:

    a) What are dense and sparse indexing? Explain with an example.

    b) Create B+ tree for the following key: Order = 3, Key: 8, 5, 1, 7, 3, 12, 9, 6.

    Tags: WBUT 2012

  5. Question 5: Construct a B+ tree for the following set of key values: (3,4,6,8,12,17,19,23,29,31). Assume that the tree is initially empty, values are added in ascending order, and the number of pointers in one node is 3. Also perform the following operations on the B+ tree: (i) insert 10 (ii) insert 11 (iii) delete 29.

    Tags: WBUT 2015

  6. Question 6: Consider the file with r = 3000 records (fixed length) of size R = 100 bytes stored on a disk with block size B = 1024 bytes. Suppose each index entry in the index file takes 15 bytes (9 bytes for index value, 5 bytes for pointer). What is the number of accessing blocks for clustering index?

    Tags: WBUT 2015

  7. Question 7: Write short notes on the following:

    • B-tree
    • File indexing
    • B+ tree OR, B+ tree file organization
    • Ordered Index
    • Primary Index and Secondary Index Tags: WBUT 2013, WBUT 2016, WBUT 2017, WBUT 2018, WBUT 2019

Transaction Processing

Short Answer Type Questions

  1. Question 1: What is the two-phase locking protocol? How does it guarantee serializability?

    Tags: WBUT 2008, WBUT 2012, WBUT 2015, WBUT 2017, WBUT 2018

  2. Question 2: Describe ACID properties in DBMS.

    Tags: WBUT 2009, WBUT 2013, WBUT 2015, WBUT 2016, WBUT 2017, WBUT 2019

  3. Question 3: With suitable examples, show how recovery in a database system can be done using LOG files with – a) immediate updation b) deferred updation.

    Tags: WBUT 2010

  4. Question 4: Explain the deferred update technique for recovery with its advantages and disadvantages.

    Tags: WBUT 2011

  5. Question 5: What is the single most significant difference between two-phase locking and the time-stamping technique concurrency control? Explain briefly.

    Tags: WBUT 2014, WBUT 2016, WBUT 2019

  6. Question 6: What do you mean by transaction? Explain the transaction states.

    Tags: WBUT 2016

  7. Question 7: Explain log-based recovery and checkpoints.

    Tags: WBUT 2016

  8. Question 8: What do you mean by shadow paging?

    Tags: WBUT 2016

  9. Question 9: What do you mean by deadlock handling? Explain in detail.

    Tags: WBUT 2016

  10. Question 10: Consider the following two transactions: T1: read (A); read (B); if A = 0, then B = B + 1; write (B); T2: read (B); read (A); if B = 0, then A = A + 1; write (A); Add lock and unlock instructions to transactions T1 and T2. Is there any deadlock observed?

    Tags: WBUT 2018

  11. Question 11: Define schedule and conflict serializable schedule.

    Tags: WBUT 2019

Long Answer Type Questions

  1. Question 1: Discuss the ACID properties of a database transaction.

    Tags: WBUT 2007

  2. Question 2:

    a) What is a schedule?

    b) Describe the wait-die and wound-wait protocols for deadlock prevention.

    Tags: WBUT 2007, WBUT 2010, WBUT 2011, WBUT 2013, WBUT 2019

  3. Question 3: Write the concepts of dense index and sparse index with example. When is it preferable to use a dense index rather than a sparse index? Explain.

    Tags: WBUT 2008, WBUT 2010

  4. Question 4:

    a) Distinguish between locking and timestamp protocols for concurrency controls. Explain multi-version two-phase locking.

    b) Define three concurrency problems: dirty read, non-repeatable read, phantoms.

    c) Consider the following two transactions: T1: read (A); read (B); if A = 0, then B: B+1; write (B); T2: read (B); read (A); if B = 0, then A: A+1; Write (A). Add lock and unlock instructions to transactions T1 and T2 so that they observe the two-phase locking protocol. Can the execution of these transactions result in a deadlock?

    Tags: WBUT 2010, WBUT 2011, WBUT 2013, WBUT 2019

  5. Question 5: What are the roles of the Analysis, Redo, and Undo phases in the recovery algorithm 'ARIES'?

    Tags: WBUT 2010, WBUT 2011, WBUT 2013, WBUT 2014, WBUT 2019

  6. Question 6:

    a) State the Two-phase commit protocol and discuss the implications of a failure on the part of i) the coordinator ii) a participant, during each of the two phases.

    b) Let T1, T2, and T3 be transactions that operate on the same data items A, B, and C. Let r1(A) mean that T1 reads A, w1(A) means that T1 writes A, and so on for T2 and T3. Consider the following schedule S1: r2(C), r1(B), w2(B), r3(C), r1(A), w1(A), w3(B), w1(C), r2(A), r1(B), w1(B), w2(A). Is the schedule serializable?

    Tags: WBUT 2011, WBUT 2013, WBUT 2017, WBUT 2018, WBUT 2019

  7. Question 7: What do you mean by integrity constraint?

    Tags: WBUT 2013, WBUT 2019

  8. Question 8:

    a) What is a transaction?

    b) Explain with example serial and serializable schedule.

    c) What are the problems of concurrent execution of transactions?

    d) Explain with the help of a precedence graph the conflict and non-conflict serializability.

    Tags: WBUT 2013, WBUT 2019

  9. Question 9:

    a) Consider the following transactions and find how many different schedules of the two transactions are possible? How many of these are serializable?

    b) Transactions cannot be nested inside one another. Why not?

    c) Systems do not allow transactions to commit changes to the database on an individual basis, i.e., without simultaneously committing changes to all other databases. Why not?

    Tags: WBUT 2014

  10. Question 10:

    a) Explain the purpose of the checkpoint mechanism. How often should checkpoints be performed?

    b) How does 'strict two-phase' and 'consecutive two-phase' locking protocol differ?

    Tags: WBUT 2015

  11. Question 11:

    a) What are the various states of a transaction? Explain with a state diagram.

    b) Consider the following schedule: S1: r2(C), r2(B), w2(B), r3(C), r1(A), w1(A), w3(B), w3(C), r2(A), r1(B), w1(B), w2(A). Is the schedule serializable?

    c) What is a cascadeless schedule? Why is cascadelessness of schedule desired?

    d) Explain log-based recovery.

    Tags: WBUT 2017

  12. Question 12:

    a) What is a deadlock? What are the necessary and sufficient conditions for a deadlock?

    b) What are the necessary measurements for deadlock avoidance?

    c) Let T1, T2, and T3 be transactions that operate on the same data items A, B, and C. Let r1(A) mean that T1 reads A, w1(A) means that T1 writes A, and so on for T2 and T3. Consider the following schedule: S: r1(C), r2(B), w2(B), r3(B), r3(C), r1(A), w1(A), w3(B), w3(C), r2(A), r1(B), w1(B), w2(A). Is the schedule serializable?

    Tags: WBUT 2018

  13. Question 13: Write short notes on the following:

    • Two-phase locking
    • Deadlock
    • Transaction state diagram
    • Timestamp-based protocol for concurrency protocol
    • Log-based recovery
    • Anomalies in concurrent execution of transactions Tags: WBUT 2006, WBUT 2009, WBUT 2013, WBUT 2017, WBUT 2018, WBUT 2019

Database Security

Long Answer Type Questions

  1. Question 1: Explain the importance of view in providing security to the database. What is the importance of GRANT and REVOKE commands? Tags: MODEL QUESTION

Advanced Topics

Short Answer Type Questions

  1. Question 1: Discuss the advantages of centralized and distributed databases.

    Tags: MODEL QUESTION

  2. Question 2: Discuss the advantages of distributed and centralized databases.

    Tags: MODEL QUESTION

  3. Question 3: Discuss the differences between DDBMS and OODBMS.

    Tags: MODEL QUESTION

  4. Question 4: How is a data warehouse different from a database?

    Tags: MODEL QUESTION

  5. Question 5: Define data mining. What are the advantages of data mining over traditional approaches?

    Tags: MODEL QUESTION

  6. Question 6: What do you understand by Web Mining? Compare Web Mining with Data Mining.

    Tags: MODEL QUESTION

Long Answer Type Questions

  1. Question 1: Write a short note on Vertical and Horizontal Fragmentation.

    Tags: WBUT 2009

  2. Question 2: What are the advantages of distributed database management systems over centralized database management systems?

    Tags: MODEL QUESTION

  3. Question 3:

    a) Identify the basic features in an object-oriented data model.

    b) Compare inheritance in the EER model and in the OO model.

    Tags: MODEL QUESTION

  4. Question 4:

    a) Explain the concept of Distributed Database. What are the advantages and functions of a distributed database?

    b) Describe briefly the following terms which are needed for Distributed Database Design: Data Fragmentation, Data Replication, Data Allocation.

    Tags: MODEL QUESTION

  5. Question 5: Illustrate the features of an object-oriented database. What are the advantages and disadvantages of using an object-oriented database management system?

    Tags: MODEL QUESTION

  6. Question 6: With a suitable diagram, discuss the referential architecture of a distributed database management system. Give a comparative study of the different levels of transparency in DDBMS. How does an auxiliary program help in fetching data in a DDBMS?

    Tags: MODEL QUESTION

  7. Question 7: Define Data Warehouse and briefly describe its characteristics.

    Tags: MODEL QUESTION

Past Year Question Papers

QUESTION 2015

GROUP – B (Short Answer Type Questions)

  • What do you mean by degree, cardinality of relationship?
  • What do you mean by data abstraction? Explain three levels of data abstraction?
  • Discuss the ACID properties of a Database transaction.
  • a) What is Data dictionary? b) What do you mean by unary and binary operations in Relational algebra? Give example.
  • Explain with example "BCNF is stricter than 3NF".
  • Briefly explain the two-phase locking protocol.

GROUP – C (Long Answer Type Questions)

  • Consider the following employee database, primary keys are underlined. Employee(employee-name, street, city), Works(employee-name, company-name, salary), Company(company-name, city), Manages(employee-name, manager-name). Write SQLs for the queries given below:
    • Find the names of all employees who work for XYZ.
    • Find all employees in the database who live in the same cities as the companies for which they work.
    • Find all employees in the database who live in the same cities and on the same streets as do their managers.
    • Find all employees who earn more than the average salary of all employees of their company.
    • Find the company that has the smallest payroll.
  • a) Consider the following two sets of functional dependencies X = {A->C, AC->D, E->AD, E->H} and Y = {A->CD, E->AH}. Check whether or not they are equivalent. b) Consider R = (A, B, C, D, E) and set of functional dependencies F = {A->BC, CD->E, B->D, E->A} to answer the following questions:
    • List the candidate keys for R.
    • Show that the following decomposition of the schema R into (A,B,C) and (A, D, E) is lossless-join decomposition if the above-given set F of functional dependencies holds.
    • Compute the canonical cover FC. c) What normal form is the following relation in? Discuss stuff (D, O, N, T, C, R, Y) FD's are DO->NTCRY, CR->D, D->N.
  • Supreme products manufacture products like pressure cookers, cookwares, water purifiers, food processors, etc. The company markets its product to wholesalers all over the country, and dealers sell them to customers. The company has five regional offices. Salespersons contact dealers and explain about products, incentives offered, painting programs for wholesalers, and demonstrations for customers, etc. Dealers place orders with the salespersons attached with the regional office of their location. After receiving goods, they make payments, which may be in installments. The company would like to develop a system to monitor sales of different products, performance of salespersons, and orders from wholesalers. Do the following:
    • Identify entities, attributes, and relationships.
    • Draw an E-R diagram.
    • Convert this to relational tables.
  • a) Explain the purpose of the checkpoint mechanism. How often should checkpoints be performed? b) How does 'strict two-phase' and 'consecutive two-phase' locking protocol differ? c) Construct a B+ tree for the following set of key values: {3,4,6,8,12,17,19,23,29,31}. Assume that the tree is initially empty, values are added in ascending order, and the number of pointers in one node is 3. Also perform the following operations on the B+ tree: (i) insert 10 (ii) insert 11 (iii) delete 29.
  • a) Describe the Three-Schema architecture of DBMS. Define physical data independence and logical data independence. b) Explain with examples the terms super key, candidate key, primary key, and alternate key. c) Consider the file with r = 3000 records (fixed length) of size R = 100 bytes stored on a disk with block size B = 1024 bytes. Suppose each index entry in the index file takes 15 bytes (9 bytes for index value, 5 bytes for pointer). What is the number of accessing blocks for clustering index?

QUESTION 2016

GROUP – B (Short Answer Type Questions)

  • What is a Data dictionary? What do you mean by unary operations in Relational algebra? Give example.
  • Explain the two-phase locking protocol.
  • Consider the relation R = {A,B,C,D,E,F,G,H,I,J} and the set of functional dependencies: F = {AB->C,A->DE,B->F,F->GH,D->IJ}. Decompose R into 3NF.
  • Discuss the different levels of views.
  • What is a Weak entity set? Explain with a suitable example.

GROUP – C (Long Answer Type Questions)

  • a) Draw the ER diagram of a hospital management system and explain. b) Consider the relation R = {A,B,C,D,E} and the set of functional dependencies: F = {AD->B,B->C,C->D}. Find out the candidate key.
  • a) What do you mean by transaction? Explain the transaction states. b) Explain log-based recovery and checkpoints. c) What do you mean by shadow paging? d) What do you mean by deadlock handling? Explain in detail.
  • Consider the employee database: Employee (emp_name, street, emp_id), Works (emp_name, company_name, salary), Company (company_name, city), Manages (emp_name, manager_name). Write the appropriate SQL statement on the basis of the above table:
    • Find the names and cities of residence of all employees who work for the UBI.
    • Find the names, street addresses, and cities of residence of all employees who work for the UBI and earn more than Rs. 50,000.
    • Find all employees in the database who do not work for UBI.
    • Find the 2nd highest salary for employees in UBI.
    • Find the company that has the most employees.
  • a) What are the advantages of normalization? b) How does BCNF differ from the 3rd normal form? c) Explain the ACID properties of transactions.
  • Write short notes on any three of the following:
    • File indexing
    • B+ tree
    • Advantages of DBMS
    • Database models
    • Inner join and Outer join

QUESTION 2017

GROUP – B (Short Answer Type Questions)

  • Discuss the ACID properties of transactions.
  • a) Distinguish between file management system and database management system. b) Discuss the role of DBA.
  • What is Cardinality ratio? What is the difference between procedural and non-procedural DML?
  • What is closure and minimal cover? What is inclusion dependency?
  • What is the 2-phase locking protocol? How does it guarantee serializability?

GROUP – C (Long Answer Type Questions)

  • a) Find out the closure of attribute set (AD) i.e., (AD)+ in the R. Set of FD's F are as given below: R = {A, B, C, D, E}, FD = {B->CD, D->E, B->A, E->C, AB->B}. b) Find out the candidate keys for R. c) Consider the following two sets of FDs: F={A->C, AC->D, E->AD, E->H}, G={A->CD, E->AH}. Check whether they are equivalent. Justify your answer.
  • Consider the relational database: Employee (person-name, street, city), Works (person-name, company name, salary), Company (company name, city), Manages (person-name, manager-name). Write down appropriate SQL statements for the following queries:
    • Find the name of all employees who work for 'SBI bank'.
    • Find name, street address, cities of residence of all employees who work for 'UBI Bank' and earn more than Rs. 5,00,000 per annum.
    • Find the second highest salary for employees in 'SBI bank'.
    • Find the names of all employees who live in the same city and on the same street as do their manager.
    • Find the company that has the most employees.
  • a) What are the various states of a transaction? Explain with a state diagram. b) Consider the following schedule: S1: r2(C), r2(B), w2(B), r3(C), r1(A), w1(A), w3(B), w3(C), r2(A), r1(B), w1(B), w2(A). Is the schedule serializable? c) What is a cascadeless schedule? Why is cascadelessness of schedule desired? d) Explain log-based recovery.
  • a) If R=(A,B,C,D) and the FDs are {AB->CE, E->AB, C->D}. Why is R in 2NF, but not in 3NF? Explain. b) Show that if a relation schema is in BCNF, then it is in 3NF, but if a relation schema is in 3NF, then it is not necessarily in BCNF. Give examples. c) What are metadata and data dictionary? d) Explain the terms candidate key, primary key, foreign key, and super key.
  • Write short notes on any three of the following:
    • Armstrong's axioms
    • Timestamp-based protocol for concurrency protocol
    • Log-based recovery
    • Ordered index
    • Deadlock

QUESTION 2018

GROUP – B (Short Answer Type Questions)

  • Describe different 2PL protocols in brief.
  • Consider the following database with primary keys underlined: Project (P_no, P_Name, P_incharge), Employee (E_no, E_Name), Assigned-To (E_no, P_no). Write relational algebra expressions for the following:
    • List details of employees working on all projects.
    • List E_no, E_name of employees who do not work on project with P_no = DB2003.
  • What is partial functional dependency? Explain BCNF with a suitable example.
  • Consider the following two transactions: T1: read (A); read (B); if A = 0, then B = B + 1; write (B); T2: read (B); read (A); if B = 0, then A = A + 1; write (A); Add lock and unlock instructions to transactions T1 and T2. Is there any deadlock observed?
  • Explain Armstrong's axiom in brief.

GROUP – C (Long Answer Type Questions)

  • a) Why do we need normalization? b) What are the advantages of a database over a traditional file system? c) Explain the role of a database administrator? d) Relation assignment (worker-id, building-id, start-date, name, skill). FD's are {worker-id -> name, {worker-id, building-id} -> start date, name -> skill}. Is the relation in 3NF, if not then make it in 3NF.
  • a) What is the utility of a Query Tree? b) Describe different phases of a Query Tree. c) Consider the employee query "Select work-on. e-name, from employee, proj-assign project where p-name = "ASSEMBLY" and p-no = "p1" and Join-Date = "21-12-17" and employee.eno = proj-assign. eno and proj-assign, pno=project. p-no. d) "Primary keys are candidate keys but reverse is not true." – explain.
  • a) What is a deadlock? What are the necessary and sufficient conditions for a deadlock? b) What are the necessary measurements for deadlock avoidance? c) Let T1, T2, and T3 be transactions that operate on the same data items A, B, and C. Let r1(A) mean that T1 reads A, w1(A) means that T1 writes A, and so on for T2 and T3. Consider the following schedule: S: r1(C), r2(B), w2(B), r3(B), r3(C), r1(A), w1(A), w3(B), w3(C), r2(A), r1(B), w1(B), w2(A). Is the schedule serializable?
  • Answer the following queries in SQL using the given database schema: EMP (E no, E name, E add, B date, super-no), DEPT (D no, D name, mgrno), PROJECT (P no, P name, D no, P location), Work-on (E no, D no, Hours).
    • List E no, E name, E add, mgrno of all employees who work in the "Research" department.
    • For all projects in "Kolkata", list p-no, controlling department name, manager name, address, and birth date.
    • List all project numbers, project name, manager name which belong to the "Product" department.
    • List all employee names who work more than 40 hours in the "Research" department.
  • Answer any three questions:
    • Timestamp-based protocol for concurrency control
    • B+ tree file organization
    • Primary Index and Secondary Index
    • Anomalies in concurrent execution of transactions
    • BCNF

QUESTION 2019

GROUP – B (Short Answer Type Questions)

  • a) What is referential integrity? b) Explain with example the difference between strong and weak entity sets.
  • Define schedule and conflict serializable schedule.
  • Explain with example super key, candidate key, and primary key.
  • Define BCNF. How does it differ from 3NF? Why is it considered stronger than 3NF?
  • What is the main difference between two-phase locking and the timestamping technique concurrency control? Explain briefly.

GROUP – C (Long Answer Type Questions)

  • a) What is mapping constraint? Describe the three-layer architecture of DBMS. b) Draw the ER diagram for the system given as follows: An organization has a number of faculties who are expert in one or more subjects. For each subject, a number of such experts are there, the system will store faculty and subject information and must support queries on finding expertise on subjects. Students get enrolled to have training on one or more subjects. The system will keep student information also. One faculty is allotted to teach one or more subjects. For one subject, only one faculty is assigned. The system must keep the information regarding such assignments.
  • a) What is a transaction? b) What is ACID property? c) Explain with example serial and serializable schedule. d) What are the problems of concurrent execution of transactions? e) Explain with the help of a precedence graph the conflict and non-conflict serializability.
  • a) Explain the terms 'full functional dependency' and 'multivalued dependency' with example. b) Differentiate between 2NF and 3NF. What is lossless decomposition? c) What is closure? Explain with example. d) What do you mean by integrity constraint?
  • a) State the two-phase commit protocol and discuss the implications of a failure on the part of the coordinator, a participant, during each of the two phases. b) Describe the wait-die and wound-wait protocols for deadlock prevention. c) Define three concurrency problems: dirty read, non-repeatable read, phantoms. d) Let T1, T2, and T3 be transactions that operate on the same data items A, B, and C. Let r1(A) mean that T1 reads A, w1(A) means that T1 writes A, and so on for T2 and T3. Consider the following schedule: S1: r2(c), r2(B), w2(b), r1(B), r3(C), r1(A), w1(A), w3(B), w3(C), r2(A), r1(B), w1(B), w2(A). Is the schedule serializable? e) What are the roles of Analysis, Redo, and Undo phases in the recovery algorithm 'ARIES'?
  • Write short notes on any three of the following:
    • Two-phase Locking protocol
    • Deadlock
    • Transaction state diagram
    • B-tree
    • Data Dictionary

© 2025 Database Systems Question Bank | Compiled for Academic Purposes

Top comments (0)