Database Management Systems

CSET3134B.Tech CSE2023-27

Mahatma Gandhi Central University, Bihar

B.Tech Computer Science & Engineering

Semester 5 Examination, 2025

Database Management Systems (CSET3134)

Faculty: Dr. Shubham KumarMaximum Marks: 95

Assessment Questions

Database Management Systems (CSET3134) - 2025

Section A (MCQ)

(MULTIPLE CHOICE QUESTIONS FOR 5 Marks) Attempt all questions. Each question carries 1 mark.

1
Which of the following operation is not Commutative in relational algebra?
2
Let R and S be two relations with schemas R(A, B, C) and S(C, D, E). If |R| = m and |S| = n, and attribute C has k distinct values in R and p distinct values in S, what is the minimum possible cardinality of R ⋈ S?
3
For relations R(A,B) with 'm' tuples and S(C,D) with 'n' tuples where B and C are foreign key relationships, the maximum number of tuples in R ⋈ S (full outer join) is:
4
If relation R has 4 tuples and relation S has 3 tuples, how many tuples will be in R × S (Cartesian product)?
5
Consider relation R(A, B, C, D, E) with FDs:

F = {AB → C, C → D, D → A, D → E, AE → B}

After computing the minimal cover, how many functional dependencies will remain?

Section B (Short Answer)

(Short Answer Questions - 5 Marks) Attempt ANY TWO questions out of the following. Each question carries 2.5 Marks.

1
(i) Given two relations:
- $R(A,B) = \{(1,2), (3,4), (5,6)\}$
- $S(B,C) = \{(2,'X'), (4,'Y'), (7,'Z')\}$
Perform the following operations and show the results:
- (a) Natural Join(R,S)
- (b) Left-Outer Join(R,S)
(ii) Differentiate between the following:
- (a) Primary Key vs Foreign Key
- (b) SELECT (σ) vs PROJECT (π) operations in relational algebra.
2
Given relations:
- $R(A, B, C) = \{(1, 'x', 10), (2, 'y', 20), (3, 'x', 30), (1, 'z', 15)\}$
- $S(C, D) = \{(10, 'P'), (20, 'Q'), (25, 'R')\}$
- $T(A, D) = \{(1, 'P'), (2, 'Q'), (4, 'P')\}$
Compute:

$\pi_{A,B}(\sigma_{C>15}(R) \bowtie S) - \pi_{A,B}(R \bowtie T)$

Show all intermediate relations clearly.
3
Consider the relation COURSE_ENROLLMENT(StudentID, CourseID, InstructorID, InstructorOffice, CourseName, Grade, Semester) with functional dependencies:
- (CourseID → CourseName, InstructorID)
- (InstructorID → InstructorOffice)
- (StudentID, CourseID, Semester → Grade)
(a) Identify the candidate key(s). Is this relation in BCNF? If not, decompose it into BCNF showing each step of decomposition. Verify that your decomposition is lossless.

(b) Define the Entity Integrity constraint and Referential Integrity constraint. Why are NULL values not allowed in primary keys but allowed in foreign keys? Justify with examples.

Section C (Long Answer)

(Long Answer Questions - 10 Marks) Attempt the questions having internal choice. Each question carries 5 marks.

1
Answer any ONE of the following
(a)
Consider relation $R(A, B, C, D, E, F, G, H)$ with functional dependencies $F$ :

$ABC \rightarrow D$

$D \rightarrow EF$

$EF \rightarrow G$

$G \rightarrow H$

$ACG \rightarrow B$

$H \rightarrow AE$

$D \rightarrow G$

(i) Find the minimal cover of $F$ . Show ALL steps systematically. (3.5 marks)

(ii) Using the minimal cover, determine ALL candidate keys. Show closure computation for verification. (1.5 marks)
OR
(b)
Design an ER diagram for an Online Food Delivery Platform with the following requirements:

Customers can place multiple orders; each order is from exactly one restaurant.

An order contains multiple food items with quantity and special instructions per item.

Restaurants have multiple branches; each branch has its own menu and delivery zones.

Delivery persons are assigned to specific zones and can deliver multiple orders, but one order is delivered by one person.

Each order has a status history (placed, confirmed, preparing, dispatched, delivered) with timestamps.

Customers can rate both restaurants (overall) and specific orders separately, with comments.

Restaurants offer combo deals where a combo consists of multiple items; track combo-specific pricing.

(i) Draw a complete ER diagram showing all entities, relationships, cardinalities, participation constraints, and key attributes. Use proper notation for weak entities if needed. (3 marks)

(ii) Convert your ER diagram to a relational schema. Show all primary keys, foreign keys, and explain how you handled any M:N or multi-valued attributes. (2 marks)
2
Answer any ONE of the following
(a)
Database schema:

SUPPLIER(SID, SName, City, Country, Rating)

PART(PID, PName, Color, Weight, Type)

CATALOG(SID, PID, Cost, AvailableQty)

Write optimized relational algebra expressions:

(i) Find SIDs of suppliers who supply ALL parts of type 'Electronic' at cost less than 1000. (1.5 marks)

(ii) Find pairs (SID1, SID2) where both suppliers are from the same country, supply at least one common part, but SID1 has a higher rating than SID2. Ensure no duplicate pairs. (2 marks)

(iii) Find suppliers who supply parts in ALL three colors (Red, Blue, Green) but do NOT supply any part weighing more than 50 units. (1.5 marks)
OR
(b)
Consider relations:

RESEARCHER(RID, RName, Affiliation, HIndex)

PAPER(PID, Title, ConferenceName, Year, Citations)

AUTHORSHIP(RID, PID, AuthorPosition)

CITATION(CitingPID, CitedPID) — tracks which paper cites which

(i) Find RIDs of researchers who have published papers at BOTH 'ICACCI' and 'INDICON' conferences in the same year. (1.5 marks)

(ii) Find papers that have been cited by at least 5 different papers, all from conferences in year 2024. Express using relational algebra with self-joins and aggregation concepts. (2 marks)

(iii) Find researcher pairs (RID1, RID2) who have co-authored together on at least 3 papers, where RID1 was first author (AuthorPosition=1) and RID2 was second author (AuthorPosition=2). (1.5 marks)

End of Question Paper