Database Term Project

Database Systems Term Project
1. Introduction:

Through this term project, the ER Design tool helps me to understand and design the complex syntax of DB schema such as entity which is related multiple entities with different cardinality and participation constraints.

The binary relationship, which I mean the arbitrary XML tag inside the COMMENT tag, is very challenging to design because if we don’t know anything about how the relational DBMS works, we won’t be able to design in an effective and efficient way.

I learn that the program-data independence is very efficient way to develop programs as the online multimedia database can be converted into any useful system if we change the data stored in the DB without modifying any programs, once we develop the program.

The example of video file of fireworks in multimedia database

2. ER Schema

Entity: MultimediaObject:

Uniqueness constraint: No two multimedia objects have the same "MediaObjectID".

Existance constraint: Every multimedia object has a "MediaObjectID".

Entity: Description:

Uniqueness constraint: No two descriptions have the same "Description_ID".

Existence constraint: Every description has a "Description_ID".

Entity: Annotation:

Uniqueness constraint: No two annotations have the same "Annotation_ID".

Existence constraint: Every annotation has an "Annotation_ID".

Entity: XMLTag:

Uniqueness constraint: No two xmltags have the same "xmltag_id".

Existence constraint: Every xmltag has a "xmltag_id".

Relationship: HasAnnotation:

Relationship Structural Constraints:

Relationship Existence Constraint: At most one annotation can exist to one multimedia object.

Cardinality: Each multimedia object can have multiple annotations.

Participation: A multimedia object can exist without annotation.

Cardinality: Each annotation can have at most one corresponding multimedia object.

Participation: Each annotation must have one corresponding multimedia object.

Relationship: HasDescription:

Relationship Structural Constraints:

Relationship Existence Constraint: At most one description instance can exist according to one HasDesciption relationship.

Cardinality: Each multimedia object can have multiple descriptions.

Participation: Each multimedia object must have at least one description.

Cardinality: Each description can have at most one corresponding multimedia object.

Participation: Each description must have in at least one corresponding multimedia object.

Relationship: HasXMLTags:

Relationship Structural Constraints:

Relationship Existence Constraint: Annotation can have Multiple XML tags

Cardinality: Each annotation can have multiple XML tags.

Participation: An annotation can exist without having any XML tag.

Cardinality: Each XML tag can have at most one corresponding annotation.

Participation: Each xml tag must have at least one corresponding annotation.

Relationship: BinTree:

Relationship Structural Constraints:

Relationship Existence Constraint: Child XML tag can exist with at most one parent XML tag.

Cardinality: Each parent XML tag can have many child XML tags.

Participation: Each parent must have at least one child XML tag.

Cardinality: Each child can have at most one corresponding parent XML tag.

Participation: Each child must have one corresponding parent XML tag.

3. ER Diagram

3.1 Mapping Rule:

Rule 1:

I created each entity with relations. The composite attributes became the part of the entity’s attributes.

Rule 2:
I added relations into entity with respect to the cardinality and participation constraints.

Rule 3:

This part is done by the ER Design Tool, but I made sure that all binary 1:1 relationship has correct unique constrains to one of the two relations corresponding to the participating entities.

Rule 4:

I made it sure that non-weak binary 1:N relationship has a foreign key attribute to the relation corresponding to the entity on the N-side of the relationship.

Rule 5:

I made it sure that each binary M:N relationship has a new relation consisting of the two entity keys and any relationship attributes.

Rule 6:

I didn’t have any implementation by this rule because I didn’t have any multi-valued attributes.

Rule 7:

I made it sure that referential constrains are all generated by the ER Design Tool for each relationship of degree greater than two.

3.2 Table diagram with null:

The only attribute that can be null is the attribute value in the XMLTag table. So, if we want to eliminate this null value, we are able to do so by adding additional table and move the value attribute into the table. (See 3.3)

3.3 Table diagram with null:

We can remove the value attribute from XMLTag table into XMLTag_value table.

Then, we can add a foreign key constraint the the XMLTag_value to XMLTag table respect to the attribute, xmltag_id.

4. 1 SQL Queries (Table Definition)

create table Users (

userid int not null,

username varchar(50) not null,

primary key (userid));

create table MMediaObject (

media_object_id int identity not null,

media_type varchar(10),

create_date datetime,

subject varchar(100),

location varchar(100) null,

media_file varchar(100),

FromUserID int,

FromIP varchar(15)

primary key (media_object_id),

foreign key (FromUserID) references Users);

create table Description(

desc_id int identity not null,

media_object_id int,

description varchar(255),

desc_order int,

primary key (desc_id, media_object_id),

foreign key (media_object_id) references MMediaObject);

create table Annotation (

annotation_id int identity not null,

media_object_id int,

annotator int,

UserIP varchar(15),

Pdate datetime,

annotation_order int,

primary key (annotation_id, media_object_id),

foreign key (media_object_id) references MMediaObject,

foreign key (annotator) references Users);

create table XMLTag (

xmltag_id int identity not null,

media_object_id int not null,

annotation_id int not null,

tagorder int,

parent_tag varchar(20) not null,

child_tag varchar(20) null,

value varchar(255),

primary key (xmltag_id, media_object_id, annotation_id),

foreign key (media_object_id) references MMediaObject,

foreign key (annotation_id, media_object_id) references Annotation);

create table MMFILE (

id int not null,

media_object_id int,

media_object_order int,

primary key (id, media_object_id, media_object_order),

foreign key (media_object_id) references MMediaObject);

4.2 SQL Queries (select queries) with JDBC Query Tool

1. Given a media object ID, retrieve all of the metadata for that media object, including the type of object (audio or image, etc.) and complete information about who submitted the media object when.

2a. Given a media object ID, retrieve all of the annotations for that media object in correct time sequence. You should also retrieve the user name (only) for each annotation.

2b.Given a user name, retrieve the media object ID’s of all media objects commented on by that individual.

3. Given a user name and a location, retrieve the media object ID’s of all media objects submitted by that individual associated with that location.

4. Given an XML tag, retrieve the media object ID’s of all media objects with annotations containing that XML tag. Hint: consider using the substring search functions of SQL, see your textbook for details!

5. Given a specific date and subject, retrieve names of all users who annotated a media object recorded on that date of that subject.

6. Given a specific IP address, retrieve the media object ID’s and posting dates of all media objects posted FROM that IP address.

4. Relational Algebra with Explanations

The notation P is projection, S is selection, JOIN is equi-join.

Query 1: Given a media object ID, retrieve all of the metadata for that media object, including the type of object (audio or image, etc.) and complete information about who submitted the media object when.

P< media_object_id, media_type, create_date, subject, location, b.username as fromname, FromIP>

(S<username = ‘searchname’>(mmediaobject JOIN <fromuserid=userid> users))

The users are joined in order to retrieve the name from mmediaobject.fromuserid.

The tree:

P< media_object_id, media_type, create_date, subject, location, b.username as fromname, FromIP>

S<username=’key’>

JOIN<fromuserid=userid>

/ \

Mmediaobject Users

Query 2a: Given a media object ID, retrieve all of the annotations for that media object in correct time sequence. You should also retrieve the user name (only) for each annotation.

P<media_object_id, annotation_id, UserName>

(S<media_object_id = searchid> (annotation JOIN<annotator=userid> users))

The users table is joined in order to retrieve the annotator name.

The tree:

P<media_object_id, annotation_id, UserName>

S<media_object_id=key>

JOIN<annotator=userid>

/ \

Annotation Users

Query 2b: Given a user name, retrieve the media object ID's of all media objects commented on by that individual.

P<media_object_id>

(S<username = ‘searchname’> (annotation join<annotator=userid> users))

The users table is used to look for the username.

The tree:

P<media_object_id>

S<username=key>

JOIN<annotator=userid>

/ \

Annotation Users

Query 3: Given a user name and a location, retrieve the media object ID's of all media objects submitted by that individual associated with that location.

P<media_object_id>

(S<location = ‘searchlocation’> mmediaobject) JOIN<fromid = userid> (S<fromname = ‘fromname’>users)

We selected with the search key before joining the two tables, but can be done after the join, depending on the implementation and the number of records.

The tree:

P<media_object_id>

JOIN<fromid=userid>

/ \

S<location=’key’> S<fromname=’key’>

| |

mmediaobject Users

Query 4: Given an XML tag, retrieve the media object ID's of all media objects with annotations containing that XML tag.

P<media_object_id>(S<parent_tag = ‘tag’ or child_tag = ‘tag’>XMLTag)

I stored XML tag into either parent_tag or child_tag attribute, so we need to search two attributes with search key.

The tree:

P<media_object_id>

S<parent_tag = ‘key’ or child_tag = ‘key’>

XMLTag

Query 5: Given a specific date and subject, retrieve names of all users who annotated a media object recorded on that date of that subject.

P<media_object_id, username, subject>

(((S<postdate = ‘searchdate’>annotation) JOIN<media_object_id> (S<subject=’searchsubject’> mmediaobject)) JOIN<annotator = userid> users)

We need two selection before joining the two tables, annotation and mmediaobject, then we add another join to retrieve the username who annotated the mediaobject.

The tree:

P<media_object_id, usermame, subject>

JOIN<annotator=userid>

/ \

JOIN<media_object_id> Users

/ \

S<postdate=’key’> S<subject=’key’>

| |

annotation Mmediaobject

Query 6: Given a specific IP address, retrieve the media object ID's and posting dates of all media objects posted FROM that IP address.

P<media_object_id, create_date>

(S<fromIP = ‘searchIP’> mmediaobject)

We just need one table, mmediaobject becaue the the mmediaobject table has media_object_id, and create_data, fromIP attributes.

The tree:

P<media_object_id, create_date>

S<fromIP=’key’>

Mmediaobject

5. Scripts

if exists (select id from dbo.sysobjects where id = object_id(N'[dbo].[Description]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table Description;

if exists (select id from dbo.sysobjects where id = object_id(N'[dbo].[XMLTag]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table XMLTag;

if exists (select id from dbo.sysobjects where id = object_id(N'[dbo].[MMFILE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table MMFILE;

if exists (select id from dbo.sysobjects where id = object_id(N'[dbo].[Annotation]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table Annotation;

if exists (select id from dbo.sysobjects where id = object_id(N'[dbo].[MMediaObject]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table MMediaObject;

if exists (select id from dbo.sysobjects where id = object_id(N'[dbo].[Users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table Users;

create table Users (

userid int not null,

username varchar(50) not null,

primary key (userid));

create table MMediaObject (

media_object_id int identity not null,

media_type varchar(10),

create_date datetime,

subject varchar(100),

location varchar(100) null,

media_file varchar(100),

FromUserID int,

FromIP varchar(15)

primary key (media_object_id),

foreign key (FromUserID) references Users);

create table Description(

desc_id int identity not null,

media_object_id int,

description varchar(255),

desc_order int,

primary key (desc_id, media_object_id),

foreign key (media_object_id) references MMediaObject);

create table Annotation (

annotation_id int identity not null,

media_object_id int,

annotator int,

UserIP varchar(15),

Pdate datetime,

annotation_order int,

primary key (annotation_id, media_object_id),

foreign key (media_object_id) references MMediaObject,

foreign key (annotator) references Users);

create table XMLTag (

xmltag_id int identity not null,

media_object_id int not null,

annotation_id int not null,

tagorder int,

parent_tag varchar(20) not null,

child_tag varchar(20) null,

value varchar(255),

primary key (xmltag_id, media_object_id, annotation_id),

foreign key (media_object_id) references MMediaObject,

foreign key (annotation_id, media_object_id) references Annotation);

create table MMFILE (

id int not null,

media_object_id int,

media_object_order int,

primary key (id, media_object_id, media_object_order),

foreign key (media_object_id) references MMediaObject);

insert into Users select 1, 'genki';

insert into Users select 2, 'newuser2';

insert into Users select 3, 'newuser3';

insert into MMediaObject (media_type,create_date,subject,location,media_file,FromUserID,FromIP) values ( 'image','Nov 29 2006 12:00AM','Half Dome1','Yosemite','/media/image/P1050371-01.jpg',1,'192.168.0.3')

insert into MMediaObject (media_type,create_date,subject,location,media_file,FromUserID,FromIP) values ( 'image','Nov 29 2006 12:00AM','Half Dome2','Yosemite','/media/image/P1050371-02.jpg',1,'192.168.0.3')

insert into MMediaObject (media_type,create_date,subject,location,media_file,FromUserID,FromIP) values ( 'image','Nov 29 2006 12:00AM','Half Dome3','Yosemite','/media/image/P1050371-03.jpg',1,'192.168.0.3')

insert into MMediaObject (media_type,create_date,subject,location,media_file,FromUserID,FromIP) values ( 'image','Nov 29 2006 12:00AM','Street in Italy','Florence','/media/image/P1050371-04.jpg',1,'192.168.0.3')

insert into MMediaObject (media_type,create_date,subject,location,media_file,FromUserID,FromIP) values ( 'image','Nov 29 2006 12:00AM','Colosseum, Rome','Rome','/media/image/P1050371-05.jpg',1,'192.168.0.3')

insert into MMediaObject (media_type,create_date,subject,location,media_file,FromUserID,FromIP) values ( 'image','Nov 29 2006 12:00AM','Vatican','Vatican','/media/image/P1050371-06.jpg',1,'192.168.0.3')

insert into MMediaObject (media_type,create_date,subject,location,media_file,FromUserID,FromIP) values ( 'image','Nov 29 2006 12:00AM','Sunset1','Monterey','/media/image/P1050371-07.jpg',1,'192.168.0.3')

insert into MMediaObject (media_type,create_date,subject,location,media_file,FromUserID,FromIP) values ( 'image','Nov 29 2006 12:00AM','Sunset2','Monterey','/media/image/P1050371-08.jpg',1,'192.168.0.3')

insert into MMediaObject (media_type,create_date,subject,location,media_file,FromUserID,FromIP) values ( 'image','Nov 29 2006 12:00AM','Ferrari','Carmel','/media/image/P1050371-09.jpg',1,'192.168.0.3')

insert into MMediaObject (media_type,create_date,subject,location,media_file,FromUserID,FromIP) values ( 'image','Nov 29 2006 12:00AM','Mountain in Hawaii','Oahu','/media/image/P1050371-12.jpg',1,'192.168.0.3')

insert into MMediaObject (media_type,create_date,subject,location,media_file,FromUserID,FromIP) values ( 'audio','Nov 30 2006 12:00AM','MouseSong1','CSC675 website','/mouse/songs/MouseSong1.wav',1,'10.8.8.3')

insert into MMediaObject (media_type,create_date,subject,location,media_file,FromUserID,FromIP) values ( 'audio','Nov 30 2006 12:00AM','MouseSong2','CSC675 website','/mouse/songs/MouseSong2.wav',1,'10.8.8.3')

insert into MMediaObject (media_type,create_date,subject,location,media_file,FromUserID,FromIP) values ( 'audio','Nov 30 2006 12:00AM','MouseSong3','CSC675 website','/mouse/songs/MouseSong3.wav',1,'10.8.8.3')

insert into MMediaObject (media_type,create_date,subject,location,media_file,FromUserID,FromIP) values ( 'audio','Nov 30 2006 12:00AM','SparrowSong1','CSC675 website','/mouse/songs/SparrowSong1.wav',1,'10.8.8.3')

insert into MMediaObject (media_type,create_date,subject,location,media_file,FromUserID,FromIP) values ( 'video','Dec 1 2006 12:00AM','Independence Day Fireworks ','San Jose','/media/video/P1020587.MOV',1,'10.8.8.3')

insert into annotation ( media_object_id, annotator, userip, pdate, annotation_order) values (1,'1','192.168.0.3','Dec 9 2006 12:00AM',1)

insert into annotation ( media_object_id, annotator, userip, pdate, annotation_order) values (2,'1','192.168.0.3','Dec 9 2006 12:00AM',1)

insert into annotation ( media_object_id, annotator, userip, pdate, annotation_order) values (3,'1','192.168.0.3','Dec 9 2006 12:00AM',1)

insert into annotation ( media_object_id, annotator, userip, pdate, annotation_order) values (11,'1','192.168.0.3','Dec 9 2006 12:00AM',1)

insert into annotation ( media_object_id, annotator, userip, pdate, annotation_order) values (12,'1','192.168.0.3','Dec 9 2006 12:00AM',1)

insert into annotation ( media_object_id, annotator, userip, pdate, annotation_order) values (13,'1','192.168.0.3','Dec 9 2006 12:00AM',1)

insert into annotation ( media_object_id, annotator, userip, pdate, annotation_order) values (14,'1','192.168.0.3','Dec 9 2006 12:00AM',1)

insert into MMFile (id, media_object_id,media_object_order) values (1, 11,1)

insert into MMFile (id, media_object_id,media_object_order) values (1, 12,2)

insert into MMFile (id, media_object_id,media_object_order) values (1,13,3)

insert into MMFile (id, media_object_id,media_object_order) values (1, 14,4)

insert into MMFile (id, media_object_id,media_object_order) values (2, 1,1)

insert into MMFile (id, media_object_id,media_object_order) values (2, 2,2)

insert into MMFile (id, media_object_id,media_object_order) values (2, 3,3)

insert into MMFile (id, media_object_id,media_object_order) values (3, 4,1)

insert into MMFile (id, media_object_id,media_object_order) values (3, 5,2)

insert into MMFile (id, media_object_id,media_object_order) values (3, 6,3)

insert into XMLTag (media_object_id,annotation_id,tagorder,parent_tag,child_tag,value) values (1,1,2,'COMMENT','travel','Yosemite')

insert into XMLTag (media_object_id,annotation_id,tagorder,parent_tag,child_tag,value) values (2,2,1,'COMMENT','travel','Yosemite')

insert into XMLTag (media_object_id,annotation_id,tagorder,parent_tag,child_tag,value) values (3,3,1,'COMMENT','travel',null)

insert into XMLTag (media_object_id,annotation_id,tagorder,parent_tag,child_tag,value) values (1,1,1,'COMMENT','FavoritePics','1')

insert into XMLTag (media_object_id,annotation_id,tagorder,parent_tag,child_tag,value) values (2,2,2,'COMMENT','FavoritePics','2')

insert into XMLTag (media_object_id,annotation_id,tagorder,parent_tag,child_tag,value) values (3,3,1,'travel','date','Spring Break 2002')

insert into XMLTag (media_object_id,annotation_id,tagorder,parent_tag,child_tag,value) values (3,3,1,'travel','location','Yosemite')

insert into XMLTag (media_object_id,annotation_id,tagorder,parent_tag,child_tag,value) values (11,4,1,'COMMENT','animal',null)

insert into XMLTag (media_object_id,annotation_id,tagorder,parent_tag,child_tag,value) values (11,4,2,'animal','mouse',null)

insert into XMLTag (media_object_id,annotation_id,tagorder,parent_tag,child_tag,value) values (11,4,2,'mouse','song','It sounds like a bird song')

insert into XMLTag (media_object_id,annotation_id,tagorder,parent_tag,child_tag,value) values (12,5,1,'COMMENT','animal',null)

insert into XMLTag (media_object_id,annotation_id,tagorder,parent_tag,child_tag,value) values (12,5,1,'animal','mouse',null)

insert into XMLTag (media_object_id,annotation_id,tagorder,parent_tag,child_tag,value) values (13,6,1,'COMMENT','animal',null)

insert into XMLTag (media_object_id,annotation_id,tagorder,parent_tag,child_tag,value) values (13,6,1,'animal','mouse',null)

insert into XMLTag (media_object_id,annotation_id,tagorder,parent_tag,child_tag,value) values (13,6,1,'mouse','song','It sounds like a bird!')

insert into XMLTag (media_object_id,annotation_id,tagorder,parent_tag,child_tag,value) values (12,5,1,'mouse','song','Very similar to the sparrow song!')

insert into XMLTag (media_object_id,annotation_id,tagorder,parent_tag,child_tag,value) values (14,7,1,'COMMENT','animal',null)

insert into XMLTag (media_object_id,annotation_id,tagorder,parent_tag,child_tag,value) values (14,7,1,'animal','sparrow',null)

insert into XMLTag (media_object_id,annotation_id,tagorder,parent_tag,child_tag,value) values (14,7,1,'sparrow','song','This is a sparrow song.')

6. JDBC Query Tool

JDBCQueryTool.java is the main program file which execute queries. Please see additional files if it’s necessary.

JDBC Query Tool loads a XML File of db connection, and the queries when it starts.

All queries are implemented in a xml file, queries.xml.

The program and storage are independent, so I easily can change queries by modifying the xml file only.

I don’t have to modify any program to do so.

[JDBCQueryTool.java]

import java.sql.ResultSet;

import java.awt.*;

import javax.swing.*;

import javax.swing.table.DefaultTableModel;

import java.awt.event.*;

import java.sql.ResultSetMetaData;

import javax.swing.table.TableColumn;

import java.net.InetAddress;

import java.io.*;

import javax.xml.transform.*;

import javax.xml.transform.stream.*;

/**

* @author Genki Kuroda <gen@sfsu.edu>

public class JDBCQueryTool extends JFrame implements ActionListener

{

private String title = "JDBC Query Tool";

private TextArea sqlInput;

private JTable tbl;

private JScrollPane pane;

private readXML queries;

private readXML dbc;

static Container ResultContainer = new Container();

static final long serialVersionUID = 6170037639785281128L; //Dummy UID

final static String CRLF = "\r\n";

public JDBCQueryTool()

{

//Load db connection info from a XML file

dbc = new readXML("dbconnection.xml");

dbc.setXMLData("item", "server", "user", "password");

super.setTitle(title + " --- DB Server: " + dbc.container.data[0].get("server")

+ " Client: " + getIP());

//Setup radio button commands by dynamically loading a XML file

queries = new readXML("queries.xml");

queries.setXMLData("item", "name", "desc", "value");

JRadioButton radioButtons[] = new JRadioButton[queries.container.data.length];

ButtonGroup group = new ButtonGroup();

JPanel radioPanel = new JPanel(new GridLayout(queries.container.data.length, 1));

for (int i = 0; i < queries.container.data.length; i++)

{

//Set a name to the radio button

radioButtons[i] = new JRadioButton();

radioButtons[i].setText(queries.container.data[i].get("name"));

//Associate the radio button name with the sql as an action command

radioButtons[i].setActionCommand("-- " + queries.container.data[i].get("desc") + "\n\n"

+ queries.container.data[i].get("value"));

//Group all radio buttons

group.add(radioButtons[i]);

radioPanel.add(radioButtons[i]);

radioButtons[i].addActionListener(this);

}

// Create SQL input textarea

sqlInput = new TextArea(10, 70);

sqlInput.setText("Plese input query or choose commands.");

// Create execution button

JButton exeBtn = new JButton("Execute!");

exeBtn.setActionCommand("execute");

exeBtn.setSelected(true);

setLayout(new FlowLayout(1, 10, 10));

add(radioPanel, BorderLayout.NORTH);

add(sqlInput, BorderLayout.CENTER);

add(exeBtn, BorderLayout.EAST);

exeBtn.addActionListener(this);

setSize(750, 600);

setVisible(true);

}

public static void main(String[] args) { new JDBCQueryTool(); }

public void actionPerformed(ActionEvent e)

{

if ("execute".equals(e.getActionCommand()))

{ execute_button_click(); return; }

sqlInput.setText(e.getActionCommand());

}

/**

* Execute the sql query.

public void execute_button_click()

{

// Clear the table and pane if they exist

if (tbl != null) tbl.removeAll();

if (pane != null) pane.removeAll();

if (ResultContainer != null) ResultContainer.removeAll();

try

{

//Create database connection with given userid and password.

MsSQL db = new MsSQL(dbc.container.data[0].get("server"),

dbc.container.data[0].get("user"),

dbc.container.data[0].get("password"), 1433);

sqlInput.setText(addParameters(sqlInput.getText()));

// Retrieve the resulting recordset

ResultSet rs = db.open(sqlInput.getText());

//Get the result set meta data in order to get attributes name

ResultSetMetaData rsmd = rs.getMetaData();

int numColumns = rsmd.getColumnCount();

// Prepare the table according to the number of attributes.

tbl = new JTable(0, numColumns);

// Set the column names into the table header

for (int i=1; i<numColumns+1; i++)

{

TableColumn column = tbl.getColumnModel().getColumn(i-1);

column.setHeaderValue(rsmd.getColumnName(i).toString());

}

// Set the retrieved records into the table

int row = 0;

while (rs.next())

{

addRow(tbl, rs, row++);

}

//Close recordset and database connection

rs.close();

//Create XMLFile

ResultSet rsXML = db.open(sqlInput.getText());

createXMLFile(db, rsXML, "output.xml");

rsXML.close();

//Transform to HTML

try

{

// Instanciate TransformerFactory

TransformerFactory factory = TransformerFactory.newInstance();

// Get tranceformer from XML file

Transformer transformer = factory.newTransformer(new StreamSource("outputxslt.xsl"));

transformer.setOutputProperty("encoding","UTF-8");

transformer.transform(new StreamSource( "output.xml"),

new StreamResult( "output.html"));

}

catch(Exception e)

{

System.out.println(e.toString());

}

db.close();

}

catch (Exception ex)

{

// Show sql syntax error in the table

tbl = new JTable(1, 1);

TableColumn column = tbl.getColumnModel().getColumn(0);

column.setHeaderValue("Error");

tbl.setValueAt(ex.toString(), 0, 0);

}

// Add scroll bar into the table

pane = new JScrollPane(tbl);

tbl.setPreferredScrollableViewportSize(new Dimension(600,280));

// Update UI

pane.updateUI();

// Disaplay the resulting table

ResultContainer.setLayout(new BorderLayout());

ResultContainer.add(pane);

add(ResultContainer, SwingConstants.BOTTOM);

tbl.setVisible(true);

setVisible(true);

}

/**

* Add Parameters if query has markers

* @param original_query the original query

public String addParameters(String original_query)

{

/** The simple format is :

* select E.dno from emloyee E

* $[$ where E.dno = #[#dno#]# $]$

String query_modified = original_query;

String start_block_marker = "$[$", end_block_marker = "$]$";

String start_param_marker = "#[#", end_param_marker = "#]#";

int start_block = 0;

int end_block = 0;

boolean initcheck = true;

while ((start_block = query_modified.indexOf(start_block_marker)) != -1)

{

end_block = query_modified.indexOf(end_block_marker);

String block_pre = query_modified.substring(start_block,end_block + end_param_marker.length());

String param_name = block_pre.substring(block_pre.indexOf(start_param_marker) + start_param_marker.length(),block_pre.indexOf(end_param_marker));

if (initcheck == true)

{

initcheck = false;

int response = JOptionPane.showConfirmDialog(null, new String[] {

/* first line of the message */ "Do you want to specify parameter(s)?",

/* second line of message */ ""},

/* dialog title */ "Specifying Parameters",

/* what buttons to display */ JOptionPane.YES_NO_OPTION,

/* icon type to display */ JOptionPane.WARNING_MESSAGE);

switch(response)

{

case JOptionPane.YES_OPTION:

break;

default:

return replace(query_modified,

query_modified.substring(query_modified.indexOf(start_block_marker),

query_modified.lastIndexOf(end_block_marker)

+ end_block_marker.length()), "");

}

String value = JOptionPane.showInputDialog(null, "Please specify " + param_name);

if (value != null)

{

String block_post = replace(block_pre,

block_pre.substring(block_pre.indexOf(start_param_marker),

block_pre.indexOf(end_param_marker) + end_param_marker.length()),

value);

block_post = replace(block_post, start_block_marker, "");

block_post = replace(block_post, end_block_marker,"");

query_modified = replace(query_modified, block_pre, block_post);

}

else

{

query_modified = replace(query_modified, block_pre, "");

}

return query_modified;

}

/**

* String replacement function

* @param str the original string to be modified

* @param pattern the target substring to be modified

* @param replace the replacement string

public String replace(String str, String pattern, String replace)

{

int s = 0;

int e = 0;

StringBuffer result = new StringBuffer();

while ((e = str.indexOf(pattern, s)) >= 0)

{

result.append(str.substring(s, e));

result.append(replace);

s = e + pattern.length();

}

result.append(str.substring(s));

return result.toString();

}

/**

* Add one row to the table

* @param tbl the resulting table to add

* @param rs the resulting record set

* @param row the position of row to add

public static void addRow(JTable tbl, ResultSet rs, int row)

{

((DefaultTableModel) tbl.getModel()).setRowCount(row+1);

try

{

for (int i = 1; true; i++)

{ // Make null visible as '<null>'

if (rs.getString(i) == null)

tbl.setValueAt("<null>", row, i-1);

else

tbl.setValueAt(rs.getString(i), row, i-1);

}

catch (Exception ex)

{}

return;

}

/**

* Get local IP Address (not sure how it works when the local pc has multiple IPs)

* @param none

private static String getIP()

{

try

{ // Get IP Address

InetAddress addr = InetAddress.getLocalHost();

byte[] ipAddr = addr.getAddress();

return ipAddr[0] + "." + ipAddr[1] + "." + ipAddr[2] + "." + ipAddr[3];

}

catch (Exception e){}

return "";

}

/**

* Create a XML file

* @param conn database connectio

* @param rs result set

* @param output_file_name the name of xml file.

private boolean createXMLFile(MsSQL conn, ResultSet rs, String output_file_name)

{

boolean ret = false;

FileOutputStream out; // declare a file output object

PrintStream p; // declare a print stream object

String sql = "";

try

{

String s = "";

// Create a new file output stream

out = new FileOutputStream(output_file_name);

// Assign print stream to the output stream

p = new PrintStream( out );

s += "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone='yes'?>" + CRLF +

"<MMFILE>" + CRLF;

while(rs.next())

{

//If MMFILE is given as Resultset, the media objects are already ordered by the attribute, MMFILE.media_object_order.

sql = " select media_object_id, media_type, create_date, subject, location, media_file, b.username as fromname, fromip " +

" from MMediaObject as a, users as b where media_object_id = '" + rs.getString("media_object_id") + "'";

//Open each media object one by one

ResultSet rsMMFILE = conn.open(sql);

while (rsMMFILE.next())

{

//Read only once to be efficient.

String media_object_id = rsMMFILE.getString("media_object_id");

String media_type = rsMMFILE.getString("media_type");

String create_date = rsMMFILE.getString("create_date");

String subject = rsMMFILE.getString("subject");

String location = rsMMFILE.getString("location");

String media_file = rsMMFILE.getString("media_file");

String fromname = rsMMFILE.getString("fromname");

String fromip = rsMMFILE.getString("fromip");

//MMOBJECT can be multiple in MMFILE, So we need to accumulate it.

s += " <MMOBJECT>" + CRLF +

" <TYPE>" + media_type + "</TYPE>" + CRLF +

" <MFILE>" + media_file + "</MFILE>" + CRLF +

" <FROM>" + CRLF +

" <FROMNAME>" + fromname + "</FROMNAME>" + CRLF +

" <FROMIP>" + fromip + "</FROMIP>" + CRLF +

" </FROM>" + CRLF +

" <DATE>" + create_date + "</DATE>" + CRLF +

" <SUBJECT>" + subject + "</SUBJECT>" + CRLF +

" <LOCATION>" + location + "</LOCATION>" + CRLF;

//Retrieve description(s)

sql = " select description from Description " +

" where media_object_id = '" + media_object_id + "'" +

" order by desc_id asc";

//Desciption can be multiple and at least one

ResultSet rs_description = conn.open(sql);

while (rs_description.next())

{

s += " <DESC>" + rs_description.getString("description") + "</DESC>" + CRLF;

}

rs_description.close();

//Retrieve annotation(s)

sql = " select * from Annotation as a, users as u" +

" where a.annotator = u.userid and media_object_id = '" + media_object_id + "'" +

" order by annotation_id asc";

ResultSet rs_annotation = conn.open(sql);

while (rs_annotation.next())

{

int level = 1; //level is used for white string format.

//Annotation can be multiple

s += " <ANNOTATION>" + CRLF +

" <USERNAME>" + rs_annotation.getString("UserName") + "</USERNAME>" + CRLF +

" <USERIP>" + rs_annotation.getString("UserIP") + "</USERIP>" + CRLF +

" <PDATE>" + rs_annotation.getString("PDate") + "</PDATE>" + CRLF;

//CommentTag has arbitary many XMLTags. So, recursively retrieve all tags.

s += " <COMMENT>" + CRLF +

getXMLTagsAndValue(conn, rs_annotation, "COMMENT", level) +

" </COMMENT>" + CRLF +

" </ANNOTATION>" + CRLF;

}

rs_annotation.close();

}

rsMMFILE.close();

s += " </MMOBJECT>" + CRLF;

}

s += "</MMFILE>" + CRLF;

//Write Once Only in order to minimize I/O time.

p.print (s);

p.close();

}

catch (Exception e)

{

System.err.println (sql + "\n" + e.toString());

}

return ret;

}

/**

* Return XML Tags And Value

* @param conn database connectio

* @param rs result set

* @param parentXMLTag the parent XML tag (initially "COMMENT").

* @param level nested level (1 is the top level and nested deeper as level increases)

private String getXMLTagsAndValue(MsSQL conn, ResultSet rs, String parentXMLTag, int level)

{

String retStr = ""; String sql = "";

// Find top-level XML tags under the parentXMLTag, initially parentXMLTag is set as "COMMENT"

// For example,

// <COMMENT>

// This part can be any parsable data.

// </COMMENT>

try

{

// Retrieve the resulting recordset

sql = " select child_tag, value from XMLTag " +

" where parent_tag = '" + parentXMLTag + "'" +

" and media_object_id = '" + rs.getString("media_object_id") + "'" +

" and annotation_id = '" + rs.getString("annotation_id") + "'" +

" order by tagorder ASC";

ResultSet rs_top = conn.open(sql);

while (rs_top.next())

{

String childtag_open = ""; String childtag_close = "";

if (rs_top.getString("child_tag") == null)

{

childtag_open = ""; childtag_close = "";

}

else

{

childtag_open = "<" + rs_top.getString("child_tag") + ">";

childtag_close = "</" + rs_top.getString("child_tag") + ">";

}

retStr += getSpace(level * 2) + childtag_open;

//Recursive calls of getXMLTagsAndValue with child_tag as parent_tag.

if (rs_top.getString("value") == null)

retStr += getXMLTagsAndValue(conn, rs, rs_top.getString("child_tag"), ++level);

else

retStr += rs_top.getString("value");

retStr += getSpace((level-1) * 2) + childtag_close + CRLF;

}

rs_top.close();

}

catch (Exception e){System.out.print(e.toString());}

return retStr;

}

/**

* Used for generating strings with white space.

* @param n the number of white space to generate

private String getSpace(int n)

{

String ret = " ";

for(int i = 1; i <= n; i++)

ret += " ";

return ret;

}

7. Extra Credit Question: Discuss how you might develop a system, that lets users share XML tags used in annotations.

The sharing the XML tags can be done by retrieving all distinct XML tags stored in the XMLTag table. To be more accurate with constrains, we could have an additional master table, MasterXMLTag which has a unique constraint on the attribute, XML tag name. This MasterXMLTag table has the parent relationship to the XMLTag table. When we share the XML tags, we want to know who posts or creates the XML tag. We can tell the original person who creates the XML tag by looking at the annotation table which has the attribute, userid. The simple implementation can be a web page which a user can chose from the dropdown list of XML tags.

Probably, the ambiguous XML tag cannot be eliminated by the program because the ambiguous is something that human is creating. If we have a XML tag, “<mouse>”, the program cannot tell the mouse means the small animal or the human mouse. So, the reasonable solution to reduce the ambiguous xml tag is nesting of XML tags.

Foe example, the xml tag, mouse can be nested as

<human>

<mouse>Human’s mouse</mouse>

</human>

<mouse>Mouse as animal</mouse>

</animal>

Therefore by adding nested tags, we could reduce the ambiguousity. If we can implement dictionary database which can tell ambiguous words, it will be very interesting.

8. Extra Credit: Generating XML and HTML transformed by XSL

JDBC Query Tool generates a XML file, output.xml when we retrieve tuples with a query.

And at the same time, I converted the xml file, output.xml file into html file, output.html transformed by a XSL file, outputxslt.xsl

The XSL file contains a grammar to convert the xml file, output.xml into HTML file, output.html.

We may need to modify this grammar if we have additional type of media objects because the implementation cab be different if we want to listen to the music or just display a single picture. So far, this grammar supports jpeg file, wave file, and video file.

Screenshots of HTML files (Pictures, Audio, Video)

Pictures taken at Yosemite

The mouse song on the left and fireworks on the right

[otput.xml]

<?xml version="1.0" encoding="UTF-8" standalone='yes'?>

<TYPE>image</TYPE>

<MFILE>/media/image/P1050371-01.jpg</MFILE>

<FROM>

<FROMNAME>genki</FROMNAME>

</FROM>

<LOCATION>Yosemite</LOCATION>

<USERNAME>genki</USERNAME>

<travel>Yosemite </travel>

</COMMENT>

</ANNOTATION>

</MMOBJECT>

<TYPE>image</TYPE>

<MFILE>/media/image/P1050371-02.jpg</MFILE>

<FROM>

<FROMNAME>genki</FROMNAME>

</FROM>

<LOCATION>Yosemite</LOCATION>

<USERNAME>genki</USERNAME>

<travel>Yosemite </travel>

</COMMENT>

</ANNOTATION>

</MMOBJECT>

<TYPE>image</TYPE>

<MFILE>/media/image/P1050371-03.jpg</MFILE>

<FROM>

<FROMNAME>genki</FROMNAME>

</FROM>

<LOCATION>Yosemite</LOCATION>

<USERNAME>genki</USERNAME>

<travel> <date>Spring Break 2002 </date>

<location>Yosemite </location>

</travel>

</COMMENT>

</ANNOTATION>

</MMOBJECT>

</MMFILE>

[output.html]

<html>

<head>

<title>Multi Media Object</title>

</head>

<body>

<h1>XHTML transformed by XSLT</h1>

<tr>

<td><b>Subject: </b></td><td>Half Dome1</td>

</tr>

<tr>

</tr>

<tr>

<td><b>Type: </b></td><td>image</td>

</tr>

<tr>

<td><b>Location:</b></td><td>Yosemite</td>

</tr>

<tr>

<td><b>FileName:</b></td><td>/media/image/P1050371-01.jpg</td>

</tr>

<tr>

<br>

<br>

<br>2006-12-09 00:00:00.0<br>genki (posted By 192.168.0.3)<br>

<br>

Comment:

<travel>Yosemite </travel>

</textarea>

<br>

</td>

</tr>

</table>

<br>

<tr>

<td><b>Subject: </b></td><td>Half Dome2</td>

</tr>

<tr>

</tr>

<tr>

<td><b>Type: </b></td><td>image</td>

</tr>

<tr>

<td><b>Location:</b></td><td>Yosemite</td>

</tr>

<tr>

<td><b>FileName:</b></td><td>/media/image/P1050371-02.jpg</td>

</tr>

<tr>

<br>

<br>

<br>2006-12-09 00:00:00.0<br>genki (posted By 192.168.0.3)<br>

<br>

Comment:

<travel>Yosemite </travel>

</textarea>

<br>

</td>

</tr>

</table>

<br>

<tr>

<td><b>Subject: </b></td><td>Half Dome3</td>

</tr>

<tr>

</tr>

<tr>

<td><b>Type: </b></td><td>image</td>

</tr>

<tr>

<td><b>Location:</b></td><td>Yosemite</td>

</tr>

<tr>

<td><b>FileName:</b></td><td>/media/image/P1050371-03.jpg</td>

</tr>

<tr>

<br>

<br>

<br>2006-12-09 00:00:00.0<br>genki (posted By 192.168.0.3)<br>

<br>

Comment:

<date>Spring Break 2002 </date>

<location>Yosemite </location>

</travel>

</textarea>

<br>

</td>

</tr>

</table>

<br>

</body>

</html>

[outputxslt.xml]

<?xml version="1.0" encoding="UTF-8"?>

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">

<xsl:template match="/">

<html>

<head>

<title>Multi Media Object</title>

</head>

<body>

<h1>XHTML transformed by XSLT</h1>

<xsl:for-each select="MMFILE/MMOBJECT">

<table>

<xsl:attribute name="border">1</xsl:attribute>

<xsl:attribute name="width">320</xsl:attribute>

<tr>

<td><b>Subject: </b></td> <td><xsl:value-of select="SUBJECT"/> </td>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

<td><b>Location:</b></td> <td><xsl:value-of select="LOCATION"/> </td>

</tr>

<tr>

<td><b>FileName:</b></td> <td><xsl:value-of select="MFILE"/> </td>

</tr>

<tr>

<br />

<xsl:if test="TYPE='image'">

<img>

<xsl:attribute name="src"><xsl:value-of select="MFILE" /></xsl:attribute>

</img>

</xsl:if>

<xsl:if test="TYPE='audio'">

<xsl:attribute name="type">application/x-mplayer2</xsl:attribute>

<xsl:attribute name="height">28</xsl:attribute>

<xsl:attribute name="width">200</xsl:attribute>

<param>

<xsl:attribute name="name">filename</xsl:attribute>

<xsl:attribute name="value">http://127.0.0.1:4444/<xsl:value-of select="MFILE" /></xsl:attribute>

</param>

<param>

<xsl:attribute name="name">autostart</xsl:attribute>

<xsl:attribute name="value">0</xsl:attribute>

</param>

</object>

</xsl:if>

<xsl:if test="TYPE='video'">

<xsl:attribute name="classid">clsid:02BF25D5-8C17-4B23-BC80-D3488ABDDC6B</xsl:attribute>

<xsl:attribute name="codebase">http://www.apple.com/qtactivex/qtplugin.cab</xsl:attribute>

<xsl:attribute name="height">300</xsl:attribute>

<xsl:attribute name="width">200</xsl:attribute>

<param><xsl:attribute name="target">myself</xsl:attribute></param>

<embed>

<xsl:attribute name="height">196</xsl:attribute>

<xsl:attribute name="pluginspage">http://www.apple.com/quicktime/download/</xsl:attribute>

<xsl:attribute name="src">http://127.0.0.1:4444/<xsl:value-of select="MFILE" /></xsl:attribute>

<xsl:attribute name="href">http://127.0.0.1:4444/<xsl:value-of select="MFILE" /></xsl:attribute>

<xsl:attribute name="target">myself</xsl:attribute>

<xsl:attribute name="type">video/quicktime</xsl:attribute>

<xsl:attribute name="width">240</xsl:attribute>

<xsl:attribute name="controller">true</xsl:attribute>

<xsl:attribute name="autoplay">true</xsl:attribute>

</embed>

</object>

</xsl:if>

</center>

<br />

<xsl:for-each select="DESC">

<xsl:value-of select="text()"/><br />

</xsl:for-each>

<br />

<xsl:for-each select="ANNOTATION">

<xsl:value-of select="PDATE"/> <br />

<xsl:value-of select="USERNAME"/> (posted By <xsl:value-of select="USERIP"/>)<br /><br />

Comment:

<xsl:attribute name="style">border: 0</xsl:attribute>

<xsl:attribute name="cols">35</xsl:attribute>

<xsl:attribute name="rows">8</xsl:attribute>

<xsl:for-each select="COMMENT">

<xsl:copy-of select="@*[not(.='')]|node()"/>

</xsl:for-each>

</textarea>

</xsl:for-each>

<br />

</td>

</tr>

</table>

<br />

</xsl:for-each>

<br />

</body>

</html>

</xsl:template>

</xsl:stylesheet>

9. Extra Credit: HTTP Server

The HTTP Server is a command line based program which has no control features which is very challenging without having decent HTTP protocol and TCP/IP knowledge.

We may want to add a feature to handle time-out session because the server memory just expands almost exponentially.

I chose to change the port as 4444 because other program using standard port, 80.

10. Conclusion

Thorough the term project, I enjoyed the development part. For the future modification, I could think a lot of features are missing. Web based interface to post and create multimedia files could be the first that should be added.

I had some experience in designing simple database tables without knowing the background information about entity or constraints. So, I learned a lot from the class.