Database Management System: Assignment 3

Total Marks : 20

January 31, 2024

Question 1
Marks: 2 MCQ
Consider the following instances:
University
UName Branch Capacity Fees
JFTT Bangalore 15000 200000
KSSL Bangalore 40000 500000
JFTT Jalandhar 5000 200000
LKUniversity Kolkata 2000 100000
LKUniversity Mumbai 2000 150000
Consider the Relational Algebra on these given instances:
ΠUName,Branch,Capacity (University) ÷ (ΠBranch,Capacity (σFees<=200000 University) ∩
ΠBranch,Capacity (σCapacity>2000 University))

What is the output of the Relational Algebra?

a) JFTT.

b) KSSL.

c) LKUniversity.

d) No row will be selected.

Answer: a)
Explanation: As per the syntax and semantics of Relational Algebra.
Hence, option (a) is correct.

1
Question 2
Marks: 2 MCQ
Consider the following instances:
Dishes
Name Price Rating
Dips
Pasta 800 5
Name Price Rating
Fajita 500 4
Cheese 200 5
Pizza 1000 4
Hummus 150 5
Pasta 200 5
Salsa 80 4
Pasta 150 5
Cheesecake 800 5
How many tuples are returned by the following Relational Algebra?
ΠDips.N ame (Dips ∩ (Dips 1 (σDips1.Rating<5∨Dips1.P rice≤80 (ρDips1 (Dips)))))

a) 1

b) 2

c) 3

d) 4

Answer: a)
Explanation: The Relational Algebra returns Salsa as output.
Hence, option (a) is correct.

2
Question 3
Marks: 2 MCQ
Consider the relational schema Sensor(SensorID, Battery, BaseStationID, Range).
Choose the correct Tuple Relational Calculus that represents the following statement
“Display all the SensorIDs associated with BaseStationID BS1.”

a) {s|t < t[BaseStationID]=‘BS1’>}

b) {<s, t>|∃s ∈ Sensor (p[BaseStationID]=‘BS1’)}

c) {t|∃s ∈ Sensor (t[SensorID]=s[BaseStationID] ∧ t[BaseStationID]=‘BS1’)}

d) {t|∃s ∈ Sensor (t[SensorID]=s[SensorID] ∧ s[BaseStationID]=‘BS1’)}

Answer: d)
Explanation: The tuple to be selected is represented by ‘t’ and the selection conditions are
written with ∧ as per the given question. According to the projection and selection operations
shown in lecture slides 12.26 - 12.28, option (d) shows the correct syntax and semantics of the
Tuple Relational Calculus.

3
Question 4
Marks: 2 MCQ
Consider the relational schema Sensor(SensorID, Battery, BaseStationID, Range).
Choose the correct Domain Relational Calculus equivalent to the following SQL query
SELECT Range FROM Sensor WHERE Battery=‘500’

a) {s|∃c, d, i (c, d, i, s ∈ Sensor ∧ Battery=‘500’)}

b) {<s>|∃c, d, i (<c, d, i, s> ∈ Sensor ∧ d=‘500’)}

c) {<s>|∃c, d, i (<c, d, i> ∈ s ∧ Battery=‘500’)}

d) {s|∃<c, d, i> (c, d, i, s ∈ Sensor ∧ d=‘500’)}

Answer: b)
Explanation: According to the syntax and semantics of Domain Relational Algebra shown
in lecture slides 12.26-12.28.

4
Question 5
Marks: 2 MCQ
A C program, with embedded SQL query allows the users to enter their Year of Birth and
Country which are stored in variables yob and cou respectively. The SQL command returns
the counts of those all other people born in the same year and country from Person(id,
Birth, Country).
Which of the following SQL queries is correct for the purpose?

a) EXEC SQL
DECLARE c CURSOR AS
SELECT id
FROM Person
WHERE Birth==yob AND Country==cou
END EXEC

b) EXEC SQL
DECLARE c CURSOR FOR
SELECT count(id)
FROM Person
WHERE :Birth= :yob & :Country=:cou
END EXEC

c) EXEC SQL
DECLARE c CURSOR AS
SELECT id
FROM Person
WHERE :Birth==yob & :Country==cou
END EXEC

d) EXEC SQL
DECLARE c CURSOR FOR
SELECT count(id)
FROM Person
WHERE Birth=:yob AND Country=:cou
END EXEC

Answer: d)
Explanation: As per the syntax and semantics of embedded SQL, option (d) is correct.

5
Question 6
Marks: 2 MCQ
A company maintains a schema of Reports where each report is identified by a Heading. The
reports are also associated with corresponding a Date and Length. Moreover, each report
can be written by multiple Authors. Which of the following schema correctly represents the
Reports entity set?

a) Reports(Heading, Date, Length, Authors)

b) Reports(Heading, Date, Length), Reports1(Heading, Authors)

c) Reports(Heading, Date, Length), Reports1(Heading, Authors)

d) Reports(Heading, Date, Length), Reports1(Heading, Authors)

Answer: d)
Explanation: Multi valued attributes like Authors should be placed in a separate schema
where it is used as an attribute of the composite primary key, along with the unique identifier.
Hence, option (d) is correct.

6
Question 7
Marks: 2 MCQ
Consider the Entity Relationship Diagram

Which of the following is true?

a) The schema for the Device entity will be Device(Model ID, Color) and
DeviceSensor(Model ID, SensorName).

b) The schema for the Dev Owner will be Dev Owner(Model ID, OID).

c) The schema for the Owner will be Owner(OID, Model ID, Name, Address).

d) The schema for the Device entity will be Device(Model ID) and
DeviceSensor(SensorName, Color).

Answer: b)
A multi valued attribute is placed in a separate schema with the primary attribute of the entity.
Hence, options (a) and (d) are not true. Similarly, the schema for Owner will be Owner(OID,
Name, Address). Thus, option (b) is correct.

7
Question 8
Marks: 2 MCQ
Consider the Entity Relationship Diagram

Which of the following is true?

a) Participation of College in University is total.

b) Participation of College in Enrolled is total.

c) Participation of Student in University is partial.

d) Participation of Uni Stu in Student is partial.

Answer: b)
Total participation of an entity in a relation is indicated by a double line whereas partial
participation (default) is indicated by single lines. Hence, option (b) is correct.

8
Question 9
Marks: 2 MSQ
Consider the Entity Relationship Diagram

Which of the following is false?

a) The schema of University is University(UName, Area, Location).

b) The schema of University is University(UName, Area, Location, Departments, Employees).

c) The schema of Technical is Technical(UName, Departments, Employees).

d) The schema of Technical are Technical1(UName, Departments) and

Answer: b), d)
Refer to week 3, slide 15.11. Hence, options (b) and (d) are the answer.

9
Question 10
Marks: 2 MCQ
An organization, collecting car renters’ information, considers the following relations:
Renter(Renter ID, Contact)
Renting(Renter ID, Car Number)
Car(Car Number, Model).

What will the following relational algebra expression return to the organization?
ΠCar N umber (Car) − ΠCar N umber (Renter 1 Renting)

a) The Car Number of those Cars that are rented by at most one Renter.

b) The Car Number of those Cars that are rented by at least one Renter.

c) The Car Number of those Cars that are rented by all Renters.

d) The Car Number of those Cars that are not rented by any Renter.

Answer: d)
As per the syntax and semantics of Relational Algebra Queries. Hence, option (d) is correct.

10