1. David Janda, Aaron John, Sanford Huynh

import java.sql.*; import java.util.ArrayList;

class JdbcTest { public static void main(String[] args) { Connection conn = null; ResultSet rs = null; Statement stmt = null; try { conn = DriverManager.getConnection("jdbc:mysql://localhost/foo?" + "user=root&password=secret"); stmt = conn.createStatement(); rs = stmt.executeQuery("SELECT * FROM goo");

        ResultSetMetaData rsmd = rs.getMetaData();
        int numberOfColumns = rsmd.getColumnCount();

        ArrayList<ArrayList<String>> results = new ArrayList<>();
        ArrayList<String> row = new ArrayList<>();

        while (rs.next()) {
            int i = 0;
            while (i < numberOfColumns) {
                String s = rs.getString(i);
                row.add(s);
                i++;
            }
            results.add(row);
            row.clear();
        }

        for (int i = 0; i < results.size(); i++) {
            for (int j = 0; j < results.get(i).size(); j++) {
                System.out.print(results.get(i).get(j) + ",");
            }
            System.out.println();
        }

    } catch (SQLException ex) {
        System.out.println("SQLException: " + ex.getMessage());
        System.out.println("SQLState: " + ex.getSQLState());
        System.out.println("VendorError: " + ex.getErrorCode());
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException sqlEx) {
            }
            rs = null;
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException sqlEx) {
            }
            stmt = null;
        }
    }
}

}

1. Spencer Wong, Stefan Do, Jessica Hoang

(a) an anonymous variable slot. Instead of variables, you replace them with underscores Example: LongMovie(t,y) <- Movies(t,y,l,,,_) AND l ≥ 100

(b) A safe variable. A safe variable is a variable that appears anywhere in the rule, and must appear in some nonnegated, relational subgoal of the body.

Example: Ancestor(x, z) <- Father(x, y), Ancestor(y, z). where x = “bob” and z=y=”tony”

(c) Intensional database. Intensional predicates are predicates whose relations are computed by applying one or more Datalog rules which are not facts. The collection of all intesional predicates in a database we call the intensional database (IDB). (collection of calculated rules) Example: H(x,z)<−R(x,y),S(y,z)

(d) Extensional Database. Extensional predicates are predicates whose relation is stored in a database (can be thought of a collection of fact rules. The collection of all intensional predicates in a database we call the Extensional database (EDB). Example: Father("bob", "tony") <-.

1. Iris Cheung, Adam Golab, Joseph Nguyen

https://i.imgur.com/hYxaeab.jpg

Isabelle, Harshit, Arthur

'''<u>Problem 7</u>'''

-- a subquery in a FROM clause SELECT name FROM MovieExec, (SELECT producerC# FROM Movies, StarsIn WHERE title = movieTitle AND year = movieYear AND starName = 'Harrison Ford' ); -- b natural join select * from (Employee NATURAL JOIN Projects);

-- c duplicate emilination SELECT DISINCT managerId FROM Project;

-- d union as bags: (SELECT title, year FROM Movies) UNION ALL (SELECT MovieTitle as title, movieYear AS year FROM StarsIn);

1. Yun Cao, Thy Nguyen, Chuan Seng Edward Lim, Sero Nazarian

a. UPDATE Employee SET salary = salary * 1.1 WHERE lastname='Pollet';

b. DELETE FROM Employee WHERE lastname = 'Negativity';

c. CREATE TABLE Employee ( EmployeeID VARCHAR(10) PRIMARY KEY, name VARCHAR(32) NOT NULL, supervisorID VARCHAR(10), FOREIGN KEY (supervisorID) REFERENCES Employee(EmployeeID) );

1. Sophie Chen, Aaron Francis, Yu Cao

https://drive.google.com/file/d/1zDU7obpoQxlMxrzZymAm_-dguAX8lm5h/view?usp=sharing

1. Connie Huynh, Chanip Chong

<br />The view should be defined by selecting (without distinct) some attributes from one relation R (which is also allowed to be an updatable view). <br /> The WHERE clause is not allowed to involve R in a subquery. <br /> The FROM clause can only consist of one occurrence of R and no other relation. <br /> The list in the SELECT clause must include enough attributes that for every tuple inserted into the view, we can fill the other attributes out with NULL or the proper default values.

1. Tan Nguyen, Welby Chan, Alexander Len

a/ SELECT title AS NAME FROM Movies

WHERE year >= 1995

ORDER BY year ASC

b/ SELECT name

FROM (StarIn SI1 JOIN StarIn SI2 ON SI1.name = SI2.name)

WHERE SI1.year <1950 AND SI2.year >1980)

BY TOAN TRAN, SUDHANSHU DANGI, AND PARMEET SINGH

6) Define the following and give an example: (a) Left Outer Join,.(b) nested for Loop query semantics. (c) correlated subquery.

A)LEFT OUTER JOIN is the dangling tuples of the left argument R are pad with (NIL) and add to the result.

EX: 	U = A(1, 4, 7), B(2, 5, 8), C(3, 6, 9) 

	V = B(2, 2, 6), C(3, 3, 7), D(10, 11, 12)

The LEFT OUTER JOIN of U & V is A(1, 1, 4, 7), B(2, 2, 5, 8), C(3, 3, 6, 9), D(10, 11, NIL, NIL)

B) Nested for loop query semantics is used when there are several tuple variables in which each variable range over the tuples of their respective relation. The FOR clause assign the tuple expression to the tuple variable to decide whether the WHERE clause is true, then we produce a tuple consisting of the values of the expression followed by a SELECT clause.

EX: 

LET the tuple variables in the from-clause range over relations R1,R2,...,Rn;

FOR each tuple t1 in relation R1 DO 

	...something

	FOR each tuple t2 in relation R2 DO

		FOR each tuple tn in relation Rn DO

			IF the where-clause is satisfied when the values from t1,t2,... ,tn are substituted for all attribute references THEN

				evaluate the expressions of the select-clause according to t1,t2,... ,tn andproducethe tuple of values that results.

C) Correlated subquery is a nested subqueries requires the subquery to be evaluated many times, once for each assignment of a value to some term in the subquery that comes from a tuple variable outside the subquery.

EX: Given this relation.

Movies(title, year, length, genre, studioName, producerC#)

We want to find the titles that have been used for two or more movies. We start with an outer query that looks at all tuples in the relation.

SELECT title

FROM Movies Old

WHERE year < ANY

	(SELECT year 

	FROM Movies

	WHERE title = Old.title);

Ealrada Piroyan, Devanshi Sukhadia, Zizhen Huang

[[https://drive.google.com/file/d/1gW3QUTulA9natO_BwjLOz_AvdZAVPCFo/view?usp=sharing|Answer to question#3]]

a) γ COUNT(title) (δ (πtitle (StarsIn)))

b) τStarName(δStarName,MIN(year)->minYear(StarIn))