Unit 1 - Practice Quiz

A DBMS is a software system specifically designed to manage databases. Its main goals are to handle data storage, retrieval, security, and integrity in an efficient and reliable manner, abstracting these complexities from the application developer.

By centralizing data management, a DBMS can control and minimize the duplication of data (redundancy), which in turn helps prevent inconsistencies where different copies of the same data might have different values.

DDL is the subset of SQL used to define the database structure or schema. Common DDL commands include CREATE, ALTER, and DROP.

The Storage Manager is the module that provides the interface between the low-level data stored in the database and the application programs and queries. It handles the interaction with the file system.

Online banking systems need to manage vast amounts of critical, structured data about customers, accounts, and transactions. This requires the features of a DBMS, such as transaction management, security, and concurrent access control.

The Application Tier acts as an intermediary, processing requests from the Presentation Tier, applying business rules and logic, and then communicating with the Data Tier to fetch or store data.

The Presentation Tier is the top-most level of the application, responsible for the user interface (UI) that the end-user sees and interacts with. It communicates with the Application Tier.

Physical data independence allows modifications to the physical storage structure (e.g., changing storage devices or file organization) without affecting the community view of the data (conceptual schema).

Logical data independence is the ability to change the conceptual schema (e.g., add a new attribute or entity) without having to rewrite existing external schemas or application programs.

A database schema is the logical structure of the database. It defines the tables, the columns in each table, the relationships between tables, and other constraints. It's like the blueprint for the database.

While the schema is the constant blueprint, the instance is the data content, which changes frequently. It's a snapshot of the data in the database at a particular point in time.

Data modeling is a design-phase activity. It involves creating a simplified, abstract diagram of the data to understand the entities, their attributes, and the relationships between them before building the actual database.

In standard ER diagram notation, a rectangle is used to represent an entity set. An entity is a real-world object or concept about which data is stored, such as 'Student' or 'Course'.

Attributes are the properties or characteristics that describe an entity. For a 'Person' entity, name, age, and address are all descriptive attributes.

A diamond shape is used in ER diagrams to represent the association between two or more entities. For example, a relationship 'Enrolls' could connect the 'Student' and 'Course' entities.

The fundamental concept of the relational model, proposed by E.F. Codd, is to organize data into two-dimensional tables, formally called relations. Each table has rows (tuples) and columns (attributes).

In relational model terminology, a table is a relation, a column is an attribute, and a row is a tuple. Each tuple represents a single record or data item in the table.

The external schema, also known as a user view, defines a portion of the database that is relevant to a specific user or user group, hiding the rest of the database for simplicity and security.

Relational databases are known for their strict, predefined schemas. Data must fit into the table structure defined by the Data Definition Language (DDL), ensuring data consistency and integrity.

The main advantage of many NoSQL databases is their schema-less or schema-on-read nature. This allows for storing data that doesn't have a consistent structure, making them highly flexible for evolving applications and unstructured data.

Concurrency control mechanisms (like locking) in a DBMS would prevent multiple users from overwriting each other's changes. The ability to perform efficient querying (using languages like SQL) would solve the problem of generating complex reports, which is difficult with flat files.

The Query Processor is responsible for parsing the query, optimizing it, and creating an execution plan. The Storage Manager is responsible for the interaction with the file system, retrieving the data blocks from the disk that are required to execute the plan.

The Application Tier contains the business logic. While initial format checks can happen on the client-side (Presentation Tier) for better user experience, the authoritative validation logic resides in the application tier to ensure security and consistency, as client-side checks can be bypassed.

Physical Data Independence is the ability to modify the physical schema (how data is stored, e.g., storage structures, indexes, file organization) without causing application programs to be rewritten. Adding an index is a change to the physical storage structure.

The External Schema (or view level) describes a part of the database that is relevant to a particular user or group of users. It hides the details of other parts of the database. The conceptual schema defines the entire logical structure, and the internal schema defines the physical storage.

The schema is the structure or blueprint of the database (EMPLOYEE(E_ID, E_NAME)), which did not change. The instance is the actual data in the database at a specific moment in time (the collection of rows). Adding a new employee changes the data, so the instance at 10:00 AM is different from the instance at 9:00 AM.

The rule explicitly states that one employee can be associated with many projects ('manages multiple projects') and one project can be associated with many employees ('managed by multiple employees'). This defines a many-to-many (M:N) relationship.

This describes a foreign key constraint. The purpose of a foreign key is to enforce referential integrity, which guarantees that a value in the referencing column (CustomerID in Orders) must match a value in the referenced primary key column (CustomerID in Customers). This prevents 'orphan' records.

The requirement for a flexible, non-uniform schema (different attributes per user) is a key strength of document-oriented NoSQL databases like MongoDB. Their architecture is also designed for horizontal scaling (sharding), which is ideal for handling massive user growth. A strict relational schema would be difficult to manage here.

Logical Data Independence is the ability to modify the conceptual schema without having to change the external schemas (views) or application programs. Here, the conceptual schema (the structure of the STUDENT table) was changed, but the external schema (the V_STUDENT view) allows the application to continue functioning as if the change never happened.

This scenario is a classic concurrency problem. Transaction management, specifically concurrency control mechanisms (like locking), ensures that transactions (e.g., booking a seat) are executed in an atomic and isolated manner. This prevents race conditions where two customers might book the same last seat, thus ensuring data integrity.

Conceptual Data Modeling is the first phase of database design. It focuses on identifying the entities, attributes, and relationships from the business requirements. It is independent of the specific DBMS to be used. The ER model is a common tool for this phase.

A weak entity is one that cannot be uniquely identified by its attributes alone and must rely on a foreign key from a related strong entity (its owner). A dependent's existence is tied to the existence of a corresponding employee. The primary key of a weak entity is formed by the primary key of the strong entity plus the weak entity's partial key.

The Natural Join (⨝) operation combines tuples from two relations that have equal values on all their common attributes. In this case, the common attribute is B. The result would be a relation with attributes (A, B, C). A subsequent projection would yield the desired (A, C) pairs. It is the most direct operation for this 'matching' task.

By separating the business logic into its own middle tier (Application Tier), this layer can be scaled out by adding more application servers without affecting the database or the clients. This modularity is crucial for handling high traffic and improving performance and maintainability in large systems.

The Recovery Manager is responsible for ensuring the durability property of transactions. It uses logs and other mechanisms to restore the database to a consistent state after a system crash or failure, typically by rolling back uncommitted transactions and redoing committed ones.

This is a classic use case for a graph database (like Neo4j). It stores entities as nodes and relationships as edges. Queries involving pathfinding and relationship traversal (like 'friends of friends') are extremely fast and intuitive in a graph model, whereas they would require complex and often slow recursive joins in a relational model.

Domain Integrity ensures that all values in a column are from a specified domain (i.e., a set of valid values). In this case, the domain for the age column is the set of integers from 18 to 65. Entity integrity relates to primary keys, and referential integrity relates to foreign keys.

This scenario represents a single fact that irreducibly connects three entities. A specific instance of the relationship requires one of each. Breaking it into three binary relationships would lose the original meaning; for example, it might imply that if an employee works at a branch and that branch has a project, the employee works on that project, which isn't necessarily true. A ternary relationship correctly captures the association.

This is the standard way to implement a many-to-many relationship in the relational model. The ENROLLMENT table is an associative or linking table. The composite primary key (StudentID, CourseID) ensures that a student can enroll in a specific course only once. The foreign keys ensure that only valid students and courses can be part of an enrollment.

Normalization (splitting one table into two) is a change to the logical schema (also called the conceptual schema). Logical Data Independence is the ability to modify the logical schema without causing application programs to be rewritten. In this case, views could be created to simulate the original single table, thus shielding applications from the change. Without this independence, any application code that performed a SELECT, INSERT, UPDATE, or DELETE on the original table structure would fail and need to be completely rewritten to work with the new, normalized tables. Physical data independence relates to changes in physical storage (like adding an index), not logical structure.

The key principle that allows the middle (application) tier to scale horizontally is statelessness. A stateless server treats every request as an independent transaction, unrelated to any previous request. This means any available application server can handle any user's request, making load balancing simple and effective. However, since all these servers must persist data, they all connect to the same database tier. This funnels all data access requests through the database, which cannot be scaled out as easily and often becomes the new performance bottleneck. The application servers typically manage a connection pool to the database, concentrating this pressure.

A ternary relationship is typically mapped to its own relation (Enrolls). The primary key of this relation is usually a composite of the primary keys of the participating entities (StudentID, CourseID, ProfessorID). The cardinality (1,1) on the Course side indicates total participation of the Enrolls relationship with the Course entity. This means every tuple in the Enrolls relation must be associated with exactly one course. In the relational model, this translates to the foreign key CourseID in the Enrolls table having a NOT NULL constraint. Since it's essential for identifying the relationship instance, it must also be part of the composite primary key. Option B is incorrect because Student and Professor are on the 'many' sides. Option D is incorrect because CourseID is required for uniqueness.

This schema has a circular foreign key reference: DEPARTMENT refers to EMPLOYEE (for the manager), and EMPLOYEE refers to DEPARTMENT. This creates a chicken-and-egg problem when inserting the first records. To insert a DEPARTMENT, you need a valid ManagerID which must exist in EMPLOYEE. To insert that EMPLOYEE, you need a valid DeptID which must exist in DEPARTMENT. Standard integrity checking would prevent both inserts. This circular dependency can only be resolved by either allowing one of the foreign keys to be temporarily NULL and updating it later, or by using deferrable constraints, which are checked at the end of the transaction rather than after each statement. This allows both records to be inserted before the constraints are validated.

The requirements describe a strong need for Consistency (ACID properties, valid business rules) and the ability to handle network failures (Partition Tolerance). The system explicitly sacrifices Availability ("halt operations in the minority partition") to ensure consistency is never compromised. This is a classic CP (Consistency, Partition Tolerance) system. Traditional relational databases (SQL DBMS) are designed around ACID transactions and strong consistency, making them the classic choice for CP systems like financial ledgers. AP systems would prioritize keeping the service online even if it means serving potentially stale or conflicting data that is reconciled later.

The database schema is the formal definition of the database's structure, constraints, and rules. The ALTER TABLE command adds a new CHECK constraint, which modifies this definition. Therefore, this action changes the schema. The database instance is the actual data content of the database at a specific point in time. The INSERT statements add new rows of data to the Users table. This action changes the content, thus changing the instance. The schema change happened before the instance change.

The Query Optimizer does not scan the entire database to make decisions. Instead, it relies on metadata and statistical information about the data. This information is stored in the system's data dictionary or system catalog. The Catalog Manager, a part of the overall Storage Manager, is responsible for maintaining this catalog. It periodically gathers statistics (e.g., through commands like ANALYZE or UPDATE STATISTICS) and provides this crucial information to the Query Optimizer, which uses it to estimate the cost (e.g., I/O operations, CPU time) of various potential query execution plans.

This sequence represents the standard, top-down data modeling process:

1. Conceptual Model: The initial, high-level, implementation-agnostic ER diagram that captures entities, attributes, and relationships from the business requirements.
2. Logical Model: The transformation of the conceptual model into a specific data model's structure (in this case, relational). It defines tables, columns, primary keys, foreign keys, and normalization (3NF), but without considering the specific DBMS or hardware. Data types like VARCHAR are defined here.
3. Physical Model: The concrete implementation of the logical model on a specific platform. It includes platform-specific details like storage structures (e.g., partitioning strategy) and access methods (e.g., B-Tree indexes) to ensure performance.

Atomicity is the "all or nothing" property of a transaction. The fund transfer is a single logical transaction composed of two operations. The failure described, where only part of the transaction completed, is a direct violation of atomicity. A DBMS ensures atomicity through its transaction manager. When the system restarts, the recovery manager (a part of the transaction management system) will inspect the transaction log. It will see that the transaction started but did not commit. It will then use the 'undo' information in the log to roll back the debit operation, returning the database to the consistent state it was in before the transaction began.

The relationship between Employee and Skill is many-to-many (M:N). The ProficiencyLevel is not a property of just the employee (an employee has different levels for different skills) nor is it a property of just the skill (different employees have different levels for the same skill). It is a property that describes the association between a specific employee and a specific skill. Therefore, the correct way to model this is to create an associative entity or a relationship with attributes. The M:N relationship HasSkill between Employee and Skill is where the ProficiencyLevel attribute belongs. When mapped to a relational schema, this creates a linking table EmployeeSkills(EmployeeID, SkillID, ProficiencyLevel).

This is the classic use case for the relational division operator (). The expression R \div S finds tuples in R that are associated with every tuple in S. In this context:

• gives us the pairs of (student, course) for all enrollments.
• gives us the set of all possible course IDs.
• Dividing the first by the second, , asks for all sids from the first relation such that the pair (sid, cid) exists in Enrolled for every cid present in the second relation. This precisely identifies students enrolled in all courses. Option A is an equivalent but much more complex way of expressing division using other operators. Option D uses extended aggregate operators and is not part of the fundamental relational algebra.

Action 1 (Reorganization) is a change to the physical schema—it alters how data is stored on disk without changing the logical table structure. The goal is to improve performance without forcing applications to be rewritten. This is the definition of Physical Data Independence. Action 2 (Creating a View) is a tool used to provide Logical Data Independence. A view is a virtual table based on a query. It creates a new external schema object. It can be used to shield users/applications from changes in the underlying base tables (e.g., if we later split table T, we could redefine view V to use a join, and the users of V would be unaffected). Thus, its primary role relates to controlling the logical presentation of data.

The impedance mismatch arises because object-oriented languages represent data as interconnected graphs of objects, while relational databases use flat tables. This requires a complex mapping layer (an ORM). Document databases fundamentally reduce this mismatch because their core data structure (e.g., a JSON document) is itself a hierarchical structure of nested objects and arrays. An entire complex object from an application can often be persisted as a single document, preserving its structure without the need for joins or complex mapping. While ORMs (B) are tools to manage the mismatch, they don't eliminate the underlying problem. Key-value stores (A) are too simplistic, treating the object as a blob. Wide-column stores (D) offer schema flexibility but don't handle nested object structures as naturally as document stores.

In a three-tier architecture, the client (presentation tier) does not connect directly to the database. It communicates with the application server (middle tier) through a well-defined API. The application server is the only component that constructs and executes SQL queries against the database. This creates a powerful abstraction layer. The client has no knowledge of the database schema, and it cannot send raw SQL queries. This architecture is a primary defense against SQL Injection, where an attacker attempts to embed malicious SQL code within user input. The middle tier can validate, sanitize, and parameterize all inputs before constructing a safe query, a control that is much harder to enforce when application logic is distributed on many clients in a two-tier model.

What about {B,C,D}? {B,C,D}+ = {B,C,D,E} (from BC->E) -> {B,C,D,E,A} (from ED->A). Yes, {B,C,D} is a candidate key. Not an option.
Let's go back to {A, C, D}. I am confident this is a candidate key. There must be an error in the question or options. Let me assume one FD is different, e.g., CD -> E. Then {A, C, D}+ gives {A, C, D, B, E}. Minimal? {A,C}+ gives {A,C,B}. {C,D}+ gives {C,D,E}. {A,D}+ gives {A,D,B}. Yes, {A,C,D} is minimal. Still no option.
There has to be a mistake in my logic or the question. Let's test {C, D} one last time. Oh, I see. Let's try the option {C, D} and assume it is correct and see if I can derive it. What if the FD was E -> A? Then {C,D,E}+ would be {C,D,E,A,B}. What if the FD was CD -> A? {C,D}+ = {C,D,A,B}. Not all attrs. Okay, the provided options seem wrong given the FDs. Let me generate a correct question/answer pair.
New FDs: A -> B, C -> D, AE -> R. For relation R(A,B,C,D,E). No that's too simple. Let's use the original FDs and find the error. R(A, B, C, D, E), {A -> B, BC -> E, ED -> A}. Let's compute the closure of {C,D,E}. {C,D,E}+ -> {C,D,E,A} (from ED->A) -> {C,D,E,A,B} (from A->B). So {C,D,E} is a superkey. Is it minimal? {C,D}+ = {C,D}. {D,E}+ = {D,E,A,B}. {C,E}+ = {C,E}. So {D,E} is a candidate key! Option B {E, D}. My first calculation was wrong. {E, D}+ = {E, D}. Use ED -> A. Get {E, D, A}. Use A -> B. Get {E, D, A, B}. We are missing C. So {E,D} is not a key. My analysis that {D,E} is a candidate key was wrong.
Let's try {B,C,D}. {B,C,D}+ = {B,C,D}. Use BC -> E. Get {B,C,D,E}. Use ED -> A. Get {B,C,D,E,A}. So {B,C,D} is a superkey. Is it minimal? {B,C}+ = {B,C,E}. {C,D}+ = {C,D}. {B,D}+ = {B,D}. Yes, {B,C,D} is a candidate key. This is not an option.
This question is flawed. I must generate a correct one.

**New Question:** Given a relation `R(A, B, C, D, E)` with functional dependencies `{AB -> C, C -> D, D -> E, E -> A}`. Which of the following is a candidate key?
**Options:** A) `{A, B}`, B) `{C, E}`, C) `{A}`, D) `{B, E}`.
**Analysis:** Let's find the closure of `{A, B}`. `{A, B}+ = {A, B}`. Use `AB -> C`. Get `{A, B, C}`. Use `C -> D`. Get `{A, B, C, D}`. Use `D -> E`. Get `{A, B, C, D, E}`. So `{A, B}` is a superkey. Is it minimal? `{A}+ = {A}`. `{B}+ = {B}`. Neither is a superkey. So `{A, B}` is a candidate key. Let's check other options to be sure. `{B,E}+ = {B,E,A}` (from `E->A`). Now we have A and B. Use `AB->C`. `{B,E,A,C}`. Use `C->D`. `{B,E,A,C,D}`. So `{B,E}` is also a candidate key. The question needs to be precise. "Which of the following is *a* candidate key" is fine.

To determine if a set of attributes is a candidate key, we must compute its attribute closure and check if it includes all attributes in the relation (making it a superkey), and then check if any of its proper subsets are also superkeys (to ensure minimality).

1. Let's compute the closure of {A, B}, denoted as {A, B}+.
   • Start with {A, B}.
   • Using , we add C: {A, B, C}.
   • Using , we add D: {A, B, C, D}.
   • Using , we add E: {A, B, C, D, E}.
   • The closure contains all attributes of R, so {A, B} is a superkey.
2. Check for minimality:
   • The proper subsets of {A, B} are {A} and {B}.
   • {A}+ = {A}.
   • {B}+ = {B}.
   • Neither subset is a superkey. Therefore, {A, B} is minimal.
     Since {A, B} is a minimal superkey, it is a candidate key. Note that {B, C}, {B, D} and {B, E} are also candidate keys in this schema, but {A, B} is the correct choice among the options.

First Normal Form (1NF) mandates that all attribute values in a relation must be atomic. Storing multiple values in a single field (like a comma-separated string or JSON array, Option A) violates 1NF. Creating a fixed number of columns (Option B) is inflexible, leads to many NULL values, and makes querying for a specific keyword difficult. Option D is incorrect because a single foreign key in the Movie table would imply a movie can have only one keyword. The standard and correct approach is to decompose the multi-valued attribute into a separate relation (MovieKeyword). This new table links movies to their keywords, with each row representing one movie-keyword association, thereby ensuring atomicity and satisfying 1NF.

This is a subtle but important distinction. The schema is the abstract concept—the blueprint or formal language description of the tables, columns, data types, constraints, and relationships. The system catalog (or data dictionary) is the concrete implementation of this. It's a set of special tables, managed by the DBMS itself, where the schema information is stored as data (i.e., metadata). The DBMS queries this catalog to understand the database structure, validate queries, and enforce constraints. So, the catalog is the physical manifestation of the schema, treated as data by the DBMS.

1. Analysis Pass: This first pass scans the log forward from the last checkpoint. Its purpose is to figure out the exact state of the system at the moment of the crash. It constructs two key pieces of information: a list of all transactions that were active (started but not committed), known as the 'transaction table', and a list of all data pages that might have been modified in the buffer pool but not written to disk, known as the 'dirty page table'. This information is essential for the subsequent passes.
2. Undo Pass: Undoes the updates of all transactions identified as incomplete by the Analysis pass, processing the log backward.

This scenario is a classic use case for a Document database (e.g., MongoDB). The requirement for highly variable attributes (heterogeneous schema) and frequent schema changes is handled natively. Each product can be stored as a JSON/BSON document, containing only the attributes relevant to it. This schema-on-read approach is highly flexible. Furthermore, document databases have powerful secondary indexes that allow for efficient querying and filtering on any attribute within the documents. While you can simulate this in a relational model with an EAV pattern (A), it is notoriously inefficient to query and complex to manage. A single table with nullable columns (D) is extremely inefficient in terms of storage and difficult to maintain as new attributes are added.

Durability is fundamentally guaranteed by the Write-Ahead Logging (WAL) protocol. The core principle is that before a modified data page is ever written from the buffer pool to the database files on disk, the log record(s) describing that modification must be written to stable storage (the transaction log file). A transaction is considered 'committed' only after its COMMIT record is safely on the stable log. This ensures that if a crash occurs, the recovery system can use the log to reconstruct all committed changes, even those that hadn't yet been written to the main database files, thus ensuring their durability. Locking (A) provides Isolation. The buffer pool (C) is a performance feature that makes Durability a non-trivial problem to solve. Rollback (D) relates to Atomicity.