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

Week 3: Assignment 3 - Database Management System (DBMS)

Description: This assignment covers various fundamental concepts in database management systems, including database recovery techniques, relational algebra, SQL queries, and Entity-Relationship Diagrams (ERD). Each question evaluates the understanding of these concepts through multiple-choice questions that require identifying the correct SQL queries, interpreting ER diagrams, and applying relational algebra.

---

Question 1:

Identify the case below in which a trigger is not preferred to use.

a) Loading data from a backup copy.
b) Maintaining summary data.
c) Replicating updates in a master-slave setup.
d) Enforcing database integrity.

Answer: a) Loading data from a backup copy.
Reason: Triggers are typically used for enforcing business rules, maintaining integrity, or automating repetitive tasks. Using a trigger during data loading from a backup can cause unwanted overhead and is generally not preferred.

---

Question 2:

Identify the correct Embedded SQL syntax for the following:

Find the names of all students whose marks are between 50 and 80 in each subject where total marks and each mark are declared in the host language.

a) EXEC SQL SELECT sname FROM students WHERE marks BETWEEN 50 AND 80;
b) EXEC SQL SELECT sname INTO :sname FROM students WHERE marks BETWEEN 50 AND 80;
c) EXEC SQL SELECT sname INTO :sname FROM students WHERE marks BETWEEN :lmark AND :umark;
d) EXEC SQL SELECT sname INTO :sname FROM students WHERE marks BETWEEN :lmark AND umark;

Answer: c) EXEC SQL SELECT sname INTO :sname FROM students WHERE marks BETWEEN :lmark AND :umark;
Reason: This syntax correctly uses placeholders for the lower and upper marks (:lmark and :umark), allowing the SQL statement to be embedded in a host programming language.

---

Question 3:

Consider the following Entity Relationship Diagram:

Song
SID
SName
Title
Key1
Key2
Key3
Music

Which of the following schema is equivalent to the entity Song?

a) Song(SID, SName, Key1, Key2, Key3, Music)
b) Song(SID, SName, Title, Key1, Key2, Key3, Music)
c) Song(SID, SName, Title, Music)
d) Song(SID, SName, Title, Key1, Music)

Answer: b) Song(SID, SName, Title, Key1, Key2, Key3, Music)
Reason: This option includes all attributes of the Song entity as shown in the ER diagram, making it the equivalent schema.

---

Question 4:

Consider the following instance of a relational PowerShop(Name, Location, Opensat, Closesat, Reviews):

Name	Location	Opensat	Closesat	Reviews
PowerGoods	Area	10am	8pm	6
PowerFoods	Place	11am	9pm	8
PowerStation	Park	12pm	10pm	7

Suppose, you are interested in selecting all shop names that open after 10 am. The appropriate SQL query is:

sql
SELECT Name
FROM PowerShop
WHERE Opensat > '10am';

What will be the result?

a) PowerFoods, PowerStation
b) PowerGoods, PowerFoods, PowerStation
c) PowerFoods, PowerGoods
d) None of these

Answer: a) PowerFoods, PowerStation
Reason: The SQL query selects shops with opening times after 10 am. PowerFoods opens at 11 am, and PowerStation opens at 12 pm, both after 10 am.

---

Question 5:

Consider the following instance of the relation Garden(Name, Location, Species, Closenat, Opensat):

Name	Location	Species	Closenat	Opensat
PlantHouse	CityArea	Flowers	10pm	7am
TreeHub	CityPark	Trees	9pm	6am
FlowerPlace	CityLane	Flowers	8pm	8am
TreeHouse	CityBase	Trees	9pm	7am

What is the result of the following Relational Algebra:

sql
π(Name, Location)(σ(Species='Trees')(Garden))

a) PlantHouse
b) TreeHouse
c) TreeHub
d) TreeHub, TreeHouse

Answer: d) TreeHub, TreeHouse
Reason: The relational algebra query selects the Name and Location of gardens where the Species is Trees. TreeHub and TreeHouse satisfy this condition.

---

Question 6:

Consider the following Entity Relationship Diagram:

Singer
SID
SName

Lead
SID
Singer_name

Background
SID
Singer_name

Which of the following statements is (are) correct?

a) Participation of Singer in Lead is total.
b) Participation of Singer in Background is total.
c) Lead and Background entity sets are disjoint specializations of Singer entity set.
d) Lead and Background entity sets are overlapping specializations of Singer entity set.

Answer: c) Lead and Background entity sets are disjoint specializations of Singer entity set.
Reason: Since a singer can either lead or be in the background, these are disjoint specializations.

---

Question 7:

Consider the relation COURSE(CID, Name, Specialization):

CID	Name	Specialization
101	DBMS	Data
102	OS	Systems
103	AI	Intelligence
104	Cloud	Networks

Which of the following Relational Algebra expressions produces exactly the same tuples as present in the instance of COURSE?

a) π(CID, Name)(COURSE) ⨝ π(CID, Specialization)(COURSE)
b) π(CID, Name)(COURSE) ⨝ π(CID, Specialization)(COURSE)
c) π(CID, Specialization)(COURSE) ⨝ π(CID, Name)(COURSE)
d) π(CID, Specialization)(COURSE) ⨝ π(CID, Name)(COURSE)

Answer: d) π(CID, Specialization)(COURSE) ⨝ π(CID, Name)(COURSE)
Reason: This operation joins the selected attributes from the COURSE relation and retrieves all tuples exactly as they are in the original COURSE table.

---

Question 8:

Consider the relation Tender(TNo, Price). Assume that there is a tuple (X, 20000) in the given relation. Identify the final value of x after the execution of the following loop:

sql
LOOP: X = 0;
FOR I IN 1..1000 LOOP
    X := X + 2;
END LOOP;

a) 20000
b) 2000
c) 200000
d) 2000

Answer: d) 2000
Reason: The loop iterates 1000 times, each time adding 2 to X, starting from 0. Thus, the final value of X will be 2000.