Live Chat

# COP 6731 Practice Midterm Exam

1. This is a CLOSED book examination. Personal copies of textbook and class notes cannot be used. However, each student may bring one 8.5 inch by 11 inch double-sided sheet of summary notes. The cheat sheet should be submitted with the exam. Please do not share this sheet with other students. Laptop computers, smart phones and calculators cannot be used.
2. There are 5 questions. Put name, student Z number, email address, course id, course name, semester, and year on the cover page.
3. All questions about SQL refer to the SQL2 or SQL3 standard.

• A null value is an attribute value that has been set to zero.
• Cascading deletions are generally not used with relationships for weak child entities.
• Many-to-many relationships are represented by two intersection tables which each have 1:N relationships with the two tables.

Given two relations R1 and R2, where R1 contains N1 tuples, R2 contains N2 tuples, and N2>N1>0. Assume that R1 and R2 are union-compatible.

• Consider the following SQL statement.

SELECT * FROM R1 UNION SELECT * FROM R2

Which of the following is true?

1. the minimum possible size (in tuples) for the resulting relation is N1
2. the minimum possible size (in tuples) for the resulting relation is N2
3. the maximum possible size (in tuples) for the resulting relation is N2 – N1
4. the maximum possible size (in tuples) for the resulting relation is N1
5. the maximum possible size (in tuples) for the resulting relation is N2 * N1
• Consider the following SQL statement.

SELECT * FROM R1 INTERSECT SELECT * FROM R2

Which of the following is true?

1. the minimum possible size (in tuples) for the resulting relation is N2 + N1
2. the minimum possible size (in tuples) for the resulting relation is N2
3. the maximum possible size (in tuples) for the resulting relation is N1
4. the maximum possible size (in tuples) for the resulting relation is N2
5. the maximum possible size (in tuples) for the resulting relation is N2 + N1
• Consider the following SQL statement.

SELECT * FROM R1 EXCEPT SELECT * FROM R2

Which of the following is true?

1. the minimum possible size (in tuples) for the resulting relation is 0
2. the minimum possible size (in tuples) for the resulting relation is 1
3. the maximum possible size (in tuples) for the resulting relation is N2 + N1
4. the maximum possible size (in tuples) for the resulting relation is N2
5. the maximum possible size (in tuples) for the resulting relation is N2 - N

Question 3. Consider the following schema. Column names in bold, underlined letters indicate identifying columns.

Actor (Id, Name)

Movie (Id, Title, Yr)

Casting (Movieid, Actorid, Ord)

The following is a database state corresponding to the above schema

## Actor Movie Casting

 Id Name Id Title Yr 134 Burt Reynolds 11 Shop Girl 2005 299 Cameron Diaz 27 You’ve Got Mail 1999 371 Mark Hamil 34 When Harry Met Sally 1989 498 Meg Ryan 49 The Crew 2000 654 Steve Martin 61 War of the Worlds 2005 731 Tom Cruise 87 Bowfinger 1999
 Movieid Actorid Ord 11 654 1 34 498 1 61 731 1 49 134 2 87 654 1 27 498 2

Show the result of extendable hashing after insertion of records with key 10, 62, 21, 35, 74, 22, 35, 58, 12, 42, 28, and 1

• Consider two-way external sorting. Each buffer page can store three records. Given 12 records, 10, 62, 21, 35, 74, 22, 35, 58, 12, 42, 28, and 1, show each pass for sorting. How many passes are required to sort these records?