HW 7

due November 15

Last modified 11/9/99 --PR
Class home page

You can do this assignment in pairs, but not in groups larger than two.

Reading

Chapters 9, 10,  11.1-11.2, Supplement on OOP; Supplement with shopping cart example

Optional: Supplement with Java program for bank account

SQL

This is a hands-on assignment. An account has been created for you on the database server running on the machine ebola.si.umich.edu. Your account gives you access to a database that has already been created, with some tables already populating that database. You can use SQL commands to create new tables, but you will not need to do that for this assignment and, in fact, you will not be able to create new tables because of the permissions we set up on your database account.

Getting Started

  1. Using your favorite SSH program, connect to "ebola.si.umich.edu". I suggest you use Tera Term SSH application available on DIAD machines. Use the username si540. Junho will send you email with a password (don't use your UMICH uniqname and password).

    2.  

     

    Alternately, you can do a telnet session to login.si.umich.edu or login.umich.edu and then type: 

    ssh ebola.si.umich.edu -l si540
    If it says that "host key not found from the list of known hosts", you should respond "yes" to confirm that you want to continue. It will then ask you for a password.
     
  2. Log in to the MySQL database server by typing one of the following. You may find it convenient to copy and paste this text into your SSH window, rather than retyping (hit the 'enter' key at the end of the line)
    3. or
  3. The database server will ask for your password. Enter the second password Junho assigned to you, the one for the mySQL server.
  4. When you are connected, you will see the follwing text and MySql prompt
    5.  Welcome to the MySQL monitor.  Commands end with ; or \g.
     Your MySQL connection id is 1 to server version: 3.22.25
     Type 'help' for help.
     mysql>
  5. Once you are connected, do the following things to see what databases are available inside the database server.
    6. mysql> show databases;
    (If you forget the ; it will keep prompting you until you type one)
  6. Scroll up and down to find your own database. As you can see, your unique name is used for your database name.  Connect to your database by the following command.  (for example, mine will be junsong)
    7. mysql> connect junsong;
  7. Do the following in order to see what tables are inside the database.
    8. mysql> show tables;
    You should see three tables, "class", "course" and "entry".  If you don't see all three tables, let me know.
     
  8. If you want to see details about "course" table, type following
    9. Mysql> describe course;
    Then, your course table structure will show up.
  9. If you want to see what's inside class table, do the following.
    mysql> select * from class;
    You should be able to see about four hundred entries.

Queries

Now, let's do the fun part. You'll be writing some SQL queries, executing them, and then looking at the results. Suggestion: use a word processor or the Notepad application to write your queries, then cut and paste them into the SSH window. That way, if you make a typo or don't get the response you were expecting, you can edit your query and cut and paste again. You can also use the arrow keys to make your previous commands appear again, so you can edit or reexecute them.

Warning: you do *not* have the latest version of the course catalog database. Query results won't match exactly with what's shown on the SI web site.

When you're satisfied with your query, cut and paste the query and output from the SSH window into a Word document that you'll turn in as your solution set to be graded.

You may find some of the following samples helpful. Try guessing what they'll do. Then try running them to see what they actually do. Finally, you may want to copy and edit them in order to answer some of the questions below.
  

select courseid, coursenumber, coursename from course where coursenumber = 603;
select courseid, coursename from course where isdtk='true' and coursename LIKE '%SGML and XML%';
INSERT INTO course VALUES('999','306','SI540','Current','540','SI540 Homework Exercise','10','NO','FALSE','FALSE','FALSE','FALSE','0','FALSE','FALSE','FALSE','FALSE','FALSE','FALSE','FALSE','FALSE','FALSE');
select * from course where courseid=999;
select count(*) as total from course where coursenumber= 603;
select isarm, count(*) from course group by isarm;
select coursename from course, class where course.courseid=class.xrefcourseid and class.yearoffered=1998 and class.termoffered='summer';
(Q1: 1 point) Show, for all and only the foundation classes,  the course id, number and course name.
(Q2: 1 point) Show the course ids and course names for all and only those courses which are required by HCI specialization.
(Q3: 1 point) Show  course ids and course names for all and only those courses whose names contain either the word archive or database.
(Q4: 2 points) Insert the following class information into the appropriate table.  Then write a query to show the  data you just entered.
classid: 777
xrefcourseid: 603
yearoffered:  9999
termoffered: FALL
classsection: 9
discgroup: FALSE
 
(Q5: 1 point) Write a query that calculates the number of classes that were offered in the winter of 1998.
(Q6: 2 points) Write a single query that calculates for each term how many classes were offered.
(Q7: 2 points) Show course names and term offered for all advanced courses offered in 1999.