Last modified 11/23/99 --PR
Class home page
You can do this assignment in pairs, but not in groups larger than two.
Chapters 9, 10, 11.1-11.2, Supplement on OOP; Supplement with shopping cart example
Optional: Supplement with Java program for bank account
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.
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.
or
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>
mysql> show databases;
(If you forget the ; it will keep prompting you until you type one)
mysql> connect junsong;
mysql> show tables;
You should see three tables, "class", "course" and
"entry". If you don't see all three tables, let me know.
Mysql> describe course;
Then, your course table structure will show up.
mysql> select * from class;
You should be able to see about four hundred entries.
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';
mysql> select coursename
-> from course
-> where isfoundation = "TRUE";
+-----------------------------------------------------------+
| coursename |
+-----------------------------------------------------------+
| Use of Information |
| Choice and Learning |
| Technology in Design: Methods and Means |
| Search and Retrieval |
| Social Systems and Collections |
| Design and Management of Information Systems and Services |
+-----------------------------------------------------------+
6 rows in set (0.01 sec)
mysql> select courseid, coursename
-> from course
-> where ishci = "required";
+----------+-------------------------------------------+
| courseid | coursename |
+----------+-------------------------------------------+
| 21 | Evaluation of Systems and Services |
| 178 | Fundamentals of Human Behavior |
| 213 | W99 Special Topics: User Interface Design |
+----------+-------------------------------------------+
3 rows in set (0.01 sec)
mysql> select courseid, coursename
-> from course
-> where coursename LIKE "%archive%" OR coursename LIKE "%database%";
+----------+-------------------------------------------------+
| courseid | coursename |
+----------+-------------------------------------------------+
| 13 | Introduction to Archives and Records Management |
| 56 | Seminar: Problems in Administration of Archives |
| 63 | Archives Practicum |
| 92 | Searching Automated Databases |
| 107 | Databases and the Web |
| 155 | Online Searching and Databases |
| 248 | Database application design |
| 292 | Microsoft Access Databases & the Web |
| 298 | Introduction to Archives & Records Management |
+----------+-------------------------------------------------+
9 rows in set (0.01 sec)
classid: 777
xrefcourseid: 603
yearoffered: 9999
termoffered: FALL
classsection: 9
discgroup: FALSE
mysql> INSERT INTO class VALUES('777','999','9999','FALL', '9','FALSE');
ERROR 1062: Duplicate entry '777' for key 1
Note: I had already entered this command once before, so I got this error message.
mysql> select *
-> from class
-> where classid = '777';
+---------+--------------+-------------+-------------+--------------+-----------+
| classid | xrefcourseid | yearoffered | termoffered | classsection | discgroup |
+---------+--------------+-------------+-------------+--------------+-----------+
| 777 | 999 | 9999 | FALL | 9 | FALSE |
+---------+--------------+-------------+-------------+--------------+-----------+
1 row in set (0.00 sec)
mysql> select count(*) -> from class -> where termoffered = 'WINTER' and yearoffered = '1998'; +----------+ | count(*) | +----------+ | 41 | +----------+ 1 row in set (0.00 sec)
mysql> select termoffered, yearoffered, count(*)
-> from class
-> group by yearoffered, termoffered;
+-------------+-------------+----------+
| termoffered | yearoffered | count(*) |
+-------------+-------------+----------+
| Fall | 1997 | 42 |
| Fall | 1998 | 50 |
| Spring | 1998 | 24 |
| Summer | 1998 | 19 |
| Winter | 1998 | 41 |
| Fall | 1999 | 48 |
| Spring | 1999 | 25 |
| Summer | 1999 | 15 |
| Winter | 1999 | 60 |
| Winter | 2000 | 39 |
| FALL | 9999 | 1 |
+-------------+-------------+----------+
11 rows in set (0.01 sec)
mysql> select coursename, termoffered
-> from course, class
-> where course.courseid=class.xrefcourseid and
-> class.yearoffered=1999 and course.isadvanced='true';
+---------------------------------------------------------------------+----------
---+
| coursename | termoffered |
+---------------------------------------------------------------------+-------------+
| Collection Development and Management | Winter |
| Professional Practice in Libraries and Information Centers | Winter |
| Medical Informatics | Winter |
| Management of Electronic Records | Winter |
| Evaluation of Systems and Services | Winter |
| Human-Computer Interaction Software Projects Lab | Winter |
| Information Economics | Winter |
| W99 Special Topics: Advanced Integrated Media Production | Winter |
| Special Topics: Current Issues in School Libraries | Winter |
| Organization and Representation of Multimedia Information Resources | Winter |
| Subject-Focused Information Resources and Services: Social Sciences | Winter |
| Subject-Focused Information Resources and Services: Government | Winter |
| Fundamentals of Human Behavior | Winter |
| Directed Field Experience | Winter |
| PE: Cultural Heritage Ourreach | Winter |
| PE: Digital Librarianship | Winter |
| PE: Community Information Systems | Winter |
| PE: University Learning Environment | Winter |
| Special Topics: Seminar in Organization Studies (ICOS) | Winter |
| Information Ethics and Policy | Winter |
| Independent Study | Winter |
| Directed Research | Winter |
| Dissertation/Precandidate | Winter |
| Dissertation/Candidate | Winter |
| W99 Special Topics: Managing the Information Technology Org | Winter |
| W99 Special Topics: User Interface Design | Winter |
| Special Topics: Design of Complex Web Sites | Winter |
| Database application design | Winter |
| Special Topics: Current Issues in Special Libraries | Winter |
| PE: Digital Librarianship | Winter |
| PE: Digital Librarianship | Winter |
| Directed Field Experience | Spring |
| Access Systems for Archival Materials | Fall |
| Seminar: Problems in Administration of Archives | Fall |
| Information Resources and Services | Fall |
| Information Visualization | Fall |
| Concepts of Information Retrieval | Fall |
| Online Searching and Databases | Fall |
| Information Policy | Fall |
| Directed Field Experience | Fall |
| Internship/Independent Study | Fall |
| PE: Digital Librarianship | Fall |
| PE: University Learning Environment | Fall |
| Special Topics: Seminar in Organization Studies (ICOS) | Fall |
| Independent Study | Fall |
| Directed Research | Fall |
| Dissertation/Precandidate | Fall |
| Dissertation/Candidate | Fall |
| Directed Field Experience | Summer |
| Independent Study | Summer |
| Directed Research | Summer |
| Dissertation/Precandidate | Summer |
| Dissertation/Candidate | Summer |
| Independent Study | Spring |
| Directed Research | Spring |
| Dissertation/Precandidate | Spring |
| Dissertation/Candidate | Spring |
| PE: Community Information Systems | Fall |
| Design in a Mosaic of Responsive Adaptive Systems (MoRAS) | Fall |
| Community Information: Knowledge and Skills | Fall |
+---------------------------------------------------------------------+-------------+
60 rows in set (0.02 sec)