Description
Querying the Fakebook Database with JDBC
Overview
While Project 1 focused primarily on database design, in this project you will focus on writing
SQL queries. In addition, you will embed your SQL queries into Java source code (using JDBC)
to implement “Fakebook Oracle,” a standalone program that answers several queries about the
Fakebook database. For this project, you will use a standardized schema that we provide to
you, rather than the schema that you designed in Project 1. You will also have access to our
public Fakebook dataset for testing.
1. Files Provided to You
You are provided with 3 Java files: TestFakebookOracle.java , FakebookOracle.java
and MyFakebookOracle.java . We have also provided a jar file ojdbc6.jar. Place all
these 4 files, including the jar file under a folder project2. In addition, we have provided you a
file solution-public. txt showing sample query results. When submitting your completed
project, you only need to turn in MyFakebookOracle.java .
1) TestFakebookOracle.java
This file provides the main function for running the program. You can use it to run and test your
program, but you don’t need to turn it in.
Please only modify the oracleUserName and password static variables, replacing them with
your own information.
1
2) FakebookOracle.java
DO NOT modify this file, although you are welcome to look at the contents if you are curious.
This class defines the query functions (discussed below in Section 3) as abstract functions,
which you must implement for Project 2. It also defines some useful data structures and
provides formatted print functions for you to make use of.
3) MyFakebookOracle.java
This is a subclass of FakebookOracle , in which you must implement the query functions. You
should ONLY fill in the body for each of the query functions. DO NOT make any other changes.
In this project, you only need to store the results of the queries in our predefined data structures
(which we have provided as member variables in the class). You don’t need to worry about
output formatting. In the base class FakebookOracle.java , a set of print functions have
been provided for you to view the query results.
The MyFakebookOracle class contains parameterized names for the tables you will need to
use in your queries, and they are constructed in the class constructor as shown in the following
lines of code. You should always use the corresponding variable when you are referring to a
table in the SQL statement to be executed through JDBC. For example, you should use the
variable cityTableNam e instead of using a constant value such as ‘PUBLIC_CITIES’ in your
Java code.
2
For creating and managing the database connection, you should use the predefined object
oracleConnection.
4) solution-public.txt
This file contains the output query results from running our official solution implementation on
the public dataset provided to you. You can make use of this to check whether or not your
queries are generating the same results from the same input dataset.
Note that your submission will be graded using a different input dataset, so producing correct
results on the public dataset is not a guarantee that your solution is entirely correct. Make sure
that your queries are designed to work more generally on any valid input dataset, not just the
sample data we provide. Also, think carefully about the semantics of your queries since it may
not be always possible to test them in all scenarios and you often will not have the benefit of
knowing the correct answers in practice.
2. Tables
For this project, your schema will consist of the following twelve tables:
1. ._USERS
2. ._FRIENDS
3. ._CITIES
3
4. ._PROGRAMS
5. ._USER_CURRENT_CITY
6. ._USER_HOMETOWN_CITY
7. ._EDUCATION
8. ._USER_EVENTS
9. ._PHOTOS
10. ._ALBUMS
11. ._TAGS
To Access Public Fakebook Data:
should be replaced with “PUBLIC” to access the public Fakebook data tables.
The public data tables are stored in the GSI’s account name (tajik). Therefore, you should
use the GSI’s account name as in order to access the public tables directly within
SQL*Plus for testing your queries. For example, to access the public USERS table, you should
refer to the table name as tajik.PUBLIC_USERS . In the Java files provided to you, the above
table names are already pre-configured in the given code.
3. Queries (100 points)
There are 10 total queries (Query 0 to Query 9). Query 0 is provided to you as an example, and
you are left to implement the remaining nine. The points are as shown. The queries vary
tremendously in terms of difficulty. If you get stuck on a harder query, try an easier one first and
come back to the tough one later. For all of these queries, sample answers on the given data
are available in the attached zip file. If the English description is ambiguous, please look at the
sample answers.
Also, for all of these queries, when feasible, you should try to do most of heavy-lifting to answer
the query within SQL. For example, if a query requires you to present the data in sorted order,
use ORDER BY in your query rather than retrieving the result and then sorting it within Java.
4 EECS 484
Also, the grading program we use does impose a time limit on the time it waits for a query. If a
query appears to be taking too much time, you should consider rewriting it in a different way to
make it faster. Nested queries are usually more expensive to run.
Query 0: Find information about month of birth (0 points)
This function has been implemented for you in MyFakebookOracle.java , so that you can
use it as an example. The function computes the month in which the most users were born and
the month in which the fewest users were born. The names of these users are also retrieved.
The sample function uses the Connection object, oracleConnection, to build a
Statement object. Using the Statement object, it issues a SQL query, and retrieves a
ResultSet. It iterates over the ResultSet object, and stores the necessary results in a Java
object. Finally, it closes both the Statement and the ResultSet objects.
Query 1: Find information about names (10 points)
The next query asks you to find information about users’ names, including 1) the longest first
name, 2) the shortest first name, and 3) the most common first name. If there are ties, you
should include all of the matches in your result.
The following code snippet illustrates the data structures that should be constructed. However,
it is up to you to add your own JDBC query to answer the question correctly.
5
Query 2: Find “lonely” users (10 points)
The next query asks you to find information about all users who have no friends in the network.
Again, you will place your results into the provided data structures. The sample code in
MyFakebookOracle.java illustrates how to do this.
Query 3: Find “world travelers” (10 points)
The next query asks you to find information about all users who no longer live in their
hometowns. In other words, the current_city associated with these users should NOT be
the same as their hometown_city (neither should be null). You will place your result into
the provided data structures.
Query 4: Find information about photo tags (10 points)
For this query, you should find the top n photos that have the most tagged users. You will also
need to retrieve information about each of the tagged users. If there are ties (i.e. photos with
the same number of tagged users), then choose the photo with smaller id first. This will be
string lexicographic ordering since the data types are VARCHARs (for instance, “10” will be less
than “2”).
Query 5: Find users to set up on dates (15 points)
For this task, you should find the top n “match pairs” according to the following criteria:
(1) One of the users is female, and the other is male (Note: they do not have to be friends of
the same person)
(2) Their age difference is <= yearDiff (just compare the years of birth for this).
(3) They are not friends with one another
(4) They should be tagged together in at least one photo
You should return up to n “match pairs.” If there are more than n match pairs, you should break
ties as follows:
(1) First choose the pairs with the largest number of shared photos
(2) If there are still ties, choose the pair with the smaller user_id for the female
(3)If there are still ties, choose the pair with the smaller user_id for the male
6 EECS 484
Query 6: Suggest friends based on mutual friends (15 points)
For this part, you will suggest potential friends to a user based on mutual friends. In particular,
you will find the top n pairs of users in the database who have the most common friends, but are
not friends themselves. Your output will consist of a set of pairs (user1_id, user2_id). No
pair should appear in the result set twice; you should always order the pairs so that user1_id
< user2_id.
If there are ties, you should give priority to the pair with the smaller user1_id. If there are still
ties, then give priority to the pair with the smaller user2_id.
Finally, please note that the _FRIENDS table only records binary friend relationships once,
where user1_id is always smaller than user2_id. That is, if users 11 and 12 are friends, the
pair (11,12) will appear in the _FRIENDS table, but the pair (12,11) will not appear.
Query 7: Find the most popular states to hold events (10 points)
Find the name of the state with the most events, as well as the number of events in that state. If
there is a tie, return the names of all the tied states. Again, you will place your result in the
provided data structures, as demonstrated in MyFakebookOracle.java .
Query 8: Find oldest and youngest friends (10 points)
Given the user_id of a user, your task is to find the oldest and youngest friends of that user. If
two friends are exactly the same age, meaning that they were born on the same day, month,
and year, then you should assume that the friend with the larger user_id is older.
Query 9: Find the pairs of potential siblings (10 points)
A pair of users are potential siblings if they have the same last name and hometown, if they are
friends, and if they are less than 10 years apart in age. While doing this, you should compute
the year-wise difference and not worry about months or days. Pairs of siblings are returned with
the lower user_id user first. They are ordered based on the first user_id and, in the event of
a tie, the second user_id.
7
4. Compiling and running your code
You are provided with an Oracle JDBC Driver (ojdbc6.jar). This driver has been tested with Java
JDK 1.7 and JDK 1.8.
If you are unsure which Java development environment you prefer to use, we suggest that you
develop your code in Eclipse. You can do this by creating a Java Project called ‘project2’ inside
Eclipse, and then Importing the 3 Java source files to the project.
You should also add your JDBC driver’s JAR to Eclipse’s classpath. To do this in Eclipse, go to
‘Project Settings’ then ‘Java Build Path’, and then click on the ‘Libraries’ tab, then ‘Add External
JAR’.
If you prefer, you can just use an editor (e.g. vi or emacs) to develop your code. In this case,
you should create a directory called ‘project2’ and put the three Java source files provided to
you in this directory.
To compile your code you should change to the directory that contains ‘project2’. In other words,
suppose you created the directory ‘project2’ in /your/home/Private/EECS484 .
cd /your/home/Private/EECS484
Then, you can compile the Java source files as follows:
javac project2/FakebookOracle.java project2/MyFakebookOracle.java
project2/TestFakebookOracle.java
You can run your program as follows (note that you should set the class path (-cp) for your copy
of ojdbc6.jar):
java -Xmx64M -cp “project2/ojdbc6.jar:” project2/TestFakebookOracle
Note the colon (:) after ojdbc6.jar.
Connect from Campus or over a VPN
If you get a timeout error from Oracle, make sure you connect from campus or use the
University VPN to connect (see this page for information: http://www.itcom.itd.umich.edu/vpn/. It
8
is possible that the guest wireless network may not work for remote access to the database
without being on the VPN. Alternatively, use UM Wireless or a CAEN machine directly for your
development.
5. Testing
A good strategy to write embedded SQL is to first test your SQL statements in a more
interactive database client such as SQL*Plus before writing them inside Java code, especially
for very complex SQL queries. You have the public dataset available to test your application.
We provide you with the output from our official solution querying against the public data
(available in solution-public.txt ). You can compare your output with ours to debug.
During grading, we will run your code on a second (hidden) dataset.
6. Submission and Grading
You only need to turn in MyFakebookOracle.java . Please submit it by going to the online
autograder: https://grader484.eecs.umich.edu
If you are working in a team, then you should join a team first and then submit. Both partners
must submit individually, even for a group project.
While no online autograder is currently available, we will be grading your answers to the
queries using an automated script, so it is important that you adhere to the given instructions,
and that your file, MyFakebookOracle.java , works correctly with an unmodified version of
FakebookOracle.java .
We might later adjust (reduce) the autograder’s score if we notice poor Java/SQL programming
style. Here are the key elements:
● For each of these tasks, think carefully about what computation is best done in SQL,
and what computation is best done in Java. Of course, you can always compute the
“correct” answer by fetching all of the data from Oracle, and loading it into Java virtual
memory, but this is very inefficient! Instead, most of the computation can (and
should!) be done in SQL, and you should only retrieve the minimum amount of
data necessary.
● Close all SQL resources cleanly. See Appendix below.
9
● Make sure your queries are nicely formatted and readable. Explain the logic briefly in
comments if the query is complicated.
● You are not being evaluated on optimizing the queries. So, no need to worry about that.
However, if you find that they are taking inordinately long, then you should think about
simplifying the queries. Else, they could fail the tests.
● Generally, non-nested queries are preferred over nested ones, when feasible. It may
not be feasible to do so in all cases. Basically, think about simplifying the queries and
use comments to explain, if needed, so that someone other than you can understand
your logic.
10
Appendix: Tips on Closing Your JDBC Resources Cleanly
It is important that you close your JDBC connections properly. Otherwise, you risk getting locked
out of the database server. Even if you kill your Java program, it is possible that the database
server thinks that the client program is still around. It may keep its end of the connection open,
waiting for data. This could eventually lock you out of the database if you end up creating too
many instances of open connections.
Here is a real example of an unfortunate situation posted by a student:
“So I’ve been having this same problem, but I locked myself over 24 hours ago and I still don’t have
access. I’m not sure what to do since ITS isn’t open and if CAEN can’t help I’m skeptical anyway. I
created this problem by my SQL queries were running on forever (can you even get into an infinite loop in
SQL? I think I was) and when you Ctrl+C out it closes SQL improperly. I’ve learned from my mistake now,
but it’s too late and I can’t get back in.”
Here are a few tips to help reduce the likelihood of the above type of problem:
1. First, use SQL*Plus to debug your queries rather than using Java. Also make sure you quit
your SQL*Plus sessions, otherwise you can still get locked out. It may help to design your
queries on paper first and avoid too much nesting of queries. Nested queries tend to run
slower as query optimizers have difficulty handling them. The above student was smart
enough to do that but still ran into trouble. So, let’s look at one more thing to do (Step 2)
that may help.
2. Make sure you close all Connections, Statements, and ResultSets in your code.
This is tricky to do. Read this for some of the nuances:
http://blog.shinetech.com/2007/08/04/howtoclosejdbcresourcesproperlyeverytime/
The problem is, even closing a connection can lead to an exception in rare cases.
We suggest using try-with-resources statements in your Java code to automatically close any
JDBC objects that you create. This is a newer feature introduced in Java SE 7 that makes
closing resources easier and more reliable. The next page shows an example of how this can
be done for JDBC connections.
11
// Example use of a try-with-resources statement
public static void viewTable(Connection con) throws SQLException {
String query = “SELECT COF_NAME, SUP_ID, PRICE, SALES, TOTAL FROM
COFFEES”;
try (Statement stmt = con.createStatement()) {
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
String coffeeName = rs.getString(“COF_NAME”);
int supplierID = rs.getInt(“SUP_ID”);
float price = rs.getFloat(“PRICE”);
int sales = rs.getInt(“SALES”);
int total = rs.getInt(“TOTAL”);
System.out.println(coffeeName + “, ” + supplierID + “, ” +
price + “, ” + sales + “, ” + total);
}
} catch (SQLException e) {
System.err.println(e.getMessage());
}
}
This code is adapted from an example at:
https://docs.oracle.com/javase/tutorial/essential/exceptions/tryResourceClose.html
Feel free to take the code and adapt it to your needs. The above code assumes that you only
need to use a single Statement object, for example. That may not always be appropriate. For
example, you may sometimes want to use multiple Statement objects to execute different
fixed queries, in which case you could have one try-with-resources block for each Statement
object that you create. Since a Connection object is provided to you in the Project 2 code,
you do not need to worry about creating a try-with-resources block for the Connection object
that you use in your query implementations.
If you think about the problem, it is actually pretty hard for a database server to distinguish
between a slow client at the other end and a dead client. Remember that the communication
between the client and the server occurs over a network. Doing all you can within Java to close
the connections in all possible situations (including when queries fail) will help the server
greatly.
12
