cs4400 Sample Quiz III

CS 4400 SAMPLE QUIZ III

Chapters 7, 8, 10 (4th edition) OR Chapters 8, 9.1, 9.2, 14 (3rd edition)

1. Assume we want to map a 1:N relationship type, which involves entity types R

and S, where R is on the N side of the relationship type and S is on the 1 side,

to the relational model. The attributes of the relationship type should be mapped

to attributes of

(a) the corresponding relation for the entity type R

(b) the corresponding relation for the entity type S

(d) none of the above

2. When we map a multivalued attribute for entity E from the ER model to the

relational model, we will create

(a) many relations, one for each of the distinct values of the attribute

(b) one relation that contains a foreign key and a column for the attribute

(d) none of the above

3. A weak entity type

(a) must have total participation in an identifying relationship

(b) does not have a key attribute(s)

(d) none of the above

4. Mapping specialization/generalization entities to the relational

model by creating a relation for each subclass that contains the attributes

of that subclass and all of the attributes of the superclass (i.e., without

having a relation for the superclass) works well if

(a) the subclasses are disjoint and there is total participation

(b) the subclasses overlap and there is total participation

(d) the subclasses overlap and there is partial participation

5. Desirable properties of relational database design include

(a) minimizing insertion/deletion anomalies

(b) minimizing redundancy

(d) all of the above

6. A Many-to-Many relationship from an ER diagram can be represented

in the relational model by a

(a) domain

(b) primary key

(d) a single attribute

7. The functional dependency A -> B for relation schema R(A,B,C,D) implies that

(a) no two tuples in R can have the same value for attribute B

(b) no two tuples in R can have the same value for attribute A

for A

(d) any two tuples in R that have the same value for A must have the same value

for B

8. If AB -> CD is one of the functional dependencies for relation schema

R(A,B,C,D) then which of the following will always hold?

(a) AB is a candidate key for R

(b) no two tuples in R can have the same values for A and B

(d) all of the above

9. An Entity from an ER diagram can be represented

in the relational model by a

(a) domain

(b) functional dependency

(d) a single attribute

10. Given the functional dependencies {AB -> C, C -> D}

for the relation schema R(ABCD), the dependency A -> D can be inferred by

(a) the reflexive rule

(b) the augmentation rule

(d) none of the above

11. Given the functional dependencies, {AB -> CDE and A -> E},

for relation scheme R = (A,B,C,D,E) we can infer the following:

(a) A is a key for R

(b) BE is a key for R

(d) none of the above

12. Given the relation Employee:

E# | Ename | D# | Mgr_E#

--------|---------|----|-------

101 Smith 1 103

102 Brown 1 103

103 Clark 1 103

104 Black 2 105

105 Green 2 105

what will be returned by the SQL query:

Select Distinct E2.Ename From Employee E1, Employee E2

Where E1.D# = 2 AND E1.Mgr_E# = E2.E#;

(a) Clark

(b) Green

(d) None of the above

13. For the relation Supplies(Snumber,Pnumber,Qty), which of the following SQL

statements is equivalent to the relational algebra project operator on Snumber?

(a) Select Snumber From Supplies;

(b) Select Distinct Snumber From Supplies;

(d) Select * From Supplies;

14. Given the relational schema consisting of Supplier(Snumber,Sname,City),

Part(Pnumber,Pname,Color) and Supplies(Snumber,Pnumber,Qty), which SQL query

retrieves (in order of Pnumber) the Pname,Pnumber and the number of suppliers

from Atlanta that supply that part in a quantity > 100?

(a) Select Y.Pnumber, Y.Pname, Count(*)

From Supplier X, Part Y, Supplies Z

Where X.Snumber = Z.Snumber and Z.Pnumber = Y.Pnumber

and X.City= 'Atlanta' and Z.Qty > 100;

(b) Select Y.Pnumber, Y.Pname, Count(*)

From Supplier X, Part Y, Supplies Z

Where X.Snumber = Z.Snumber and Z.Pnumber = Y.Pnumber

and X.City = 'Atlanta' and Z.Qty > 100

Group By Y.Pnumber, Y.Pname

Order by Y.Pnumber;

From Supplier X, Part Y, Supplies Z

Where X.Snumber = Z.Snumber and Z.Pnumber = Y.Pnumber

and X.City = 'Atlanta'

Group By Y.Pnumber,Y.Pname

Having Count(*) > 100

Order by Y.Pnumber;

(d) Select Y.Pnumber, Y.Pname, Count(*)

From Supplier X, Part Y, Supplies Z

Where X.Snumber = Z.Snumber and Z.Pnumber = Y.Pnumber

and X.City = 'Atlanta' and Z.Qty > 100

Order by Y.Pnumber;

15. Given the relation Supplies:

Snumber | Pnumber | Qty

--------|---------|-----

101 1 20

101 2 30

102 1 14

103 4 21

104 4 10

105 1 5

what will be returned by the SQL query:

Select Pnumber From Supplies Group By Pnumber Having Count(*) =

(Select Max(Count(*)) From Supplies Group By Pnumber)

(a) 1

(b) 2

(d) 4

16. For the relation Supplies(Snumber,Pnumber,Qty), which of the following SQL

statements corresponds to a relational algebra project and selection operators?

(a) Select Snumber From Supplies;

(b) Select Distinct S1.Snumber From Supplies S1, Supplies S2 Where S1.Qty > S2.Qty;

(d) Select * From Supplies;

17. Given the two relations

Supplies Supplier

Snumber | Pnumber | Qty Snumber | Sname | City

--------|---------|----- --------|---------|-----

101 1 20 101 Ajax NY

101 2 30 102 Acme LA

102 1 10 105 AAA SF

102 4 20

102 5 5

105 1 5

what will be returned by the SQL query:

Select Sname From Supplier Where Snumber IN

(Select Snumber From Supplies Group By Snumber

Having Sum(Qty) > 40);

(a) Ajax

(b) Acme

(d) None of the above

18. Using the tables from question 17, what is returned by the following SQL query:

Select Distinct City From Supplier, Supplies

Where Supplier.Snumber = Supplies.Snumber And Qty >10 And Pnumber = 1;

(a) NY

(b) LA

(d) None of the above

19. Given the relational schema consisting of Supplier(Snumber,Sname,City),

Part(Pnumber,Pname,Color) and Supplies(Snumber,Pnumber,Qty), which SQL query

retrieves the Pnumber and the total quantity of that part supplied?

(a) Select Pnumber,sum(Qty)

From Supplies

Group By Pnumber;

(b) Select Pnumber,Qty

From Supplies;

(d) Select Sname, Pnumber, sum(Qty)

From Part, Supplies

Where Part.Pnumber = Supplies.Pnumber

Group by Sname,Pnumber;

20. Given the relational schema consisting of Department(Dnumber,Dname,Budget)

and Employee(Enumber,Ename,City,Salary,Dnumber), which SQL query retrieves

the names of departments whose budget is less than the the total salaries for

employees working in that department?

(a) Select Dname

From Department, Employee

Where Employee.Dnumber = Department.Dnumber

AND Budget < Salary;

(b) Select Dname

From Department

Where Budget < (Select sum(Salary)

From Employee);

From Department;

(d) Select Dname

From Department

Where Budget < (Select sum(Salary)

From Employee

Where Employee.Dnumber = Department.Dnumber);

ANSWERS ***********************************************************************

1. a 2. b 3. c 4. a 5. d 6. c 7. d 8. b 9. c 10. d

11. c 12. b 13. b 14. b 15. a 16. c 17. a 18. a 19. a 20. d

*******************************************************************************