Consider the following relational schema.

Students(rollno: integer, sname: string)

Courses(courseno: integer, cname: string)

Registration(rollno: integer, courseno; integer, percent: real)

Which of the following queries are equivalent to this query in English?

“Find the distinct names of all students who score more than 90% in the course numbered 107”

(I) SELECT DISTINCT S.sname
FROM Students as S, Registration as R
WHERE R.rollno=S.rollno AND R.Courseno=107 AND R.percent>90

(II)

(III) {T | ∃S∈ Students, ∃R∈ Registration (S.rollno = R.rollno ∧ R.courseno = 107 ∧ R.percent > 90 ∧ T.sname = S.name)}

(IV)

Four queries given in SQL, RA, TRC and DRC in four statements respectively retrieve the required information.

Workspace

Report

Relation R has eight attributes ABCDEFGH. Fields of R contain only atomic values.

**A. ** 3

**B. ** 4

**C. ** 5

**D. ** 6

**Answer : ****Option B**

**Explaination / Solution: **

Candidate keys are AD, BD, ED and FD

F = {CH → G, A → BC, B → CFH, E → A, F → EG} is a set of functional dependencies (FDs) so that F^{+} is exactly the set of FDs that hold for R

How many candidate keys does the relation R have?

Candidate keys are AD, BD, ED and FD

Workspace

Report

Relation R has eight attributes ABCDEFGH. Fields of R contain only atomic values.

**A. ** in INF, but not in 2NF

**B. ** in 2NF, but not in 3NF

**C. ** in 3NF, but not in BCNF

**D. ** in BCNF

**Answer : ****Option A**

**Explaination / Solution: **

A → BC,B → CFH and F → EG are partial dependencies. Hence it is in 1NF but not in 2NF

F = {CH → G, A → BC, B → CFH, E → A, F → EG} is a set of functional dependencies (FDs) so that F+ is exactly the set of FDs that hold for R

The relation R is

A → BC,B → CFH and F → EG are partial dependencies. Hence it is in 1NF but not in 2NF

Workspace

Report