Check-off Lab 3

---

Objective

The objective of this lab is to get you up and running on MySQL, including running it through MyEclipse.

Resources

• Books on MySQL in the monitor room:
  • MySQL Tutorial, published by MySQL Press
  • MySQL Language Reference, published by MySQL Press

---

• Web resources for MySQL:
  • The MySQL home page
  • MySQL tutorial
  • MySQL documentation

---

• MyEclipse: if you select Help > Help Contents, MyEclipse will open its help in a web browser. (Note: you can specify what web browser MyEclipse uses in the MyEclipse preferences.) You can then go to MyEclipse Learning Center > Database Development to get some additional hints on what you can do from the Database Perspective in MyEclipse.

Tasks

Your first task will be to connect to the MySQL server using the shell (terminal) program mysql, repeating some of what I did in class. Your second task will be to connect to the MySQL server through MyEclipse, and also to set up a Java project with a schema folder that will hold your database definitions in a convenient manner.

1. To replicate the SQL example I did in class, you will first need to connect to the MySQLserver on mcs-mysql.gac.edu using the username and password I emailed you; create the movie database; select that database with the use command; create the tables; and finally, load the tables from the files I give you.
   
   In order to use the mysql program, you will need to get on a computer that has it installed. The easiest way I know to do this is to use the terminal application on a Macintosh and issue the following command:

       ssh username@ssh.gac.edu
   

   Here, you need to change username to your username and, when prompted, enter your email password. ssh is a secure shell, and is the preferred (sometimes required) method of issuing commands from one computer to another.
   
   Once you are on ssh.gac.edu, following are the commands you do to connect to the MySQL server. (Again you need to change username to your username; however, now you will need to use the password I emailed you). The first command connects to the mysql server. (Here is where you'll be prompted for your mysql password.) The second and third commands are used to create a database and begin using it. Once it's created, the database should remain in place between connections to the server until you (or root) drops the database.

       mysql -h mcs-mysql.gac.edu -u username -p
   
       create database username_movies;
       use username_movies;
   

   Now use SQL commands to create the movies, persons, and acts_in tables, put the data into the tables, and do a couple sample queries to make sure it works. To put the data in, you should issue the following load commands in mysql:

       load data local infile '/Net/gac/home/k/a/karl/public/270/movies/persons.data' into table persons;
       load data local infile '/Net/gac/home/k/a/karl/public/270/movies/movies.data' into table movies;
       load data local infile '/Net/gac/home/k/a/karl/public/270/movies/acts_in.data' into table acts_in;
   

   Check-off: Show me that you have done this by issuing showing me the result of a couple of queries.

---

2. Now create Java project called Movies and create a folder (not a source folder) called schema in the top level of this project. Next, download and expand the zip file movieSchema.zip and copy the files in it into the schema folder you just created.
   
   You now need to connect to the MySQL server mcs-mysql.gac.edu like I did in class on Monday. To do this, you should first switch to the MyEclipse Database Explorer perspective under the Window > Open Perspective menu option. If you then right-click in the open space of the left panel and select New..., you will then be able to configure a New Database Connection Driver. It should be fairly clear how to fill out the fields appropriately (be sure to test the connection first). The one thing you will need is the appropriate driver jar. I am using mysql-connector-java-5.1.7-bin.jar, but you can probably use a later version from MySQL. For your convenience, I am including that jar here. I recommend putting it (along with other usefule jar files you need) in some easy-to-access directory.
   
   After you have done this, you should then open the connection and select your movies database. I want you to then open the three files deleteTables.sql, createTables.sql, and loadInitialData.sql (you might want to first add the Package Explorer view to the Database Explorer perspective by selecting it under Window > Show View > Other...; you will find it there under General.) If you then evaluate the three files in the order I gave them, you will effectively reset the database to its initial state, a very valuable ability when working with databases. Note: you will need to change the references to the three data files appropriately, reflecting where your workspace is located and whether your are using Windows or Mac/Unix filename conventions.
   
   Finally, I included a file called queries.sql which contains the queries in the SQL example I did in class. I would like you to run and understand all these queries. Suggestions: I recommend that you create an SQL file called query.sql where you can run the queries individually. BTW: I can imagine that there is an easy way of running a single query within a file in MyEclipse, but I couldn't see how to do it. If you figure that out, please tell me.
   
   Check-off: Show me that you have done these two things (i.e., re-initializing the database and running the queries from queries.sql).