Second Year Question Bank: Database Management System (DBMS)

DBMS QUESTION BANK

UNIT I

PART A - Two Mark Questions

1. What is DBMS and what’s the goal of it?

2. What are the advantages of DBMS?

3. What are the Disadvantages of DBMS?

4. What are the disadvantages of File Systems?

5. Define Entity, Entity Set, and extensions of entity set. Give one example for each?

6. Define and give examples to illustrate the four types of attributes in database?

7. Define relationship and participation?

8. Define mapping cardinality or cardinality ratio?

9. Explain the four types of mapping cardinality with example?

10. Define E-R diagram?

11. Define weak Entity set. Give an example and explain why it is weak entity set?

12. Define discriminator or partial key of a weak entity set. Give example?

13. Explain Referential Integrity?

14. Define Instances and schemas?

15. Define and explain the two types of Data Independence?

16. Define the type types of DML?

17. List out the functions of DBA?

18. What is the need for DBA?

19. Explain DML pre-compiler?

20. Define file manager and buffer manager?

21. Define Data Dictionary?

22. State the various processes of database?

23. What are the characteristics of database?

24. Define-metadata?

25. Define data abstraction?

26. What is data model?

27. What are the categories of data model?

28. Define entity, attributes ad relationships?

29. Define database state or snapshot?

30. What are the types of attributes in the ER model?

31. What are composite and simple attributes?

32. What are stored and derived attribute?

33. How are complex attributes represented?

34. Define value sets of attributes?

35. Define degree of relationship type?

36. What is a role name?

37. What are the types of relationship constraints?

38. Define participation constraint?

39. What are the types of participation constraints?

40 Define a partial key?

41. Define specialization?

42. Define relational database schema?

43. Define relational database state?

44. List any eight applications of DBMS?

45. Give the levels of data abstraction?

46. Define the terms 1) physical schema 2) logical schema.

47. What is conceptual schema?

48. What is storage manager?

49. What are the components of storage manager?

50. What is the purpose of storage manager?

51. List the data structures implemented by the storage manager?

52. What is a data dictionary?

53. What is an entity relationship model?

54. Define the terms

i) Entity set

ii) Relationship set

55. Define single valued and multivalued attributes?

56. Define null values?

57. Define the terms

i) Key attribute

ii) Value set

58. Define weak and strong entity sets?

59. What does the cardinality ratio specify?

60. Define the terms

i) DDL

ii) DML

61. List the disadvantages of relational database system?

PART – B (16 MARK QUESTIONS)

1. Explain the architecture of DBMS with a neat block diagram?

2. Explain ER model in detail?

3. Explain the design issues of ER-model?

4. Draw an ER diagram for a banking enterprise?

5. Explain: Data models, Schemas, and Instances?

6. Write about Entity types, Entity sets, Attributes, and keys.?

UNIT II

PART A - Two Mark Questions

1. Write short notes on relational model?

2. Define tuple and attribute?

3. Define the term relation?

4. Define tuple variable?

5. Define the term Domain?

6. What is a candidate key? Illustrate with example.

7. What is a primary key? Illustrate with example.

8. What is a super key? Illustrate with example.

9. Define- relational algebra?

10. What is a SELECT operation?

11. What is a PROJECT operation?

12. Write short notes on tuple relational calculus?

13. Write short notes on domain relational calculus?

14. Define query language?

15. Write short notes on Schema diagram?

16. What is foreign key?

17. What are the parts of SQL language?

18. What are the categories of SQL command?

19. What are the three classes of SQL expression?

20. Give the general form of SQL query?

21. What is the use of rename operation?

23. List the string operations supported by SQL?

24. List the set operations of SQL?

25. What is the use of Union and intersection operation?

26. What are aggregate functions? And list the aggregate functions supported by SQL?

27. What is the use of group by clause?

28. What is the use of sub queries?

29. What is view in SQL? How is it defined?

30. What is the use of with clause in SQL?

31. List the table modification commands in SQL?

32. List the SQL domain Types?

33. What is the use of integrity constraints?

34. Mention the 2 forms of integrity constraints in ER model?

35. What is trigger?

36. What are domain constraints?

37. What are referential integrity constraints?

38. What is assertion? Mention the forms available.

39. Give the syntax of assertion?

40. What is the need for triggers?

41. List the requirements needed to design a trigger?

42. Give the forms of triggers?

43. Name the various privileges in SQL?

44. Mention the various user privileges?

45. Give the limitations of SQL authorization?

46. List out the statements associated with a database transaction?

47. What is transaction?

48. What is a homogeneous distributed database?

49. What is a heterogeneous distributed database?

50. What are the two approaches to store relations in distributed database?

51. What are the two different schemes for fragmenting a relation?

52. What is horizontal fragmentation?

53. What is vertical fragmentation?

54. What are the various forms of data transparency?

55. Give the syntax for creating the table with composite primary key?

56. Write a query to display loan number, branch name where loan amount is between 500 and 1000 using comparison operators?

57. Find the names of all branches with customers who have an account in the bank and who live in the Harrison city using Equi-join?

58. Find the names of all branches with customers who have an account in the bank and who live in the Harrison city using Sub-Queries?

59. Select the rows from borrower such that the loan numbers are lesser than any loan number where the branch name is Downtown?

60. Define self-join and give an example query to illustrate self-join with a sample table?

61. What is a view and give an example query to create a view from an existing table?

62. Define Degree and Domain of a relation?

63. Define how a relation is defined mathematically?

64. List out the six fundamental operators and 4 additional operators in relational algebra?

65. Which operators are called as unary operators and explain why they are called so?

66. Which operators are called as binary operators and explain why they are called so?

67. Write a relational algebra expression to find those tuples pertaining to loans of more than 1200 made by the Perry ridge branch?

68. Explain the use of set difference operator and give an example to illustrate the same.

69. Explain the two conditions needed for the set difference operation (union Operation) to be valid?

70. Explain with one example why the additional operators are separated from the fundamental operators?

71. Explain theta join?

72. Define how an expression can be expressed in tuple relational calculus and list out the notations used?

73. Write an expression to find the names of all customers who have a loan from the Perry ridge branch in tuple relational calculus?

74. Write an expression to find all customers who have an account at all branches located in Brooklyn in tuple relational calculus?

75. Write an expression to find all customers who have an account, a loan or both at the Perry ridge branch in domain relational calculus?

76. Define and give the general format used for generalized projection. Give one example expression to illustrate the same?

77. What is the use of outer join and list out the three types of outer join with the notations used in relational algebra?

78. Write a relational algebraic expression to delete all accounts at branches located in Brooklyn?

79. Write a relational algebraic expression to insert the information about Smith with his new account number ‘A-157’ taken at the Perry ridge branch with Rs.1200?

80. Write a relational algebraic expression to insert a gift amount of Rs. 200/- for all loan customers of the Perry ridge branch?

81. Write a relational algebraic expression to update by adding the balances more than 10000 with Rs.60/- interest and otherwise Rs. 50/- interest?

82. Define materialized views and explain the use of such views?

83. Define Distributed database systems?

84. What are the advantages of distributed system?

85. What is query processor?

86. Explain Client/Server?

87. What are the rules that have to be followed during fragmentation?

88. What are the failures in distributed DBMS?

89. Define relation schema?

90. Define degree of relation?

PART – B (16 MARK QUESTIONS)

1. Explain the various relational algebra operation?

2. Explain nested Queries with examples?

3. Explain embedded SQL and dynamic SQL in detail?

4. Explain the integrity constraints supported by SQL?

5. Explain triggers with example?

6. Explain distributed Database in detail?

7. Explain in detail the recovery techniques in distributed databases?

8. Write about Structured Query Language (SQL) commands?

9. Explain about the concept of Tuple Relational Calculus?

10. Explain about the concept of Domain Relational Calculus?

UNIT III

PART A - Two Mark Questions

1. What is first normal form?

2. Define Boyce codd normal form?

3. What is meant by functional dependencies?

4. What are the uses of functional dependencies?

5. Explain trivial dependency?

6. What are axioms?

7. What is meant by computing the closure of a set of functional dependency?

8. What is meant by normalization of data?

9. Define canonical cover?

10. List the properties of canonical cover?

11. What are the desirable properties of decomposition?

12. What is 2NF?

13. Define Functional Dependencies?

14. What are the main characteristics of functional dependencies?

15. Define Closure of Functional Dependency?

16. Define the three Armstrong’s Axioms or rules of inference?

13. Define union, decomposition, and Pseudo-transitivity rules?

14. Define normalization of data and denormalization?

15. Explain shortly the four properties or objectives of normalization?

16. Define Partial Functional Dependency?

17 Define functional dependency?

18. State the inference rules of functional dependency?

19. What is normalization?

20. What is a prime attribute?

21. Define full functional dependency?

22. What is lossy decomposition?

23. What do you mean by lossy-join decomposition?

24. What is lossless-join decomposition?

25. What are the two problematic issues in the design of relational databases?

26. What do you mean by Determinant?

27. What is a trivial functional dependency?

28. Explain the terms reflectivity, augmentation and transitivity?

29. Define the terms self-determination, decomposition, union and composition?

30. What is transitive dependency?

31. Explain how to identify the functional dependencies of a relation using an example?

PART – B (16 MARK QUESTIONS)

1. What is decomposition? Explain with the help of an example?

2. Explain the different normal forms in detail?

3. Explain the process of Functional Dependency?

4. What are the inference rules for functional dependencies?

5. Compare BCNF and 3NF with suitable example?

6. Explain First Normal form and second normal form with suitable example?

7. Explain the 4NF and 5NF with suitable example?

8. Explain the Domain-Key normal form (DKNF) with suitable example?

9. What is Denormalization? Explain with suitable example?

UNIT IV

PART A - Two Mark Questions

1. What is transaction?

2. What are the two statements regarding transaction?

3. What are the properties of transaction?

4. What is recovery management component?

5. When is a transaction rolled back?

6. What are the states of transaction?

7. What is a shadow copy scheme?

8. Give the reasons for allowing concurrency?

9. What is average response time?

10. What are the two types of serializability?

11. Define lock?

12. What are the different modes of lock?

13. Define deadlock?

14. Define the phases of two phase locking protocol?

15. Define upgrade and downgrade?

16. What are the two methods for dealing deadlock problem?

17. What is a recovery scheme?

18. What are the two types of errors?

19. What are the storage types?

20. Define blocks?

21. What is meant by Physical blocks?

22. What is meant by buffer blocks?

23. What is meant by disk buffer?

24. What is meant by log-based recovery?

25. What are uncommitted modifications?

27. Define shadow paging?

28. Define page?

29. Differentiate strict two phase locking protocol and rigorous two phase locking protocol?

30. How the time stamps are implemented?

31. What are the time stamps associated with each data item?

32. Define transaction-processing systems?

33. Define read only transaction?

34. When does the transaction go into an active state and partially committed state?

35. What is called as committed state?

36. Define ACID property?

37. What is isolation of ACID properties?

38. Define cascading rollback?

39. What is called as a time stamp?

40. What is shared lock and Exclusive lock?

41. When does a deadlock occur?

42. What is meant by transaction rollback?

43. Write the reasons for using concurrent execution?

44. Define recoverable schedule?

45. What are the objectives & phases of concurrency control?

46. What are the types of locking protocols?

47. What are the problems that occur in transaction if they run concurrently?

48. What are the types of failures?

49. What are the transaction operations?

50. When does a transaction reach its commit point?

51. What are the properties of transaction?

52. Define consistent state?

53. When is a schedule said to be serial?

54. When is a schedule serializable?

55. Define precedence graph or serialization graph?

56. What is a lock?

57. What is a binary lock?

58. What are the fields present in a lock?

59. What are the locking operations of a shared/exclusive lock?

60. What are the phases in a locking transaction?

70. What are the values present in a timestamp algorithm?

71. What are the phases in concurrency control protocol?

PART – B (16 MARK QUESTIONS)

1. Explain ACID in detail?

2. Explain serializability?

3. Explain lock based protocols?

4. Explain two phase locking in detail?

5. Explain log based recovery in detail?

6. Explain: Locking techniques for Concurrency Control?

7. Explain the process of concurrency control based on timestamp ordering?

UNIT V

PART A - Two Mark Questions

1. What are the main categories of storage hierarchy? Draw the storage device hierarchy according to their speed and their cost?

2. Give the measures of quality of a disk?

3. Compare sequential access devices versus random access devices with an example?

4. What are the types of storage devices?

5. What are called jukebox systems?

6. What is called remapping of bad sectors?

7. Define access time?

8. Define seek time?

9. Define average seek time?

10. Define rotational latency time?

11. Define average latency time?

12. What is meant by data-transfer rate?

14. What are a block and a block number?

15. What are called journaling file systems?

16. What is the use of RAID?

17. Explain how reliability can be improved through redundancy?

18. What is called mirroring?

19. What is called mean time to repair?

20. What is called bit-level striping?

21. What is called block-level striping?

22. What are the two main goals of parallelism?

23. What are the factors to be taken into account when choosing a RAID level?

24. What is meant by software and hardware RAID systems?

25. Define hot swapping?

26. Which level of RAID is best? Why?

27. Distinguish between fixed length records and variable length records?

28. Explain the concept of variable length records?

30. What are the two types of blocks in the fixed –length representation? Define them.

32. What is known as heap file organization?

33. What is known as sequential file organization?

34. What is hashing file organization?

35. What is known as clustering file organization?

36. What is an index?

37. What are the two types of ordered indices?

38. What are the types of indices?

39. What are the techniques to be evaluated for both ordered indexing and hashing?

40. What is known as a search key?

41. What is a primary index?

42. What are called index-sequential files?

43. What are the two types of indices?

44. What are called multilevel indices?

45. What are called secondary indices?

46. What are the disadvantages of index sequential files?

47. What is a B+-Tree index?

48. What is B-Tree?

49. What is hashing?

50. How do you create index in SQL?

51. Distinguish between static hashing and dynamic hashing?

52. What is a hash index?

53. What can be done to reduce the occurrences of bucket overflows in a hash file organization?

54. Differentiate open hashing and closed hashing (overflow chaining)?

55. What is linear probing?

56. What is called query processing?

57. What are the steps involved in query processing?

58. What is called an evaluation primitive?

59. What is called a query evaluation plan?

60. What is called a query –execution engine?

61. How do you measure the cost of query evaluation?

62. List out the operations involved in query processing?

63. What are called as index scans?

64. What is called as external sorting?

65. Explain nested loop join?

66. What is meant by block nested loop join?

67. What is meant by hash join?

68. What is called as recursive partitioning?

69. What is called as an N-way merge?

70. What is known as fudge factor?

71. Define query optimization?

72. Define track?

74. What is the hardware address of a block?

75. Define block transfer time?

76. Define bulk transfer rate?

77. What is data striping?

80. What is mirroring or shadowing?

81. What is bit level data striping?

82. What are the two techniques of implementing query optimization?

83. Define external sorting?

84. What are the phases of external sorting?

85. Define degree of merging?

PART – B (16 MARK QUESTIONS)

1. Explain file organization in details?

2. Explain indexing and hashing?

3. Explain B+ tree index in detail?

4. Explain Query processing in detail?

5. Explain RAID levels in detail?

6. Explain about secondary storage devices?

7. Write about various operations that can be performed on files?

8. Explain the concept of parallelizing disk access using RAID technology?

9. Explain: Hashing Techniques?

10. Explain the different types of single-level ordered indexes?

11. Explain the basic algorithms for executing query operations?

12. Explain the process of database tuning?