uiz Space

May 2024 term · Database Management Systems · BSCS2001

Database Management Systems Quiz 1: 7 July 2024 (May 2024 term)

The IIT Madras BS Database Management Systems (DBMS) Quiz 1 paper sat on 7 Jul 2024, in the May 2024 term: 15 questions for 50 marks in 120 minutes. Every question is below with its answer. Take it as a timed mock test to be marked, or read it through first.

Questions
15
Marks
50
Duration
120 min
MCQ
7
Numerical
2
MSQ
6
Take as mock testPractise with answers

Updated

Official paper: IIT M DIPLOMA AN EXAM QDD2 7 July 2024 · No negative marking.

Question 1

+3 marksOne correct option

Consider the table Champions_League for given subquestions

NameAgeTeamCountryGoals
De jong26BarcelonaNetherlands20
Bellingham20Real MadridEngland18
Haaland23Manchester CityNorway18
Araujo25BarcelonaUruguay20
Martinelli22ArsenalBrazil16
Mbappe25PSGFrance16
Kroos34Real MadridGermany18
Dembele26PSGFrance14
Saka22ArsenalEngland16
Cubarsi17BarcelonaSpain12

Table 1: Figure 1: Champions_League

Choose the correct SQL statement that will return the resultant table given in Figure 2.

GoalsTeamCount
20Barcelona2
18Real Madrid2
18Manchester City1
16Arsenal2
16PSG1
14PSG1
12Barcelona1

Table 2: Figure 2: Result

  1. A
  2. B
  3. C
  4. D
Show answer

Correct answer

  • A
Question 1 on its own page

Question 2

+4 marksNumerical answer

Consider the table Champions_League for given subquestions

NameAgeTeamCountryGoals
De jong26BarcelonaNetherlands20
Bellingham20Real MadridEngland18
Haaland23Manchester CityNorway18
Araujo25BarcelonaUruguay20
Martinelli22ArsenalBrazil16
Mbappe25PSGFrance16
Kroos34Real MadridGermany18
Dembele26PSGFrance14
Saka22ArsenalEngland16
Cubarsi17BarcelonaSpain12

Table 1: Figure 1: Champions_League

Show answer

Correct answer: 4

Question 2 on its own page

Question 3

+3 marksOne correct option

Consider the following SQL statement:

sql
Create table Cars(
CarID varchar(8),
CarName varchar(20),
CarColour varchar(8),
YearOfPurchase integer,
Weight integer,
primary key (CarID),
check(YearOfPurchase in (1990, 2000, 2005, 2010)));

Which among the following will cause an integrity constraint violation in the Cars table?

Note: Insertions are done in the order of the options given.

  1. A
  2. B
  3. C
  4. D
Show answer

Correct answer

  • A
Question 3 on its own page

Question 4

+3 marksOne correct option
  1. A
  2. B
  3. C
  4. D
Show answer

Correct answer

  • A
Question 4 on its own page

Question 5

+3 marksOne or more correct options

Select all that apply.

  1. A

    1002

  2. B

    101020

  3. C

    10200

  4. D

    10022

Show answer

Correct answers

  • B

    101020

  • D

    10022

Question 5 on its own page

Question 6

+3 marksOne or more correct options

Consider the following relations:

players(pid, name, age, jersey_no)
teams(team_name, matches, points, pid)
Choose the correct TRC or DRC expression which is equivalent to the below SQL query.

sql
SELECT p.name, t.points
FROM players p natural join teams t
WHERE p.jersey_no = 7

Select all that apply.

  1. A
  2. B
  3. C
  4. D
Show answer

Correct answers

  • A
  • C
Question 6 on its own page

Question 7

+4 marksOne correct option

Consider the following data for given subquestions:

Driver_IDNameTeamSalary
55Carlos SainzScuderia Ferrari2500000
16Charles LeclercScuderia Ferrari2300000
4Lando NorrisMcLaren2100000
81Oscar PiastriMcLaren1800000
44Lewis HamiltonMercedes AMG2600000
63George RussellMercedes AMG2000000
1Max VerstappenRedBull Racing2800000
11Sergio PerezRedBull Racing2500000
14Fernando AlonsoAston Martin2300000
18Lance StrollAston Martin1700000

Table 4: Figure 3: F1_Drivers

Team_IDTeamCountryRanking
1Scuderia FerrariItaly1
2Mercedes AMGGermany4
3McLarenUK2
4RedBull RacingAustria5
5Aston MartinUK3

Table 5: Figure 3: F1_Teams

What will be the output of the following SQL query:

sql
select distinct Team
from F1_Teams
where exists (select name
             from F1_Drivers
             where F1_Drivers.Team = F1_Teams.Team and salary>2000000);
  1. A

    Distinct names of all such teams that have no driver with salary higher than 2000000

  2. B

    Distinct names of all such teams that have all drivers with salary higher than 2000000

  3. C

    Distinct names of all such teams that have at least one driver with salary higher than 2000000

  4. D

    Distinct names of all such teams that have exactly one driver with salary higher than 2000000

Show answer

Correct answer

  • C

    Distinct names of all such teams that have at least one driver with salary higher than 2000000

Question 7 on its own page

Question 8

+4 marksNumerical answer

Consider the following data for given subquestions:

Driver_IDNameTeamSalary
55Carlos SainzScuderia Ferrari2500000
16Charles LeclercScuderia Ferrari2300000
4Lando NorrisMcLaren2100000
81Oscar PiastriMcLaren1800000
44Lewis HamiltonMercedes AMG2600000
63George RussellMercedes AMG2000000
1Max VerstappenRedBull Racing2800000
11Sergio PerezRedBull Racing2500000
14Fernando AlonsoAston Martin2300000
18Lance StrollAston Martin1700000

Table 4: Figure 3: F1_Drivers

Team_IDTeamCountryRanking
1Scuderia FerrariItaly1
2Mercedes AMGGermany4
3McLarenUK2
4RedBull RacingAustria5
5Aston MartinUK3

Table 5: Figure 3: F1_Teams

How many tuples will the following query return?

sql
select name
from F1_Drivers
where salary > all (select salary
                       from F1_Drivers
                       where Team = 'Mercedes AMG');
Show answer

Correct answer: 1

Question 8 on its own page

Question 9

+2 marksOne or more correct options

Which of the following statement(s) is/are correct?

Select all that apply.

  1. A

    All candidate keys are primary keys

  2. B

    A primary key is also a candidate key

  3. C

    A primary key consists of exactly one attribute

  4. D

    A candidate key is a minimal super key

Show answer

Correct answers

  • B

    A primary key is also a candidate key

  • D

    A candidate key is a minimal super key

Question 9 on its own page

Question 10

+3 marksOne correct option

Consider the E-R diagram given in Figure 1 and answer the subquestions.

Identify the correct relational schema for the relationship set enrolls.
Note: The primary key is underlined.

  1. A
  2. B
  3. C
  4. D
Show answer

Correct answer

  • D
Question 10 on its own page

Question 11

+3 marksOne correct option

Consider the E-R diagram given in Figure 1 and answer the subquestions.

Identify the correct relational schema for the entity set Assignment.
Note: The primary key is underlined.

  1. A
  2. B
  3. C
  4. D
Show answer

Correct answer

  • B
Question 11 on its own page

Question 12

+3 marksOne or more correct options

Consider the E-R diagram given in Figure 1 and answer the subquestions.

Select all that apply.

  1. A

    Each course must have at least one student.

  2. B

    Each student must have enrolled for at least one course.

  3. C

    Some courses may have no students.

  4. D

    A student may enroll for many courses.

Show answer

Correct answers

  • C

    Some courses may have no students.

  • D

    A student may enroll for many courses.

Question 12 on its own page

Question 13

+4 marksOne or more correct options

Consider the tables r and s.

ABCD
p1pa
q2ra
r4qb
p1ra
s2qb

Table r

BDE
1ap
3aq
1ar
2bs
3bt

Table s

ABD
p1a
s2b

result

Which of the following relational algebra operation(s) on tables r and s will produce table result?

Select all that apply.

  1. A
  2. B
  3. C
  4. D
Show answer

Correct answers

  • C
  • D
Question 13 on its own page

Question 14

+4 marksOne or more correct options

Consider a relational schema:
Parts(Pid, Pname, Price, Quantity)
Vendors (Vid, Vname, Location, Pid)

Consider the SQL query:

sql
SELECT Distinct Vname FROM Parts NATURAL JOIN Vendors
WHERE Price < 5000 and location = 'Mumbai'

Choose the correct relational algebra expression(s) which will give the same output as of the above SQL query.

Select all that apply.

  1. A
  2. B
  3. C
  4. D
Show answer

Correct answers

  • B
  • D
Question 14 on its own page

Question 15

+4 marksOne correct option

Consider the following table which has three attributes: X, Y and Z, where X is the primary key and Z is the foreign key referencing X.

XYZ
115
225
315
424
524
611
741
847

Which of the following sets of tuples are additionally deleted when the tuple (4,2,4) is deleted and ON DELETE CASCADE construct is applied over the table?

  1. A

    (2,2,5) and (3,1,5)

  2. B

    (5,2,4), (1,1,5), (2,2,5) and (3,1,5)

  3. C

    (6,1,1),(7,4,1) and (8,4,7)

  4. D

    All the remaining rows will be deleted

Show answer

Correct answer

  • D

    All the remaining rows will be deleted

Question 15 on its own page