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

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

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

<nowiki> Student Names: Alexander Duong Adam Ball Ada La Parameswaran Ranganthan

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

<nowiki> Student Names: Alexander Duong Adam Ball Ada La Parameswaran Ranganthan

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

Group members: Hovsep Lalikian, Sam Esmaeili, Erin Yang, Wendy Chen

1. Map weak entity sets and supporting sets to relations

• 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

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

R(name, street, city, title, year)

! name !! street !! city !! title !! year |-

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

Names: Himanshu Mehta, Conover Wang, Ryan Moore, Sherwyn Sen, Andrew Yuan<br>

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)

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

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.

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.

<u>Group Members: </u>

Sunil Thapa

Yosias Hailu

Yu Ning

Priscilla Ng

Monsi Magal

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