Question

Tasks:
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.

Preliminaries:
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 Populate.java 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 Populate.java
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 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.io.*;
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 {

Class.forName("com.mysql.jdbc.Driver");

} catch (ClassNotFoundException e) {

System.out.println("Can't find MySQL JDBC Driver!");
e.printStackTrace();
}
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 + "/",
                connProps);

System.out.println("Connected to database: " + dbname + "!");
return connection;
}...

This is only a preview of the solution. Please use the purchase button to see the entire solution

Related Homework Solutions

Reviewing PL/SQL
Homework Solution
$12.00
Computer
Science
Database
Development
Data
PL/SQL
DBA
Procedures
Triggers
Technology
Applications
SQL Server for Business Intelligence
Homework Solution
$20.00
Computer Science
Database Development
SQL
Server
Business
Intelligence
Multidimensional
Tabular
Analysis
Services
Warehouse
MOLAP
HOLAP
ROLAP
Partition
Engine
MDX
Hierarchies
Fact
Relationship
Property
Unknownmember
Action
Cube
Pr
Database Development Questions
Homework Solution
$20.00
Database
Design
Implementation
Computer Science
Models
Foreign Keys
Tables
Medicine
Diagnosis
Functional Dependencies
Paid Date
Database Development Questions
Homework Solution
$20.00
Database Development
Computer Science
SQL Statements
Tables
Keys
Conditions
Statements
Variables
Teachers
Students
Grades
Courses
Database Developments & SQL Queries
Homework Solution
$20.00
Computer Science
Database Development
Relationships
SQL Queries
MS Access
Relational Schema
Referential Integrity
Books
Authors
Orders
Sales
Data Sets
Tables
Get help from a qualified tutor
Live Chats