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.

Question 1. True or False Questions (Briefly Justify your answer)

• 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.

Question 2. Choice question (Briefly justify your answer)

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

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?