Q. Student (school-id, sch-roll-no, sname, saddress)
School (school-id, sch-name, sch-address, sch-phone)
Enrolment(school-id sch-roll-no, erollno, examname)
ExamResult(erollno, examname, marks)

What does the following SQL query output?

SELECT sch-name, COUNT (*)
FROM School C, Enrolment E, ExamResult R
WHERE E.school-id = C.school-id
AND
E.examname = R.examname AND E.erollno = R.erollno
AND
R.marks = 100 AND S.school-id IN (SELECT school-id
FROM student
GROUP BY school-id
HAVING COUNT (*) > 200)
GROUP By school-id

View solution

Q. Given the basic ER and relational models, which of the following is INCORRECT?

View solution

Q. Consider a join (relation algebra) between relations r(R)and s(S) using the nested loop method. There are 3 buffers each of size equal to disk block size, out of which one buffer is reserved for intermediate results. Assuming size(r(R)) < size(s(S)), the join will have fewer number of disk block accesses if

View solution

Q. Let E1 and E2 be two entities in an E/R diagram with simple single-valued attributes. R1 and R2 are two relationships between E1 and E2, where R1 is one-to-many and R2 is many-to-many. R1 and R2 do not have any attributes of their own. What is the minimum number of tables required to represent this situation in the relational model?

View solution

Q. In a schema with attributes A, B, C, D and E following set of functional dependencies are given

A → B A → C CD → E B → D E → A

Which of the following functional dependencies is NOT implied by the above set?

View solution

Q. A database of research articles in a journal uses the following schema.

(VOLUME, NUMBER, STARTPGE, ENDPAGE, TITLE, YEAR, PRICE)

The primary key is (VOLUME, NUMBER, STARTPAGE, ENDPAGE) and the following functional dependencies exist in the schema.

(VOLUME, NUMBER, STARTPAGE, ENDPAGE) -> TITLE
(VOLUME, NUMBER) -> YEAR
(VOLUME, NUMBER, STARTPAGE, ENDPAGE) -> PRICE

The database is redesigned to use the following schemas.

(VOLUME, NUMBER, STARTPAGE, ENDPAGE, TITLE, PRICE)
(VOLUME, NUMBER, YEAR)

Which is the weakest normal form that the new database satisfies, but the old one does not?

View solution

Q. Which of the following relational query languages have the same expressive power?

1.Relational algebra
2.Tuple relational calculus restricted to safe expressions
3.Domain relational calculus restricted to safe expressions

View solution

Q. A Relation R with FD set {A->BC, B->A, A->C, A->D, D->A}. How many candidate keys will be there in R?

View solution

Q. What is the min and max number of tables required to convert an ER diagram with 2 entities and 1 relationship between them with partial participation constraints of both entities?

View solution

Q. Relation R has eight attributes ABCDEFGH. Fields of R contain only atomic values. 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?

View solution

Q. Consider the FDs given in above question. The relation R is

View solution

Q. Which of the following is TRUE?

View solution

Q. Consider a relational table with a single record for each registered student with the following attributes.

1. Registration_Num: Unique registration number
of each registered student
2. UID: Unique identity number, unique at the
national level for each citizen
3. BankAccount_Num: Unique account number at
the bank. A student can have multiple accounts
or join accounts. This attribute stores the
primary account number.
4. Name: Name of the student
5. Hostel_Room: Room number of the hostel

Which one of the following option is INCORRECT?

View solution

Q. Consider a relational table with a single record for each registered student with the following attributes.

1. Registration_Num: Unique registration number
of each registered student
2. UID: Unique identity number, unique at the
national level for each citizen
3. BankAccount_Num: Unique account number at
the bank. A student can have multiple accounts
or join accounts. This attribute stores the
primary account number.
4. Name: Name of the student
5. Hostel_Room: Room number of the hostel

Which one of the following option is INCORRECT?

View solution

Q. Consider the following relational schema:

Suppliers(sid:integer, sname:string, city:string, street:string)
Parts(pid:integer, pname:string, color:string)
Catalog(sid:integer, pid:integer, cost:real)
(sid,pid are primary keys)
Assume that, in the suppliers relation above, each supplier and each street within a city has a unique name, and (sname, city) forms a candidate key. No other functional dependencies are implied other than those implied by primary and candidate keys. Which one of the following is TRUE about the above schema?

View solution

Q. Consider the following relational schemes for a library database: Book (Title, Author, Catalog_no, Publisher, Year, Price) Collection (Title, Author, Catalog_no) with in the following functional dependencies:

I. Title Author --> Catalog_no
II. Catalog_no --> Title, Author, Publisher, Year
III. Publisher Title Year --> Price

Assume {Author, Title} is the key for both schemes. Which of the following statements is true?

View solution

Q. Consider the relation scheme R = {E, F, G, H, I, J, K, L, M, M} and the set of functional dependencies {{E, F} -> {G}, {F} -> {I, J}, {E, H} -> {K, L}, K -> {M}, L -> {N} on R. What is the key for R?

View solution

Q. Given the following two statements:

S1: Every table with two single-valued
attributes is in 1NF, 2NF, 3NF and BCNF.

S2: AB->C, D->E, E->C is a minimal cover for
the set of functional dependencies
AB->C, D->E, AB->E, E->C.

Which one of the following is CORRECT?

View solution

Q. The maximum number of superkeys for the relation schema R(E,F,G,H) with E as the key is

View solution

Q. Which one of the following statements about normal forms is FALSE?

View solution