CS157a Fall 2019Practice 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 (2pts 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. Make an E/R diagram that represents the situation where we have Musician entities that participate in a many-one Record-for relationship with MusicLabel entitities. List at least a couple attributes for each entity and relationship. Indicate primary keys.
2. What is a UML Association Class? Give a diagram containing it and then show how this diagram would be converted to the Relational Model.
3. Suppose we have the StarsIn(title, year, starName) relation. Explain how to do the following in the relation algebra: (a) Determine the number of distinct movie titles StarsIN contains. (b) For each starName, output starName together the year of their first movie as minYear sorting the result by starName.
4. Define the following in datalog and give an example: (a) an anonymous variable slot. (b) A safe variable. (c) Intensional database. (d) Extensional Database.
5. Assume we have the Movies and StarsIn relations we have been using throughout the semester. Express the following queries in SQL: (a) Movies titles output as name of movies since 1995 sorted by year. (b) Stars who appear in movies before 1950 and after 1980.
6. Define the following and give an example: (a) Left Outer Join,.(b) nested for Loop query semantics. (c) correlated subquery.
7. Give examples of the SQL syntax for the following: (a) Subquery in a FROM clause. (b) Natural Join. (c) duplicate elimination. (d) Union as bags.
8. Give the SQL for the following DML operations: (a) Give all employees in an EMPLOYEE table whose last name is Pollett a 10 percent raise. (b) Delete all people from EMPLOYEE whose last name is Negativity. (c) Create an EMPLOYEE table with a SupervisorID column which foreign key reference the EmployeeID column.
9. Give our simplified conditions under which a view might be updatable.
10. Give a short Java code snippet that demonstrates setting up a JDBC connection to a Mysql database foo running on localhost as user root with secret as the password. Your program should then return all rows and columns of table goo as sequence output lines where columns values are delimited by commas.