DBMS MID-2 2 MARKS

UNIT-3 PART-B
1. What is a “Foreign Key ” explain with an appropriate
example
1A) A Foreign Key is a column or a group of columns in a
table that links to the primary key of another table. It is
used to maintain referential integrity between the two
tables, ensuring that the value in the foreign key column
matches a value in the referenced table’s primary key
column.
Example:
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE,
FOREIGN KEY (CustomerID) REFERENCES
Customers(CustomerID)
);
In this example, CustomerID in the Orders table is a
foreign key that references the CustomerID in the
Customers table.

2. List all Aggregate Functions

2A) Aggregate functions perform calculations on a set of
values and return a single result. The common aggregate
functions in SQL are:
1. COUNT(): Returns the number of rows in a set.
2. SUM(): Returns the sum of a set of values.
3. AVG(): Returns the average of a set of values.
4. MIN(): Returns the smallest value in a set.
5. MAX(): Returns the largest value in a set.

3. State the purpose of ‘ORDER BY’ keyword and write the

detailed syntax.
3A) The ORDER BY keyword is used to sort the result set
of a query by one or more columns. It can sort data in
ascending (default) or descending order.
Syntax:
SELECT column_name(s)
FROM table_name
ORDER BY column_name [ASC | DESC];
Example:
SELECT Name, Age
FROM Employees
ORDER BY Age DESC;
This query sorts the Employees table by Age in
descending order.

4. State the purpose of ‘GROUP BY’ keyword and write it’s

syntax.
4A) The GROUP BY keyword is used to group rows that
have the same values into summary rows. It is often used
with aggregate functions like COUNT(), SUM(), AVG(), etc.,
to perform operations on each group.
Syntax:
SELECT column_name(s),
aggregate_function(column_name)
FROM table_name
GROUP BY column_name(s);
Example
SELECT DeptID, COUNT(*)
FROM Employees
GROUP BY DeptID;
This query groups the employees by DeptID and returns
the number of employees in each department.

5. List all Relational Set operators

5A) The relational set operators in SQL are:
1. UNION: Combines the result sets of two queries,
eliminating duplicates.
2. INTERSECT: Returns only the common rows between
two queries.
3. EXCEPT (or MINUS in some DBMS): Returns rows from
the first query that are not present in the second query.
4. UNION ALL: Combines the result sets of two queries,
including duplicates.

6)What is a View in SQL? write it’s syntax.

6A) A View is a virtual table based on the result of a
SELECT query. It does not store data itself but displays
data stored in one or more tables.
Syntax:
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Example:
CREATE VIEW EmployeeView AS
SELECT Name, Age
FROM Employees
WHERE Age > 30;
This creates a view EmployeeView that shows
employees older than 30 years.
 7)What is an Updated View
7A) An Updated View is a view that is modifiable,
allowing you to perform INSERT, UPDATE, or DELETE
operations on the underlying tables through the view.
Not all views are updatable; it depends on the query
structure.
8. List the different types in Ordering
8A) There are two main types of ordering in SQL:
1. Ascending (ASC): Orders data from smallest to largest
(default).
2. Descending (DESC): Orders data from largest to
smallest.

9. Whatis Intersect operation on two data sets

9A) The INTERSECT operator returns only the rows that are
common between two data sets. It removes duplicates and
returns distinct rows.
Example:
SELECT Name FROM Employees
INTERSECT
SELECT Name FROM Managers;
This returns the names that are both in Employees and
Managers tables.
10. Write short note on EXCEPT condition
10A) The EXCEPT (or MINUS in some databases) operator
returns rows from the first query that are not present in the
second query. It eliminates duplicates.
Example:
SELECT Name FROM Employees
EXCEPT
SELECT Name FROM Managers;
This returns the names of employees who are not
managers.

11. What is Union operation on two data sets

11A) The UNION operator combines the results of two
queries into a single result set and eliminates duplicate
rows.
Example:
SELECT Name FROM Employees
UNION
SELECT Name FROM Managers;
This query returns all unique names from both the
Employees and Managers tables.

12. Write a short note on Nested Query

12A) A Nested Query (or subquery) is a query inside another
query. It is used to perform operations that require
intermediate results, allowing one query to depend on
another.
Example
SELECT Name
FROM Employees
WHERE DeptID = (SELECT DeptID FROM Departments
WHERE DeptName = 'IT');
This query selects employees who belong to the IT
department by using a nested query to find the DeptID of
the IT department.

UNIT-4 2 MARKS
1. Expand BCNF
1A) BCNF stands for Boyce-Codd Normal Form — a type of
database normalization used to reduce redundancy and
avoid anomalies.

2. Expand MVD
2A) MVD stands for Multivalued Dependency — it occurs
when one attribute in a table uniquely determines
another attribute, independent of all others.
3. What is a Functional Dependency
3A) A Functional Dependency is a relationship where one
attribute uniquely determines another attribute.
Example: If A → B, then knowing A means you can
determine B.
4. What is a Closure explain with an Example
4A) The closure of a set of attributes is the set of all
attributes that can be functionally determined from it.
Example:
If A → B and B → C, then Closure(A) = {A, B, C}.

5. List the advantages of Normalization

5A)
 Reduces data redundancy.
 Avoids data anomalies.
 Improves data integrity.
 Makes database design efficient.

6. What is the other name of BCNF

6A) BCNF is sometimes referred to as a stronger version of
the Third Normal Form (3NF).
7. State the Rule of First Normal Form
7A) A table is in First Normal Form (1NF) if all its columns
contain atomic (indivisible) values, and each record is
unique.
8. What is Strict Normal Form
8A) Strict Normal Form usually refers to the Boyce-Codd
Normal Form (BCNF), where even slight redundancy is
removed.
9. What is the Rule for 2 nd Normal Form
9A) A table is in Second Normal Form (2NF) if:
 It is in 1NF.
 No partial dependency exists (non-prime attributes
must fully depend on the primary key).

10. What is the Rule for 3rd Normal Form

10A) A table is in Third Normal Form (3NF) if:
 It is in 2NF.
 No transitive dependency exists (non-prime attributes
must depend only on the primary key).

11. What is Transitive Dependency

11A) A Transitive Dependency occurs when one non-
prime attribute depends on another non-prime attribute
rather than directly on the primary key.
 Example:
If A → B and B → C, then C is transitively dependent on A.

[Link] is a Prime Attribute

12A) A Prime Attribute is an attribute that is part of any
candidate key of a relation.
13) What is a Non-Prime Attribute
13A) A Non-Prime Attribute is an attribute that is not part of
any candidate key.

14). What is De-Normalization

14A) De-Normalization is the process of intentionally
introducing redundancy into a database for performance
optimization purposes.
15),What is a Super Key
15A) A Super Key is any set of attributes that uniquely
identifies a record in a table.
Example:
{RollNo, Name} is a super key if RollNo uniquely identifies a
student.

16)What is a Candidate Key

16A) A Candidate Key is the minimal set of attributes that
can uniquely identify a tuple in a table.
It is a minimal super key.
17)What is a Foreign Key
17A) A Foreign Key is a field (or collection of fields) in one
table that refers to the primary key in another table,
maintaining referential integrity.

18)What is a Surrogate Key

18A) A Surrogate Key is an artificial key assigned to each
record, usually a number, that uniquely identifies a record
without using real-world data.
Example:
An auto-incremented ID field.

UNIT-5 2 MARKS
Unit-V-Short Questions
1. Define Transaction
1A) A Transaction is a logical unit of work that must
be either completely completed or aborted. It
consists of a sequence of operations performed as a
single logical unit.

2. What is Atomicity
 2A) Atomicity ensures that either all operations of a
transaction are completed or none of them are.
There is no partial execution.
3. What is Consistency
3A) Consistency ensures that a transaction brings the
database from one valid state to another valid state,
maintaining database rules like constraints.
4. What is Isolation
4A) Isolation means that concurrent transactions are
executed independently, without interfering with
each other’s operations and results.

5. What is Durability
5A) Durability ensures that once a transaction
commits, its changes are permanently stored in the
database, even if a system failure occurs.
6. Discuss Validation Phase
6A) The Validation Phase in optimistic concurrency
control verifies if the transaction can commit
without violating consistency, by checking if it
conflicts with other transactions.

7. List all Lock Based Protocols

 7A) Two-Phase Locking (2PL)
 Strict Two-Phase Locking
 Conservative Two-Phase Locking
 Rigorous Two-Phase Locking

8. List the types of failures

 8A) Transaction Failures
 System Crash
 Disk Failure
 Communication Errors

9. Distinguish Shared Lock and Exclusive Lock

 9A) Shared Lock (S-lock): Allows multiple transactions
to read but not modify the item.
 Exclusive Lock (X-lock): Allows only one transaction to
read and modify the item.

10. List various anomalies that arise due to

parallel execution of transactions
 10A) Lost Update
 Temporary Update (Dirty Read)
 Incorrect Summary
 Unrepeatable Read
 11. Write about recovery facilities in transaction
management.
11A) Recovery facilities include:
 Log-Based Recovery
 Checkpoints
 Shadow Paging
They help restore the database to a consistent state
after a failure.

12. Write about wait die scheme in transaction

management.
12A) In Wait-Die Scheme, if an older transaction
requests a lock held by a younger one, it waits;
otherwise, it is aborted ("dies") and restarted later.
13. What is a Conflict
13A) A Conflict occurs when two transactions try to
access the same data item, and at least one of them
is performing a write operation.
14. What is Dirty Read in concurrent execution
14A) A Dirty Read happens when a transaction reads
data written by another uncommitted transaction,
which may later be rolled back.

15. Write about wound wait scheme in

transaction management.
15A) In Wound-Wait Scheme, if an older transaction
requests a lock held by a younger one, it forces the
younger one to abort ("wound"); otherwise, the
older transaction waits.