http://www.cs.sjsu.edu/faculty/pollett/157a.3.18f/?PracMid2.shtml

-- Practice Midterm 2 Solutions

7. Express the following queries in relational algebra (assuming the movie database relations from the book): (a) for each year, total number of movies made in that year, sorted by increasing year, (b) Stars and the average year of the movies they appear in. (a) τyear(γyear, COUNT(title) -> ctMovies(Movies)) (b) γstarName, AVERAGE(movieYear) -> avgYear(StarsIn) By: Cindy Ho, Emerson Ye, Chico Malto, Hongbin Zheng

-- Practice Midterm 2 Solutions

   relationship Star star;

   relationship Star star

       inverse Star::starredIn;

   relationship Set<Movie> starredIn;

   relationship Set<Movie> starredIn

       inverse Movie::star;

6. <br> In ODL a many relation is represented by the keyword Set. A one relationship is represented by just the class name. Say there are two classes Star and Movies who have a many to one relationship. With Star being the one relationship and Movies the many relationship. Movies to Star is a Many to one relationship. Like below. <br> relationship Star star;<br> becomes<br> <br> relationship Star star<br> inverse Star::starredIn; <br> and <br> relationship Set<Movie> starredIn; <br> becomes <br> <br> relationship Set<Movie> starredIn<br> inverse Movie::star;<br> <br> - David Bui, Yuta Sugiura, Kevin Prakasa

-- Practice Midterm 2 Solutions

<nowiki> Student Names: Alexander Duong Adam Ball Ada La Parameswaran Ranganthan 3. Draw an example of the following as both an E/R and UML diagram: two related/associated entities/classes in a many one relationship where the relationship has two attributes </nowiki> ((resource:Question3.png|Resource Description for Question3.png))

-- Practice Midterm 2 Solutions

<nowiki> Student Names: Alexander Duong Adam Ball Ada La Parameswaran Ranganthan 9. Express the following queries in SQL: (a) name and square of salary as sal2 of all movie executives making less than $1million, (b) All movies produced by MGM in increasing order of length. a. SELECT name, salary * salary AS sal2 FROM executive WHERE salary < 1000000; b. SELECT * FROM movie WHERE studio = "MGM" ORDER by length; /*(Note that not specifying a direction for ORDER defaults to ascending)*/ </nowiki>

-- Practice Midterm 2 Solutions

 - Create relation `R_W` from a weak entity set W with supporting set E
 - Include all attributes of W as an attribute of `R_W`
 - Include foreign key reference to the key of the translation `R_E` to E
 - The key of `R_W` is the foreign key with the partial key of W

 - Intentional DB - collection of all intentional predicates in a DB
 - Intentional predicate - predicate whose relation is computed by one or more datalog rules that are not facts
 - Extensional DB - collection of all extensional predicates in a DB
 - Extensional predicate - predicate whose relation is stored in a DB (datalog facts)
 - Safe variable - a variable that appears in a non-negated relational algebra of the body.
 Ex: P(x, y) <- Q(x, z), NOT R(w, x, y), x < y.
 Not safe because w and y appear only in a negated relational subgoal. Y also appears in an arithmetic operation.

Group members: Hovsep Lalikian, Sam Esmaeili, Erin Yang, Wendy Chen 5. Map weak entity sets and supporting sets to relations - Create relation @BT@R_W@BT@ from a weak entity set W with supporting set E - Include all attributes of W as an attribute of @BT@R_W@BT@ - Include foreign key reference to the key of the translation @BT@R_E@BT@ to E - The key of @BT@R_W@BT@ is the foreign key with the partial key of W 8. Explain the datalog concepts of intentional databases, extensional databases, and a variable appearing safely in a rule. - Intentional DB - collection of all intentional predicates in a DB - Intentional predicate - predicate whose relation is computed by one or more datalog rules that are not facts - Extensional DB - collection of all extensional predicates in a DB - Extensional predicate - predicate whose relation is stored in a DB (datalog facts) - Safe variable - a variable that appears in a non-negated relational algebra of the body. Ex: P(x, y) <- Q(x, z), NOT R(w, x, y), x < y. Not safe because w and y appear only in a negated relational subgoal. Y also appears in an arithmetic operation.

-- Practice Midterm 2 Solutions

R(name, street, city, title, year) {| |- ! name !! street !! city !! title !! year |- | C. Fisher || 123 Maple St. || Hollywood || Star Wars || 1977 |- | C. Fisher || 5 Locust Ln. || Malibu || Star Wars || 1977 |- | C. Fisher || 123 Maple St. || Hollywood || Empire Strikes Back || 1980 |- | C. Fisher || 5 Locust Ln. || Malibu || Empire Strikes Back || 1980 |- | C. Fisher || 123 Maple St. || Hollywood || Return of the Jedi || 1983 |- | C. Fisher || 5 Locust Ln. || Malibu || Return of the Jedi || 1983 |} There are no nontrivial dependencies. No attribute is determined by the other four. Therefore it is in BCNF. Here name ->-> Street, city. For a relation to be in 4NF, the left side of the MVD should be a super-key, but for the above relation, name is not a super key. Therefore, it is not in 4NF. ---- Names: Alex Frank, Dominic Dinh, Vinny Senthil, Parnika De

-- Practice Midterm 2 Solutions

    (AB)+
    AC->B -> no
    AE->D -> no

    (AC)+

    AB->C -> no

    AE->D -> no

    (AE)+

    AB->C -> no

    AC->B -> no

Names: Himanshu Mehta, Conover Wang, Ryan Moore, Sherwyn Sen, Andrew Yuan<br> 1. Perform 3NF Decomposition on R(A, B, C, D, E) AB->C AC->B AE->D step 1. find minimal basis of FDs delete AB->C? (AB)+ AC->B -> no AE->D -> no can't delete<br> <br> delete AC->B?<br> (AC)+<br> AB->C -> no<br> AE->D -> no<br> can't delete<br> <br> delete AE->D?<br> (AE)+<br> AB->C -> no<br> AC->B -> no<br> can't delete<br> <br> minimal basis<br> -----<br> AB->C<br> AC->B<br> AE->D<br> <br> step 2. make relations from minimal basis<br> AB->C R(A,B,C)<br> AC->B R(A,B,C) --> unnecessary<br> AE->D R(A,D,E)<br> <br> step 3. if none of the relations are a superkey, find superkey and add a new relation from that<br> (ABC)+ -> no<br> (ADE)+ -> no<br> <br> now find a superkey<br> del E? (ABCD)+ -> no<br> del D? (ABCE)+ -> (ABCDE) (from AE->D) <br> del C? (ABE)+ -> (ABCE) (from AB->C) -> (ABCDE) (from AE->D) <br> del B? (AE)+ -> (ADE) (from AE->D) -> no<br> del A? (BE)+ -> no<br> <br> smallest superkey -> (ABE)<br> so make R(A,B,E)<br> <br> answer: R(A,B,C,D,E) = R1(A,B,C) u R2(A,D,E) u R3(A,B,E)

-- Practice Midterm 2 Solutions

4) Define the following kinds of constraints and show with a diagram how to represent it in the E/R model: (a) key, (b) referential integrity constraint, (c) degree constraint. ---- a) A key for an entire set E is a set K of one or more attributes such that, given any two elements e1 and e2 of E, e1 and e2 cannot have identical values for each of the attributes in K. Every entity set must have a key. A classic example would be making attributes title and year key of the entity set Movies(title, year, length, genre). In an E/R Diagram, the key attributes are underlined. ((resource:Screen Shot 2018-11-14 at 3.29.09 PM.png|Resource Description for Screen Shot 2018-11-14 at 3.29.09 PM.png)) b) If an entity is involved in at most one tuple and at least once as well, but does not require all members of the many side to be involved with any entity of the one side, we use a referential integrity. An Example can be Teaches(Professor, Course) where the University only allows a course to have one professor. In an E/R Diagram, we would use a rounded arrow in the one side. ((resource:Screen Shot 2018-11-14 at 3.29.13 PM.png|Resource Description for Screen Shot 2018-11-14 at 3.29.13 PM.png)) c) To indicate the cardinality of the participants in a relationship using an inequality on the edge is called a degree constraint. An example could be the number of students allowed to be enrolled in a course is less than or equal to 40. ((resource:Screen Shot 2018-11-14 at 3.29.26 PM.png|Resource Description for Screen Shot 2018-11-14 at 3.29.26 PM.png)) <u>Group Members: </u> Sunil Thapa Yosias Hailu Yu Ning Priscilla Ng Monsi Magal

-- Practice Midterm 2 Solutions

    SELECT firstName

    FROM Employee E1

    WHERE dateOfBirth < ANY

        (SELECT dateOfBirth

        FROM Employee E2

        WHERE E1.firstName = E2.firstName);

    SELECT name 

    FROM MovieExec

    WHERE cert# IN

        (SELECT producerC#

         FROM Movies

         WHERE (title, year) IN

             (SELECT movieTitle, movieYear

              FROM StarsIn

              WHERE starName = 'Harrison Ford' ) );

10) Give an example of a correlated and an example of an uncorrelated query, explain why your examples work. ---- <u>Correlated</u> When our subqueries are evaluated more than one for all the results used in a higher level query it's called corollated subqueries. In particular, nested subqueries that require the subquery to be evaluated many times, once for each assignment of a value to some term in the subquery. The following query is from an Employee table that has firstName and dateOfBirth as two of its many attributes. The query is correlated because subqueries change as we cycle over different firstNames. SELECT firstName FROM Employee E1 WHERE dateOfBirth < ANY (SELECT dateOfBirth FROM Employee E2 WHERE E1.firstName = E2.firstName); <u>Uncorrelated</u> Our subqueries are evaluated once and for all the result used in a higher level query. The following query is from Movie DB schemas we have been using throughout this semester. The query is uncorrelated because the innermost subquery gives a fixed list of movies Harrison Ford stars in, which does not change as we execute the outer query. Also, the 2nd inner query gives a fixed list producerC numbers. An easy to check if the subqueries are uncorrelated is imagining the subquery as a temporary table that is used through the whole query. SELECT name FROM MovieExec WHERE cert# IN (SELECT producerC# FROM Movies WHERE (title, year) IN (SELECT movieTitle, movieYear FROM StarsIn WHERE starName = 'Harrison Ford' ) ); <u>Group Members: </u> Sunil Thapa Yosias Hailu Yu Ning Priscilla Ng Monsi Magal