NPTEL Data Base Management System Week 4 Assignment Answers 2024 (July-October)

 NPTEL Week 4 Assignment Solutions

provides the solutions for Week 4 of the NPTEL course. Each question has been carefully analyzed, and the correct answer is selected along with a detailed explanation for each. The topics covered include functional dependencies, normalization forms, candidate keys, canonical covers, and decomposition of relations.

Question 1: Find the functional dependencies that stand valid on the part of the relation shown below:

R	P	Q
A	1	C
B	2	C
A	1	C
B	2	C

Options: a) $P \rightarrow Q, P \rightarrow R$
b) $Q \rightarrow P, R \rightarrow P$
c) $P \rightarrow R, R \rightarrow P, P \rightarrow R$
d) $P \rightarrow Q, R \rightarrow P, R \rightarrow P$

Answer: a) $P \rightarrow Q, P \rightarrow R$

Explanation: From the table, it's clear that for each unique value of $P$, the values of $Q$ and $R$ are consistent. This means that $P$ can determine $Q$ and $R$, so the functional dependency $P \rightarrow Q, P \rightarrow R$ is valid.

---

Question 2: Consider the relation Email(Gender, Receiver, Date, Time, Subject) with the following functional dependencies:

• FD1: $\text{(Receiver, Date)} \rightarrow \text{Time}$
• FD2: $\text{(Receiver, Date)} \rightarrow \text{Subject}$
• FD3: $\text{Receiver} \rightarrow \text{Subject}$

What is the highest normal form for the given relation?

Options: a) 1 NF
b) 2 NF
c) 3 NF
d) BCNF

Answer: b) 2 NF

Explanation: The relation is in 2NF because it does not have any partial dependencies (a non-prime attribute is not dependent on a part of a candidate key). However, since there is a transitive dependency (Receiver → Subject), it is not in 3NF or BCNF.

---

Question 3: Consider the relation Office(OID, Emp, Branch, Dept) with the following functional dependencies:

• FD1: $(\text{OID}, \text{Emp}) \rightarrow \text{Branch}$
• FD2: $\text{Branch} \rightarrow \text{Dept}$
• FD3: $\text{Dept} \rightarrow \text{OID}$

How many candidate keys are there for the given relation?

Options: a) 1
b) 2
c) 3
d) 4

Answer: c) 3

Explanation: The candidate keys for the relation can be determined by examining the dependencies and possible keys. The keys that satisfy all functional dependencies are combinations of attributes that can uniquely identify a tuple in the relation.

---

Question 4: Consider the relation TICKET(PNR, DEPARTURE, ARRIVAL, SEATNO, COACHNO) with the following functional dependencies:

• FD1: $(\text{PNR}, \text{DEPARTURE}) \rightarrow \text{ARRIVAL}$
• FD2: $\text{ARRIVAL} \rightarrow \text{SEATNO}$
• FD3: $(\text{DEPARTURE}, \text{SEATNO}) \rightarrow \text{COACHNO}$

Which of the following attributes cannot functionally determine COACHNO?

Options: a) ARRIVAL
b) $(\text{PNR}, \text{DEPARTURE})$
c) $(\text{PNR}, \text{DEPARTURE}, \text{ARRIVAL})$
d) $(\text{DEPARTURE}, \text{ARRIVAL})$

Answer: a) ARRIVAL

Explanation: According to the given functional dependencies, ARRIVAL alone does not have any direct influence on COACHNO. Therefore, ARRIVAL cannot functionally determine COACHNO.

---

Question 5: Find the canonical cover of Relation R={A, B, C} where P={A→BC, B→C, A→B, AB→C}?

Options: a) $A \rightarrow BC$
b) $A \rightarrow B, B \rightarrow C$
c) $A \rightarrow B, AB \rightarrow C$
d) $A \rightarrow B, AB \rightarrow C$

Answer: b) $A \rightarrow B, B \rightarrow C$

Explanation: The canonical cover is a minimal set of functional dependencies that preserves the closure of the original set. In this case, the minimal set is $A \rightarrow B$ and $B \rightarrow C$.

---

Question 6: Consider the following relational table FRUIT_STORE:

F-ID	E-Name	Category	Price	Owner
1281	Apple	Uttar Pradesh	100	W. Hauna
1282	Mango	Uttar Pradesh	150	C. Buck
1283	Mango	West Bengal	150	K. Bhardwaj
1284	Banana	Tamil Nadu	50	O. Kozmkov

If (F-Name, Owner) is a key for this instance, what may be the value of X?

Options: a) W. Hauna
b) C. Buck
c) W. Hauna or K. Bhardwaj
d) O. Kozmkov

Answer: c) W. Hauna or K. Bhardwaj

Explanation: Given that (F-Name, Owner) is a key, there could be multiple valid combinations of F-Name and Owner. The only options that match the provided data are W. Hauna and K. Bhardwaj, who own Apple and Mango, respectively.

---

Question 7: Consider a relation Customer(CID, NAME, ADDRESS) with the following functional dependencies:

• FD1: $\text{CID} \rightarrow \text{NAME}$
• FD2: $\text{NAME} \rightarrow \text{ADDRESS}$

The current instance of the schema contains the tuple (121, Lucy, Delhi). Which of the following tuples can be inserted?

Options: a) (256, Tom, Kolkata)
b) (137, Lucy, Delhi)
c) (233, Lucy, Mumbai)
d) (121, Jenny, Kolkata)

Answer: a) (256, Tom, Kolkata)

Explanation: To maintain the functional dependencies, the tuple with CID 121 must have the same NAME and ADDRESS. Therefore, the tuple with different NAME and ADDRESS can only be inserted if CID is different.

---

Question 8: The relation R(A, B, C, D, E, F, G) with the functional dependencies: {A → B, A → C, AE → D, BE → C, D → G} is normalized into BCNF. Which of the following decomposition is obtained as a result of this normalization?

Options: a) R(A, B, E, F), R(C, D, G)
b) R(A, C, D), R(A, E, D, F)
c) R(A, B, C, E), R(A, C, D, G)
d) $\text{None of the above}$

Answer: b) R(A, C, D), R(A, E, D, F)

Explanation: The BCNF decomposition will ensure that all resulting relations have candidate keys as their sole determinants. The provided functional dependencies indicate that the second option aligns with the correct decomposition.

---

Question 9: Consider a relation STUDENT(Name, Subject, Location, Marks).

Name	Subject	Location	Marks
John	Comp	NY	96
John	DBMS	NY	100

STUDENT is decomposed into the following:

1. STUD1(Name, Subject, Location) and STUD2(Name, Subject, Marks)
2. STUD1(Name, Location) and STUD2(Subject, Marks)

Which of the following is TRUE?

Options: a) 1 is lossless but 2 lossy.
b) 1 is lossy but 2 lossless.
c) Both 1 and 2 are lossless.
d) Both 1 and 2 are lossy.

Answer: a) 1 is lossless but 2 lossy.

Explanation: The decomposition 1 maintains the original relation by preserving the information, making it lossless. The second decomposition loses information about the relationship between the Name and Subject attributes, making it lossy.

---

Question 10: Consider the relation DVDLibrary(Name, Company, Format, Price) with the following functional dependencies:

• FD1: $\text{(Name, Company)} \rightarrow (\text{Format, Price})$
• FD2: $\text{Company} \rightarrow \text{Format}$
• FD3: $\text{Format} \rightarrow \text{Price}$

Which of the following statements is (are) true?

Options: a) The primary key for DVDLibrary is Company.
b) DVDLibrary is in 2NF.
c) Decomposition of DVDLibrary into DVDLibrary1(Name, Company, Format), DVDLibrary2(Company, Price) will be dependency-preserving.
d) Decomposition of DVDLibrary into DVDLibrary1(Name, Company, Format), DVDLibrary2(Company, Price) will be lossless.

Answer: b) DVDLibrary is in 2NF.

Explanation: The relation DVDLibrary is in 2NF because there are no partial dependencies with respect to any candidate key.

---

These answers cover the questions from Week 4 Assignment with a detailed explanation of each solution, ensuring clarity in understanding.