Create a database using DLL statements in the mysql command line tool, populate the database row by row from raw data (all the .csv files) using JDBC, write SQL queries to the database to be executed from the mysql command line tool query and manipulate the database programmatically using JDBC.

MYSQL 5.5.28 and Java SE 6 Update 35 JDK,
Use MySQL Connector/J 5.1.22 to connect

Application Domain Description
´┐╝You will implement the database backend for an online video sharing website using the open source MySQL Community Server pseudo-relational database management system.
Obviously, the database needs to keep track of information about registered users of the website and the videos that they upload, view, and rate. Each uploaded video is put into one of a predefined set of content categories. A user can watch the same video multiple times; they may give a different rating to a video each time they view it, or they may not rate it at all.
To make money, the video sharing service depends on revenue generated from advertising. When a user views a video, they are often (but not always) shown an advertisement before the video begins. Any user can create an advertisement and pay a fixed total price to have it shown a specified number of times (number of views wanted). The advertiser can target their advertising at users with relevant interests by specifying one or more content categories for their advertisement; advertisements are more likely to be shown together with videos in those selected categories. For accounting and statistical purposes, the database tracks when advertisements are shown and whether or not users clicked on the advertisement.
Some prolific users who have uploaded many high quality videos may be invited into a revenue- sharing partnership in which they get paid a fraction of the earnings from advertisements that are shown on their uploaded videos.

Part 2: Populating the database

You are required to implement a Java program that will accept filenames of the input data files as command line parameters, open and parse each file, and populate the data contained within them into your database by executing individual INSERT statements for each row using JDBC. Note: when executing the same statement repeatedly, you should use the JDBC PreparedStatement construct for greater efficiency as compared to the more generic Statement class.

Your program should be compiled and executed from the Windows command line or Linux/Mac shell. It should be compiled like this:
> javac -classpath .;mysql-connector-java-5.1.22-bin.jar
and executed like this (as one single long command):
> java -classpath .;mysql-connector-java-5.1.22-bin.jar
Populate <users filename> <videos filename>
<advertisements filename> <ad_target_categories filename> <views filename> <partners filename>

Note that every time you run this program, it should start by first removing the previous data in your tables; otherwise the tables will have redundant or incorrect data.

Part 3: SQL queries on the database

Write the following queries in SQL and run them on your MySQL database via the mysql command line tool. Prepare and submit them as separate files q1.sql to q8.sql. If two or more SQL statements are needed for a single question, they should be written after each other in one file.
Do not create or use views. Storing intermediate query results is not an appropriate use of views.
Q1. List the advertisement ID, and advertiser name and email for each advertisement with a CPM (cost per mille, the price paid per one thousand views wanted) of less than 7 cents.
Q2. Find the total price of all advertisements targeted at each category. (An advertisement targeted at multiple categories is counted in every category that it is targeted at.)
Q3. List the names of all advertisers who have had at least one of their advertisements shown more times than they had requested. Each advertiser should not be listed more than once.
Q4. Find the three partners who earned the most in the month of June 2012. List their names and June 2012 earnings. Partners are paid based on their revenue share and on a per ad shown basis.
Q5. Find all videos that have received no views since the first two weeks after they were uploaded. For each such video, give the name of the upload user who uploaded it, the video title, and the date and time it was uploaded.
Q6. List the names of all users in order of date registered with the newest member first. For those users who have uploaded at least one video, also give the number of views on their most-viewed video.
Q7. Find the five videos with the lowest average rating despite having been rated by their own uploader with the highest rating of 5. List their video ID, title and average rating.
Q8. Find all poorly targeted advertisements.
A poorly targeted advertisement is defined as one that has a lower click-through rate (click- to-view percentage) when shown alongside videos that are in categories that it is targeted at, compared to videos that are not in one of the targeted categories.
For each such advertisement give the advertisement ID, the name and email address of the advertiser, CPM paid on the advertisement, and the overall click-through rates when targeted and non-targeted. Sort the results with the highest CPM first.

Solution PreviewSolution Preview

This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Date;
import java.util.*;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;

public class Populate {

public static String host;
public static String port;
public static String dbname;
public static String username;
public static String password;

//connect to the DBMS using DriverManager
public static Connection getConn() throws SQLException, ClassNotFoundException{

try {


} catch (ClassNotFoundException e) {

System.out.println("Can't find MySQL JDBC Driver!");
Connection connection = null;
System.out.println("MySQL JDBC Driver Registered!");
Properties connProps = new Properties();
connProps.put("user", username);
connProps.put("password", password);
connection = DriverManager.getConnection(
                "jdbc:" + "mysql" + "://" +
                host +
                ":" + port + "/",

System.out.println("Connected to database: " + dbname + "!");
return connection;
$240.00 for this solution

PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

Find A Tutor

View available Database Development Tutors

Get College Homework Help.

Are you sure you don't want to upload any files?

Fast tutor response requires as much info as possible.

Upload a file
Continue without uploading

We couldn't find that subject.
Please select the best match from the list below.

We'll send you an email right away. If it's not in your inbox, check your spam folder.

  • 1
  • 2
  • 3
Live Chats