Please code in Java, NO CSS STYLING OR ANYTHING ELSE. Regular java. Please do it in some coding program and check if it works. Put the answer here.

TPC-H is one of the most important benchmarks in the area of databases. All large database systems try to optimize their performance for this benchmark. In order to compare how efficient a database processes queries, it makes sense to program queries by hand in order to determine what a database system might achieve if it incurred no overhead.

The goal of this assignment is to read in three files, parse their contents and join them in order to answer simple queries to the given data set as efficiently as possible.

The queries which your program is meant to answer are as follows. What is average quantity of line items of a customer's order belonging to a particular market segment. The signature of the corresponding method is given by public long getAverageQuantityPerMarketSegment(String marketsegment).

Subsequently, your approach is detailed so that no further database background is required.

Reading in Data

The template provides you with classes Customer, LineItem and Order representign the corresponding database tables. The class Database with your implementation has already the static attribute private static Path baseDataDirectory together with a setter method to change its value for tests.

Your first task is to read in the three included *.tbl files form the baseDataDirectory. Your implemented methods should look as follows:

public static Stream processInputFileLineItem(), public static Stream processInputFileCustomer() and public static Stream processInputFileOrder(). .tbl files are .csv files which instead of , use | for separating values.

The Quantity of Lineitems you should parse with Integer.parseInt(str) * 100 in order to subsequently operate on int values only.

HashJoins

In order to realize Joins of tables, you should use the classes of the interface Map in Java. First, you should map for each Customer, custKey -> marketSegment. Then, you should map for each Order, orderkey -> marketSegment by using custkey. Now, you should use this second Map for iterating over all LineItems in order to determine to which marketSegment it belongs.

By this approach, you should then be able to determine the average quantity of LineItems fr a given marketSegment.

Implementation

Implement the method public long getAverageQuantityPerMarketSegment(String marketsegment) of the class Database which as parameter receives a String marketsegment and returnd the average quantity of LineItem per order in marketsegment. During computation of the average, store the number as well as the total sum as long. Only at the very end, compute the quotient of these two values and return it again as long value (integer division). You are permitted to introduce further auxiliary methods for this class.

All other classes should be left unchanged!

classes that are provided

customer:

package fop.w10join;

import java.util.Arrays;

public class Customer { int custKey; String mktsegment; private char[] varChar; private int nationkey; private char[] phone; private float acctbal; private char[] comment;

public Customer(int custKey, char[] varChar, int nationkey, char[] phone, float acctbal, String mktsegment, char[] comment) { this.custKey = custKey; this.varChar = varChar; this.nationkey = nationkey; this.phone = phone; this.acctbal = acctbal; this.mktsegment = mktsegment; this.comment = comment; }

public void output() { System.out.println(this.toString()); }

@Override public String toString() { return "Customer{" + "custKey=" + custKey + ", varChar=" + Arrays.toString(varChar) + ", nationkey=" + nationkey + ", phone=" + Arrays.toString(phone) + ", acctbal=" + acctbal + ", mktsegment=" + mktsegment + ", comment=" + Arrays.toString(comment) + '}'; } }

Order:

package fop.w10join;

import java.time.LocalDate; import java.util.Arrays;

public class Order { int orderKey; int custKey; private char orderStatus; private float totalPrice; private LocalDate orderDate; private char[] orderPriority; private char[] clerk; private int shippingPriority; private char[] comment;

public Order(int orderKey, int custKey, char orderStatus, float totalPrice, LocalDate orderDate, char[] orderPriority, char[] clerk, int shippingPriority, char[] comment) { this.orderKey = orderKey; this.custKey = custKey; this.orderStatus = orderStatus; this.totalPrice = totalPrice; this.orderDate = orderDate; this.orderPriority = orderPriority; this.clerk = clerk; this.shippingPriority = shippingPriority; this.comment = comment; }

@Override public String toString() { return "Order{" + "orderKey=" + orderKey + ", custKey=" + custKey + ", orderStatus=" + orderStatus + ", totalPrice=" + totalPrice + ", orderDate=" + orderDate + ", orderPriority=" + Arrays.toString(orderPriority) + ", clerk=" + Arrays.toString(clerk) + ", shippingPriority=" + shippingPriority + ", comment=" + Arrays.toString(comment) + '}'; }

public void output() { System.out.println(this.toString()); } }

Line Item:

package fop.w10join;

import java.time.LocalDate; import java.util.Arrays;

public class LineItem { int orderKey; int quantity; private int partKey; private int suppKey; private int lineItem; private float extendedPrice; private float discount; private float tax; private char returnFlag; private char lineStatus; private LocalDate shipDate; private LocalDate commitDate; private LocalDate receiptDate; private char[] shipInstruct; private char[] shipMode; private char[] comment;

public LineItem(int orderKey, int partKey, int suppKey, int lineItem, int quantity, float extendedPrice, float discount, float tax, char returnFlag, char lineStatus, LocalDate shipDate, LocalDate commitDate, LocalDate receiptDate, char[] shipInstruct, char[] shipMode, char[] comment) { this.orderKey = orderKey; this.partKey = partKey; this.suppKey = suppKey; this.lineItem = lineItem; this.quantity = quantity; this.extendedPrice = extendedPrice; this.discount = discount; this.tax = tax; this.returnFlag = returnFlag; this.lineStatus = lineStatus; this.shipDate = shipDate; this.commitDate = commitDate; this.receiptDate = receiptDate; this.shipInstruct = shipInstruct; this.shipMode = shipMode; this.comment = comment; }

@Override public String toString() { return "LineItem{" + "orderKey=" + orderKey + ", partKey=" + partKey + ", suppKey=" + suppKey + ", lineItem=" + lineItem + ", quantity=" + quantity + ", extendedPrice=" + extendedPrice + ", discount=" + discount + ", tax=" + tax + ", returnFlag=" + returnFlag + ", lineStatus=" + lineStatus + ", shipDate=" + shipDate + ", commitDate=" + commitDate + ", receiptDate=" + receiptDate + ", shipInstruct=" + Arrays.toString(shipInstruct) + ", shipMode=" + Arrays.toString(shipMode) + ", comment=" + Arrays.toString(comment) + '}'; }

public void output() { System.out.println(this.toString()); } }

work in here the database:

package fop.w10join;

// TODO Import the stuff you need

public class Database { private static Path baseDataDirectory = Paths.get("data");

public static void setBaseDataDirectory(Path baseDataDirectory) { Database.baseDataDirectory = baseDataDirectory; }

public static Stream processInputFileCustomer() { // TODO }

public static Stream processInputFileLineItem() { // TODO

// For quantity of LineItems please use Integer.parseInt(str) * 100. }

public static Stream processInputFileOrders() { // TODO }

public Database() { // TODO }

public static void main(String[] args) { // TODO Test your implementation } }