Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

Midterm1

Q1

IMDB has asked you to develop a database managing movie related information. Movies are identified by their titles and year of release. Movies are classified into different genres, have a language, and length. Names and addresses of actors who starred in movies, and the director and producer, are also recorded in the database. Names and addresses of studios which own movies are also recorded. Information about with movies were nominated for Oscars, and which ones were victorious, is also stored in the database

Each movie must be owned by at least one studio.

Each actor/actress may star in multiple movies and each movie may have multiple actors/actresses. However, a movie can have only one director. A movie must have at least one producer.

Actors/actresses sign contracts with studios to star in movies owned by the studios. Salaries are individually negotiated with each studio on each movie.

A movie may or may not be nominated for an Oscar, and if nominated may or may not win the award.

Design an E-R schema showing the above relationships. Indicate what kind of relationships they are. Identify and model any constraints. Clearly state any assumptions you make.                         

Your Answer:

The graph was attached in the file uploaded.

assumption: actors/actresses, directors, and producers are cooperate to produce the movie.

Q2

The following questions are based on a database schema described below. The keys are in boldface. Also, the notation 1–oo means the relationship is 1–Many. 

(a) Suppose faculty hired between January 1st, 2020, and June 30th, 2021, are asked to delay their joining date by 6 months. Write an SQL query to list the Name, Rank, City, and salary for such faculty.   (8 pts)

(b) Write an SQL query to list the offer number, the course number, The course description, and the first name and the last name of instructors teaching the courses scheduled in Fall 2021, taught by assistant professors.                                              (8 pts)

(c) Write an SQL query to list the number of students with GPA above 3.0, for each courses offered in Fall 2021.                                                                                      (8 pts)

(d) Suppose we create a Hash index on the Offering relation. Which attribute would be a good choice for it? What kind of queries would benefit from it – give an example in English of such a query.                                                                             (2+2+2 pts)

Q.3. For each of the questions below, please keep your answers brief and clear. [20 pts]

a) Explain the exact meaning of the following ER model notation: ________  [2pts]

b) What is an ‘identifying relationship’? What kind of constraints does it have? [3pts]

c) Distinguish between ‘aggregation’ and ‘generalization’.               [2pts]

d) In “cascading delete”, deletion of a referenced tuple causes deletion of all referencing tuples. Why does it not happen the other way around? [3pts]  

e) Why is cascading delete: (i) a good idea, (ii) a bad idea.                          [2pts]

f) Distinguish between a ‘key’ and a ‘super key’.                                   [2pts]

g) Relational calculus is said to be a declarative language, in contrast to algebra, which is a procedural language.  Explain the distinction.      [3 pts]

h) What is a “view”? How do views support logical data independence?  [3 pts]

 Q4

Consider the B+ tree shown in the figure. Answer following questions. 

 a. Describe in detail the path taken when searching for a key with value 79. [8 points]

b. Describe in detail the steps in inserting a key with value 18 in the tree. Show the node(s) which undergoes the change, and how does it look after the change. [8 points]

c. What problem of an “Inverted List Index” does a “B tree Index” solve? [4 pts]