Database Management System: Assignment 1

Total Marks : 20

July 6, 2023

Question 1
Which of the following statements is (are) correct? Marks: 2 MSQ

a) Physical level abstraction describes how a record is stored.

b) View level abstraction hides details of data types.

c) Physical level abstraction describes data stored in a database and their relationships.

d) Logical level abstraction defines the physical schema.

Answer: a), b)
Explanation: As per the lecture notes (Module 02: Introduction to DBMS/1).
Physical level: describes how a record is stored.
Logical level: describes data stored in the database, and the relationships among the data.
View level: application programs hide details of data types.

1
Question 2
Consider the following relations:
Subject(sid, sname, credit)
Faculty(fid, sid)
Consider the following Relational Algebras:
RA1 : ΠSubject.sid, sname (Subject 1 Faculty)
RA2 :ΠSubject.sid, sname (Subject × Faculty)

Which of the following is correct? Marks: 2 MCQ

a) RA1 ⊂ RA2

b) RA2 ⊆ RA1

c) RA1 ⊆ RA2

d) RA1 = RA2

Answer: c)
Explanation: Natural join projects only those tuples where the sid matches. Cartesian
product projects all tuples after cross-product.
Hence, option (c) is correct.

2
Question 3
What does the following Relational Algebra expression return? Marks: 2 MCQ

Πheight (Mountain) − ΠMountain.height (σMountain.height<m.height (Mountain × ρm (Mountain)))

a) All heights except the maximum height from Mountain relation.

b) All heights except the minimum height from Mountain relation.

c) Minimum height from Mountain relation.

d) Maximum height from Mountain relation.

Answer: d)
Explanation: As per the syntax of the relational algebra.

3
Question 4
Consider the relational schema PhoneBook(Name, PhoneNo, Location, LastCalled).

If the tuple Ankit 2222586110 Kolkata 15 Jul is present in an instance of PhoneBook,

which of the following tuples can NOT be inserted to PhoneBook?

Marks: 2 MCQ

a) Ankit 3222265783 Kolkata 15 Jul

b) Shreya 3222265783 Delhi 16 Jul

c) Ankit 2222586110 Delhi 16 Jul

d) Shreya 2222586110 Kolkata 15 Jul

Answer: (c)
Explanation: According to the given relational schema, Name, PhoneNo combined forms the
primary key of PhoneBook and hence, must be unique and non-null. Since, Ankit, 2222586110
is present in the instance and uniquely identifies Location and LastCalled as Kolkata and
15 Jul, the tuple shown in option (c) cannot be inserted. Hence, option (c) is the answer.

4
Question 5
Consider the relation Flight(FlightNo, Source, Destination, Duration) where {FlightNo}
and {Source, Destination, Duration} are the 2 candidate keys. What is the possible num-
ber of superkeys of Flight?
Marks: 2 MCQ

a) 8

b) 9

c) 10

d) 16

Answer: (b)
Explanation: Total number of superkeys of Flight = Superkeys of Flight with {FlightNo}
as the key only + Superkeys of Flight with {Source, Destination, Duration} as the key
only - Superkeys of Flight with both {FlightNo} and {Source, Destination, Duration}
as the keys
= 24−1 + 24−3 − 24−4 = 9. Hence, option (b) is correct.

5
Question 6
Consider a truth table having the following columns
P Q R=((P ∨ Q) → ¬P) S=((P ∨ Q) → P)
If the truth table, with all the values, is represented as a relational instance, which column(s)
(attribute(s)) should be chosen as candidate keys? Marks: 2 MSQ

a) {P,R}

b) {P,Q}

c) {Q,R}

d) {Q,S}

Answer: (b), (c)

Explanation: According to the truth table, the instance should be
P Q R S
0 0 1 1
0 1 1 0
1 0 0 1
1 1 0 1
We see that {P, Q} and {Q,R} have unique and non-null values that can be used to identify
any tuple uniquely. Hence, options (b) and (c) are correct.

6
Question 7
Consider the following instance of ChatBox(SenderID, ReceiverID).
SenderID ReceiverID
104 2
22 10
If SenderID is the foreign key in the relational schema ChatMsg(ChatID, Text, SenderID),
which of the following is a valid instance of ChatMsg? Marks: 2 MCQ

ChatID Text SenderID

a) 50 ABC 104
50 MNO 22

ChatID Text SenderID

b) 50 ABC 104
104 MNO 22

ChatID Text SenderID

c) 50 NULL 50
104 MNO 22

ChatID Text SenderID

d) NULL NULL 50
104 MNO 104

Answer: (b)
Explanation: Options (a) and (d) are incorrect as ChatID is the primary key of ChatMsg
and must be unique and not NULL. Option (c) is incorrect as SenderID 50 is not referencing
to any tuple in the referenced relation ChatBox. Hence, option (b) is correct.

7
Question 8
Consider the following table:
MountainDetails
MountainName Altitude StateName
Kangchenjunga 8586 Sikkim
Nanda Devi 7816 Uttarakhand
Trisul 7120 Uttarakhand
Kamet 7756 Uttarakhand
Sandakfu 3636 West Bengal
Saltoro Kangri 7742 Jammu and Kashmir
Reo Purgyill 7742 Himachal Pradesh
Identify the correct operation(s) which produces the following output from the above relation.
Marks: 2 MCQ
MountainDetails
MountainName Altitude StateName
Kangchenjunga 8586 Sikkim
Nanda Devi 7816 Uttarakhand
Kamet 7756 Uttarakhand

a) σ(StateName=‘Uttarakhand’)∧(Altitude>=7750) (MountainDetails)

b) σ(StateName=‘Uttarakhand’)∨(Altitude>=7750) (MountainDetails)

c) σ(StateName=‘Uttarakhand’) (MountainDetails)

d) σ(Altitude>=7750) (MountainDetails)

Answer: d)
Explanation: As per Relational Operators syntax and semantics, option d) is correct.

8
Question 9
Consider the following tables:
MountainDetails1
MountainName Altitude StateName
Kangchenjunga 8586 Sikkim
Nanda Devi 7816 Uttarakhand
Sandakfu 3636 West Bengal
Saltoro Kangri 7742 Jammu and Kashmir
Reo Purgyill 7742 Himachal Pradesh

MountainDetails2
MountainName Altitude StateName
Kangchenjunga 8586 Sikkim
Trisul 7120 Uttarakhand
Kamet 7756 Uttarakhand
Sandakfu 3636 West Bengal
Identify the correct operation(s) which will be produce the following output from the above
two relations. Marks: 2 MCQ

MountainName Altitude StateName

Kangchenjunga 8586 Sikkim
Sandakfu 3636 West Bengal

a) MountainDetails1 − MountainDetails2

b) MountainDetails2 − MountainDetails1

c) (MountainDetails1 ∪ MountainDetails2 ) ∩ (MountainDetails1 ∩ MountainDetails2 )

d) (MountainDetails1 − MountainDetails2 ) ∪ (MountainDetails2 − MountainDetails1 )

Answer: c)
Explanation: As per Relational Operators syntax and semantics, options c) is correct.

9
Question 10
Which of the following can be a candidate key for the following instance? Marks: 2 MCQ

MountainDetails
MountainName Altitude StateName
Kangchenjunga 8586 Sikkim
Nanda Devi 7816 Uttarakhand
Trisul 7120 Uttarakhand
Kamet 7756 Uttarakhand
Sandakfu 3636 West Bengal
Saltoro Kangri 7742 Jammu and Kashmir
Reo Purgyill 7742 Himachal Pradesh

a) {Altitude}

b) {MountainName}

c) {StateName}

d) {MountainName, Altitude}

Answer: b)
Explanation: In the above instance, each row can be uniquely identified by using {MountainName}
attribute only.
Hence, (b) is the correct option.

10