dbms final exam

What is a transaction in the context of a database?

a. A set of database records
b. A sequence of operations on a database
c. A database query
d. A data structure used for indexing

B. A sequence of operations on a database

Which of the following is an example of a concurrency control technique?
a. Indexing
b. Normalization
c. Locking
d. Data Encryption

C. Locking

In the context of transaction management, what is a deadlock?
a. A failure in the durability property of a transaction
b. The end state of a successfully executed transaction
c. A situation where two or more transactions are waiting for each other to release locks
d. The isolation level of a transaction

C. A situation where two or more transactions are waiting for each other to release locks

What is the purpose of the COMMIT statement in a database transaction?
a. To initiate a new transaction
b. To permanently save the changes made by a transaction
c. To roll back a transaction
d. To release locks held by a transaction

B. To permanently save the changes made by a transaction

What is the purpose of the ROLLBACK statement in a database transaction?
a. To initiate a new transaction
b. To permanently save the changes made by a transaction
c. To reverse the effects of a transaction that cannot be completed
d. To release locks held by a transaction

C. to reverse the effects of a transaction that cannot be completed

What does the SQL statement SELECT * FROM Employees; do?
a. Deletes all records from the Employees table
b. Inserts a new record into the Employees table
c. Retrieves all columns and records from the Employees table
d. Updates the records in the Employees table

C. Retrieves all columns and records from the Employees table

What is the purpose of the SQL GROUP BY clause?
a. To join filter rows based on a specified condition
b. To sort the result set in ascending or descending order
c. To aggregate data based on one or more columns
d. To join multiple tables in a query

C. To aggregate data based on one or more columns

Which of the following SQL functions is used to find the total number of rows in a table?
a. COUNT(*)
b. SUM()
c. AVG()
d. MAX()

A. COUNT(*)

What is the purpose of the SQL JOIN operation?
a. To filter rows based on a specified condition
b. To sort the result set in ascending or descending order
c. To combine rows from two or more tables based on a related column
d. To group rows based on a specified condition

C. To combine rows from two or more tables based on related columns

Which SQL clause is used to sort the result set of a query in ascending or descending order?
a. GROUP BY
b. ORDER BY
c. WHERE
d. HAVING

B. ORDER BY

Which of the following is not a data type in SQL?
A. VARCHAR
B. NUMBER
C. INDEX
D. DATE

C. INDEX

Which of the following is an example of a Data Definition Language (DDL) statement?
A. SELECT
B. INSERT
C. CREATE
D. UPDATE

C. CREATE

In a database, what does the acronym DML stand for?
A. Data Management Language
B. Database Modification Language
C. Data Manipulation Language
D. Database Modeling Language

C. Data Manipulation Language

A Data Warehouse is typically:
A. OLTP system
B. Normalized database
C. Subject-oriented, integrated, time-variant, non-volatile
D. Real-time data entry system

C. Subject-oriented, integrated, time-variant, non-volatile

The process of cleaning, transforming, and loading data into a warehouse is called:
A. OLTP
B. Data Mining
C. ETL
D. SQL Tuning

C. ETL

“Fact tables” in a Data Warehouse store:
A. Descriptive attributes
B. Historical and quantitative measures
C. Metadata about schemas
D. User access logs

B. Historical and quantitative measures

A Data Mart is:
A. A small operational database
B. A subset of a Data Warehouse designed for a department
C. A replication server
D. A backup copy of a warehouse

B. A subset of a Data Warehouse designed for a department

Data fragmentation in distributed databases means:
A. Breaking data into smaller pieces stored at different locations
B. Duplicating all data at multiple sites
C. Encrypting data
D. Compressing data

A. Breaking data into smaller pieces stored at different locations

Replication in distributed databases refers to:
A. Storing multiple copies of data at different sites
B. Removing unnecessary data
C. Compressing data for storage
D. Merging relational tables

A. Storing multiple copies of data at different sites

Which of the following provides high availability in DDBMS?
A. Deadlock
B. Replication
C. Normalization
D. SQL Views

B. Replication

Write a query to Return the sum of all Quantity fields in the OrderDetails table:

Select sum(quantity) from orderdetails

Write a query to Return the average of the Quantity field for the product with orderID 10248:

Select avg(quantity) from orderdetails
Where orderID=10248

return all the records from the Employee table where the Gender is not a Male

select * from employee where Gender !=”Male”

return all the records from the Employee table where salary is higher than 45000 from IT department

select * from employee where Salary>45000 AND Department=”ID”

Sort the products from highest to lowest price

Select * from product
Order by price desc

Sort the products alphabetically by ProductName

Select * from product
Order by productname ASC

Find the lowest price in the Price column for category 2

Select min(price) from product
Where categoryID=2

Return the average price of products in category 1:

Select avg(price from product)
Where categoryID=1

Let us assume, we have a table “GeeksTab”
SELECT COUNT(Name)
FROM GeeksTab;
Calculate.

4

Let us assume, we have a table “GeeksTab”,
SELECT AVG(Age)
FROM GeeksTab
Where salary >6000;
Calculate

31

Let us assume, we have a table “GeeksTab”
SELECT SUM(Salary)
FROM GeeksTab
Where age>30;
Calculate

12300

What is the Full Function dependency

(C1, C3) → C2, C4, C5

What is Passive dependency

C1 → C2

What is Transitive Dependency?

C4 → C5

A theater shows one or many movies
A movie can be shown in one or many theaters
A movie receives one or more reviews
Each review belongs to one and only one movie
A reviewer writes one or more reviews
Each review can be written by one and only one reviewer

What is DBMS purpose?

Store data
Retrieve data efficiently
Reduce redundancy
Maintain integrity

What are the problems of File System?

Data redundancy
Data inconsistency
No centralized control

What is a Schema

Logical structure of database

What is a Instance

Data at a specific time

In Chen’s Notation, What does the symbol Rectangle mean?

Entity

In Chen’s Notation, What does the symbol Double Rectangle mean?

Weak Entity

In Chen’s Notation, What does the symbol Ellipse mean?

Attribute

In Chen’s Notation, What does the Underlined Attribute mean?

Primary key

In Chen’s Notation, What does the Double Ellipse mean?

Multivalued attribute

In Chen’s Notation, What does the Dashed Ellipse mean?

Derived Attribute

In Chen’s Notation, What does the Diamond mean?

Relationship

In Crowfoot Notation, What does the Rectangle mean?

Entity

In Crowfoot Notation, What does the Double Rectangle mean?

Weak Entity

In Crowfoot Notation, What does the Oval (inside entity) mean?

Attribute

In Crowfoot Notation, What does the PK (or underlined) mean?

Primary Key

In Crowfoot Notation, What does the FK mean?

Foreign Key

In Crowfoot Notation, What does the — mean?

One (Mandatory)

In Crowfoot Notation, What does the O— mean?

Zero or one

In Crowfoot Notation, What does the —< mean?

One to Many one or more

In Crowfoot Notation, What does the O—< mean?

Zero to Many Optional many

What does 1:1 mean

One to one

What does 1:M mean

One to Many

What does M:N mean

Many to Many (needs associative entity)

What does Recursive Entity relate to?

Relates to itself.

What does Many to Many always create?

Bridge table

What is Primary key?

Uniquely identifies record

What is Foreign key?

References another table

What is Composite key

More than one attribute

What is Candidate key?

Possible primary key

What is Super Key?

Any unique attribute combination

In relational model, what is a Table?

Relation

In relational model, what is a Row?

Tuple

In relational model, what is a Column?

Attribute

In relational model, what is a Domain?

Allowed values of attribute

What must every relation have?

Primary key

What is 1NF?

Atomic Values
No repeating groups

What is 2NF?

In 1NF
No Partial Dependency

What is 3NF?

In 2NF
No Transitive Dependency

What is BCNF?

Every determinant is a candidate key

What is Selection in Basic Operations?

Chooses specific rows from table that satisfy a condition

What is Projection in Basic Operations?

Chooses specific columns from a table

What is Union in Basic Operations?

Combines rows from two compatible tables and removes duplicates

What is Intersection in Basic Operations?

Returns only the rows that appear in both tables

What is Difference in Basic Operations?

Returns rows that exist in one table but not in the other

What is Catesian Product (x) in Basic Operations?

Combines every row of one table with every row of another table

What is Partial Dependency?

Depends on part of composite key

What is Transitive Dependency?

Depends on non-key attribute

What is Entity Integrity?

PK cannot be null

What is Referential Integrity

FK must match PK or be NULL

Transaction Management

Process of controlling and organizing database transactions to ensure data is accurate and reliable

Concurrency Control

Prevents conflicts when multiple users access data

What are the problems with Concurrency Control?

Lost update
Uncommitted data
Inconsistent retrival

Transaction Basics

Fundamental concepts of a transaction, which is a sequence of database operations treated as one unit of work that either fully completes or fully fails

A transaction follows ACID properties

Atomicitiy → All or nothing
Consistency → Maintains integrity
Isolation → Transactions dont’t intefere
Durability → Changes are permanent

What is Locking Technique (2PL - Two-Phase Locking)

A concurrency control method where a transaction first acquires all needed locks before releasing any, helping maintain consistency.

What is Shared Lock (Read)?

Allows multiple transactions to read the same data at the same time, but prevents writing

What is Exclusive Lock (Write)?

Allows only one transaction to read/write data while preventing others from accessing it.

What is Timestamp Ordering?

A concurrency control technique that uses timestamps to determine the order transactions should execute

What is Deadlock Handling?

Methods used to detect, prevent, or resolve deadlocks, where transactions wait on each other indefinitely

What are the key features of Distributed DBMS?

Data stored across multiple locations
Appears as a single system
Reliable & scalable

What is Horizontal Fragmentation?

Rows of table are divided across locations

What is Vertical Fragmentation?

Columns of a table are divided across locations

What is Replication?

Copies of the same data stored at multiple sites

What is Location Transparency

Users do not need to know where data is physically stored

What is Replication Transparency

Users do not see multiple copies of data