Model Questions for practice in DBMS

Unit 1: Introduction to Database System

Multiple Choice Questions

(Based on knowledge and understanding, Application and Higher Ability)

1. Which of the following best describes information?

(A) Raw numbers or symbols with no meaning

(B) A collection of unrelated facts

(D) Random isolated letters and numbers

2. What does the word 'Data' represent?

(A) A processed message (B) Raw or isolated facts

3. Which of the following is an example of data?

(A) A student's marksheet (B) Numbers like 45 or A

4. Which of these is NOT considered data?

(A) Words and letters (B) Raw numbers

5. The process of converting data into a useful or intelligible form results in:

(A) Raw data (B) Information (C) Unrelated facts (D) Confusing data

6. What is a database?

(A) A collection of unorganized data
(B) An organized collection of structured data stored electronically
(C) A computer program that deletes data
(D) An application for playing media files

7. What is the main role of a Database Management System (DBMS)?

(A) To build hardware for storage
(B) To provide an interface for managing and organizing databases
(C) To create random data
(D) To market databases

8. Which of the following is a core component of a DBMS responsible for storing and retrieving data?

(A) Query language (B) Database engine (C) Data integrity (D) Backup tools

9. Which query language is commonly used to interact with relational databases?

(A) HTML (B) SQL (C) CSS (D) Python

10.What feature of a DBMS ensures that multiple users can access the database simultaneously without conflicts?

(A) Backup mechanism (B) Data modeling
(C) Concurrency control (D) Data encryption

11.Which of the following is NOT a typical tool provided by a DBMS?

(A) Backup and recovery mechanisms (B) Data encryption
(C) Video editing software (D) Security controls to prevent unauthorized access

12.Which DBMS example is open source and widely used?

(A) Oracle (B) Microsoft SQL Server (C) MySQL (D) IBM DB2

13.What ensures data accuracy and consistency in a DBMS?

(A) Data integrity checks (B) User interface design
(C) File name conventions (D) Password complexity

14.Which of the following best describes the schema in a DBMS?
(A) The physical storage device
(B) The structure defining tables, fields, and relationships in the database
(C) A tool for backing up data
(D) A query command

15. How does a DBMS ensure data security?
(A) By limiting physical access to computers only
(B) Through security controls like authentication and authorization
(C) By using hyperlinks in data
(D) By compressing data

16.        Which of these is NOT a common type of data stored in databases?
(A) Customer information     (B) Inventory levels
(C) Web browser history       (D) Transaction records

17. What term describes the method a DBMS uses to define and manage relationships between tables?
(A) Data mining (B) Data modeling (C) Data encryption (D) Data normalization

18.        Which DBMS feature is crucial for maintaining data accuracy when multiple users access the system simultaneously?
(A) Backup and recovery      (B) Concurrency control
(C) Query language             (D) Views and reports

19. Which of the following is a benefit of using a query language in a DBMS?
(A) It allows users to specify what data to retrieve or modify
(B) It speeds up the physical storage of data (C) It increases data redundancy
(D) It bypasses database security

20.        What role does the database engine perform in a DBMS?
(A) It designs the user interface     (B) It manages physical storage and data retrieval
(C) It schedules data backups        (D) It encrypts data

21. What primary role does a DBMS serve between databases and users?

A) It acts as a physical storage device

B) It serves as an interface to organize and provide data access

C) It executes mathematical calculations on data

D) It designs application user interfaces

22. Which operations are typically supported by users on a database via DBMS?

A) Create, Read, Update, Delete B) Create, Encrypt, Analyze, Backup

C) Calculate, Compress, Delete, Backup D) Encrypt, Decrypt, Analyze, Compress

23. What is a significant disadvantage regarding the cost of DBMS?

A) DBMS hardware and software are inexpensive

B) DBMS costs are so high they may affect organizational budgets

C) DBMS is always free and open-source

D) Hardware costs are zero with a DBMS

24. What feature does the DBMS implement to protect against unauthorized access?

A) Encryption with no user controls B) Integrity constraints for high security

C) Open access to all users D) Data compression techniques

25. What problem can occur when many users access the same application simultaneously through a DBMS?

A) Improved system speed B) Data loss due to conflicting operations

C) Automatic data backup D) System hardware upgrade

26. How does a DBMS handle multiple applications requiring the same data?

A) It duplicates data for each application

B) It rejects access to secondary applications

C) It acts as a reliable manager to avoid conflicts

D) It only allows one application at a time

27. Which of the following is an advantage of DBMS related to data access?

A) DBMS allows data to be stored and retrieved in various ways

B) DBMS stores data only in a linear file

C) DBMS makes data inaccessible to most users

D) DBMS automatically deletes obsolete data daily

28.What role does a DBMS serve between users and databases?
a) It acts only as a storage device
b) It serves as an interface ensuring organized and accessible data
c) It encrypts all data permanently
d) It deletes unnecessary data automatically

29.Which operations are users typically allowed to perform on data in a DBMS?
a) Encrypt only b) Create, read, update, and delete (CRUD)
c) Backup only d) Copy and paste

30.What is one major financial disadvantage of using a DBMS?
a) It requires no training b) It could be very costly in hardware and software.
c) It uses outdated hardware d) It requires no maintenance

31.Why is a DBMS considered reliable for handling multiple applications requiring the same data?
a) Because it automatically deletes duplicates. b) Because it ensures data consistency.
c) Because it encrypts data at all times d) Because it does not allow simultaneous users

32.Which of the following is a limitation of DBMS?
a) Inability to store data b) Poor at complex calculations.
c) Poor security features d) Lack of user interface

33.What does the DBMS use to protect against unauthorized data access?
a) Integrity constraints b) Backup files
c) Automated deletion d) Data replication

34.What is a common challenge organization face when using DBMS?
a) Lack of data storage b) Complexity requiring continuous employee training
c) Inability to modify records d) No support for queries

35.What potential risk arises when many individuals use the same application simultaneously?
a) Data loss b) Increase in storage capacity
c) Automatic backup d) Faster data retrieval

36.Which of the following is an advantage of DBMS?
a) It eliminates the need for security b) It guarantees data integrity and security
c) It requires no initial investment d) It is simple and requires no user training

37.How does a DBMS typically store and retrieve data?
a) Only in one fixed method b) In a series of manners depending on requirements
c) By using non-electronic means d) By random allocation

38.In which sector are database systems used to manage customer orders, shipping information, and payment details?
a) Banking b) E-commerce c) Healthcare d) Education

39.Which type of data do research organizations typically manage using database systems?
a) Sales records b) Scientific data like experiments and simulations
c) Employee information d) Tax records

40.Which sector uses database systems for managing taxes, public safety, and public services?
a) Business b) Government c) Social media d) Banking

41.How do social media platforms utilize database systems?
a) To manage transportation data b) To store user profiles, posts, comments, and likes
c) To monitor bank transactions d) To manage academic records

42.What is the common factor across all applications of database systems?
a) They all require hardware maintenance
b) They involve storing, managing, and retrieving large amounts of structured data effectively
c) They only store text documents
d) They do not require a user interface

43.What is the traditional method of storing data on devices like hard drives called?
a) Database Management System b) File System
c) Data Warehouse d) Data Mining

44.How does a file system organize data?
a) Into databases and schemas b) Into tables and columns
c) Into files, directories, and subdirectories d) Into graphs and charts

45.In a DBMS, data is logically organized into:
a) Files and folders b) Tables, rows, and columns
c) Directories and subdirectories d) Random blocks

46.At the physical level, what role does the file system play in a DBMS?
a) It queries data b) It stores data on disk efficiently
c) It creates user interfaces d) It performs data encryption

47.Which DBMS feature helps prevent data corruption during simultaneous access, unlike file systems?
a) Data normalization b) Backup mechanism
c) Concurrency control d) Data encryption

48.Which system provides a hierarchical structure for organizing data into directories and subdirectories?
a) DBMS b) File System c) Cloud Storage d) Data Warehouse

49.What is a major cause of data inconsistency in file systems?
a) Use of encryption b) Redundant storage of the same data in multiple files
c) Advanced query processing d) Real-time backups

50.Which layer within a DBMS abstract and optimizes interactions with the operating system’s file system?
a) Query Processor b) File Management Layer
c) Transaction Manager d) Security Manager

51.Scalability issues in file systems occur primarily because:
a) They compress data automatically b) Performance degrades as data size grows
c) They use SQL for queries d) They have automatic concurrency control

52.Which feature ensures data durability and accuracy in DBMS but is lacking in file systems?
a) Backup scheduling b) Data integrity and recovery after failures
c) User interface design d) Manual file editing

53.How does a file system organize data?
a) Into databases and schemas b) Into graphs and charts
c) Into files, directories, and subdirectories d) Into tables and columns

54.In a DBMS, data is logically organized into:
a) Tables, rows, and columns b) Directories and subdirectories
c) Files and folders d) Random blocks

55.At the physical level, what role does the file system play in a DBMS?
a) Creates user interfaces b) Stores data on disk efficiently
c) Queries data d) Performs data encryption

56.Compared to DBMS, what data manipulation capability do file systems offer?
a) Limited capabilities requiring user-written procedures

b) Real-time concurrency control

c) Automated transaction management

d) Advanced querying and indexing

57.Which DBMS feature helps prevent data corruption during simultaneous access, unlike file systems?
a) Backup mechanism b) Data normalization
c) Data encryption d) Concurrency control

58.Why are file systems considered weak in security compared to DBMS?
a) They encrypt all data by default b) They support multi-factor authentication
c) They lack access control and user permission management
d) They use too many passwords

59.Which of the following DBMS features help overcome file system limitations?
a) Write-only access b) Data normalization and transaction management
c) File copying d) Manual data backup

60.The overall design of a database system that describes how data is stored, accessed, and managed is called what?
a) Data security b) Database architecture c) Data model d) Data structure

61.Which components are typically included in database architecture?
a) User interface, graphics, and network protocols
b) Hardware, data organization, relationships, and security tools
c) Only hardware and software
d) Physical storage devices only

62.What are the main parts of a database architecture?
a) Query optimizer, data miner, and security module
b) Data entry form, audit trail, and backup system
c) User interface, network layer, power supply, and CPU
d) Storage manager, query processor, transaction manager, and authorization manager

63.Which structure involves a client, application server, and database working together?
a) Cloud-based architecture b) Centralized architecture
c) Two-tier architecture d) Three-tier architecture

64.What is one advantage of 3-tier architecture?
a) It works only with small data sets b) It eliminates network communication
c) It is highly scalable and secure d) It is simple to design and maintain

65.Which type of DBMS architecture allows users to interact directly with the database for quick access?
a) 3-Tier architecture (Multi-Tier) b) Cloud database
c) 1-Tier architecture (Standalone) d) 2-Tier architecture (Client-Server)

66.What is a main disadvantage of 1-Tier architecture?
a) Poor security and no data sharing b) High cost
c) Supports too many users d) Difficult to set up

67.Why are cloud-based databases popular?
a) They are difficult to access from anywhere b) They are stored on local hardware only
c) They are easy to scale and accessible from anywhere
d) They are not suitable for large data

68.Which type of database is designed mainly for analyzing large amounts of data collected from other sources?
a) NoSQL Database b) Cloud Database c) Distributed Database d) Data Warehouse

69.The overall design of a database system that describes how data is stored, accessed, and managed is called what?
a) Data security b) Database architecture c) Data model d) Data structure

70.Which components are typically included in database architecture?
a) User interface, graphics, and network protocols
b) Hardware, data organization, relationships, and security tools
c) Only hardware and software
d) Physical storage devices only

71.What are the main parts of a database architecture?
a) Query optimizer, data miner, and security module
b) Data entry form, audit trail, and backup system
c) User interface, network layer, power supply, and CPU
d) Storage manager, query processor, transaction manager, and authorization manager

72.Which structure involves a client, application server, and database working together?
a) Cloud-based architecture b) Centralized architecture
c) Two-tier architecture d) Three-tier architecture

73.What is one advantage of 3-tier architecture?
a) It works only with small data sets b) It eliminates network communication
c) It is highly scalable and secure d) It is simple to design and maintain

74.Which type of DBMS architecture allows users to interact directly with the database for quick access?
a) 3-Tier architecture (Multi-Tier) b) Cloud database
c) 1-Tier architecture (Standalone) d) 2-Tier architecture (Client-Server)

75.What is a main disadvantage of 1-Tier architecture?
a) Poor security and no data sharing b) High cost
c) Supports too many users d) Difficult to set up

76.Why are cloud-based databases popular?
a) They are difficult to access from anywhere b) They are stored on local hardware only
c) They are easy to scale and accessible from anywhere
d) They are not suitable for large data

77.Which type of database is designed mainly for analyzing large amounts of data collected from other sources?
a) NoSQL Database b) Cloud Database c) Distributed Database d) Data Warehouse

78.Which level of database architecture shows the overall design of the database system?
a) Logical level b) Conceptual level c) Physical level d) User level

79.What role does the transaction manager play in database architecture?
a) Manages user permissions b) Ensures database transactions are processed reliably
c) Handles physical data storage d) Displays data to users

80.Which architecture model uses APIs like ODBC or JDBC to communicate between client and server?
a) 1-Tier architecture b) 3-Tier architecture
c) 2-Tier architecture d) Cloud architecture

81.In the Hierarchical Model, what kind of relationship is primarily used to organize data?
a) Many-to-many b) One-to-many c) One-to-one d) Circular

82.Which of the following is a disadvantage of the Hierarchical Model?
a) Easy to search data anywhere b) Complex structure allowing multiple parent nodes
c) Searching can be slow due to sequential top-to-bottom search
d) Tables are flexible

83.What is one advantage of the Network Model?
a) Easy access and multi-relation support. b) Very user-friendly in design
c) No need for pointers d) Fully achieves structural independence

84.What is a major disadvantage of the Network Model?
a) Complex structure with many pointers. b) Lacks many-to-many support.
c) Easy to understand and design d) Fully automated query optimization

85.Who proposed the Relational Database Model?
a) Edgar F. Codd b) Alan Turing c) Charles Bachman d) Bill Gates

86.What is a strength of the Relational Model?
a) Handles hierarchical data best b) Simple, flexible, and maintains data integrity
c) Poor security features d) Inefficient for queries

87.What does the Object-Oriented Database Model store data as?
a) Only tables and rows b) Objects combining data and behaviors
c) Simple files d) Hierarchical trees only

88.Which feature is supported by the Object-Oriented Model?
a) Polymorphism and inheritance b) Only SQL queries
c) Flat file structures d) Lack of extensibility

89.What is a disadvantage of the Object-Oriented Database Model?
a) Easy to understand and widely used b) Complex management; no standard query.
c) High community support d) Simple security features

90.Which database model organizes data in a clear tree-like structure with parent and child nodes?
a) Network Model b) Object-Oriented Model
c) Relational Model d) Hierarchical Model

91.Which model supports multi-parent relationships, allowing complex data connections?
a) Hierarchical Model b) Object-Oriented Model
c) Relational Model d) Network Model

92.What technical complexity is associated with network databases?
a) Pointers complicate updates b) No data integrity enforcement
c) No support for transactions d) No relationship between records

93.Why is normalization used in relational databases?
a) To organize data and reduce redundancy b) To allow multi-parent relationships
c) To simplify object behavior d) To create complex data types

94.Which database model was designed mainly to handle multimedia and complex data types?
a) Network Model b) Object-Oriented Model
c) Relational Model d) Hierarchical Model

95.Which model is widely recognized for supporting complex queries and maintaining data accuracy?
a) Object-Oriented Model b) Relational Model
c) Hierarchical Model d) Network Model

96.Which characteristic makes the relational model popular?
a) Enables flexible, secure data management b) Difficulty of use
c) Complex pointer usage d) Lack of security features

97.What does a database schema define?
a) How data is encrypted b) How data is organized and stored within a database
c) The speed of the database system d) The user interface of a database

98.Which of the following is NOT a schema object in a database schema?
a) Tables b) Fields c) Packages d) Operating system

99.What type of schema defines how data is physically stored on disks including files and indexes?
a) Logical Schema b) View Schema c) Physical Schema d) Conceptual Schema

100. Which schema describes the logical organization of data, including tables, relationships, and constraints?
a) Physical Schema b) Logical Schema c) View Schema d) Storage Schema

101. Which schema has the highest level of abstraction?
a) Logical Schema b) Physical Schema c) View Schema d) Storage Schema

102. Which schema is managed and dependent on specific DBMS and hardware?
a) Logical Schema b) Physical Schema c) View Schema d) Conceptual Schema

103. What is an example of a logical schema representation?
a) SQL views b) Entity-Relationship diagrams c) Index definitions d) Storage files

104. What type of schema includes subsets of tables, columns, computed fields, and joins?
a) View Schema b) Physical Schema c) Logical Schema d) Network Schema

105. The structure that defines how data is organized and stored, including tables, fields, and relationships, is called:
a) Physical Schema b) Database Schema c) Network Model d) View Schema

106. The schema that describes how data is physically stored in files and indexes, managed by the database administrator, is known as:
a) Logical Schema b) Physical Schema c) Conceptual Schema d) View Schema

107. Which schema focuses on how individual users or applications see customized parts of the database?
a) Logical Schema b) Physical Schema c) View Schema d) Storage Schema

108. A schema that organizes data logically with tables, relationships, and constraints such as in ER diagrams is called:
a) Logical Schema b) Physical Schema c) View Schema d) Network Schema

109. Storage structures, indexes, and file paths are components of which schema?
a) Logical Schema b) Physical Schema c) View Schema d) Conceptual Schema

110. Which schema ensures data integrity and defines the organization of data with attributes and relationships?
a) Logical Schema b) Physical Schema c) View Schema d) Database Model

ANSWER KEY
1	C	11	C	21	B	31	B	41	B	51	B	61	B	71	D	81	B	91	D	101	C
2	B	12	C	22	A	32	A	42	B	52	B	62	D	72	D	82	C	92	A	102	B
3	B	13	A	23	B	33	A	43	B	53	C	63	D	73	C	83	A	93	A	103	B
4	C	14	B	24	B	34	B	44	C	54	A	64	C	74	C	84	A	94	B	104	A
5	B	15	B	25	B	35	A	45	B	55	B	65	C	75	A	85	A	95	B	105	B
6	B	16	C	26	C	36	B	46	B	56	A	66	A	76	C	86	B	96	A	106	B
7	B	17	B	27	A	37	B	47	C	57	D	67	C	77	D	87	B	97	B	107	C
8	B	18	C	28	B	38	B	48	B	58	C	68	D	78	B	88	A	98	D	108	A
9	B	19	A	29	B	39	B	49	B	59	B	69	B	79	B	89	B	99	C	109	B
10	C	20	B	30	B	40	B	50	B	60	B	70	B	80	C	90	D	100	B	110	A

Sort Questions (Knowledge and Understanding)

1. Define data and explain how it differs from information. Provide two examples of each.

2. What is a database? Describe its main purpose and give three examples of database applications.

3. What is the purpose of a DBMS, and what four features make it better than a file system?

4. Discuss two advantages and two disadvantages of using a DBMS in organizations.

5. Identify and explain five common applications of database systems in different sectors.

6. What are the primary limitations of file systems compared to DBMS? Discuss at least three with examples.

7. Explain the three main types of DBMS architectures (1-Tier, 2-Tier, 3-Tier) with their pros and cons.

8. What are centralized, distributed, cloud, and NoSQL databases? Provide a brief explanation of each type.

9. Define the hierarchical database model and discuss its advantages and limitations.

10. Compare the network database model with the hierarchical model, focusing on data relationships and complexity.

11. What is the relational model of databases? Outline its advantages and disadvantages.

12. Describe the object-oriented database model and explain how it differs from the relational model. Include two advantages and two disadvantages.

13. Define database schema and explain the difference between physical, logical, and view schemas with examples.

14. Discuss how changes in logical schema differ in impact from changes in physical and view schemas.

15. Explain the importance of data integrity and security in a DBMS. How does DBMS enforce these features?

16. Describe concurrency control in DBMS and explain why it is essential for multi-user environments.

17. What is the role of the database engine and query language in a DBMS? Provide examples of popular query languages.

18. Discuss the challenges and performance issues faced by relational databases when handling very large datasets.

19. Explain how backup and recovery mechanisms in DBMS protect data and ensure business continuity.

20. Describe the responsibilities of a system analyst in the context of database systems.

21. Compare and contrast naive, sophisticated, and casual/temporary end-users in terms of their database usage and technical knowledge.

22. What factors should be considered when choosing a database architecture for an organization?

23. Explain the advantages and disadvantages of cloud databases compared to centralized and distributed databases.

24. Discuss the significance of database schema design and its impact on database efficiency and user interaction.

Short Questions (Application Based)

25. Describe the various types of database users and their roles, highlighting the responsibilities of a Database Administrator (DBA).

26. What is the difference between data and information?

27. Define a database in simple terms.

28. What does a Database Management System (DBMS) do?

29. Name two advantages of using a DBMS over a traditional file system.

30. List three common applications of database systems.

31. What is a limitation of a traditional file system compared to a DBMS?

32. Who is responsible for managing and securing a database system?

33. What is the function of the query language in a DBMS?

34. Explain the difference between centralized and distributed databases.

35. What is a database schema?

36. What does DBMS stand for and what is its main purpose?

37. Explain the difference between a file system and a database system.

38. What is data redundancy and why is it a problem in file systems?

39. Name two types of database users and their roles.

40. What is a primary key in a database table?

41. Define data integrity in the context of a database system.

42. What is the function of the query processor in a DBMS?

43. Explain what a distributed database is.

44. How does the three-tier architecture enhance database security?

45. What is the difference between logical schema and physical schema in a database?

Short Questions (Higher Ability)

46. Explain query optimization techniques and how they improve database performance.

47. Discuss the differences between hierarchical, network, and relational database models with examples.

48. What is the CAP theorem? How does it affect distributed database design?

49. Describe the concept of data fragmentation in distributed databases and its benefits.

50. Explain hashing and how it helps in efficient data retrieval in databases.

51. Discuss concurrency control methods in DBMS and why they are important.

52. Explain the role of transactions and ACID properties in maintaining database integrity.

53. Describe the object-oriented database model and its advantages over relational databases.

54. Explain the three levels of database schema and their significance.

55. Discuss backup and recovery techniques in DBMS and how they prevent data loss.

Long Questions (Knowledge and understanding)

56. Explain the concept of data, information, and a database. How do they differ, and why is each important in database management? Illustrate your answer with examples.

57. Describe the key features of a Database Management System (DBMS). How do these features improve data management compared to traditional file processing systems?

58. Discuss the advantages and disadvantages of DBMS. Explain how these advantages and disadvantages impact organizational data management and decision-making.

59. Explain the different types of database users and their roles within an organization. Why is it important to manage user access and roles carefully?

60. Describe the architecture of DBMS, including 1-Tier, 2-Tier, and 3-Tier architectures. Discuss the pros and cons of each architecture and give examples of their typical applications.

61. Compare and contrast the hierarchical, network, relational, and object-oriented database models. Discuss the advantages and disadvantages of each model in handling different types of data.

62. Define a database schema and explain the differences between physical, logical, and view schemas. How do these schemas contribute to effective data management?

63. Explain the limitations of traditional file systems compared to DBMS, focusing on data redundancy, inconsistency, concurrency control, security, and scalability.

Long Questions (Application Based)

64. How does a database system help an online shopping website manage products, orders, and payments? Why is it important to keep data correct and manage many users at once?

65. How are patient records, doctor appointments, and personal privacy protected by databases in hospitals? How do database systems keep this information safe?

66. How do banks use databases to handle money transfers, detect fraud, and protect customer information? What happens if data is lost, and how do they fix it?

67. Imagine you are designing a database for a university to keep track of students, courses, and teachers. How would you organize it so everything is accurate and easy to find?

68. Social media sites have users all over the world. How do they use databases that are spread out across many computers to make sure data is always available and fast?

69. How does choosing different database designs (like one-level, two-level, or three-level systems) affect big company software used for managing resources and employees?

70. How does a data warehouse help stores understand customer buying habits and improve their business? What important parts should this kind of database have?

71. You have a delivery company that needs to track packages and plan routes. How can a database help make this work better and faster?

72. How is storing multimedia files like videos or images easier with object-oriented databases compared to old-style table-based databases?

Long Questions (Higher Ability)

73. Explain how a database system processes a user’s query step-by-step.

74. What problems happen when many users use the database at the same time? Describe three common issues.

75. What are the weaknesses of relational databases that caused the creation of object-oriented databases?

76. What features should a DBMS have to help restore data after a system crash? List four important ones.

77. Explain what a deadlock is in a database and describe two ways to fix it.

78. What is big data? Describe its main three features in simple words.

79. What are the main tasks when designing the physical storage of data in a database?

80. What are the key benefits and drawbacks of object-oriented databases compared to relational ones?

81. Describe the three-tier database system and explain what data independence means.

82. What are four important rules that distributed databases must follow?

83. What kind of information is saved in a database log file?

Unit 2: Entity Relationship Model (ER-Model)

MCQ based on Knowledge and Understanding

1.   What is the main purpose of the Entity-Relationship (ER) model in database design?
A) To map entity relationships            B) To perform physical storage of data
C) To calculate database statistics       D) To encrypt data in the database

2. Which of the following is NOT a main component of the ER model?
A) Entities B) Attributes C) Relationships D) Indexes

3. In an ER diagram, entities are represented by which shape?
A) Oval B) Rectangle C) Diamond D) Circle

4. Attributes in ER diagrams are shown as:
A) Diamonds B) Rectangles C) Ovals D) Pentagons

5. Which shape is used to represent Relationships in an ER diagram?
A) Rectangle B) Triangle C) Diamond D) Ellipse

6. What types of relationship cardinality can an ER model describe?
A) One-to-one B) One-to-many C) Many-to-many D) All of the above

7.   What is the significance of a key in the ER model?
A) To uniquely identify entities or attributes        B) To show relationship strength
C) To group multiple attributes together   D) To link two entities with a relationship

8. Multi-valued attributes in an ER diagram are represented by:
A) Single oval B) Double oval C) Dashed rectangle D) Double rectangle

9. Which of the following best describes a composite attribute?
A) An attribute that can be broken down into smaller sub-parts
B) An attribute that is derived from other attributes
C) An attribute that uniquely identifies an entity
D) An attribute with multiple values

10.A weak entity is typically represented by:
A) A single rectangle B) An oval C) A double rectangle D) A diamond

11.What is an entity in a database?
A) A foreign key that links tables
B) A specific object, person, place, or thing identifiable and distinguishable from others
C) A set of columns in a table
D) A relationship between two tables

12.Which of the following elements is used to represent an entity in a relational database?
A) Column B) Row C) Table D) Index

13.A weak entity is different from a strong entity because:
A) It has fewer attributes
B) It cannot be uniquely identified by its own attributes alone
C) It has a primary key different from the strong entity
D) It exists independently in the database

14.How is a weak entity typically represented in a database table?
A) With a composite primary key including a foreign key from a related strong entity
B) With a unique primary key unrelated to other entities
C) As part of the strong entity’s table attributes
D) Without any primary key

15.What is an entity set?
A) A collection of attributes belonging to an entity
B) A logical grouping of similar entities sharing common characteristics
C) The connections between two entity types
D) The primary key attributes of an entity

16.Which data type is NOT commonly used for attributes in a database?
A) Numeric B) Character C) Boolean D) Folder

17.What defines a composite attribute?
A) A single-valued attribute
B) An attribute that uniquely identifies an entity
C) An attribute composed of multiple simple attributes
D) An attribute derived from other attributes

18.Derived attributes are:
A) Calculated from other attributes B) Primary keys
C) Attributes with multiple values D) Attributes representing entity relationships

19.Key attributes in a database are used to:
A) Uniquely identify an entity within an entity set B) Link one entity set to another
C) Store multiple values in a single attribute D) Define relationships between entities

20.Foreign key attributes are used to:
A) Uniquely identify an entity in its own entity set
B) Connect one entity set to another entity set
C) Derive values from other attributes
D) Represent multi-valued attributes

21.What defines a relationship between two tables in a relational database?
A) Two tables sharing the same primary key
B) One table having a foreign key referencing the primary key of another
C) Tables having identical column names
D) Separate tables without any common attribute

22.In a one-to-one relationship, how many records from the first table can relate to the second table?
A) Only one B) Zero or many C) Multiple D) One or more

23.In the one-to-one example provided, the Student ID in Contact Info table acts as:
A) Foreign key B) Composite key C) Candidate key D) Primary key

24.What type of relationship is also known as many-to-one?
A) Many-to-many B) One-to-many C) Partial participation D) One-to-one

25.In a one-to-many relationship, which table contains the foreign key?
A) The table on the "many" side B) Neither table
C) Both tables D) The table on the "one" side

26.What characterizes a many-to-many relationship?
A) All records are identical
B) There is no relationship between tables
C) Records in both tables can relate to multiple records in the other
D) Each record in one table relates to at most one record in the other

27.What is the role of a join table in a many-to-many relationship?
A) To store only primary keys
B) To split the many-to-many relationship into two one-to-many relationships
C) To store duplicate data
D) To merge tables

28.Which foreign keys typically exist in a join table like Enrollments?
A) Composite keys unrelated to other tables
B) No keys
C) Primary keys from both related tables as foreign keys
D) Only the primary key of the first table

29.Additional attributes in a join table might include:
A) Only the primary key from one table
B) Calculated columns unrelated to relationships
C) Mission statements
D) Date and cost related to the relationship instance

30.If a student registers for three classes, how many records exist for that student in the Enrollments table?
A) Three B) One C) Two D) Zero

31.What does cardinality represent in a database relationship?
a) The number of attributes in an entity
b) The number of times an entity participates in a relationship
c) The length of data stored in a column
d) The number of tables in the database

32.Which of the following represents a one-to-one cardinality?
a) Multiple students enroll in multiple courses
b) One student has one unique student ID, and each student ID belongs to only one student
c) One doctor serves many patients
d) Many doctors serve many patients

33.If several compounders work in a hospital, which cardinality type represents the relationship between the hospital and compounders?
a) Many-to-one b) One-to-many c) One-to-one d) Many-to-many

34.Which key type uniquely identifies each record in a database table and cannot contain null values?
a) Foreign key b) Composite key c) Primary key d) Candidate key

35.What is a composite key in database terms?
a) A field that refers to a primary key in another table
b) Combination of multiple fields that uniquely identify a record
c) Any field that contains null values
d) An attribute used to calculate other data

Application Based MCQs

36.You are designing a database for a university. Which ER model component would you use to represent a “Student”?
a) Attribute b) Entity c) Relationship d) Key

37.In a hospital database, the “Patient” entity is related to “Doctor” entity through a relationship “Consults.” How would this relationship likely be represented in an ER diagram?
a) Rectangle b) Oval c) Diamond d) Triangle

38.When designing a bank database, the “Account Number” attribute uniquely identifies each account. What kind of attribute is this?
a) Composite attribute b) Derived attribute
c) Key attribute d) Multivalued attribute

39.If a “Course” entity has an attribute “Course Duration” that can be calculated from the “Start Date” and “End Date”, what type of attribute is “Course Duration”?
a) Composite attribute b) Derived attribute
c) Simple attribute d) Key attribute

40.You need to model a “Library” database where books may have multiple “Authors.” Which kind of relationship denotes this?
a) One-to-one b) One-to-many c) Many-to-one d) Many-to-many

41.In an ER diagram, how would you represent an attribute like “Full Name” that includes “First Name” and “Last Name”?
a) Simple attribute b) Key attribute
c) Composite attribute d) Multivalued attribute

42.When implementing a “Weak Entity” in a school management system, which type of key is essential in its table?
a) Primary key alone b) Foreign key alone
c) Composite key composed of foreign key and own attributes d) No key needed

43.In an ER design, why is cardinality important?
a) To determine how many instances of one entity relate to instances of another entity
b) To specify the size of attributes
c) To uniquely identify entities
d) To model composite attributes

44.You want to ensure that every “Invoice” is linked to exactly one “Customer.” What type of participation is this in the ER model?
a) Partial participation b) Total participation
c) Optional participation d) Both a and c

45.In a university ER diagram, if “Student” can be enrolled in multiple “Courses” and a “Course” can have multiple “Students,” which ER component best models this?
a) One-to-many relationship b) Entity set
c) Many-to-many relationship d) Weak entity

46.You are tasked with designing a database for a retail store. Which ER model component would best represent a “Product”?
a) Attribute b) Entity c) Relationship d) Key

47.In an ER diagram, how would you represent the fact that a “Customer” places multiple “Orders”?
a) One-to-one relationship b) Many-to-many relationship
c) Many-to-one relationship d) One-to-many relationship

48.A hospital wants to track patients and their visits. The “Visit” entity cannot be distinguished without the “Patient” entity. What type of entity is “Visit”?
a) Weak entity b) Strong entity c) Associative entity d) Composite entity

49.If the “Full Name” attribute of a student includes “First Name” and “Last Name,” what type of attribute is it?
a) Simple attribute b) Derived attribute
c) Composite attribute d) Multi-valued attribute

50.You need to model an attribute “Age” that is calculated from “Date of Birth.” What kind of attribute should “Age” be?
a) Key attribute b) Derived attribute
c) Composite attribute d) Simple attribute

51.In a university database, “Student ID” uniquely identifies each student. What type of attribute is “Student ID”?
a) Key attribute b) Foreign key attribute
c) Multivalued attribute d) Derived attribute

52.In a database schema, how would you represent the many-to-many relationship between “Students” and “Courses”?
a) By adding a foreign key in the “Student” entity
b) By creating a relationship entity linking both
c) By merging both entities into one
d) By creating composite attributes

53.Which of the following best represents foreign key attributes in a relational database?
a) Attributes that uniquely identify entities within a table
b) Attributes that link one table/entity set to another
c) Attributes that are derived from other attributes
d) Attributes that are composed of multiple other attributes

54.A “Customer” entity has an attribute “Phone Numbers” where each customer can have multiple numbers. Which type of attribute is this?
a) Key attribute b) Simple attribute
c) Multi-valued attribute d) Composite attribute

55.When can a weak entity be uniquely identified in a database?
a) By its attributes alone b) With a strong entity's primary key.
c) When it has no foreign key d) By a foreign key alone

56.In an e-commerce database, how would you represent the relationship that multiple customers can place multiple orders?
a) One-to-one relationship b) Many-to-many relationship
c) One-to-many relationship d) Total participation

57.In a hospital database, patient records are linked to doctors. Each patient can see multiple doctors, and each doctor can see multiple patients. What kind of relationship does this represent?
a) One-to-many b) One-to-one c) Many-to-many d) Partial participation

58.In a university database, each student has one unique student ID, which is used as a primary key in the Students table and as a foreign key in the Enrollment table. This supports what kind of relationship?
a) One-to-one b) Many-to-one c) One-to-many d) Many-to-many

59.In a company system, each employee may have one parking space, and each parking space is assigned to one employee. What relationship is this?
a) One-to-one b) Many-to-one c) One-to-many d) Many-to-many

60.Why are join tables used in many-to-many relationships?
a) To create more tables unnecessarily
b) To split the many-to-many relationship into two one-to-many relationships
c) To avoid using foreign keys
d) To store redundant data

61.If a student enrolls in three courses, how many records will be added to the Enrollments table (join table)?
a) One b) Two c) Three d) Zero

62.In a sales database, the Customer ID is a primary key in the Customers table and a foreign key in the Orders table. What relationship type does this indicate?
a) One-to-many b) Many-to-one c) One-to-one d) Many-to-many

63.What additional information might be stored in a join table besides foreign keys?
a) Date of transaction b) Cost or price of order
c) Payment method d) All of the above

64.In a relational database like MySQL or Oracle, what language is typically used to define and query these relationships?
a) Python b) SQL c) Java d) HTML

65.What benefit does splitting data into multiple related tables offer?
a) Data redundancy b) More storage space usage
c) Increased query complexity d) Improved data integrity and reduced duplication

66.In an e-commerce database, what cardinality best describes the relationship between products and reviews?
a) Many-to-one b) One-to-many c) One-to-one d) Many-to-many

67.In a social media platform, users can have many friends, and each friend can have many friends as well. What type of cardinality is this?
a) One-to-one b) One-to-many c) Many-to-many d) Many-to-one

68.In a hospital database, multiple patients are treated by the same doctor. What cardinality describes the relationship between patients and doctors?
a) One-to-one b) Many-to-one c) One-to-many d) Many-to-many

69.In financial transaction databases, each account may have multiple transactions. What cardinality best fits this relationship?
a) One-to-many b) Many-to-many c) One-to-one d) Many-to-one

70.A content management system stores articles and tags, where one article can have many tags and one tag can refer to many articles. What type of cardinality is this?
a) One-to-one b) One-to-many c) Many-to-many d) Many-to-one

Higher Ability MCQs

71.In the Enhanced ER (EER) model, what concept allows entities to inherit attributes and relationships from a higher-level entity?
a) Aggregation b) Specialization and Generalization
c) Multivalued Attributes d) Composite Attributes

72.How does aggregation in an ER model help in database design?
a) By combining multiple entities and relationships into a higher-level abstract entity
b) By dividing an entity into sub-entities
c) By defining keys for entities
d) By removing redundant attributes

73.Which ER model feature supports modeling hierarchical data structures within a database?
a) Weak entities b) Supertypes and Subtypes
c) Composite keys d) multi-valued attributes

74.In ER modeling, what does cardinality specify?
a) The number of attributes an entity possesses
b) The complexity of the relationship
c) The uniqueness of the entity identifier
d) The number of instances in one entity that relate to instances in another entity

75.Why are weak entities significant in database design?
a) They reduce redundancy by existing independently
b) They help model entities that cannot be uniquely identified by their own attributes alone
c) They simplify database queries
d) They represent a many-to-many relationship

76.What kind of relationship is represented when multiple entities on one side connect to multiple entities on the other?
a) One-to-one b) One-to-many c) Many-to-many d) Total participation

77.Which symbol in an ER diagram represents a multivalued attribute?
a) Single oval b) Double oval c) Rectangle d) Diamond

78.If an attribute can be derived from another, what type of attribute is it?
a) Key attribute b) Composite attribute
c) Derived attribute d) Simple attribute

79.In an ER model, what does total participation signify?
a) Every instance of an entity participates in at least one relationship
b) Entities can optionally participate in relationships
c) Participation of entities is not required
d) Entities can participate multiple times

80.How do keys contribute to the ER model?
a) By grouping entities b) By linking attributes together
c) By uniquely identifying entities or attributes d) By describing entity relationships

81.What is the main purpose of introducing Generalization, Specialization, and Aggregation in the ER model?
a) To increase database complexity
b) To support abstraction and manage complex data
c) To convert ER model into relational model
d) To store more data types

82.Generalization in ER modeling is best described as:
a) Top-down approach combining entities b) Bottom-up approach dividing entities
c) Parallel approach d) Side-by-side approach

83.Which of these is an example of generalization?
a) EMPLOYEE → DEVELOPER, TESTER b) STUDENT + FACULTY → PERSON
c) CAR + BIKE → VEHICLE → ROAD d) PROJECT → WORKS_FOR → EMPLOYEE

84.Specialization in ER modeling refers to:
a) Merging two entities into one b) Dividing an entity into more specific entities
c) Converting attributes into entities d) Removing entities from the model

85.Specialization typically follows which design approach?
a) Horizontal b) Bottom-up c) Top-down d) Diagonal

86.Aggregation in ER modeling is used to:
a) Model complex relationships by treating relationships as entities
b) Remove redundant attributes
c) Define attribute keys
d) Simplify derived attributes

87.What does total participation of an entity in a relationship signify?
a) Each entity instance must participate in at least one relationship instance
b) Entity participation is optional
c) Participation depends on foreign keys
d) Entities can participate multiple times

88.In an ER diagram, which symbol indicates a weak entity?
a) Rectangle b) Double rectangle c) Oval d) Diamond

89.Derived attributes in an ER model are represented by:
a) Single oval b) Double oval c) Dashed oval d) Diamond

90.Participation inheritance in ER modeling refers to:
a) An entity inheriting attributes from its subclasses
b) A subclass inheriting participation constraints from the superclass in relationships
c) Aggregating multiple attributes into one
d) Assigning keys in multi-valued attributes

91.In a healthcare database, a "Patients" table is linked one-to-one with a "Medical Records" table for confidentiality. What is a key benefit of this design?
a) It duplicates data to improve speed
b) It allows multiple patients to share one medical record
c) It combines all data into a single table for easy access
d) It minimizes redundant data while separating sensitive information

92.Specialization in ER modeling is best used when:
a) Creating new independent attributes
b) Merging multiple tables into a larger one
c) Eliminating weak entities
d) You need to break a broad entity into more specific sub-entities

93.Aggregation in an ER model allows:
a) Combining relationships and entities into a higher-level abstract entity
b) Breaking an attribute into sub-attributes
c) Representing multi-valued attributes
d) Simplifying primary keys

94.A many-to-many relationship between "Students" and "Courses" is implemented using:
a) A join table containing foreign keys referencing both entities
b) A composite attribute in the "Students" table
c) Foreign key in only one of the tables
d) Multi-valued attributes in both tables

95.What does total participation of an entity in a relationship indicate?
a) Every instance of the entity must be involved in at least one relationship instance
b) Participation is optional for each instance
c) The entity is a weak entity
d) The entity can have multiple attributes

96.Which symbol in an ER diagram represents a weak entity?
a) Double rectangle b) Diamond c) Single oval d) Double oval

97.Derived attributes in an ER model are usually:
a) Plastically stored in the database
b) Composite attributes with multiple sub-attributes
c) Calculated from other attributes logically
d) Key attributes

98.What kind of participation constraint typically applies to subclasses in specialization?
a) Total participation b) No participation required
c) Optional participation d) Partial participation

99.In a relational database, why is normalization important when designing relationships?
a) To increase redundant data
b) To minimize data redundancy and improve data integrity
c) To justify the use of multivalued attributes
d) To increase table size artificially

100. In an ER diagram, what distinguishes a composite attribute?
a) An attribute derived from two or more attributes
b) An attribute that can be subdivided into smaller meaningful parts
c) An attribute used as a foreign key
d) An attribute with multiple values

101. In a hospital database, multiple patients undergo surgeries performed by a single surgeon. What type of cardinality best represents this relationship?
a) One-to-one b) Many-to-one c) Many-to-many d) One-to-many

102. In a corporate database, employees may work on multiple projects, and projects can have multiple employees assigned. What type of cardinality applies here?
a) Many-to-many b) One-to-many c) Many-to-one d) One-to-one

103. When a "Student ID" is used as a unique identifier in a student table, and referenced in an enrollment table to establish relationships, what type of key is "Student ID" in the enrollment table?
a) Primary key b) Candidate key c) Foreign key d) Composite key

104. What is the advantage of using a composite key in database tables?
a) It allows combining multiple foreign keys into one
b) It enables unique identification by combining multiple fields
c) It creates redundancy in data
d) It eliminates the need for primary keys

105. Which mapping cardinality allows for the relationship where a single student ID can link to multiple class IDs, and each class ID can link to multiple student IDs?
a) One-to-one b) One-to-many c) Many-to-one d) Many-to-many

ANSWER KEY
1	A	11	B	21	B	31	B	41	C	51	A	61	C	71	B	81	B	91	D	101	B
2	D	12	C	22	A	32	B	42	C	52	B	62	A	72	A	82	A	92	D	102	D
3	B	13	B	23	A	33	B	43	A	53	B	63	D	73	B	83	C	93	A	103	C
4	C	14	A	24	B	34	C	44	B	54	C	64	B	74	D	84	B	94	A	104	B
5	C	15	B	25	A	35	B	45	C	55	B	65	D	75	B	85	C	95	A	105	D
6	D	16	D	26	C	36	B	46	B	56	B	66	B	76	C	86	A	96	A
7	A	17	C	27	B	37	C	47	D	57	C	67	C	77	B	87	A	97	C
8	B	18	A	28	C	38	C	48	A	58	C	68	B	78	C	88	B	98	D
9	A	19	A	29	D	39	B	49	C	59	A	69	A	79	A	89	C	99	B
10	C	20	B	30	A	40	D	50	B	60	B	70	C	80	C	90	B	100	B

Short Questions based on knowledge and understanding Test

Define the Entity-Relationship (ER) model and explain its use in database design.
What are the three main components of the ER model? Briefly describe each.
How is an entity represented in an ER diagram, and what examples can you give?
What is an attribute in the ER model? List different types of attributes with examples.
Explain the concept of a weak entity and how it differs from a strong entity.
What does the term "entity set" mean? How is it represented in a relational database?
Describe one-to-one, one-to-many, and many-to-many relationships in the context of ER modeling.
How does a foreign key relate to entities and relationships in a database?
What do the cardinality mappings one-to-one, one-to-many, many-to-one, and many-to-many signify in database relationships? Give a real-life example of any one.
List and explain the different types of keys in DBMS and their significance.

Short Questions based on Application Test

Explain how the ER model helps in designing complex databases in a business application.
How can ER diagrams assist in visualizing the relationships between entities in a healthcare management system?
What role do attributes play in representing customer data in an e-commerce database using the ER model?
Describe how weak entities are used in real-world database systems, such as in representing dependent insurance policies.
How does defining cardinality mapping improve the design of a university course registration system?
Illustrate the use of primary and foreign keys in maintaining relationships between employee and department tables in an organization.
How can ER modeling be used to optimize database queries in a retail inventory management system?
Describe the significance of many-to-many relationships in social networking database design.
Explain how composite attributes can be beneficial in storing address information in a customer relationship management (CRM) system.
How does the concept of entity sets simplify data organization in a library management database?

Short Questions based on Higher Ability Test

Explain how generalization and specialization enhance the basic ER model in handling complex data relationships.
Discuss the role of inheritance in the Enhanced ER (EER) model and its impact on entity attribute management.
How does aggregation help to represent complex relationships in an ER diagram? Provide an example.
Differentiate between weak and strong entities with reference to their identification in an ER model.
Analyze the importance of cardinality constraints in accurately modeling real-world business rules.
Describe how multi-valued and derived attributes affect database normalization and query optimization.
Explain the significance of keys (primary, foreign, candidate) in maintaining referential integrity within relational schemas derived from ER diagrams.
Evaluate how ER diagrams assist stakeholders in communicating database design requirements effectively.
Critically assess common mistakes in ER model design that can lead to redundancy and inconsistency in databases.
Propose a strategy to convert an ER diagram with complex relationships and keys into an efficient relational schema.

Long Questions based on knowledge and understanding Test

Explain the Entity-Relationship (ER) model and discuss how it helps in designing complex databases by representing entities, attributes, and relationships effectively.
Define the three main components of the ER model. Illustrate with examples how entities, attributes, and relationships are typically represented in ER diagrams.
Discuss the concept of weak entities. How do weak entities depend on strong entities, and how are they represented in both ER diagrams and relational databases?
What is an entity set? Explain how entity sets contribute to organizing data in relational databases with a practical example.
Describe the different types of attributes in an ER model. How does each attribute type, such as simple, composite, derived, key, and foreign key, function in representing data?
Explain the types of relationships in a relational database with examples, including one-to-one, one-to-many, many-to-one, and many-to-many relationships.
How does cardinality define the nature of relationships in database systems? Discuss the significance of cardinality mapping with relevant real-life examples.
Explain the process and importance of creating a join table in many-to-many relationships. How does it facilitate database normalization and efficient querying?
What are the different types of keys in DBMS? Compare primary key, foreign key, candidate key, and composite key in terms of their roles and usage.
How does the ER model help in identifying and resolving issues of redundancy and inconsistency in database design? Discuss with examples of how proper relationships and keys prevent such issues.

Long Questions based on Application Test

Explain how the ER model supports database designers in creating a logical and structured view of a complex database. Provide examples of entities, attributes, and relationships in a real-world system.
Considering a school management system, design an ER diagram for entities like Student, Course, and Teacher, describing the possible relationships and cardinalities between them.
Discuss how weak entities are represented and managed in a database. Give an example scenario where weak entities are necessary and explain why.
Describe the different types of attributes in an ER model. How would you decide which attributes to use for a Customer entity in an e-commerce database?
Explain the one-to-one, one-to-many, many-to-one, and many-to-many relationships with appropriate illustrations from a hospital management system.
For an online retail store, describe how keys (primary, foreign, and composite) can be used to maintain database integrity and establish relationships among Order, Customer, and Product tables.
How does cardinality mapping influence database normalization? Discuss the impact of incorrect cardinality assumptions using examples from a library management system.
Propose a method to convert an ER diagram with multi-valued and composite attributes into a relational schema, considering data redundancy and query efficiency.
Explain how a join table works to realize many-to-many relationships in relational databases. Illustrate your answer using a Student-Project assignment example.
Describe the role of ER diagrams in database migration projects. How can they help reduce data redundancy and inconsistency during the migration process?

Long Questions based on Higher Ability Test

Explain how the ER model helps to design complex databases by showing the connection between entities, their attributes, and relationships with simple examples.
Describe the difference between entities and weak entities, and explain why weak entities need a strong entity to give them meaning, with real-life examples.
How does an entity set help organize data in a database? Illustrate with examples of a school’s students and courses.
Explain the different types of attributes in a database such as simple, composite, derived, key, and foreign key, and why they are important with examples.
What are the different types of relationships (one-to-one, one-to-many, many-to-one, and many-to-many) in a database? Give examples showing their practical use.
Define cardinality in databases. Explain how it impacts the way entities relate in a real system and why it is important for database design.
Describe the use of a join table in creating many-to-many relationships. Use an example of students enrolling in multiple classes to explain.
What are keys in databases? Explain the roles of primary, foreign, candidate, and composite keys with examples and how they ensure data integrity.
Explain how careful choice of attributes can make database design more efficient and easier to understand. Provide examples for better clarity.
Discuss how ER models help to identify and solve problems like data redundancy and inconsistency during database design.

Unit 3: Relational Model

MCQ based on Knowledge and Understanding

1. What represents a row in a relational table?
a) Tuple b) Attribute c) Domain d) Schema

2. In the relational model, which of the following uniquely identifies each record in a table?
a) Foreign key b) Composite key c) Primary key d) Domain

3. What is the term for a set of columns that together uniquely identify a row in a relation?
a) Candidate key b) Primary key c) Composite key d) Alternate key

4. Which key is a candidate key not selected as the primary key?
a) Foreign key b) Composite key c) Alternate key d) Super key

5. What does the domain of an attribute describe?
a) The number of attributes in a table
b) The possible values that an attribute can take
c) The number of rows in a relation
d) The naming convention for a column

6.   When mapping an ER model to relational schema, what should an entity’s attributes become?
a) Foreign keys           b) Fields of tables with respective data types
c) Views                     d) Constraints

7. What does a relational schema contain?
a) Data values of a database
b) Description of table structure including tables and attributes
c) Names of database users
d) Configuration files

8. Which key type consists of one or more attributes that uniquely identify a tuple but are not used as the primary key?
a) Candidate key b) Alternate key c) Foreign key d) Artificial key

9. Which attribute type can contain multiple values for a single entity?
a) Null attribute b) Multi-valued attribute c) Simple attribute d) Derived attribute

10.What is a relational instance?
a) The database schema design
b) A snapshot of all the records in the database tables at a point in time
c) An empty set of tables
d) The process of normalization

MCQ based on Application

11.Which of these real-world systems typically uses a relational database model to manage structured data?
a) Social media user profiles and connections b) Streaming video content delivery
c) Cryptocurrency blockchain d) CDN caching servers

12.In e-commerce platforms like Amazon, relational databases are used primarily to:
a) Store and process orders, customer data, and inventory
b) Manage real-time video playback
c) Minimize content delivery network latency
d) Encrypt all user communications

13.Which banking function benefits most from relational databases for transaction consistency?
a) Stock market trend visualization b) Fraud detection and transaction accuracy
c) Social media marketing analytics d) Video streaming recommendation engines

14.Healthcare systems like Mayo Clinic use relational databases to:
a) Synchronize patient records, prescriptions, and lab results
b) Store unstructured video files
c) Host public health website content
d) Perform large-scale image rendering

15.How do relational databases support online learning platforms like Coursera?
a) By delivering video through edge servers b) Analyzing social media trends
c) Streaming live lectures exclusively d) Organizing educational content and tracking learner progress

16.Relational databases assist enterprise applications in managing which of the following?
a) Customer relationships and sales pipeline b) Real-time 3D graphics rendering
c) Fast data indexing for search engines d) Blockchain-based smart contracts

17.Why might relational databases struggle with big data and unstructured data workloads?
a) They can only store numerical data
b) Their schema rigidity limits flexible data types
c) They have no query language
d) They do not support transactions

18.Which solution helps relational databases handle large-scale data and complex workloads efficiently?
a) Using flat file systems only b) Ignoring database normalization
c) Cloud-native architectures and AI-driven indexing d) Manual data entry

19.Financial systems use relational databases mostly because they require:
a) Highly flexible data schemas b) ACID compliance for reliable transactions
c) Real-time video processing d) Social media content management

20.What kind of data do relational databases organize for efficient access and modification?
a) Structured data organized into tables with rows and columns
b) Mixed video and audio data streams
c) Blockchain transaction blocks
d) Encrypted files

MCQ based on Higher Ability

21.What is a key advantage of the relational database model in real-world applications?
a) It uses flexible, schema-less data storage
b) It stores only unstructured data easily
c) It ensures consistent and rules-based data management across instances
d) It is optimized for blockchain transactions

22.Which feature of relational databases allows querying related data across multiple tables?
a) Declaration of columns without constraints
b) Storing data in flat files
c) Use of unstructured text files
d) Use of relationships and foreign keys to link tables

23.In a retail system using an RDBMS, how can a manager analyze customer order patterns?
a) By exporting raw files for manual processing
b) By running declarative SQL queries across related customer and order tables
c) By querying only the product descriptions table
d) By using blockchain analytics tools

24.Which widely used languages is designed around relational algebra for managing relational databases?
a) Python b) JavaScript c) SQL d) HTML

25.What is an example of a modern RDBMS software?
a) MySQL b) MongoDB c) Hadoop d) Cassandra

26.In an RDBMS, how is data consistency maintained when multiple clients access the database simultaneously?
a) Through distributed static files
b) By using ACID properties ensuring transactional integrity
c) Without any locking mechanisms
d) By generating random data snapshots

27.What kind of data organization does the relational model use internally?
a) JSON objects b) Graph nodes and edges
c) Two-dimensional tables with rows and columns d) Hierarchical tree structures

28.Which of the following best describes a declarative query in SQL?
a) Step-by-step procedure on how to fetch data
b) A specification of what data is needed without how to retrieve it
c) A command to insert new records only
d) A script to design the database schema

29.Relational schemas are often developed using which data modeling notation?
a) JSON schema notation b) UML class diagrams exclusively
c) HTML markup language d) Entity-Relationship (ER) model diagrams

30.Why are relational databases still widely preferred despite the emergence of NoSQL?
a) Because they are optimized for large unstructured data storage
b) Because of their robustness in handling complex queries and ensuring data consistency
c) Due to their lack of ACID compliance
d) Because they cannot be used in cloud environments

ANSWER KEY
1	A	11	A	21	C
2	C	12	A	22	D
3	C	13	B	23	B
4	C	14	A	24	C
5	B	15	D	25	A
6	B	16	A	26	B
7	B	17	B	27	C
8	A	18	C	28	B
9	B	19	B	29	D
10	B	20	A	30	B

Short Questions based on Knowledge and understanding Test

What is the relational model in DBMS and how does it organize data?
Define the terms "relation schema" and "relation instance."
Explain the roles of tuples and attributes in a relational table.
What is a domain in the context of the relational model?
Describe the purpose of keys in a relational database.
Differentiate between primary key and foreign key with examples.
What is cardinality in a relation, and how does it differ from degree?
List common data types used for attributes in a relational database.
What is a composite key? Give an example scenario where it is used.
How does a relational database ensure unique identification of records?

Short Questions based on Application Test

How does the relational model organize data in a database using tables?
What role do rows and columns play in a relational table?
How does using relations make data sorting and access easier?
Explain the significance of domains in defining table attributes with an example.
How can relational keys uniquely identify records in a table?
Differentiate between primary key and foreign key with real-world examples.
Why are candidate keys important in relational databases?
How is a composite key formed, and when is it used?
Describe the relationship between employee and department tables using foreign keys.
How do data types of columns influence database organization and querying?

Short Questions based on Higher ability Test

How does the relational model organize data in tables for efficient management?
Why are relations considered as two-dimensional tables, and what do rows and columns represent?
Explain with an example what a relational schema is and its components.
How do keys help in uniquely identifying records and establishing relationships in tables?
Differentiate between primary key, candidate key, and foreign key with examples.
What is a composite key, and when would you use it in a database?
How does a foreign key link two tables together? Give a practical example.
What role does cardinality play in defining relationships between tables?
Why is it important for tuples in a relation not to contain duplicate data?
How can understanding of the domain of attributes improve database integrity?

Long Questions based on Knowledge and understanding Test

Explain the relational model and its role in organizing data within a database using two-dimensional tables.
Describe the structure of a relation by defining tuples, attributes, and domains, illustrating with an example.
Discuss the importance of relational schema and how it represents the design of a relation, using a sample table.
Define and distinguish primary key, foreign key, candidate key, and composite key, highlighting their purpose with examples.
Explain how keys in a relational database ensure data integrity and facilitate relationships between tables.
Discuss the concept of cardinality and its role in defining relationships between tuples in related tables.
Describe the properties of relations including atomicity of values, uniqueness of tuples, and column naming conventions.
Explain how an ER diagram is mapped to a relational schema, covering entity mapping, relationship mapping, and handling of weak entities.
Discuss how relational instances represent snapshots of a database at a given time and the significance of their dynamic nature.
Explain how relational schemas support database design and maintenance, including the role of metadata and relationships among tables.

Long Questions based on Application Test

Explain how data is organized in the relational model using tables. How do rows and columns correspond to entities and their attributes?
Describe the importance of relational schemas. How do they help in organizing and managing data across multiple tables?
Discuss the role of keys in relational databases. Explain how primary keys, foreign keys, and candidate keys differ and provide examples of each.
How does the relational model ensure data integrity and avoid duplication within a relational instance?
Explain the concept of cardinality within relational databases and how it influences the relationships between tables.
Describe the process of mapping an ER diagram to a relational schema. How are entities and relationships handled during this mapping?
How can composite keys be used in scenarios where an entity requires multiple attributes to uniquely identify records? Give a practical example.
Discuss the structure of tables in a relational database and the significance of data types assigned to table columns.
Explain how relational databases support dynamic data management through relational instances. Why is it important for databases to maintain this dynamic nature?
How do foreign keys facilitate relationships between tables? Illustrate this with a real-world example involving employee and department tables.

Long Questions based on Higher ability Test

How does the relational model organize data using tables, rows, and columns? Explain with an example.
Describe what a relational schema is and how it helps structure data in a relational database.
Explain the different types of keys in a relational database and give practical examples of each.
How does the relational model prevent duplicate data entries and maintain data integrity?
Discuss the concept of cardinality and how it defines the relationship between two tables in a database.
Explain how to convert an ER diagram into a relational schema, including how to handle entities and relationships.
What is a composite key? Give an example scenario where it would be necessary in database design.
Describe the components of a relational table, including fields, records, and data types. Why is this important for data management?
Explain what a relational instance is and how it provides a snapshot of the database at a point in time.
How do foreign keys link tables, and why are they vital in relational database design? Illustrate with an example.

Unit 4: Structured Query Language (SQL)

MCQ based on Knowledge and understanding Test

What does the relational model in DBMS primarily organize data into?
a) Hierarchical trees b) Two-dimensional tables
c) Graphs d) Arrays
In a relational table, what does a row represent?
a) An attribute b) A database schema
c) An entity or record d) A domain
What is a primary key used for in a relational database?
a) Store multiple values b) Uniquely identify a record
c) Represent relationships between entities d) Store foreign keys
Which key type refers to the primary key of another table to establish relationships?
a) Primary key b) Candidate key
c) Foreign key d) Alternate key
What is a tuple in a relational database?
a) A set of related tables b) A single record or row in a table
c) A type of attribute d) A column or field name
Which of the following is true about candidate keys?
a) Only one candidate key can exist per table
b) Candidate keys cannot uniquely identify records
c) Candidate keys are potential primary keys
d) Candidate keys are always composite keys
What does cardinality specify in a relational database?
a) The data type of columns b) The number of attributes
c) The number of tuples in a table d) The relationship mapping between entities
Which attribute type cannot be subdivided further?
a) Composite attribute b) Derived attribute

c) Simple attribute d) Multi-valued attribute

What happens when a relational instance changes over time?
a) The database schema changes b) The snapshot of data updates
c) New tables are created automatically d) Relational keys become invalid
How are weak entity sets handled when mapping ER models to the relational model?
a) Ignored in the relational schema
b) Converted to foreign keys only
c) Created as separate tables with the identifying entity’s primary key included
d) Merged into strong entities

MCQ based on Application Test

11.What does SQL stand for?

A) Structured Query Language B) Simple Query Language

C) Standard Question Logic D) Scientific Query List

Which SQL command is used to create a new database?

A) MAKE DATABASE B) CREATE DATABASE

C) INSERT DATABASE D) NEW DATABASE

What type of SQL command is DELETE?

A) Data Control Language (DCL) B) Data Definition Language (DDL)

C) Data Manipulation Language (DML) D) Transaction Control Language (TCL)

What does the TRUNCATE TABLE command do?

A) Deletes entire table structure permanently

B) Deletes all records but keeps the table structure

C) Updates records conditionally

D) Adds new records to the table

Which SQL clause is used to filter records based on a condition?

A) WHERE B) ORDER BY C) GROUP BY D) JOIN

What SQL command removes a table and its data from the database?

A) DELETE TABLE B) DROP TABLE C) TRUNCATE TABLE D) REMOVE TABLE

Which command is used to give a user permission on a database object?

A) ALLOW B) GRANT C) PERMIT D) REVOKE

What type of join returns all rows from the left table with matching rows from the right table?

A) INNER JOIN B) FULL OUTER JOIN

C) RIGHT OUTER JOIN D) LEFT OUTER JOIN

What keyword is used to rename a table in SQL Server?

A) MODIFY TABLE B) EDIT TABLE C) RENAME D) ALTER TABLE

What is the default order of sorting with ORDER BY clause if ASC or DESC is not specified?

A) Descending B) Ascending C) Random D) None

MCQ based on Higher Ability Test

21.Which SQL command would you use to change the size of a column?

A) ALTER TABLE MODIFY B) UPDATE COLUMN SIZE

C) CHANGE TABLE COLUMN D) MODIFY COLUMN SET

What is a natural join in SQL?

A) A join that uses a common column with the same name and data type

B) A join that uses an explicitly specified ON condition

C) A join that only selects data from the first table

D) A union of two tables without matching conditions

What happens if you use DROP DATABASE on a database currently connected by other users?

A) It deletes the database immediately B) It causes data corruption or issues

C) Waits until all users disconnect D) Returns an error but does not delete

Which component of SQL handles optimizing queries before execution?

A) SQL Query Engine B) Optimization Engines

C) Query Dispatcher D) Classic Query Engine

How does TRUNCATE differ from DELETE in SQL?

A) TRUNCATE logs each deleted row; DELETE does not

B) TRUNCATE deletes faster without logging individual rows

C) DELETE resets identity columns; TRUNCATE does not

D) Both are identical in function and performance

What does the CASE statement in SQL do?

A) Defines groups of records

B) Implements logical if-then-else conditions

C) Controls user access

D) Combines two or more tables

Which data type in SQL Server stores fixed-width Unicode characters?

A) char B) varchar C) nchar D) nvarchar

Which DCL command revokes a previously granted permission?

A) REMOVE B) REVOKE C) DENY D) CANCEL

To add a primary key constraint on an existing table, which command is correct?

A) ALTER TABLE ADD PRIMARY KEY

B) ADD PRIMARY KEY TABLENAME

C) ALTER TABLE ADD CONSTRAINT PRIMARY KEY

D) MODIFY TABLE PRIMARY KEY

Which SQL join returns all rows from both tables, filling with NULLs where there is no match?

A) INNER JOIN B) LEFT JOIN C) RIGHT JOIN D) FULL OUTER JOIN

ANSWER KEY
1	B	11	A	21	A
2	C	12	B	22	A
3	B	13	C	23	B
4	C	14	B	24	B
5	B	15	A	25	B
6	C	16	B	26	B
7	D	17	B	27	C
8	C	18	D	28	B
9	B	19	C	29	C
10	C	20	B	30	D

Short Questions based on Knowledge and understanding Test

What does SQL stand for?
Name the four main types of SQL commands.
What is the primary function of the SELECT command?
Define the TRUNCATE TABLE command.
What type of SQL command is used to grant privileges?
What is the function of the WHERE clause in SQL?
State the difference between DROP and DELETE commands.
What does a JOIN operation do in SQL?
Describe the purpose of the ORDER BY clause.
What is the CASE statement used for in SQL?

Short Questions based on Application Test

Write the command to create a database named "Books".
How would you add a new column "FathersName" to an existing table "Student"?
Write an SQL statement to delete a table named "Employees".
How can you revoke update permission from user "Bhanu" on the table "employees"?
Write a query using INNER JOIN to combine two tables "Orders" and "Customers" on the column "CustomerID".
Write an SQL command to rename a table from "OldTable" to "NewTable".
How do you retrieve all records from a table named "Sales"?
Write a command to insert a new record into the "Student" table with id=104, name="Anmol", marks=89, age=19.
Explain how to add a primary key constraint on an existing table "Employee".
Write a query using GROUP BY to count the number of employees per designation from an "employee" table.

Short Questions based on Higher Ability Test

Explain how the SQL engine optimizes query execution.
Write a query that lists the top 3 products by total revenue percentage.
Describe the difference between a NATURAL JOIN and an INNER JOIN.
What is the effect of using the DROP DATABASE IF EXISTS command?
Explain how the CASE statement improves query logic compared to nested IF statements.
How do you drop a foreign key constraint named "fk" from the table "Employee"?
Discuss the differences between TRUNCATE and DELETE commands in terms of logging and speed.
Explain the role of the Data Control Language (DCL) in database security.

Long Questions based on Knowledge and understanding Test

Explain the role of SQL in relational database management systems and describe its main advantages.
Discuss the different types of SQL commands (DDL, DML, DCL, TCL) with examples for each.
Describe the purpose and functionality of the WHERE clause in SQL queries.
Explain the differences between the DROP, TRUNCATE, and DELETE commands in terms of their effects on tables and data.
Describe the use of the CASE statement in SQL with an example highlighting simple and searched CASE statements.
What are the different data types supported by SQL Server? Explain the difference between char, varchar, nchar, and nvarchar.
Discuss the importance of Data Control Language (DCL) commands in database security with examples.
Explain the concept of indexes in SQL and how they influence database performance.
Describe SQL's role in managing transactions using Transaction Control Language (TCL) commands with examples.
What is SQL, and why is it important for managing databases?
Explain the main groups of SQL commands with examples: DDL, DML, DCL, and TCL.
What does the WHERE clause do in an SQL query? Give an example.
Describe the differences between DROP, TRUNCATE, and DELETE commands.
What are SQL joins? Explain the key types like INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.
What is the CASE statement in SQL, and how is it useful?
List different SQL data types and explain the difference between char and varchar.
Why is managing database permissions using DCL important? Give examples.
What are indexes in databases and how do they help speed up queries?
What is the purpose of transaction control commands like COMMIT and ROLLBACK?

Long Questions based on Application Test

Write the SQL commands necessary to create a database named "Library" and a table "Books" with columns for BookID, Title, Author, and PublicationYear.
Demonstrate how to insert multiple records into a table "Students" and then update a specific student's marks using SQL.
Write SQL queries to:

- Add a new column "Email" to an existing table "Employees".

- Remove the "Age" column from the same table.

Compose an SQL statement to delete all records from the table "Orders" and explain why TRUNCATE might be preferred over DELETE in some cases.
Write the command to grant SELECT and UPDATE privileges on the table "Products" to the user "SalesRep".
Provide the SQL statement to rename the table "OldEmployee" to "NewEmployee" and explain the implications of renaming tables in a database.
Write an SQL query to implement a simple CASE statement that categorizes employees' salary ranges into 'Low', 'Medium', and 'High'.
Demonstrate with SQL commands how to add a primary key and a foreign key constraint to respective tables.
Write SQL commands to create a database called "Library" and a table "Books" with appropriate columns.
Show how to insert new student records and then update some student’s marks.
Write SQL commands to add a new column and remove an existing column from a table.
Write a query to delete all records from an "Orders" table and explain when TRUNCATE is better than DELETE.
Write a query to count employees by department using GROUP BY.
Write a command to rename a table and explain what this means for the database.
Write a query using CASE to classify salaries into groups like low, medium, and high.
Write commands to add primary key and foreign key constraints to tables.

Long Questions based on Higher Ability Test

Discuss the SQL query optimization process, highlighting the roles of the Query Dispatcher and Optimization Engines.
Write an advanced SQL query that retrieves the top 5 customers with the highest total purchase amount using aggregation and sorting.
Compare and contrast NATURAL JOIN and INNER JOIN with examples, including scenarios where each is preferable.
Detail the steps and SQL commands necessary to safely drop a database considering active connections and potential risks.
Discuss the importance of transaction management with COMMIT, ROLLBACK, and SAVEPOINT in maintaining data integrity.
Explain the impact of using TRUNCATE TABLE versus DELETE on transaction logs and database performance.
Write an SQL query to modify the datatype and size of a column in an existing table without losing data, explaining the precautions necessary.
Describe how Data Control Language (DCL) supports permission management in multi-user environments and give practical examples.
Write an SQL query using a LEFT OUTER JOIN combined with a WHERE clause to fetch all products, including those without any sales.
Design an SQL query that uses a CASE statement to handle NULL values in a salary column, displaying 'Not Disclosed' where the salary is NULL.
Explain how an SQL engine decides the best way to run a query.
Write a query to find the top 5 customers by total purchase amount.
Compare NATURAL JOIN with INNER JOIN and explain when to use each.
Describe how to safely delete a database that might be in use.
Explain how COMMIT, ROLLBACK, and SAVEPOINT help keep database data safe.
Discuss the difference between TRUNCATE and DELETE in terms of speed and logging.
Write a query to change the data type of a column without losing data.
Explain how DCL commands manage user access in a multi-user database.
Write a query using CASE to show "Not Disclosed" if a salary is missing.

Unit 5: Relational Database Design

MCQ Based on Knowledge and Understanding

1.   What does Functional Dependency (FD) represent in a database?
A) A relationship between two tables
B) A constraint between two attributes in a relation
C) A method to store redundant data
D) A normalization rule
2.   In notation X → Y, what does X represent?
A) The dependent attribute                                     B) The primary key
C) The determinant (left-hand side attribute)          D) A foreign key
3.   In a trivial functional dependency, what must be true?
A. Y ⊂ X       B. X ⊂ Y       C. X = Y       D. Y is independent of X
4.   Which of the following is an example of a non-trivial functional dependency?
A) roll_no → roll_no                  B) {roll_no, name} → name
C) roll_no → name           D) name → {roll_no, age}
5.   What type of dependency exists when dependents are not dependent on each other?
A) Transitive          B) Multivalued        C) Partial     D) Derived
6.   If a → b and b → c, then a → c is an example of:
A) Trivial dependency                B) Transitive dependency
C) Multivalued dependency        D) Redundant dependency
7.   Which of the following is not an advantage of functional dependency?
A) Improves database quality    B) Eliminates redundancy
C) Reduces the need for keys     D) Helps in normalization
8.   Normalization primarily aims to:
A) Increase redundancy   B) Reduce redundancy
C) Merge relations           D) Reduce number of tables
9.   The database is in 1NF if:
A) Each attribute holds atomic values            B) Each record can repeat values
C) There are transitive dependencies             D) There are multivalued attributes
10.In 3NF, which dependency must not exist?
A) Partial dependency                B) Transitive dependency
C) Non-trivial dependency         D) Multivalued dependency
11.Which of the following is the correct order of normal forms (from basic to advanced)?
A) 1NF → 2NF → 3NF                B) 2NF → 3NF → 1NF
C) 3NF → 2NF → 1NF                D) 1NF → 3NF → 2NF
12.If a database is not normalized, what can occur during an update operation?
A) Efficient data retrieval           B) Update anomaly
C) Increased data security         D) Elimination of redundancy
13.Which of the following statements is false about normalization?
A) It reduces data redundancy                      B) It removes insertion anomalies
C) It ensures one-to-many relationships        D) It increases data duplication
14.In a normalized database design, foreign keys are used to:
A) Store duplicate data                       B) Link tables and maintain relationships
C) Create transitive dependencies       D) Eliminate primary keys
MCQ Based on Application Test
15.Given roll_no → {name, age} and no dependency between name and age, this represents:
A) Trivial FD           B) Multivalued FD C) Transitive FD     D) Partial FD
16.From the table Emp_ID, Emp_Name, Emp_Address, Emp_Dept, when deletion of a department deletes an employee record too, it’s an example of:
A) Insertion anomaly       B) Update anomaly
C) Deletion anomaly        D) Functional dependency

17.The dependency {Emp_No, Department_No} → Emp_Name in a table shows what about Emp_Name?

A) It depends on a single key                        B) It is partially dependent
C) It depends on the whole composite key     D) It violates 1NF
18.The data below violates which normalization rule?

Emp_ID

Emp_Name

Emp_Mobile

101

Ron

9991112222, 8887776666

A) 1 NF        B) 2 NF        C) 3 NF        D) None
19. When a table is in 2NF, which statement must be true?
A) It is in 1NF and has no partial dependencies
B) It contains atomic values only
C) It has no transitive dependencies
D) It contains repeating groups
20.6. Consider: Emp_No → SalarySlip_No and SalarySlip_No → Salary.
To make this structure in 3NF, what must be done?
A) Merge both into one table               B) Split into Employee and Salary tables
C) Remove the foreign key                  D) Add composite key
21. A company stores multiple phone numbers for each employee in one column. Which normal form is violated?
A) 1NF         B) 2NF         C) 3NF         D) BCNF
22. If Dept_No determines Dept_Name, and Emp_ID determines Dept_No, then Emp_ID → Dept_Name shows:
A. Partial dependency      B. Transitive dependency
C. Trivial dependency       D. Multivalued dependency
23. Which operation ensures removing inconsistent data like multiple addresses for the same employee?
A) Denormalization                             B) Normalization
C) Functional dependency removal      D) Key decomposition
24. Which anomaly occurs if we cannot add an employee without assigning a department?
A) Deletion anomaly        B) Insertion anomaly
C) Update anomaly          D) Functional anomaly
25. Which of the following best describes data redundancy?
A) Same data stored in multiple places          B) Missing data from the database
C) Irrelevant data                                         D) Aggregated data
26. The process of breaking a large table into smaller related tables is known as:
A) Denormalization          B) Normalization
C) Decomposition            D) Aggregation
27. A database design that violates normalization rules may lead to:
A) Improved query speed          B) Anomalies
C) Easier updates                      D) Automatic key generation
28. The purpose of normal forms in DBMS is to:
A) Increase redundancy             B) Simplify design and ensure data consistency
C) Merge all tables into one       D) Increase query complexity

MCQ Base on Higher Ability Test
29.Which of the following relationships indicates transitive functional dependency?
A. roll_no → name, name → age          B. roll_no → name, age → roll_no
C. roll_no → age                                 D. roll_no → roll_no
30.If X → Y and Y ⊂ X, what kind of dependency is it, and what does it imply about redundancy?
A) Trivial; may still exist without redundancy B) Non-trivial; causes redundancy
C) Multivalued; implies duplication                D) Transitive; implies inconsistency
31.A relation violates 2NF if:
A) Some attribute depends on a part of a composite key
B) There is transitive dependency
C) Attributes are not atomic
D) The table has no primary key
32. What would happen if a database is over-normalized?
A) Redundancy increases           B) Query performance may decrease
C) Data duplication rises            D) Update anomalies occur
33. Suppose in a database, Dept → Manager, Manager → Phone. Which normalization rule removes the dependency Dept → Phone?
A) 1NF         B) 2NF         C) 3NF         D) BCNF
34. In a multivalued dependency X →→ Y, which of the following is true?
A) Y depends on X only    B) Y depends on other attributes
C) X depends on Y           D) X and Y are unrelated
35. A table with fields (Emp_ID, Emp_Name, Emp_Dept, Dept_Location) where Emp_Dept → Dept_Location violates which form?
A) 1NF         B) 2NF         C) 3NF         D) BCNF
36.If every non-key attribute depends on the primary key but not transitively, the table is in:
A) 1NF         B) 2NF         C) 3NF         D) 4NF
37.To find candidate keys, which concept must be applied first?
A) Normalization             B) Functional dependency
C) Decomposition            D) Transitive closure
38.Given Emp_ID → Emp_Name and Emp_ID → Emp_Dept, but Emp_Dept → Emp_Location, what is the minimal decomposition to remove transitive dependency?
A) Two tables: Employee(Emp_ID, Emp_Name, Emp_Dept), Department(Emp_Dept, Emp_Location)
B) One table with all fields
C) Merge Emp_Dept and Emp_Location
D) Add Emp_Location to Employee table
39.Which of the following rules defines that every attribute must contain only atomic values?
A) First Normal Form                 B) Second Normal Form
C) Third Normal Form                D) Boyce-Codd Normal Form
40.Which dependency is represented as X → Y and Y ⊂ X?
A) Non-trivial dependency         B) Multivalued dependency
C) Trivial dependency                D) Composite dependency
41. In a relation Employee(Emp_ID, Emp_Name, Emp_Dept, Dept_Location),
if Emp_Dept → Dept_Location, this indicates:
A) Partial dependency                B) Transitive dependency
C) Multivalued dependency        D) Trivial dependency
42. If two employees share the same name but have different roll numbers, which statement is true?
A) name → roll_no           B) roll_no → name
C) name → age               D) name determines department
43. Functional Dependency is a foundation for which database concept?
A) Views      B) Normalization    C) Indexing            D) Triggers
44. Which type of dependency does not violate the second normal form?
A) Partial dependency                B) Trivial dependency
C) Transitive dependency           D) Multivalued dependency
45. If {A, B} → C and A → C, then which rule is violated?
A) Transitivity                  B) Additivity
C) Partial dependency      D) Reflexivity
46.Which of the following anomalies can lead to loss of important information after a delete operation?
A) Update anomaly          B) Insertion anomaly
C) Deletion anomaly        D) Functional dependency anomaly
47.Which of the following dependencies cannot exist if the database is in 3NF?
(A) Emp_ID → Emp_Name                  (B) Dept_No → Dept_Name
(C) Emp_ID → Dept_Name through Dept_No          (D) Dept_Name → Dept_No
48.What does it mean if “A → B” but “B → A” does not hold true?
A) A and B are mutually dependent     B) A uniquely determines B
C) B uniquely determines A                 D) There is redundancy in A
49.Which of the following is not an anomaly type in DBMS?
A) Insertion B) Update    C) Transitive          D) Deletion
50.When we remove transitive dependencies from a 2NF table, we get:
A) 1NF         B) 3NF         C) 2NF         D) BCNF

Emp_ID	Emp_Name	Emp_Mobile
101	Ron	9991112222, 8887776666

ANSWER KEY
1	B	11	A	21	A	31	A	41	A
2	C	12	B	22	B	32	B	42	B
3	A	13	D	23	B	33	C	43	B
4	C	14	B	24	B	34	A	44	B
5	B	15	B	25	A	35	C	45	C
6	B	16	C	26	B	36	C	46	C
7	C	17	C	27	B	37	B	47	C
8	B	18	A	28	B	38	A	48	B
9	A	19	A	29	A	39	A	49	C
10	B	20	B	30	A	40	C	50	B

Short Questions based on Knowledge & Understanding

(These test definitions, concepts, and basic understanding.)

1. Define Functional Dependency in a relational database.

2. What does the symbol X → Y indicate in DBMS?

3. State the condition for a trivial functional dependency to exist.

4. Differentiate between trivial and non-trivial functional dependencies.

5. What is meant by a multivalued functional dependency?

6. Describe transitive functional dependency with reference to attributes A, B, and C.

7. List any two major roles of functional dependency in database design.

8. Define Normalization and mention its primary objective.

9. Identify and name the three types of anomalies that occur in unnormalized data.

10. State any two conditions required for a table to satisfy First Normal Form (1NF).

11. What is meant by the term determinant in a functional dependency?

12. Explain the relationship between attributes and tuples in a relation.

13. Give one example of a trivial and one of a non-trivial dependency.

14. What does the arrow symbol (→) represent in functional dependency notation?

15. Write any two advantages of using normalization in databases.

16. Define what an anomaly means in database design.

17. Mention two objectives of applying normalization rules in a database.

18. What do you understand by the term atomic value?

19. State the two basic rules that make a relation in Second Normal Form (2NF).

20. Write down the condition to be a relation in Third Normal Form (3NF)?

Short Questions based on Application-Based Questions

(These involve using the concept to explain or identify situations.)

21. Given roll_no → name, identify the type of functional dependency and explain why.

22. In the employee table with multiple phone numbers stored in one cell, which normalization rule is being violated?

23. How would you modify a table to remove partial dependency and bring it into Second Normal Form (2NF)?

24. Given that Emp_No → SalarySlip_No and SalarySlip_No → Salary, identify the dependency type and how to make it 3NF compliant.

25. Why is it necessary to separate employee and department information into different tables?

26. In a table where Dept_No → Dept_Name and Emp_ID → Dept_No, what kind of dependency exists between Emp_ID and Dept_Name?

27. Describe how normalization helps to reduce insertion anomalies in a database.

28. A table shows {roll_no, name} → name. Identify the dependency type and explain.

29. In a student table, if Student_ID → {Course, Instructor}, but Course and Instructor are independent, what type of dependency is this?

30. Why does having a composite key sometimes cause partial dependency?

31. Suppose you find that Dept → Manager and Manager → Office_No. Which normal form is violated and why?

32. How does splitting a table into smaller related tables reduce redundancy?

33. Given Emp_ID → Dept_No and Dept_No → Dept_Name, identify and describe the transitive dependency.

34. Write one step you would take to correct a transitive dependency in a database schema.

Short Questions based on Higher Ability / Analytical Questions

(These test reasoning, evaluation, and design-based thinking.)

35. How do functional dependencies help in identifying candidate keys in a relation?

36. Compare the differences between 2NF and 3NF in terms of dependency removal.

37. Analyze how transitive dependencies can lead to redundant data in a database.

38. How does normalization contribute to maintaining data integrity and consistency?

39. Suppose a database is already in 3NF. What additional improvements might still be needed for optimization?

40. Discuss how normalization can prevent update, insert, and delete anomalies from occurring.

41. Why are functional dependencies considered constraints in relational databases?

42. Explain how identifying dependencies can help to find primary keys and foreign keys.

43. Discuss why a well-normalized database improves data consistency.

44. Suggest two possible drawbacks of applying too many normalization levels.

45. Design a simple example to show how a transitive dependency can be removed.

46. Describe how functional dependency assists in detecting poor database design.

47. Evaluate why 3NF is often sufficient for most practical database systems.

48. Explain how functional dependency and normalization work together to prevent data duplication.

Unit 6: Database Transaction

Multiple Choice Questions

MCQ Based on Knowledge & Understanding

(Basic recall and conceptual clarity)

1.   What is a transaction in DBMS?
A. A hardware component          B. A single logical unit of work on a database
C. A backup operation               D. A recovery process

2.   Which of the following is not an ACID property?
A. Atomicity           B. Durability
C. Concurrency      D. Isolation

3.   What does the property of Atomicity ensure?
A. Data can be shared               B. Transaction must be executed fully or not at all
C. Data is permanently lost        D. Multiple users can access at once

4.   The ability of multiple transactions to access and modify the same data simultaneously is called:
A. Concurrency                B. Redundancy
C. Serial Execution          D. Locking

5.   What does a shared lock allow a transaction to do?
A. Write data only            B. Read data only
C. Delete data                 D. Both read and write data

6. What is the purpose of Concurrency Control?
A. To prevent transactions from starting
B. To ensure correct execution of concurrent transactions
C. To improve speed of hardware
D. To merge multiple databases

7.   Which of the following is a concurrency problem?
A. Deadlock           B. Phantom Read
C. Data Mining       D. Serialization

8.   What does Durability mean in ACID properties?
A. The system never fails           B. Data remains saved even after system failure
C. Data can be erased easily      D. The transaction restarts automatically

9.   What is the first state of any transaction?
A. Failed                B. Committed
C. Active                D. Aborted

10.What happens in a partially committed state?
A. Transaction has not started B. Final operations are done, but data not yet saved
C. Transaction is aborted D. Data is deleted from disk

11.What is the main purpose of a transaction in DBMS?
A. To manage database files B. To perform a logical unit of work on data
C. To increase data redundancy D. To create backups

12.Which of the following actions does a transaction perform?
A. Reads and writes data B. Formats the database
C. Deletes the schema D. Updates indexes only

13. The “I” in ACID properties stands for:
A. Interaction B. Isolation
C. Integration D. Integrity

14. What ensures that a transaction’s results are permanently saved?
A. Atomicity B. Durability
C. Consistency D. Locking

15. When does a database achieve consistency?
A. When all transactions are active B. When all data is duplicated
C. When transactions maintain data integrity D. When all locks are released

16. What is concurrency control used for?
A. Allowing multiple users to access data simultaneously
B. Reducing storage capacity
C. Ensuring data is duplicated
D. Restricting database use

17. Which of these is not a concurrency problem?
A. Temporary Update B. Phantom Read
C. Consistent Read D. Lost Update

18.What type of lock allows only reading data?
A. Shared Lock B. Exclusive Lock
C. Write Lock D. Temporary Lock

19.Which mechanism assumes conflicts between transactions are rare?
A. Locking B. Time Stamping
C. Optimistic Concurrency Control D. Rollback

20.What happens during a read operation?
A. Data is brought from disk to main memory B. Data is deleted permanently
C. Data is encrypted D. Data is written to disk

MCQ Based on Application-Based MCQs

(Tests understanding through examples and real use cases)

21.A transaction reads a value from the disk into memory and changes it. What operation is this?
A. Write Operation B. Read Operation
C. Fetch Operation D. Commit Operation

22.If a transaction fails after modifying data in memory but before writing to the disk, which property ensures recovery?
A. Isolation B. Durability
C. Atomicity D. Consistency

23.When two transactions update the same data simultaneously and one overwrites the other’s result, what problem occurs?
A. Temporary Update B. Lost Update
C. Incorrect Summary D. Phantom Read

24. In a banking system, if one transaction is calculating total balance while another updates an account, the total may be wrong. What is this called?
A. Incorrect Summary Problem B. Lost Update
C. Dirty Read D. Phantom Read

25. Which lock type allows both reading and writing the data?
A. Shared Lock B. Exclusive Lock
C. Parallel Lock D. Hard Lock

26. The timestamp used in concurrency control is mainly used to:
A. Identify the start time of a transaction B. Record disk usage

C. Store backup copies D. Count number of users

27. In optimistic concurrency control, the system assumes that:
A. Conflicts are common B. Conflicts are rare
C. Transactions always fail D. Locks are never needed

28. If a transaction performs all its operations but an error occurs before saving data, it moves to:
A. Committed state B. Failed state C. Active state D. Terminated state

29. A transaction that successfully completes all operations and commits will be in which state?
A. Failed B. Aborted C. Committed D. Active

30.When a transaction ends and the database is ready for a new one, it enters:
A. Partially Committed B. Terminated State
C. Aborted State D. Restarted State

31.A user transfers money between two accounts. The process must complete entirely or not at all. Which ACID property applies?
A. Consistency B. Atomicity C. Durability D. Isolation

32.Two transactions access the same record — one reads while the other writes — without control. What issue can occur?
A. Phantom Read B. Lost Update C. Temporary Update D. Committed State

33. If the system crashes after committing data but before writing to disk, which property ensures that data is eventually stored?
A. Durability B. Consistency C. Isolation D. Atomicity

34. A transaction updates a value, but another transaction reads the old value before the first commits. What issue is this?

A. Incorrect Summary B. Temporary Update

C. Phantom Read D. Lost Update

35. What should happen if a transaction fails during execution?
A. Continue running B. Restart or Rollback
C. Stay active D. Delete the table

36. Which concurrency problem causes different results when a query is run multiple times within the same transaction?
A. Lost Update B. Unrepeatable Read
C. Phantom Read D. Temporary Update

37. A transaction has finished its operations but not yet saved data. What state is it in?
A. Partially Committed B. Committed
C. Aborted D. Failed

38.In a sales database, two customers try to buy the last item at once. What control technique should prevent data conflict?
A. Timestamp B. Locking C. Normalization D. Indexing

39.When a transaction is completed successfully, what must happen next?
A. It moves to Aborted state B. It enters the Terminated state
C. It restarts D. It goes back to Active state

40.What concurrency control technique assigns a unique ID based on transaction start time?
A. Time Stamping B. Optimistic Control C. Locking D. Replication

MCQ Based Higher Ability / Analytical MCQs

(Tests reasoning, interrelation, and problem-solving skills)

41.If a transaction violates the atomicity property, what may happen?
A. Data may remain partially updated B. Data will be perfectly stored
C. Concurrency increases D. Speed of transaction improves

42.When multiple users update the same record without control, what is compromised?
A. Durability B. Isolation C. Consistency D. Redundancy

43.Which ACID property ensures that only valid data is written to the database?
A. Consistency B. Isolation C. Atomicity D. Durability

44. After a system crash, data remains safe due to which property?
A. Atomicity B. Durability C. Consistency D. Isolation

45. If one transaction affects another’s intermediate data, which ACID property is violated?
A. Isolation B. Atomicity C. Durability D. Consistency

46. When a transaction fails but the database returns to its original consistent state, which property is maintained?
A. Atomicity B. Isolation C. Durability D. Timestamp

47.In concurrency, which problem occurs when a transaction reads different values of the same row at different times?
A. Unrepeatable Read B. Lost Update
C. Incorrect Summary D. Phantom Read

48. Phantom Read occurs when:
A. New rows appear during a repeated read B. Deleted rows reappear
C. Data is locked D. Rows disappear permanently

49. What is the purpose of the recovery system during a failed transaction?
A. To delete the failed transaction B. To restore the database to a consistent state
C. To lock all transactions D. To restart the DBMS

50. Which of the following statements best describes committed state?
A. The transaction is terminated before execution
B. All changes are saved permanently to the database
C. Data is temporarily stored in memory
D. The transaction is rolled back

51. If transactions are executed concurrently without control, what database property is most likely at risk?
A. Redundancy B. Consistency C. Durability D. Atomicity

52. A system uses both shared and exclusive locks. What is the main advantage?
A. Transactions run faster B. Data integrity is maintained
C. It disables concurrency D. It prevents all access

53. When a transaction reads some rows, and later finds additional rows that match the condition, it’s known as:
A. Phantom Read B. Lost Update

C. Unrepeatable Read D. Temporary Update

54. Which ACID property prevents one transaction from seeing uncommitted changes made by another?
A. Durability B. Atomicity C. Isolation D. Consistency

55. If the database returns to its previous consistent state after rollback, which property is being enforced?
A. Atomicity B. Isolation C. Durability D. Timestamp

56. In which situation would the use of Optimistic Control be preferred?
A. When transactions frequently conflict B. When conflicts are rare
C. When data changes continuously D. When there are few users

57. Which of the following correctly matches transaction states and outcomes?
A. Failed → Restarted immediately B. Committed → Data permanently saved
C. Aborted → Data kept in memory D. Active → Database restored

58. If a transaction modifies the same record multiple times before committing, which control ensures the database remains stable?
A. Concurrency Control B. Redundancy Elimination
C. Normalization D. Index Management

59. Why is isolation necessary when multiple transactions execute together?
A. To reduce disk space B. To ensure results are independent of other transactions
C. To duplicate results D. To speed up the process

60. Which statement correctly explains Aborted State?
A. The transaction is successful
B. The transaction is rolled back to maintain consistency
C. The transaction is active
D. The transaction data is saved permanently

ANSWER KEY
1	B	11	B	21	A	31	B	41	A	51	B
2	C	12	A	22	B	32	B	42	B	52	B
3	B	13	B	23	B	33	A	43	A	53	A
4	A	14	B	24	A	34	B	44	B	54	C
5	B	15	C	25	B	35	B	45	A	55	A
6	B	16	A	26	A	36	B	46	A	56	B
7	B	17	C	27	B	37	A	47	A	57	B
8	B	18	A	28	B	38	B	48	A	58	A
9	C	19	C	29	C	39	B	49	B	59	B
10	B	20	A	30	B	40	A	50	B	60	B

Short Questions Based Knowledge & Understanding

(Tests recall and conceptual understanding)

1. What is a transaction in a database management system (DBMS)?

2. List the four ACID properties of a transaction.

3. What is meant by the “Atomicity” property in transactions?

4. Define “Consistency” in the context of ACID properties.

5. What does the “Isolation” property of a transaction ensure?

6. Explain the term “Durability” in database transactions.

7. What are the two basic operations that a transaction performs on data?

8. What is concurrency in transactions?

9. List any three common problems that occur when transactions run concurrently.

10. Name and briefly describe two types of locks used in concurrency control.

11. Define a “transaction” in a DBMS.

12. What is the primary purpose of a transaction in a database?

13. List the four ACID properties and give one-line meaning for each.

14. What is the role of a transaction in maintaining database consistency?

15. Explain what a “Read operation” in a transaction does.

16. Explain what a “Write operation” in a transaction does.

17. Define concurrency in database transactions.

18. Name five concurrency problems that can occur in a DBMS.

19. What is the difference between a shared lock and an exclusive lock?

20. Define the “Active state” of a transaction.

Short Questions Based Application-Based Short Questions

(Applies theory to real-world or practical database situations)

21. When two users update the same record at the same time, which concurrency problem may occur?

22. How does the shared lock mechanism help when multiple users try to read the same data?

23. What does the timestamp mechanism record, and how does it help manage concurrent transactions?

24. A student database allows two updates at once. What technique can prevent conflicts in such cases?

25. Explain the difference between a “Committed” state and a “Partially Committed” state of a transaction.

26. What happens when a transaction reaches the “Failed” state?

27. Describe a situation in which “Optimistic Concurrency Control” would be preferred over locking.

28. When a transaction successfully completes all operations and saves the data, which state does it move to?

29. If a transaction fails in the middle of execution, which state does it move to?

30. A transaction successfully completes and writes data to the database. What state is it now in?

31. How does locking prevent multiple transactions from updating the same data simultaneously?

32. A transaction updates a bank account but the system crashes before saving. Which ACID property ensures the update is not lost?

33. When multiple transactions read the same data without interference, which concurrency control technique is used?

34. What is the difference between “Partially Committed” and “Committed” states?

35. How can optimistic concurrency control reduce delays compared to locking?

36. A transaction aborts due to an error. How does the DBMS restore consistency?

Short Questions Based Higher Ability / Analytical Short Questions

(Tests reasoning, comparison, and deeper conceptual understanding)

37. Why is it important for a transaction to follow all four ACID properties together and not just one?

38. Compare the “Lost Update” problem and the “Unrepeatable Read” problem in concurrency.

39. Explain how the database ensures consistency after a transaction fails midway.

40. In what way does “Durability” support “Consistency” in maintaining data integrity?

41. Why might a DBMS use time stamping instead of locking in some environments?

42. Suggest two ways to minimize concurrency-related problems in a database system.

43. Explain how isolation prevents one transaction from affecting another.

44. Compare the effects of a “Lost Update” and a “Phantom Read” on a database.

45. How does durability ensure reliability even after a system crash?

46. A transaction reads inconsistent data due to concurrent updates. Which ACID property is violated?

47. Why might a DBMS choose timestamps over locks in certain high-transaction environments?

48. Explain why ACID properties are interdependent rather than independent.

Long Questions Based on Knowledge & Understanding

49. Define a transaction in a database and explain its significance.

50. What are ACID properties? Explain each property with examples.

51. Describe the difference between Read and Write operations in a transaction.

52. What is concurrency in database transactions? Why is it important?

53. List and briefly explain the five concurrency problems in a DBMS.

54. Explain the role of locks in concurrency control and describe the types of locks.

55. Define the concept of transaction states and explain the Active state.

56. What is the difference between Partially Committed, Committed, and Aborted states?

57. Explain the importance of durability in a database transaction.

58. What is meant by a “logical unit of work” in a database transaction?

59. Explain how a transaction contributes to database consistency.

60. Describe the difference between a transaction and a single database operation.

61. Define concurrency control and explain its necessity in a DBMS.

62. What are the main functions of a DBMS recovery system during transaction failure?

63. Explain the difference between Active and Failed states of a transaction.

64. How does the Committed state differ from the Terminated state?

65. Define Time Stamping in transaction management.

66. What is the role of an optimistic concurrency control method?

67. Explain what is meant by a “phantom read” in concurrent transactions.

Long Questions Based Application-Based Questions

68. A bank transaction debits an account but the system crashes before the balance updates. Which ACID property ensures consistency, and how?

69. Give an example of a Lost Update problem and explain how it can be prevented.

70. A transaction fails during execution. Describe the steps the DBMS will take to maintain database consistency.

71. Illustrate how timestamp-based concurrency control resolves conflicts in concurrent transactions.

72. A transaction reads and writes multiple records. How do ACID properties ensure these operations are reliable?

73. Explain how optimistic concurrency control allows transactions to proceed without locking.

74. How does the DBMS move a transaction from Partially Committed to Committed state in practice?

75. A transaction updates a customer’s record and crashes before saving. How should the DBMS handle it?

76. A transaction reads data that another transaction is currently updating. Which concurrency problem could occur?

77. How would you apply a shared lock to prevent inconsistent reads?

78. A transaction completes its final write operation but the database hasn’t saved it. What is the current state of the transaction?

79. Explain how timestamping resolves conflicts when two transactions start at the same time.

80. Describe a situation where an Aborted transaction may be restarted successfully.

Long Questions Based on Higher Ability / Analytical Questions

81. Analyze why a transaction must be atomic to avoid leaving the database in an inconsistent state.

82. Compare the effects of Lost Update and Phantom Read problems on database integrity.

83. How does isolation property ensure that concurrent transactions do not interfere with each other?

84. Discuss why durability is critical in a database system, giving an example of system failure.

85. A system allows multiple transactions to execute simultaneously. Analyze how ACID properties work together to maintain consistency.

86. Explain how a partially committed transaction can lead to errors if recovery mechanisms fail.

87. Why is concurrency control essential in high-transaction database environments? Discuss with examples.

88. Critically examine how transaction states ensure reliable execution from start to termination.

89. Analyze why Read and Write operations must be carefully controlled in concurrent transactions.

90. Compare Locking and Optimistic methods for concurrency control in terms of efficiency and reliability.

91. Discuss how ACID properties collectively prevent data anomalies in a multi-user environment.

92. Evaluate the advantages and disadvantages of Shared vs Exclusive locks.

93. Analyze how a transaction can move from Active → Partially Committed → Committed without violating consistency.

94. Critically examine how Time Stamping ensures serializability in concurrent transactions.

Unit 7: Database Backup, Recovery and Security

Multiple Choice Questions

MCQ Based on Knowledge & Understanding

What is the main purpose of a backup in databases?
a) To delete old data b) To copy data for recovery
c) To increase database speed d) To change data format
Which backup type copies all the data and metadata in a database?
a) Differential Backup b) Full Backup
c) Incremental Backup d) Transaction Log Backup
Which backup takes the least storage space?
a) Full Backup b) Differential Backup
c) Incremental Backup d) Copy-Only Backup
What does a transaction log backup include?
a) Only user data b) Entire database
c) Record of transactions d) No data
Logical backups typically do NOT include:
a) Tables b) Database files c) Schemas d) Procedures
Which type of failure involves unintended data destruction by users?
a) System Crash b) User Error c) Media Failure d) Network Failure
What kind of backup is suggested for quick restoration with minimal downtime?
a) Incremental Backup b) Physical Backup
c) Logical Backup d) Full Backup
Which disaster could cause natural physical damage to data storage?
a) User Error b) Network Failure c) Fire or Flood d) SQL Injection Attack
What recovery technique re-applies changes of completed transactions?
a) Undo/rollback b) Checkpoint recovery
c) Commit/redo d) Backup recovery
What is an important objective of database security?
a) Improve data speed b) Protect data integrity
c) Delete old data d) Create backups only

MCQ Based on Application

11.If you want to save only data changed since last full backup, which method to use?
a) Full Backup b) Differential Backup
c) Incremental Backup d) Snapshot Backup

For point-in-time recovery, which backup type would you restore from?
a) Full backup b) Transaction Log Backup
c) Copy-Only Backup d) Physical Backup
You want frequent backups but minimal storage usage, which backup is best?
a) Full Backup b) Differential Backup
c) Incremental Backup d) Logical Backup
When performing full physical backups, when should you ideally take them?
a) During peak hours b) During database downtime
c) Any random time d) When server is online
If an application program fails an SQL statement, what recovery can be used?
a) Manual restore b) Undo/rollback recovery
c) Full backup recovery d) Incremental backup recovery
Which security step can stop unauthorized database access?
a) Regular backups b) Strong multifactor authentication
c) Load testing d) Encryption
If you want a backup that does not affect regular backup chains, which one to use?
a) Transaction Log Backup b) Copy-Only Backup
c) Snapshot Backup d) Differential Backup
Which failure could cause loss of volatile storage contents?
a) Natural disaster b) Network failure
c) System crash d) Media failure
How can a DBA best protect against media failure?
a) Avoid backups b) Regular backups with redundancy
c) Ignoring disk changes d) Frequent updates to SQL version
To reduce database recovery time, which technique is used?
a) Incremental backups b) Rollback recovery
c) Checkpoint recovery d) Snapshots

MCQ Based on Higher Ability

21.Why is the rollback/undo recovery technique critical in transaction management?
a) To redo completed transactions b) To revert incomplete or failed transactions
c) To speed up backups d) To create backup copies

How do differential backups compare to incremental in restoration speed?
a) Faster b) Slower c) Same d) Not applicable
What is a key disadvantage of incremental backups when it comes to restoring data?
a) Lots of storage space needed
b) Slow restoration times due to multiple backup files
c) Risk of data duplication d) Requires full downtime
Which backup method is least affected by high database write activity?
a) Full Backup b) Snapshot Backup
c) Physical Backup d) Logical Backup
What is the role of the database administrator in security?
a) Build hardware b) Define, implement, and monitor security policies
c) Write application software d) Manage database queries
Which backup type provides a complete snapshot without interrupting regular backups?
a) Copy-Only Backup b) Full Backup
c) Differential Backup d) Transaction Log Backup
When should a logical backup be preferred?
a) When migrating databases between platforms
b) For quick restoration to a point in time
c) To recover media failure
d) To replace full physical backups entirely
How do checkpoint files help in recovery?
a) They log all transactions b) They store latest database state to speed recovery
c) They backup physical files only d) They prevent data corruption automatically
What is one common cause of database failure from internal sources?
a) System crashes b) Sabotage by insider threats
c) Fire or flood d) Hardware failure
Why is encryption important in database security?
a) To keep data readable b) To protect data confidentiality and integrity
c) To speed up database operations d) For backup copy creation

ANSWER KEY
1	B	11	B	21	B
2	B	12	B	22	A
3	C	13	C	23	B
4	C	14	B	24	B
5	B	15	B	25	B
6	B	16	B	26	A
7	B	17	B	27	A
8	C	18	C	28	B
9	C	19	B	29	B
10	B	20	C	30	B

Short Questions based on Knowledge and Understanding

What is a database backup?
Why are backups essential for businesses?
Name the backup type that copies all data including metadata.
What does a differential backup store?
List one advantage of incremental backups.
Which backup records every transaction made in a database?
What is physical backup in databases?
How often is a full physical backup recommended?
Define logical backup in databases.
What could cause system crashes in a database?

Short Questions based on Application

11.Which backup type should be used for quick data restore from recent changes?

When is an incremental physical backup preferable?
If a user accidentally deletes a row, what is the recommended recovery action?
How can transaction log backups help in recovery?
Suggest a backup method suitable for high-write activity databases.
What should be done before running full physical backups?
How can a DBA protect against media failure?
What role does network failure play in database availability?
Which backup type would you use to migrate databases across environments?
What is the purpose of checkpoint recovery in DBMS?

Short Questions based on Higher Ability

21.Explain the principle behind rollback/undo recovery technique.

How does commit/redo recovery maintain database consistency?
Compare the storage needs of full, differential, and incremental backups.
What are the risks of relying solely on full backups?
Why is logical backup not suitable for OS-level restoration?
Discuss the importance of multifactor authentication in database security.
What are insider threats and how can they affect databases?
How does data encryption contribute to database security?
Describe the potential impact of denial of service (DoS) attacks on databases.
Why is regular vulnerability assessment necessary in database security?

Long Questions based on Knowledge and Understanding

Explain the concept of a backup and why it is critical for any organization.
Describe the main differences between full, differential, and incremental backups.
What is a transaction log backup and how does it aid in database recovery?
Define physical backup and logical backup, and explain in which scenarios each is most useful.
What are some typical causes of database failure that necessitate backup and recovery planning?
Discuss the advantages and disadvantages of using a full backup method.
How does a snapshot backup differ from other types of backups?
Explain the importance of storing backups in separate, dedicated storage locations.
What are the roles of transaction logs in maintaining database integrity?
Describe the concept of checkpoint recovery and its benefits in the recovery process.

Long Questions based on Application

11.Describe the steps you would take to perform a differential backup on a live database.

How would you decide whether to use incremental or full backups for a database with heavy daily changes?
Explain how you would recover a database after a user accidentally deletes important data.
Describe how you would configure transaction log backups to allow point-in-time recovery.
What considerations would you keep in mind when scheduling full physical backups for an active system?
How can incrementally backing up recent transactions help minimize downtime during backup operations?
Explain the process and tools you would use to move a database backup to a different platform using logical backups.
What recovery technique would you apply to correct a failed SQL statement during a transaction?
Describe the impact of network failure on a distributed database system and how backups aid in recovery.
How would you ensure that a backup strategy protects against catastrophic events like natural disasters?

Long Questions based on Higher Ability

21.Analyze the rollback/undo and commit/redo recovery techniques and explain how they complement each other in transactional recovery.

Evaluate the risks and benefits of relying solely on incremental backups versus a mixed backup strategy.
Discuss how database security measures, such as multifactor authentication and encryption, help prevent backup data breaches.
Propose a backup and recovery plan for a database system prone to frequent hardware and software failures.
Critically assess the role of a database administrator in planning and maintaining backup and recovery operations.
Compare the recovery speed and storage efficiency trade-offs among full, differential, and incremental backups in different database environments.
Analyze the security threats posed by SQL injection attacks and insider threats, and suggest preventive backup and security measures.
Discuss how the choice of backup type affects business continuity in case of media failure or data corruption.
Examine the implications of weak authentication mechanisms on both database security and backup integrity.
Develop a comprehensive review plan for regularly testing and updating backup strategies to handle emerging threats and ensure data availability.

Search This Blog

Shree Secondary School

Model Questions for practice in DBMS

Comments

Post a Comment

Popular posts from this blog

CHERM Model Question

Model Questions for practice in CHERM Grade 10 Technical Stream

Tutorial Notes on Website Design