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

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

COP 6731 Practice Midterm Exam Image 1

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?
citation generator
citaion generator
make money online