database homework

Create a database of the information in the attached text file of taglines from the Internet Movie Database.  

You can find information on JDBC on the wiki

Note that at this point you probably need to explicitly provide the classpath for BOTH compilation and running the Java Virtual Machine

javac -classpath “./:/opt/lablibs/ojdbc6.jar” DBCon.java

java -classpath “./:/opt/lablibs/ojdbc6.jar” DBCon

Take the following steps:

– Connect to the database using JDBC 

– Create two tables (see below) using createUpdate statements

– Open the text file for reading (see notes on file storage from CSci 160)

– Read in line by line

– As you are parsing the text don’t worry too much about special cases:  It is fair to assume that any movie title begins after the “#” and ends before the first “(” and that the movie year consists of the 4 digits after the first “(“.  If there are movies for which those 4 characters done form an integer you can ignore the corresponding record.

– As you parse the input, insert records into both tables using createUpdate

– Create a query that does a natural join of the two tables using createQuery

– Print the first 20 lines of the result to standard output

Because the taglines are multi-valued you need two tables.  The first table, Movie, has the following attributes

– iD is an integer that is automatically generated in your Java code (or using a sequence in Oracle), and serves as primary key

– movieName 

– movieYear

The second table, Tagline has the attributes

– movieID which is a foreign key that references iD in Movie

– tLine holds the actual tagline

Both attributes together form the primary key.

Submit

– your java file

– the typescript file that was created when running the program, which has to include the 20 lines of output

– a text file containing one paragraph on how you approached the problem and what you learned (for this assignment one paragraph is enough and you don’t need an entire page as listed in the syllabus)

I will include the web page containing the database with the login info after we handshake.