Model Question Solution – DBMS

Brief answer questions:

1.) Write the advantages of using a database system over traditional file-based system.

• Databases provide better data consistency, reduced redundancy, improved data sharing, centralized control, and stronger security than traditional file-based systems.

2.) What is data abstraction?

• Data abstraction refers to the process of hiding the complexities of a database system and providing users with a simplified interface. It includes three levels: physical, logical, and external.

3.) Define weak entity with example.

• A weak entity is an entity that cannot be uniquely identified by its attributes alone and depends on a related strong entity. Example: Dependent is a weak entity that depends on the Employee entity.

4.) What is the use of GROUP BY clause?

• The GROUP BY clause in SQL is used to group rows with the same values in specified columns and perform aggregate operations like SUM, COUNT, or AVG on each group.

5.) What do you mean by functional dependency?

• Functional dependency is a relationship where one attribute uniquely determines another attribute in a relation. For example, in a relation R(StudentID → StudentName), StudentID determines StudentName.

6.) Why NoSQL databases have gained popularity in recent years?

• NoSQL databases are popular for handling unstructured and semi-structured data, offering high scalability, flexibility, and better performance for big data applications.

7.) What is the primary purpose of division operation in relational algebra within DBMS.

• The division operation is used to find tuples in one relation that match all values in a specified set from another relation.

8.) Define serializable schedule?

• A serializable schedule is a non-serial schedule that produces the same result as a serial schedule, ensuring consistency in concurrent transaction execution.

9.) What is concurrent transaction?

• Concurrent transactions are multiple transactions executed simultaneously, potentially accessing the same data, requiring mechanisms for concurrency control to ensure data consistency.

10.) Differentiate between data warehouse and DBMS.

• A data warehouse is designed for analytical processing and decision-making, focusing on historical and aggregated data. A DBMS is used for operational tasks, managing real-time transactions and day-to-day data operations.

Short Answers Questions

11.) Explain three-schema architecture in brief.

The three-schema architecture, also known as the ANSI/SPARC architecture, is a framework for database systems that aims to separate the user applications from the physical database.

• This architecture is designed to support data independence, which allows changes to the schema at one level to not affect the schema at another level.

The three-schema architecture consists of three levels:

a.) External Level (User View):

• This level defines how individual users or user groups view the data. It includes multiple external schemas, or user views, which represent different ways of looking at the data for different users.

b.) Conceptual Level (Logical View):

• This level defines the logical structure of the entire database as a community of users sees it. It includes the logical schema, which describes all the entities, attributes, relationships, constraints, and security and integrity information.

c.) Internal Level (Physical View):

• This level describes the physical storage of the database. It includes the physical schema, which specifies how data is stored in the storage devices, such as data structures, file organizations, and indexing mechanisms.

12.) Discuss the concept of specialization and generalization.

• Specialization is a top-down approach where a higher-level entity is divided into lower-level entities based on unique characteristics. Example: An Employee entity can be specialized into Manager and Technician.

• Generalization is a bottom-up approach where multiple lower-level entities are combined into a higher-level entity. Example: Car and Truck entities can be generalized into a Vehicle entity.

13.) Write the purpose of unary relational operations: SELECT and PROJECT with example.

• SELECT (σ): Filters rows from a relation based on specified conditions.
  • Example: σ Age > 30 (Employees) selects employees older than 30.

• PROJECT (π): Extracts specific columns from a relation.
  • Example: π Name, Salary (Employees) retrieves only the names and salaries of employees.

14.) Differentiate between logical and physical data independence.

15.) Describe the data definition language and data manipulation language features of SQL.

Data Definition Language (DDL) is a type of SQL command that is used to define, modify, and manage the structure of database objects such as tables, indexes, schemas, and views.

• It primarily focuses on the creation, alteration, and deletion of database schema and its components.

Key Operations of DDL:

• CREATE: Used to create new database or objects in a database.
• ALTER: Used to modify the structure of a database, such as adding or dropping columns in a table.
• DROP: Deletes a database object from the database permanently.
• TRUNCATE: Deletes data from table.
• RENAME: Used to rename a table or a column.
• COMMENT: Used to comment on the data dictionary.

Data Manipulation Language (DML) refers to the type of SQL commands used to retrieve, insert, update, and delete data from a database.

• These operations are crucial for interacting with data in relational databases.

Common DML Statements:

• SELECT: Used to retrieve data from the database.
• INSERT: Used to insert data into a table.
• UPDATE: Used to update existing data within a table.
• DELETE: Used to delete all records from a table.

16.) Define transaction and discuss their properties.

• A transaction is a sequence of operations performed as a single logical unit of work, ensuring data integrity in a database.

Properties of transactions (ACID):

• Atomicity: Ensures all operations are completed or none at all.
• Consistency: Maintains the database in a valid state before and after the transaction.
• Isolation: Ensures concurrent transactions do not interfere with each other.
• Durability: Guarantees changes are permanently stored even in case of a system failure.

Long Answer Questions

17.) What are the different types of relation used in ER diagram? Explain how ER diagram is
converted to table.

19.) Why database recovery is essential? How shadow paging is used for database recovery?

Importance of Database Recovery

• Database recovery ensures data integrity, consistency, and availability in the event of:
  • System Failures: Unexpected power outages, crashes, or hardware malfunctions.
  • Transaction Failures: Errors during transaction execution due to logical or system-level issues.
  • Catastrophic Failures: Natural disasters, malicious attacks, or hardware destruction.
  • Data Corruption: Errors in storage media or during data transfers.

Recovery mechanisms restore the database to its previous consistent state, ensuring no data is lost or corrupted.

Shadow Paging for Database Recovery

• Shadow paging is a technique that maintains two page tables:
  • Shadow Page Table: Represents the state of the database before a transaction begins.
  • Current Page Table: Tracks updates made during the transaction.

Steps in Shadow Paging:

• Initialization:
  • At the start of a transaction, the shadow page table is copied to the current page table.

• Page Modifications:
  • During the transaction, changes are made to new disk pages, leaving the original (shadow) pages untouched.

• Commit:
  • If the transaction is successful, the current page table replaces the shadow page table, making the changes permanent.

• Abort/Failure:
  • If the transaction fails, the shadow page table is retained, effectively discarding all changes made by the transaction.

20.) Why do we need concurrency control in databases? Explain.

Need for Concurrency Control
Concurrency control is essential to manage simultaneous transactions in a database system. It ensures:

• Data Integrity: Prevents conflicting operations from corrupting data.
  • Example: Two users withdrawing money from the same account simultaneously.
• Consistency: Ensures the database adheres to rules and constraints despite concurrent operations.
• Isolation: Transactions appear as though they execute sequentially, even if processed concurrently.
• Avoidance of Anomalies:
  • Lost Update: One transaction overwrites another’s update.
  • Dirty Read: A transaction reads uncommitted changes from another transaction.
  • Unrepeatable Read: Repeated reads return different data due to other transactions.