Mock Test

Question 1

Which of the following SQL statements removes from a BRANCH table the row for the branch with branchNo B002?

Answer 1

DELETE FROM branch WHERE branchNo = ’B002’;

Question 2

What is the definition of a database according to the Copyright, Designs and Patents Act 1988?

Answer 2

A shared collection of logically related data, and its description, designed to meet the information needs of an organisation.

Question 3

Consider the following UML diagram of a ER model representing people working on a movie. Which of the following sentences is NOT true.

Answer 3

“Role” is an attribute of a relation.

Question 4

Consider the following relation:
Staff (Name, Department, OfficeNumber, Telephone)
Suppose a table implementing the relation contains the following data:
Smith, Databases, 1.05, 1270
Jackson, Networks, 3.02, 3610
Brown, Networks, 2.06, 2214
Smith, Programming, 1.05,1270
Brown, Databases, 2.06, 2214
Assume that the dataset above is representative and that there are no dependencies between attributes besides the ones you can find in this dataset. Choose a Primary Key for this table among the options below.

Answer 4

Name and OfficeNumber.

Question 5

In SQL, what is the most appropriate way to implement the following constraint:
the salary of a teacher must not be greater than the salary of the head-teacher of the school he/she works for.

Answer 5

This requirements is not supported by SQL.

Question 6

Which of the following statements about indexes is not true?

Answer 6

D. Values in an index are ordered.

Question 7

Suppose the following set of functional dependencies holds on the universal relation: R={A,B,C,D,E,F}
A → C
AC → D
E → AD
E → B
What is the closure of A, or A+?

Answer 7

The set {A,C,D}.

Question 8

Consider the following table:
Movies (MovieID, Title, Country, RunningTime)
Which of the following SQL statements retrieves the cardinality of this table?

Answer 8

SELECT * FROM Movies;

Question 9

Which statement below is true about modelling ER diagrams as tables ?

Answer 9

A binary many-to-many relation will be implemented as a set of 2 tables.

Question 10

Which of the following statements about weak entities is not true?

Answer 10

Weak entities represent virtual data, and are not actually stored in the database.

Question 11

A set of relations in a database such that no spurious tuples are generated after normalisation is said to have which property:

Answer 11

A. Non additive join property.

Question 12

Suppose you have two Relation schemas
R1 = {A, B, C} and
R2 = {A, D, E, F }.
Select the most appropriate SQL version of the RA statement below.

Answer 12

C. SELECT B,D FROM R1 JOIN R2 ON R1.A=R2.A WHERE D=2;

Question 13

Which of the following statements is not true about a functional dependence: A → B ?

Answer 13

C. A is a primary key.

Question 14

Consider the constraint definition:
”Entity Lecturer has partial participation to relationship Teaching with entity Course, and entity Course has total participation to relationship Teaching with entity Lecturer”
Which combination of the following statements is correct?
Every lecturer must teach at least one course.
Every course must be taught by least one lecturer.
There can be lecturers who do not teach.
A lecturer can’t teach more than one course.
There can be courses which are not taught.

Answer 14

C. 1 and 5

Question 15

Which of the following SQL statements represents a correlated nested query?

Answer 15

C. SELECT * FROM EMPLOYEE E

WHERE E.deptno IN (SELECT D.deptno FROM DEPARTMENT D);