CS157a Fall 2023Practice Final

To study for the final I would suggest you:

1. Know how to do (by heart) all the practice problems.
2. Go over your notes at least three times. Second and third time try to see how much you can remember from the first time.
3. Go over the homework problems.
4. Try to create your own problems similar to the ones I have given and solve them.
5. Skim the relevant sections from the book.
6. If you want to study in groups, at this point you are ready to quiz each other.

Here are some facts about the actual final:

1. It is comprehensive.
2. It is closed book, closed notes. Nothing will be permitted on your desk except your pen (pencil) and test.
3. You should bring photo ID.
4. There will be more than one version of the test. Each version will be of comparable difficulty.
5. It is 10 problems (3pts each), 6 problems will be on materials since the second midterm, 4 problems will be from the topics of the midterm.
6. Two problems will be exactly (less typos) off of the practice final, and one will be off of the practice midterm.

The practice final is below:

1. Give example of how to model the following things with an E/R diagram: (a) An Order relationship involves one Buyer and one Seller Entities, (b) The TALL_SHIP entity set is a sub-entity of BOAT, (c) DEPENDENT is a weak entity set that depends on EMPLOYEE.
2. Show three different ways that an Order relationship involves one Buyer and one Seller Entities could be mapped from an E/R diagram to the relational model.
3. Suppose we have a relational schema Movies(title, year, length, genre, studioName, producerC#), MovieStar(name, address, gender, birthdate), StarsIn(movieTitle, movieYear, starName). Express using the extended relational algebra with bag semantics, the following queries: (a) Unique titles after 1970 of movies starring Robert Redford. (b) The number of movies after 2000 which had more female stars than male stars, (c) For each distinct starName in StarsIn, one row in the output with the name of a star, the year of the first movie they appeared in and the total number of movies they have appeared in sorted ascending by starName.
4. Show how set union, interection and difference of relations can be computed using Datalog rules.
5. Express the following queries in SQL (using the relations from Problem 3): (a) the title output as name and length output as duration of movies produced by MGM in 2010, (b) Distinct movie titles contain the word "Little" starring someone whose name contains "Charlie" sorted in descending order of year (use join syntax), (c) The name of each studio that made more than 3 movies and the number of movies they made each year since 1980.
6. Define and give an example of the following concepts: (a) tuple variables, (b) nested for loop semantics of joins, (c) correlated subquery.
7. Give examples of performing the following using SQL (a) a subquery from the FROM clause (b) right outer join, (c) INSERTs using a query.
8. Give examples of the following DDL commands in SQL: (a) creating a table with a foreign key constraint which when the referenced value is delete sets the column value to null, (b), a Mysql Trigger used to update of the counts of log messages in LOG_DATA of different log levels when inserts are done, (c) creating a stored procedure that uses a cursor.
9. Give the Java code needed to initialize the JDBC driver for Mysql and open a connection to the database test1 on localhost using the default port for Mysql.
10. Give an example XML DTD that would be suitable for a collection of business cards. Create a document of this type. Give the PostgresSQL code needed to create a table with at least one column for xml data. Show in PostgresSQL how to insert your document into this table, and do a simple XPath query of it.