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

The NPTEL Database Management System course provides a comprehensive understanding of database systems, including SQL queries, database design, and data manipulation. In Week 2, the assignment focuses on practical applications of SQL queries to solve real-world database problems. Here are the detailed solutions and explanations for each question in the assignment.Week 2: Assignment 2

Question 1

Question:
In a particular company application, the instances of ClassTeacher to HostelAddress are as follows:

ClassTeacherID	HostelAddressID
1101	12
1202	12
1303	13
1404	14
1505	15

We need to maintain the instances for four class teachers and five hostel addresses. Write the SQL query to output the ClassTeacherID, and HostelAddressID from the above data.

Answer:

sql
SELECT ClassTeacherID, HostelAddressID
FROM ClassTeacher
JOIN HostelAddress
ON ClassTeacher.HostelAddressID = HostelAddress.HostelAddressID;

Reason:
This query selects the ClassTeacherID and HostelAddressID from the joined tables ClassTeacher and HostelAddress based on the matching HostelAddressID.

---

Question 2

Question:
In a particular company application, the instances of ClassTeacher and TreeType are as follows:

ClassTeacherID	TreeTypeID
1101	5
1202	4
1303	3
1404	2
1505	1

We need to maintain the instances for five class teachers and five tree types. Write the SQL query to output the ClassTeacherID, and TreeTypeID from the above data.

Answer:

sql
SELECT ClassTeacherID, TreeTypeID
FROM ClassTeacher
JOIN TreeType
ON ClassTeacher.TreeTypeID = TreeType.TreeTypeID;

Reason:
This query selects the ClassTeacherID and TreeTypeID from the joined tables ClassTeacher and TreeType based on the matching TreeTypeID.

---

Question 3

Question:
Which of the following scenarios is incorrect?

a) ATM transaction record is infrequently modified once it is written.
b) E-commerce record is infrequently modified once it is written.
c) OTP transaction record is valid for less than a minute.
d) None of the above.

Answer:
d) None of the above.

Reason:
All the given scenarios are correct descriptions of their respective contexts. ATM transactions and e-commerce records are typically not modified frequently, and OTP transactions are indeed valid for a very short duration.

---

Question 4

Question:
Consider the data instances:

BoardingID	FoodItemID	TotalItems	TotalCost
001	102	10	500
002	103	20	1000
003	104	15	750
004	105	25	1250
005	106	30	1500

Write the SQL query to output the BoardingID, FoodItemID, TotalItems, and TotalCost from the above data.

Answer:

sql
SELECT BoardingID, FoodItemID, TotalItems, TotalCost
FROM Boarding
JOIN FoodItem
ON Boarding.FoodItemID = FoodItem.FoodItemID;

Reason:
This query selects the BoardingID, FoodItemID, TotalItems, and TotalCost from the joined tables Boarding and FoodItem based on the matching FoodItemID.

---

Question 5

Question:
Consider the following instance of BranchDetails and BranchName relations:

BranchID	BranchName
1	Alpha
2	Beta
3	Gamma
4	Delta

What will be the output of the following query?

sql
SELECT BranchID
FROM BranchDetails
WHERE BranchName = 'Delta';

Answer:
4

Reason:
The query selects the BranchID from BranchDetails where the BranchName is 'Delta'. Since BranchName 'Delta' corresponds to BranchID 4, the output will be 4.

---

Question 6

Question:
Consider the following instance of NewRecords and NewAddress relations:

RecordID	NewRecordID	TotalCost
001	101	500
002	102	1000
003	103	750
004	104	1250

Identify the correct statement to display the RecordID and Address of NewRecords instance based on NewAddress relation.

Answer:

sql
SELECT RecordID, Address
FROM NewRecords
JOIN NewAddress
ON NewRecords.NewRecordID = NewAddress.NewRecordID;

Reason:
This query selects the RecordID and Address from the joined tables NewRecords and NewAddress based on the matching NewRecordID.

---

Question 7

Question:
Consider the following instance of NewRecords of a company application:

RecordID	NewRecordID	TotalCost
001	201	500
002	202	1000
003	203	750
004	204	1250

Identify the correct statement to display RecordID, NewRecordID, and TotalCost from the NewRecords instance.

Answer:

sql
SELECT RecordID, NewRecordID, TotalCost
FROM NewRecords;

Reason:
This query simply selects the RecordID, NewRecordID, and TotalCost from the NewRecords table.

Conclusion

The Week 2 assignment in the NPTEL Database Management System course effectively reinforces the understanding of SQL query construction and table relationships. By solving these practical problems, students gain valuable skills in data manipulation and retrieval, essential for any database management role.