1What is the primary purpose of a Database Management System (DBMS)?
purpose of database systems
Easy
A.To provide a graphical interface for operating systems
B.To store, retrieve, and manage data efficiently and securely
C.To connect computer networks together
D.To compile programming code into machine language
Correct Answer: To store, retrieve, and manage data efficiently and securely
Explanation:
A DBMS is software designed to store, retrieve, define, and manage data in a database efficiently, ensuring data integrity and security.
Incorrect! Try again.
2Which of the following problems does a DBMS solve compared to a traditional file processing system?
purpose of database systems
Easy
A.Slow internet connection speeds
B.Operating system crashes
C.Data redundancy and inconsistency
D.High hardware costs
Correct Answer: Data redundancy and inconsistency
Explanation:
Traditional file systems often duplicate data across multiple files (redundancy), which can lead to conflicting data (inconsistency). A DBMS centralizes data to solve these issues.
Incorrect! Try again.
3Which component of a DBMS is responsible for parsing, translating, and optimizing user requests into a form that the system can understand?
components of dbms
Easy
A.Storage Manager
B.Transaction Manager
C.Query Processor
D.Buffer Manager
Correct Answer: Query Processor
Explanation:
The Query Processor interprets user queries (like SQL), optimizes them, and translates them into low-level instructions for the database engine to execute.
Incorrect! Try again.
4What is the role of the Database Administrator (DBA) as a human component of the DBMS environment?
components of dbms
Easy
A.To design the operating system the database runs on
B.To purely enter daily transaction data into forms
C.To write application programs for end-users
D.To manage the overall structure, security, and performance of the database
Correct Answer: To manage the overall structure, security, and performance of the database
Explanation:
The DBA is responsible for maintaining the database system, including schema design, security, access authorization, and performance tuning.
Incorrect! Try again.
5In which of the following areas is a DBMS most commonly used?
applications of dbms
Easy
A.Word processing software to format text
B.Banking systems for managing customer accounts and transactions
C.Compiling C++ programs
D.Image editing software to apply filters
Correct Answer: Banking systems for managing customer accounts and transactions
Explanation:
Banking systems rely heavily on DBMS to safely store and manage massive amounts of structured data regarding customer accounts, balances, and transactions.
Incorrect! Try again.
6Why do airlines heavily rely on Database Management Systems?
applications of dbms
Easy
A.To pilot the airplane automatically
B.To manage flight schedules, ticket reservations, and passenger information
C.To serve in-flight entertainment videos
D.To design the aerodynamics of the airplane
Correct Answer: To manage flight schedules, ticket reservations, and passenger information
Explanation:
Airlines use databases to handle massive, concurrent transactions related to reservations, passenger details, and scheduling.
Incorrect! Try again.
7In a three-tier DBMS architecture, which tier acts as the intermediary between the end-user and the database?
three tier dbms architecture
Easy
A.Database tier (Data)
B.Application tier (Business Logic)
C.Presentation tier (Client)
D.Network tier
Correct Answer: Application tier (Business Logic)
Explanation:
The Application tier sits between the Client (Presentation) tier and the Database tier. It processes the business logic and translates user actions into database queries.
Incorrect! Try again.
8Which tier in the three-tier architecture is responsible for providing the graphical user interface (GUI) to the end user?
three tier dbms architecture
Easy
A.Database tier
B.Application tier
C.Presentation tier
D.Storage tier
Correct Answer: Presentation tier
Explanation:
The Presentation (or Client) tier is the topmost level of the application, responsible for displaying information to and collecting input from the user.
Incorrect! Try again.
9What does the concept of 'Data Independence' mean in a DBMS?
data independence
Easy
A.The database works independently without needing a computer.
B.Data is stored without any relationships to other data.
C.The ability to modify a schema at one level without changing the schema at the next higher level.
D.Users can independently delete any data they want.
Correct Answer: The ability to modify a schema at one level without changing the schema at the next higher level.
Explanation:
Data independence allows structural changes to the database at a lower level (like physical storage or logical design) without affecting the higher levels (like application programs or user views).
Incorrect! Try again.
10Which type of data independence allows you to change the conceptual (logical) schema without having to modify external views or application programs?
data independence
Easy
A.Platform data independence
B.Logical data independence
C.Hardware data independence
D.Physical data independence
Correct Answer: Logical data independence
Explanation:
Logical data independence shields the external level (user views/apps) from changes made at the logical level (conceptual schema), such as adding a new column to a table.
Incorrect! Try again.
11What is a database schema?
database schema
Easy
A.The actual data stored in the database at a specific moment
B.The user manual for the DBMS software
C.The logical structure and overall design of the database
D.The hardware used to store the database
Correct Answer: The logical structure and overall design of the database
Explanation:
A database schema is the skeleton structure that represents the logical view of the entire database. It defines how the data is organized and how relationships are associated.
Incorrect! Try again.
12Which of the following statements about a database schema is true?
database schema
Easy
A.It changes every time a user inserts new data.
B.It only contains the physical file paths of the data.
C.It is relatively static and rarely changes once the database is operational.
D.It is automatically generated by the operating system.
Correct Answer: It is relatively static and rarely changes once the database is operational.
Explanation:
The schema defines the structure (like tables and columns). It is designed before data is added and changes infrequently, unlike the actual data which changes constantly.
Incorrect! Try again.
13What is a database instance?
instance
Easy
A.A specific brand of DBMS software
B.The actual content or data stored in the database at a specific moment in time
C.The blueprint or design of the database
D.A physical server hosting the database
Correct Answer: The actual content or data stored in the database at a specific moment in time
Explanation:
While a schema is the structure, an instance is a snapshot of the data contained within that structure at a given point in time.
Incorrect! Try again.
14How does a database instance differ from a database schema?
instance
Easy
A.There is no difference; they are synonymous terms.
B.The instance changes frequently as data is updated, while the schema remains relatively static.
C.The instance represents the structure, while the schema holds the data.
D.The schema is hardware, and the instance is software.
Correct Answer: The instance changes frequently as data is updated, while the schema remains relatively static.
Explanation:
Because an instance reflects the current data in the database, it changes with every insert, update, or delete operation. The schema (structure) does not.
Incorrect! Try again.
15What is the primary purpose of data modeling in database design?
data modeling
Easy
A.To compress data so it takes up less physical storage
B.To create a conceptual representation of data, defining its structures and relationships
C.To write front-end code for the database GUI
D.To encrypt sensitive data automatically
Correct Answer: To create a conceptual representation of data, defining its structures and relationships
Explanation:
Data modeling is the process of creating a visual or conceptual representation of an information system to communicate connections between data points and structures.
Incorrect! Try again.
16Which of the following is a popular high-level conceptual data model used in database design?
data modeling
Easy
A.The OSI Model
B.The TCP/IP Model
C.The Client-Server Model
D.The Entity-Relationship (ER) Model
Correct Answer: The Entity-Relationship (ER) Model
Explanation:
The Entity-Relationship (ER) Model is a widely used conceptual data model that uses entities and relationships to design databases.
Incorrect! Try again.
17In an Entity-Relationship (ER) model, what does an 'entity' represent?
entity relationship model
Easy
A.A real-world object or concept that has an independent existence
B.An action performed by the database administrator
C.The physical hard drive storing the data
D.A specific type of data query
Correct Answer: A real-world object or concept that has an independent existence
Explanation:
An entity is a recognizable object, concept, or thing (like a Person, Order, or Product) about which data is collected in the database.
Incorrect! Try again.
18What standard geometric shape is used to represent an entity in an Entity-Relationship (ER) diagram?
entity relationship model
Easy
A.Diamond
B.Rectangle
C.Oval
D.Triangle
Correct Answer: Rectangle
Explanation:
In standard ER diagrams, entities are represented by rectangles, attributes by ovals, and relationships by diamonds.
Incorrect! Try again.
19How is data fundamentally organized and represented in a relational model?
relational model
Easy
A.As interconnected graphs and edges
B.As isolated text documents
C.As tables (relations) consisting of rows and columns
D.As a hierarchical tree of nodes
Correct Answer: As tables (relations) consisting of rows and columns
Explanation:
The relational model organizes data into tables (relations). Each table consists of columns (attributes) and rows (tuples).
Incorrect! Try again.
20Which of the following is a key difference between relational and non-relational (NoSQL) databases?
Comparison of relational and non-relational databases
Easy
A.Non-relational databases require SQL for querying, while relational databases do not.
B.Relational databases use flexible data structures like documents, while non-relational databases use rigid tables.
C.Relational databases organize data into rigid tables, while non-relational databases use flexible data models like documents or key-value pairs.
D.Relational databases cannot handle transactions, while non-relational databases can.
Correct Answer: Relational databases organize data into rigid tables, while non-relational databases use flexible data models like documents or key-value pairs.
Explanation:
Relational databases enforce a strict tabular schema, whereas non-relational (NoSQL) databases offer flexible schemas using formats like JSON documents, key-value stores, or graphs.
Incorrect! Try again.
21A company previously used file systems for payroll. They faced issues where updating an employee's address in the payroll file did not update it in the HR file. Which primary purpose of a DBMS directly solves this issue?
purpose of database systems
Medium
A.Enforcing atomicity of transactions
B.Providing data abstraction
C.Eliminating data redundancy and inconsistency
D.Ensuring concurrent access
Correct Answer: Eliminating data redundancy and inconsistency
Explanation:
In file systems, data is often duplicated across multiple files (redundancy), leading to situations where updates are missed in some files (inconsistency). A DBMS centralizes data, eliminating redundant copies and ensuring updates are consistent across the system.
Incorrect! Try again.
22During a sudden power outage, a database system shuts down unexpectedly. Upon restart, it recovers its previous consistent state. Which component of the DBMS architecture is primarily responsible for ensuring this?
components of dbms
Medium
A.DDL Compiler
B.Query Optimizer
C.Buffer Manager
D.Transaction Manager
Correct Answer: Transaction Manager
Explanation:
The Transaction Manager (often working with a recovery manager) ensures that transactions exhibit ACID properties. It is responsible for rolling back incomplete transactions and committing completed ones after a system crash, ensuring consistency.
Incorrect! Try again.
23A database administrator (DBA) wants to restrict a newly hired junior developer from viewing the salary details in the Employees table. Which DBMS component handles this authorization check when the developer runs a query?
components of dbms
Medium
A.Security and Authorization Subsystem
B.Query Processor
C.DML Precompiler
D.Storage Manager
Correct Answer: Security and Authorization Subsystem
Explanation:
The Security and Authorization Subsystem verifies user credentials and checks access privileges before allowing a query to be executed on specific tables or columns.
Incorrect! Try again.
24In a banking application, an account transfer requires deducting money from one account and adding it to another. The system must guarantee that neither operation fails independently to prevent money loss. Which feature of a DBMS makes it essential for this application?
applications of dbms
Medium
A.Support for ACID properties
B.High-level query language support
C.Schema definition and abstraction
D.Physical data independence
Correct Answer: Support for ACID properties
Explanation:
ACID properties (specifically Atomicity) ensure that a sequence of database operations is treated as a single, indivisible unit. If part of the transaction fails, the entire transaction is rolled back, which is critical for banking applications.
Incorrect! Try again.
25In a web-based retail system, users interact with a browser, which communicates with a web application server handling business logic, which in turn queries the backend database. This is a classic example of which architecture?
three tier dbms architecture
Medium
A.Three-tier architecture
B.Two-tier architecture
C.Peer-to-peer architecture
D.Single-tier architecture
Correct Answer: Three-tier architecture
Explanation:
A three-tier architecture separates the presentation layer (client/browser), the application layer (business logic server), and the data layer (database server). This improves scalability, security, and maintenance.
Incorrect! Try again.
26What is the primary advantage of using a three-tier DBMS architecture over a two-tier architecture in a large-scale web application?
three tier dbms architecture
Medium
A.Better security and scalability by isolating business logic
B.Faster direct database access for the client application
C.Elimination of the need for a dedicated database server
D.Reduction in the number of network layers
Correct Answer: Better security and scalability by isolating business logic
Explanation:
By adding an intermediate application server, business rules are hidden from the client, preventing direct database access. This intermediate layer allows for connection pooling, better load balancing (scalability), and enhanced security.
Incorrect! Try again.
27An administrator moves a database from a traditional magnetic hard drive (HDD) to a Solid State Drive (SSD) to improve read/write performance. Neither the database tables nor the application programs require any modifications. This scenario demonstrates:
data independence
Medium
A.Logical data independence
B.Conceptual data independence
C.External schema independence
D.Physical data independence
Correct Answer: Physical data independence
Explanation:
Physical data independence is the ability to modify the physical storage structures (like moving from HDD to SSD or changing file organizations) without altering the logical schema or application programs.
Incorrect! Try again.
28Adding a new attribute like Date_of_Birth to the Employee table does not break existing applications that only query the Name and Salary columns. This is an example of:
data independence
Medium
A.Physical data independence
B.View data independence
C.Internal data independence
D.Logical data independence
Correct Answer: Logical data independence
Explanation:
Logical data independence allows changes to the conceptual schema (like adding new tables or columns) without requiring changes to existing external views or application programs that do not rely on the new data.
Incorrect! Try again.
29Why is logical data independence generally considered harder to achieve than physical data independence?
data independence
Medium
A.Because application programs are heavily dependent on the logical structure of the data they access
B.Because physical storage structures cannot be modified once created
C.Because the external level is directly connected to the internal level
Correct Answer: Because application programs are heavily dependent on the logical structure of the data they access
Explanation:
Application programs are built based on the logical structure (tables, relationships). If the underlying logical structure changes fundamentally (e.g., splitting a table in a way views cannot fully abstract), the application logic must often be rewritten.
Incorrect! Try again.
30A university database defines tables for Students, Courses, and Enrollments, specifying their data types, constraints, and relationships. This structural definition corresponds to which level of the database architecture?
database schema
Medium
A.Physical schema
B.Internal schema
C.External schema
D.Conceptual schema
Correct Answer: Conceptual schema
Explanation:
The conceptual (or logical) schema describes the overall structure of the entire database for a community of users, including entities, data types, relationships, and constraints, hiding details of physical storage.
Incorrect! Try again.
31When a database designer configures how a specific table is stored on a disk using B+ trees and defines data allocation policies, they are working at the level of the:
database schema
Medium
A.Internal schema
B.Conceptual schema
C.External schema
D.Logical schema
Correct Answer: Internal schema
Explanation:
The internal schema (or physical schema) describes the physical storage structure of the database, determining how data is actually stored on storage media, including access paths like B+ trees.
Incorrect! Try again.
32While a database schema is defined during the design phase and rarely changes, a database instance is highly dynamic. Which of the following best defines a database instance?
instance
Medium
A.A specific user's view of a portion of the database
B.The overall structural design of the database
C.The collection of data stored in the database at a specific moment in time
D.The physical storage blueprint on the hard drive
Correct Answer: The collection of data stored in the database at a specific moment in time
Explanation:
An instance (or state) of a database is the actual content or data stored in the database at a particular point in time. It changes every time data is inserted, updated, or deleted.
Incorrect! Try again.
33If an application executes a DELETE query that successfully removes 50 rows from an Orders table, which of the following is modified?
instance
Medium
A.The logical data independence
B.The database schema
C.The conceptual view
D.The database instance
Correct Answer: The database instance
Explanation:
Executing DML operations like DELETE, INSERT, or UPDATE modifies the data within the database. This changes the current state of the data, which is known as the database instance, without affecting the schema.
Incorrect! Try again.
34Which of the following scenarios best demonstrates the use of a conceptual data model?
data modeling
Medium
A.Defining clustered and non-clustered indexes to optimize query speed
B.Creating an Entity-Relationship (ER) diagram to discuss business requirements with non-technical stakeholders
C.Configuring a NoSQL document database to store JSON files
D.Writing SQL CREATE TABLE statements with primary and foreign keys
Correct Answer: Creating an Entity-Relationship (ER) diagram to discuss business requirements with non-technical stakeholders
Explanation:
A conceptual data model is a high-level representation (like an ER diagram) used to define business concepts and rules. It abstracts away technical details, making it ideal for communication between designers and stakeholders.
Incorrect! Try again.
35In a hospital's ER diagram, a Patient can have multiple Medical_Records, but a single Medical_Record belongs to exactly one Patient. What type of mapping cardinality does this describe?
entity relationship model
Medium
A.Many-to-many
B.One-to-one
C.One-to-many
D.Many-to-one
Correct Answer: One-to-many
Explanation:
Because one entity (Patient) relates to multiple instances of another entity (Medical_Records), and each instance of the second entity relates to only one instance of the first, it is a one-to-many relationship.
Incorrect! Try again.
36A weak entity set Dependent must be associated with a strong entity set Employee to exist. How is the relationship connecting a weak entity to its strong entity typically represented in an ER diagram?
entity relationship model
Medium
A.By a solid line with a single diamond
B.By an identifying relationship shown as a double diamond
C.By a dashed line connecting the entities
D.By an ellipse with a double border
Correct Answer: By an identifying relationship shown as a double diamond
Explanation:
In an ER diagram, a weak entity relies on a strong entity for its existence. This dependency is represented by an identifying relationship, depicted graphically as a double diamond.
Incorrect! Try again.
37In the relational model, relation has a foreign key referencing relation . To maintain referential integrity, which of the following conditions MUST be true?
relational model
Medium
A. and must have the exact same number of attributes
B.Every value in the foreign key of must match a primary key value in or be null
C.The foreign key in must also serve as its primary key
D.Every primary key in must be referenced by at least one tuple in
Correct Answer: Every value in the foreign key of must match a primary key value in or be null
Explanation:
Referential integrity dictates that a foreign key value must either be null (if allowed) or exactly match an existing primary key value in the referenced relation, ensuring there are no 'orphan' records.
Incorrect! Try again.
38A relation has a primary key composed of attributes and . Which core relational model constraint ensures that neither nor can contain a null value?
relational model
Medium
A.Key equivalence constraint
B.Referential integrity constraint
C.Domain constraint
D.Entity integrity constraint
Correct Answer: Entity integrity constraint
Explanation:
The entity integrity constraint states that no part of a primary key can be null. This ensures that every tuple in a relation can be uniquely identified.
Incorrect! Try again.
39A startup is building an IoT application where incoming sensor data structures vary significantly between devices and evolve rapidly. Why might they choose a non-relational (NoSQL) database over a traditional relational database?
Comparison of relational and non-relational databases
Medium
A.NoSQL databases provide better support for complex multi-table joins
B.NoSQL databases offer a flexible schema design suitable for semi-structured data
C.Relational databases do not support ACID properties
D.Relational databases cannot scale to handle high volumes of data
Correct Answer: NoSQL databases offer a flexible schema design suitable for semi-structured data
Explanation:
Non-relational (NoSQL) databases do not require a fixed, predefined schema. This flexibility makes them highly suitable for applications where the data structure is semi-structured, highly variable, or evolving rapidly.
Incorrect! Try again.
40When designing a core banking system to handle financial ledgers, why would a relational database typically be preferred over a non-relational document database?
Comparison of relational and non-relational databases
Medium
A.It inherently prioritizes eventual consistency over strong consistency
B.It scales horizontally much better than non-relational databases
C.It provides robust ACID compliance and strict schema enforcement
D.It uses hierarchical data storage, which is easier to parse for financial records
Correct Answer: It provides robust ACID compliance and strict schema enforcement
Explanation:
Financial systems require absolute data accuracy and consistency. Relational databases are preferred because they strictly enforce schemas (ensuring data structure integrity) and provide strong ACID guarantees, preventing partial transaction failures.
Incorrect! Try again.
41A financial system allows two concurrent transactions: and . Both check a shared joint account balance (60 withdrawal doesn't drop the balance below 100, both proceed to withdraw 20 balance. Which database anomaly does this represent, and which ACID property is primarily intended to prevent it?
purpose of database systems
Hard
A.Lost Update; Atomicity
B.Phantom Read; Consistency
C.Dirty Read; Durability
D.Write Skew; Isolation
Correct Answer: Write Skew; Isolation
Explanation:
This scenario describes a Write Skew anomaly, which occurs when two concurrent transactions read overlapping data, make a decision, and then modify disjoint data (or concurrent modifications lead to constraint violations). It is prevented by higher isolation levels (like Serializable) which govern how transactions interact concurrently.
Incorrect! Try again.
42In a DBMS, the Buffer Manager interacts with the Recovery Manager using a specific page replacement policy. If the system uses a 'Steal/No-Force' policy, what are the implications for the logging and recovery components?
components of dbms
Hard
A.The system requires REDO logging but not UNDO logging.
B.The system requires UNDO logging to remove uncommitted changes from stolen pages, and REDO logging to restore committed changes not forced to disk.
C.The system requires UNDO logging only, as 'No-Force' guarantees committed pages are instantly written to disk.
D.The system does not require a Write-Ahead Log (WAL) because all transactions are forced to disk.
Correct Answer: The system requires UNDO logging to remove uncommitted changes from stolen pages, and REDO logging to restore committed changes not forced to disk.
Explanation:
'Steal' means an uncommitted transaction's pages can be written to disk (requiring UNDO if it aborts). 'No-Force' means a committed transaction's pages do not have to be immediately written to disk (requiring REDO if a crash occurs).
Incorrect! Try again.
43In contrasting Online Transaction Processing (OLTP) and Online Analytical Processing (OLAP) applications, which of the following schema designs and indexing strategies is highly optimized for OLAP but detrimental to OLTP performance?
applications of dbms
Hard
A.Highly normalized 3NF schemas with B-Tree indexes on primary keys.
B.Third Normal Form schemas with clustered indexes on rapidly incrementing timestamp columns.
C.Eventual consistency key-value stores with reverse-key indexes.
D.Denormalized Star schemas with Bitmap indexes on dimension attributes.
Correct Answer: Denormalized Star schemas with Bitmap indexes on dimension attributes.
Explanation:
OLAP applications benefit from denormalized Star schemas (for fast aggregations) and Bitmap indexes (highly efficient for reading categorical data). These are detrimental to OLTP because denormalization causes write anomalies and Bitmap indexes suffer severe locking/performance penalties during frequent concurrent updates.
Incorrect! Try again.
44In a three-tier DBMS architecture, an architectural change requires moving business logic constraints from database triggers into the application server tier. How does this shift affect the ANSI-SPARC architecture mapping and system security?
three tier dbms architecture
Hard
A.It removes constraint enforcement from the conceptual schema (DBMS level) to the external tier, potentially allowing direct database access tools to bypass business rules.
B.It merges the physical and logical tiers, guaranteeing identical constraint enforcement.
C.It shifts constraints from the internal schema to the conceptual schema, increasing database-level security.
D.It translates logical data independence into physical data independence, neutralizing SQL injection risks.
Correct Answer: It removes constraint enforcement from the conceptual schema (DBMS level) to the external tier, potentially allowing direct database access tools to bypass business rules.
Explanation:
Database triggers exist at the conceptual (logical) schema level of the DBMS. Moving them to the application tier (the middle tier in a three-tier setup) means the DBMS itself no longer enforces them. If a user bypasses the application and connects directly to the database, they can violate business rules.
Incorrect! Try again.
45A Database Administrator (DBA) identifies a performance bottleneck and decides to replace a large table's B+ Tree index with a Hash index, and horizontally partitions the table across two disks. Assuming the SQL queries in the application remain completely unmodified and execute successfully, this scenario is a strict demonstration of:
data independence
Hard
A.Application-Tier Independence
B.Schema Evolution Independence
C.Physical Data Independence
D.Logical Data Independence
Correct Answer: Physical Data Independence
Explanation:
Physical data independence is the ability to change the physical schema (internal structure, such as indexes, file organization, or partitioning) without having to alter the conceptual schema or application programs.
Incorrect! Try again.
46Let be the conceptual schema and be the internal schema of a database. If an entity in is mapped to a relation , which of the following operations strictly modifies but leaves the definition of effectively abstracted away from the application developers?
database schema
Hard
A.Adding a new non-nullable attribute to relation with a default value.
B.Changing the disk block size used to store relation .
C.Altering the storage path of relation 's tablespace.
D.Changing the clustered index on relation to a non-clustered index.
Correct Answer: Adding a new non-nullable attribute to relation with a default value.
Explanation:
Adding an attribute alters the conceptual schema (), which defines the logical structure of the data. The other options (tablespaces, indexing, disk block sizes) are modifications strictly to the internal/physical schema ().
Incorrect! Try again.
47Consider a database schema defined as an Intension. A database Instance is considered the Extension of this schema at a specific timestamp . If an instance at is updated to instance at , which mathematical property MUST hold true for to be considered a 'valid state'?
instance
Hard
A. must reside in a newly allocated physical memory block distinct from .
B.The cardinality of must equal the cardinality of .
C. must contain a superset of the tuples in .
D. must strictly satisfy all integrity constraints defined in the schema's Intension.
Correct Answer: must strictly satisfy all integrity constraints defined in the schema's Intension.
Explanation:
An instance is a snapshot of the database at a given time. A valid database state (instance) is one that satisfies all the structure and integrity constraints (domain, key, referential) defined by the schema (the Intension).
Incorrect! Try again.
48In the context of semantic data modeling, what is the primary consequence of the 'semantic gap' between the conceptual model (e.g., pure ER model) and the logical model (e.g., Relational model)?
data modeling
Hard
A.The logical model automatically infers ternary relationships without explicit foreign keys.
B.Physical storage structures become heavily fragmented during the translation phase.
C.Business rules represented explicitly in the conceptual model (such as disjoint specialization) must often be enforced via application logic or triggers in the relational model.
D.The conceptual model restricts the cardinality of relationships, while the logical model allows infinite cardinality bounds.
Correct Answer: Business rules represented explicitly in the conceptual model (such as disjoint specialization) must often be enforced via application logic or triggers in the relational model.
Explanation:
The semantic gap refers to the loss of expressive power when converting from a high-level conceptual model to a lower-level logical model. Complex constraints like disjoint/overlapping specialization cannot be natively declared via standard SQL foreign keys and require triggers or application logic.
Incorrect! Try again.
49In an Entity-Relationship model, a weak entity set is dependent on a strong entity set via an identifying relationship . If the primary key of is and the partial key (discriminator) of is , what is the implication of total participation of in ?
entity relationship model
Hard
A.Every entity in must be associated with at least one entity in .
B. can exist independently in the database if is set to NULL.
C.Every entity in must be associated with exactly one entity in , and the primary key of is mathematically the composite .
D.The primary key of becomes exactly , ignoring .
Correct Answer: Every entity in must be associated with exactly one entity in , and the primary key of is mathematically the composite .
Explanation:
A weak entity cannot exist without its identifying strong entity, meaning it has total participation in the identifying relationship. Its primary key is formed by combining the primary key of the strong entity () and its own partial key ().
Incorrect! Try again.
50A conceptual schema requires modeling a scenario where a 'Doctor' prescribes a 'Drug' to a 'Patient'. If this is modeled as a single ternary relationship 'Prescription', how does it fundamentally differ from modeling it as three separate binary relationships?
entity relationship model
Hard
A.The ternary relationship allows recording an attribute like 'Dosage' specific to the exact combination of Doctor, Patient, and Drug, which is impossible to guarantee with three independent binary relationships.
B.The ternary relationship prevents a doctor from prescribing the same drug to multiple patients.
C.Three binary relationships reduce the risk of database normalization anomalies to zero.
D.There is no mathematical or semantic difference; they compile to the identical relational schema.
Correct Answer: The ternary relationship allows recording an attribute like 'Dosage' specific to the exact combination of Doctor, Patient, and Drug, which is impossible to guarantee with three independent binary relationships.
Explanation:
A ternary relationship captures an association among three entities simultaneously, meaning an attribute on the relationship belongs to the specific trio. Three binary relationships cannot guarantee that the specific Doctor-Drug-Patient triplet is correlated as a single event.
Incorrect! Try again.
51Given a relation and the functional dependencies , , and . Which of the following statements about the candidate keys of is true?
relational model
Hard
A.There is only one candidate key: .
B.The relation has no candidate keys because of the cyclic dependencies.
C.There is only one candidate key: .
D.The candidate keys are , , and .
Correct Answer: The candidate keys are , , and .
Explanation:
Attribute does not appear on the right side of any functional dependency, so it must be part of any candidate key. Because , , and , attributes , , and are mutually determining. Combining each with yields , , and as candidate keys, all capable of determining all attributes of .
Incorrect! Try again.
52Relation has a foreign key referencing Relation with the constraint ON DELETE CASCADE. Relation has a foreign key referencing with ON DELETE SET NULL. If a tuple in is deleted, what is the guaranteed chronological outcome of the integrity enforcement?
relational model
Hard
A.The deletion in is blocked unless ON DELETE CASCADE is explicitly applied to both tables.
B.An infinite loop of deletions occurs, leading to a transaction rollback.
C.The DBMS detects a cyclic dependency and prevents the creation of the tables.
D.The cascading delete triggers the deletion of dependent tuples in , which in turn sets the corresponding foreign keys in any remaining dependent tuples in to NULL.
Correct Answer: The cascading delete triggers the deletion of dependent tuples in , which in turn sets the corresponding foreign keys in any remaining dependent tuples in to NULL.
Explanation:
Relational databases process referential actions sequentially. Deleting a row in triggers CASCADE, deleting related rows in . The deletion of rows in then triggers the SET NULL action on any other related rows back in . This does not loop infinitely because the actions are finite and resolve constraints state-by-state.
Incorrect! Try again.
53According to the CAP Theorem, a distributed non-relational database like Cassandra prioritizes Availability and Partition Tolerance (AP) over Consistency. Under network partition, which of the following best describes the database's behavior compared to a traditional relational database cluster prioritizing CP?
Comparison of relational and non-relational databases
Hard
A.Cassandra guarantees ACID properties across all partitions, whereas the relational database guarantees only BASE properties.
B.Cassandra requires synchronous replication across all nodes to function, whereas the relational system uses asynchronous replication.
C.Cassandra will refuse read/write requests to prevent data divergence, while the CP relational database will allow reads but block writes.
D.Cassandra will accept reads and writes on all available nodes, potentially resulting in stale reads, whereas the CP relational database will halt operations on the minority partition to guarantee strongly consistent reads.
Correct Answer: Cassandra will accept reads and writes on all available nodes, potentially resulting in stale reads, whereas the CP relational database will halt operations on the minority partition to guarantee strongly consistent reads.
Explanation:
In an AP system under partition, nodes continue to accept requests (Availability) even if they cannot sync with the rest of the cluster, leading to eventual consistency (stale reads). A CP system prioritizes Consistency, so it will reject requests on partitioned nodes that cannot establish a quorum.
Incorrect! Try again.
54When addressing the 'impedance mismatch' between object-oriented application code and database storage, which scenario fundamentally highlights a structural advantage of Document-oriented NoSQL databases over Relational databases?
Comparison of relational and non-relational databases
Hard
A.Enforcing strict declarative referential integrity constraints across bounded contexts.
B.Implementing granular row-level security based on hierarchical roles.
C.Executing highly complex multi-table analytical joins over normalized data.
D.Persisting deeply nested, polymorphic object structures where the schema varies dynamically between instances.
Correct Answer: Persisting deeply nested, polymorphic object structures where the schema varies dynamically between instances.
Explanation:
Document databases (like MongoDB) store data as JSON/BSON, which naturally maps to object-oriented structures (nested arrays/objects), bypassing the impedance mismatch. Relational databases require complex Object-Relational Mapping (ORM) and multiple joined tables to reconstruct polymorphic or deeply nested objects.
Incorrect! Try again.
55Before the advent of DBMS, file-processing systems suffered from the 'data isolation' problem. In a modern DBMS, how is this specific problem solved structurally?
purpose of database systems
Hard
A.By storing data entirely in volatile memory to eliminate disk I/O bottlenecks.
B.By preventing concurrent users from reading the exact same data file simultaneously.
C.By enforcing the Isolation property of ACID using Two-Phase Locking.
D.By providing a centralized catalog/data dictionary and standardized query interfaces (like SQL) that abstract data access formats.
Correct Answer: By providing a centralized catalog/data dictionary and standardized query interfaces (like SQL) that abstract data access formats.
Explanation:
In early file systems, 'data isolation' referred to data being scattered in different files and formats, making it hard to write unified application programs. A DBMS solves this by abstracting the data layer and providing a standardized schema and query language (SQL), decoupled from the physical file format.
Incorrect! Try again.
56A DBMS Query Optimizer evaluates multiple execution plans for the query: SELECT * FROM A JOIN B ON A.id = B.a_id JOIN C ON B.id = C.b_id. Which internal component dictates whether the optimizer chooses a Hash Join over a Nested Loop Join based on the cardinality of relations?
components of dbms
Hard
A.The Buffer Manager's Eviction Policy
B.The Transaction Manager
C.The DDL Compiler
D.The Statistical Metadata in the System Catalog
Correct Answer: The Statistical Metadata in the System Catalog
Explanation:
The Query Optimizer relies on statistical data (like table cardinality, index distribution, and histograms) stored in the System Catalog (or Data Dictionary) to estimate the cost of different algorithms (Hash Join vs Nested Loop) and select the most efficient execution plan.
Incorrect! Try again.
57In a robust Three-Tier DBMS web application architecture, an attacker attempts an SQL injection attack by inputting malicious strings into a web form. Which tier is primarily responsible for sanitizing this input and binding parameters to prevent the internal tier's query engine from executing the malicious payload?
three tier dbms architecture
Hard
A.The Database Tier (DBMS Storage Manager)
B.The Application Tier (Application Server)
C.The Database Tier (DBMS Buffer Manager)
D.The Presentation Tier (Client)
Correct Answer: The Application Tier (Application Server)
Explanation:
The middle tier (Application Server) is responsible for business logic, processing inputs from the presentation tier, and safely communicating with the database tier using parameterized queries/prepared statements to neutralize SQL injection.
Incorrect! Try again.
58Logical Data Independence is notoriously more difficult to achieve than Physical Data Independence. Which of the following schema changes best illustrates the limit of Logical Data Independence, requiring modifications to external application views?
data independence
Hard
A.Changing the underlying data structure of an index from a B-Tree to a Bitmap.
B.Decomposing a single table into two tables and due to normalization, where the application requires updating attribute based on .
C.Increasing the length of a VARCHAR column from 50 to 100 characters.
D.Splitting a heavily accessed table into two physical files on different hard drives.
Correct Answer: Decomposing a single table into two tables and due to normalization, where the application requires updating attribute based on .
Explanation:
While read operations on the decomposed tables could be abstracted via a View joining and , update operations on such a view are often non-updatable (the view update problem). This forces the application logic to be rewritten to handle updates to the two new base tables explicitly, breaking logical data independence.
Incorrect! Try again.
59A Database Schema is often considered relatively static, whereas the database state changes rapidly. Which of the following operations alters the 'Intension' of the database rather than its 'Extension'?
database schema
Hard
A.Executing DELETE FROM Employees WHERE age > 65;
B.Committing a transaction that updates a bank balance.
The 'Intension' refers to the schema (the structure, rules, and constraints). Adding a CHECK constraint alters the rules governing the database (Intension). The 'Extension' refers to the actual data/tuples at a given moment, which is changed by INSERT, UPDATE, or DELETE statements.
Incorrect! Try again.
60When designing an Enhanced Entity-Relationship (EER) model, a designer creates a superclass 'Vehicle' with subclasses 'Car' and 'Truck'. If the business rule states that a Vehicle can be both a Car and a Truck simultaneously, and that there are other types of Vehicles not represented by these subclasses, which constraint pair applies?
data modeling
Hard
A.Overlapping, Total
B.Overlapping, Partial
C.Disjoint, Partial
D.Disjoint, Total
Correct Answer: Overlapping, Partial
Explanation:
'Overlapping' means an entity can belong to more than one subclass simultaneously (a Vehicle can be both a Car and a Truck). 'Partial' (or partial participation) means not every entity in the superclass has to belong to one of the specified subclasses (there are other vehicles besides Cars and Trucks).