Adv Database Systems

16.11 Design a query plan for the join example in Section 16.3.1, assuming the same table sizes as in that section, but with the following differences:

A.      An application at site B requested the join. 

1)      Send Student (Id, Major) from site B to site C, Do natural JOIN with Transcript ( StudID, CrsCode) at Site C, Send it back to Site B

§  B -> C             |   (natural JOIN )        at C |      C -> B

§  (15,000 * 12)          +         ( 20,000 * 18)   =    540,000 bytes

2)      Send Transcript ( StudID, CrsCode) from Site C to Site B and perform natural join at Site B.

§ 
C -> B    | (natural JOIN )         at B

§  (20,000 * 15) =  300,000 bytes

Option 2 is best.  

B.      An application at site C requested the join.

1)      Send Transcript ( StudID, CrsCode) from site C to site B, Do natural JOIN with Student (Id, Major) at Site B, Send it back to Site C.

§  C -> B             |    (natural JOIN )       at B |      B -> C

§  ( 20,000 * 15)            +            (20,000 * 18)   =    660,000 bytes

2)      Send Student (Id, Major) from Site B to Site C and perform natural join at Site C.

§  B -> C                 | (natural JOIN )         at C

§  (15,000 * 12)        =     180,000 bytes

Option 2 is best.  

17.7 Suppose that an application has four dimension tables, each of which contains 100 rows.

a.       Determine the maximum number of rows in the fact table.

ü As we have 100 rows in each of the four Dimension tables,

The fact table must have 100 * 100 * 100 * 100 tuples in total.

Thus Maximum number of rows in the fact table should be 100,000,000 tupes.

b.      Suppose that a one-dimension table has an attribute that can take on 10 values. Determine the size in bytes of a bit index on that attribute.

ü  I think the answer to this question is not specific,

According to the question the size of the index on that attribute depends on the DATA Type + Number of Tuples ( 10 in this case ) + the maximum size of the attribute.

For e.g. if the attribute is INTEGER(10) that has 10 values,

So size would be -> 4 byte * 10 * 10 = 400 bytes

                                                E.G.2 If the attribute is FLOAT(10) that has 10 values,

                                                                Size will be -> 8 byte * 10 * 10 = 800 bytes

                                                E.G 3 If the attribute is VARCHAR(10) that has 10 values,

                                                                Size will be -> 2 bytes * 10 * 10 = 200 bytes

So there could be multiple answers for this question, according to what one has supposed in their table

c.       Determine the maximum number of tuples in a join index for a join between one of the dimension tables and the fact table.

ü As I have determined in 17.7 (a) that my Fact table consists maximum of 100,000,000 tuples.

üIf I make a join between one of the dimension tables with the fact table,

TOTAL TUPLES will obviously 100,000,000 only.

#4. Socket Prgrmming | MailClient Source Code.

//Message.java
//Generating Header and message body

import java.util.*;
import java.text.*;

public class Message {
    /* The headers and the body of the message. */
    public String Headers;
    public String Body;

    /* Sender and recipient. With these, we don't need to extract them
       from the headers. */
    private String From;
    private String To;

    /* To make it look nicer */
    private static final String CRLF = "\r\n";

    /* Create the message object by inserting the required headers from
       RFC 822 (From, To, Date). */
    public Message(String from, String to, String subject, String text) {
/* Remove whitespace */
From = from.trim();
To = to.trim();
Headers = "From: " + From + CRLF;
Headers += "To: " + To + CRLF;
Headers += "Subject: " + subject.trim() + CRLF;

/* A close approximation of the required format. Unfortunately
  only GMT. */
SimpleDateFormat format =
   new SimpleDateFormat("EEE, dd MMM yyyy HH:mm:ss 'GMT'");
String dateString = format.format(new Date());
Headers += "Date: " + dateString + CRLF;
Body = text;
    }

    /* Two functions to access the sender and recipient. */
    public String getFrom() {
return From;
    }

    public String getTo() {
return To;
    }

    /* Check whether the message is valid. In other words, check that
       both sender and recipient contain only one @-sign. */
    public boolean isValid() {
int fromat = From.indexOf('@');
int toat = To.indexOf('@');

if(fromat < 1 || (From.length() - fromat) <= 1) {
   System.out.println("Sender address is invalid");
   return false;
}
if(toat < 1 || (To.length() - toat) <= 1) {
   System.out.println("Recipient address is invalid");
   return false;
}
if(fromat != From.lastIndexOf('@')) {
   System.out.println("Sender address is invalid");
   return false;
}
if(toat != To.lastIndexOf('@')) {
   System.out.println("Recipient address is invalid");
   return false;
}
return true;
    }
 
    /* For printing the message. */
    public String toString() {
String res;

res = Headers + CRLF;
res += Body;
return res;
}
}

#3. Socket Prgrmming | MailClient Source Code.

//MailClient.java
//User Interface is being developed here!

 import java.io.*;
import java.net.*;
import java.awt.*;
import java.awt.event.*;
import javax.swing.JOptionPane;
/*MY INFO  */
/**
 * @author Alek
 * amodi@clunet.edu
 *
 * Using Cal Lutheran Local Mailserver and port number
 * this app can send and process SMT Protocol.
 *
 */
//Use this- smtpauth.callutheran.edu

/*
 * Developed on - Sep 08, 2011
 *
 * WireShark Lab work - MailClient
 */


public class MailClient extends Frame
{
private static final long serialVersionUID = 1L;
private Button JButtonSend = new Button("Send");
private Button JButtonClear = new Button("Clear");
private Button JButtonQuit = new Button("Quit");  
private Label Slb = new Label("Local mailserver:");
private TextField Sfield = new TextField(" ", 40);  
private Label FLabel = new Label("From:");  
private TextField FField = new TextField("", 40);
private Label toLabel = new Label("To:");
private TextField toField = new TextField("", 40);
private Label subjectLabel = new Label("Subject:");
private TextField subjectField = new TextField("", 40);
private Label messageLabel = new Label("Message:");  
private TextArea messageText = new TextArea(10, 40);  

public MailClient()
{  
super("Java Mailclient - By Alek Modi");  
// Organize the layout    
Panel serverPanel = new Panel(new BorderLayout());
Panel fromPanel = new Panel(new BorderLayout());
Panel toPanel = new Panel(new BorderLayout());  
Panel subjectPanel = new Panel(new BorderLayout());
Panel messagePanel = new Panel(new BorderLayout());  
serverPanel.add(Slb, BorderLayout.WEST);  
serverPanel.add(Sfield, BorderLayout.CENTER);
fromPanel.add(FLabel, BorderLayout.WEST);  
fromPanel.add(FField, BorderLayout.CENTER);  
toPanel.add(toLabel, BorderLayout.WEST);  
toPanel.add(toField, BorderLayout.CENTER);
subjectPanel.add(subjectLabel, BorderLayout.WEST);
subjectPanel.add(subjectField, BorderLayout.CENTER);
messagePanel.add(messageLabel, BorderLayout.NORTH);
messagePanel.add(messageText, BorderLayout.CENTER);
Panel fieldPanel = new Panel(new GridLayout(0, 1));
fieldPanel.add(serverPanel);
fieldPanel.add(fromPanel);  
fieldPanel.add(toPanel);  
fieldPanel.add(subjectPanel);
Panel buttonPanel = new Panel(new GridLayout(1, 0));
JButtonSend.addActionListener(new SendListener());  
JButtonClear.addActionListener(new ClearListener());
JButtonQuit.addActionListener(new QuitListener());  
buttonPanel.add(JButtonSend);  
buttonPanel.add(JButtonClear);  
buttonPanel.add(JButtonQuit);
add(fieldPanel, BorderLayout.NORTH);  
add(messagePanel, BorderLayout.CENTER);
add(buttonPanel, BorderLayout.SOUTH);  

pack();
setVisible(true);
}


static public void main(String argv[])
{  
new MailClient();

}    
//SendListener  
class SendListener implements ActionListener
{  
public void actionPerformed(ActionEvent event)
{    
System.out.println("Sending mail");
if ((Sfield.getText()).equals(""))
{      
Component frame = null;  
JOptionPane.showMessageDialog(frame,  
"Please provide the local mailserver.",        
"Error",        
JOptionPane.ERROR_MESSAGE);
return;      
}            
if((FField.getText()).equals(""))
{      
Component frame = null;
JOptionPane.showMessageDialog(frame,  
"Please provide the sender's email.",
"Error",        
JOptionPane.ERROR_MESSAGE);  
return;      
}      
if((toField.getText()).equals(""))
{        
Component frame = null;  
JOptionPane.showMessageDialog(frame,  
"Please provide the recipient's email.",  
"Error",        
JOptionPane.ERROR_MESSAGE);  
return;        
}            

Message mailMessage = new Message(FField.getText(),
toField.getText(),  
subjectField.getText(),
messageText.getText());      
if(!mailMessage.isValid())
{      
return;
}            
Envelope envelope = null;
try
{    
envelope = new Envelope(mailMessage,  
Sfield.getText());
}
catch (UnknownHostException e)
{            
return;    
}      
try
{          
SMTPConnection connection = new SMTPConnection(envelope);
connection.send(envelope);  
connection.close();    
}
catch (IOException error)
{      
System.out.println("Sending failed: " + error);  
Component frame = null;
JOptionPane.showMessageDialog(frame,
"Error",    
"Error",    
JOptionPane.ERROR_MESSAGE);
return;  
}    
System.out.println("Mail sent succesfully!");  
Component frame = null;
JOptionPane.showMessageDialog(frame,  
" succesful!",    
"",    
JOptionPane.INFORMATION_MESSAGE);
}  
}  
    class ClearListener implements ActionListener
    {
    public void actionPerformed(ActionEvent e)
    {    
    System.out.println("Clearing fields");
    FField.setText("");      
    toField.setText("");      
    subjectField.setText("");  
    messageText.setText("");  
    }  
    }      
    class QuitListener implements ActionListener
    {
    public void actionPerformed(ActionEvent e)
    {      
    System.exit(0);
    }  
    }
}

#2. Socket Prgrmming | MailClient Source Code.

//SMTPConnection.java

import java.net.*;
import java.io.*;
import java.util.*;

/**  * Open an SMTP connection to a remote machine and send one mail.  */
/*MY INFO  */
/**
 * @author Alek amodi@clunet.edu
 *
 *         Using Cal Lutheran Local Mailserver and port number this app can send
 *         and process SMT Protocol.
 *
 *         To: Dr. Chu
 */

/*
 * Developed on - Sep 08, 2011
 *
 * WireShark Lab work - MailClient
 */

public class SMTPConnection {
/* The socket to the server */
public Socket connection;
/* Streams for reading and writing the socket */
public BufferedReader fromServer;
public DataOutputStream toServer;
private static final int SMTP_PORT = 587;
private static final String CRLF = "\r\n";
/* Are we connected? Used in close() to determine what to do. */
private boolean isConnected = false;

/*
* Create an SMTPConnection object. Create the socket and the associated
* streams. Send HELO-command and check for errors.
*/
public SMTPConnection(Envelope envelope) throws IOException {
connection = new Socket(envelope.DestAddr, SMTP_PORT);
fromServer = new BufferedReader(new InputStreamReader(
connection.getInputStream()));
toServer = new DataOutputStream(connection.getOutputStream());
String reply = fromServer.readLine();
if (parseReply(reply) != 220) {
System.out.println("Error in connect.");
System.out.println(reply);
return;
}
String localhost = (InetAddress.getLocalHost()).getHostName();
try {
sendCommand("HELO " + localhost, 250);
} catch (IOException e) {
System.out.println("HELO failed. Aborting.");
return;
}
isConnected = true;
}

/*
* Send the message. Simply writes the correct SMTP-commands in the correct
* order. No checking for errors, just throw them to the caller.
*/
public void send(Envelope envelope) throws IOException {
sendCommand("MAIL FROM:<" + envelope.Sender + ">", 250);
sendCommand("RCPT TO:<" + envelope.Recipient + ">", 250);
sendCommand("DATA", 354);
sendCommand(envelope.Message.toString() + CRLF + ".", 250);
}

/*
* Close the connection. First, terminate on SMTP level, then close the
* socket.
*/
public void close() {
isConnected = false;
try {
sendCommand("QUIT", 221);
connection.close();
} catch (IOException e) {
System.out.println("Unable to close connection: " + e);
isConnected = true;
}
}

/*
* Send an SMTP command to the server. Check that the reply code is what is
* is supposed to be according to RFC 821.
*/
private void sendCommand(String command, int rc) throws IOException {
String reply = null;
toServer.writeBytes(command + CRLF);
reply = fromServer.readLine();
if (parseReply(reply) != rc) {
System.out.println("Error in command: " + command);
System.out.println(reply);
throw new IOException();
}
}

/* Parse the reply line from the server. Returns the reply code. */
private int parseReply(String reply) {
StringTokenizer parser = new StringTokenizer(reply);
String replycode = parser.nextToken();
return (new Integer(replycode)).intValue();
}

/* Destructor. Closes the connection if something bad happens. */
protected void finalize() throws Throwable {
if (isConnected) {
close();
}
super.finalize();
}
}

#1. Socket Prgrmming | MailClient Source Code.

// Envelope.java

import java.io.*;

import java.net.*;

import java.util.*;

/*MY INFO  */

/**

 * @author Alek amodi@clunet.edu

 * 

 *         Using Cal Lutheran Local Mailserver and port number this app can send

 *         and process SMT Protocol.

 * 

 *         To: Dr. Chu

 */

/*

 * Developed on - Sep 08, 2011

 * 

 * WireShark Lab work - MailClient

 */

public class Envelope {

/* SMTP-sender of the message (in this case, contents of From-header. */

public String Sender;

/* SMTP-recipient, or contents of To-header. */

public String Recipient;

/* Target MX-host */

public String DestHost;

public InetAddress DestAddr;

/* The actual message */

public Message Message;

/* Create the envelope. */

public Envelope(Message message, String localServer)

throws UnknownHostException {

/* Get sender and recipient. */

Sender = message.getFrom();

Recipient = message.getTo();

/*

* Get message. We must escape the message to make sure that there are

* no single periods on a line. This would mess up sending the mail.

*/

Message = escapeMessage(message);

/*

* Take the name of the local mail-server and map it into an InetAddress

*/

DestHost = localServer;

try {

DestAddr = InetAddress.getByName(DestHost);

} catch (UnknownHostException e) {

System.out.println("Unknown host: " + DestHost);

System.out.println(e);

throw e;

}

return;

}

/*

* Escape the message by doubling all periods at the beginning of a line.

*/

private Message escapeMessage(Message message) {

String escapedBody = "";

String token;

StringTokenizer parser = new StringTokenizer(message.Body, "\n", true);

while (parser.hasMoreTokens()) {

token = parser.nextToken();

if (token.startsWith(".")) {

token = "." + token;

}

escapedBody += token;

}

message.Body = escapedBody;

return message;

}

/* For printing the envelope. Only for debug. */

public String toString() {

String res = "Sender: " + Sender + '\n';

res += "Recipient: " + Recipient + '\n';

res += "MX-host: " + DestHost + ", address: " + DestAddr + '\n';

res += "Message:" + '\n';

res += Message.toString();

return res;

}

}