Week 3: Assignment 3 - Database Management System (DBMS) Description: This assignment covers various fundamental concepts in database manag...
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:
sqlSELECT 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:
sqlLOOP: 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.
COMMENTS