CSE2316 and CSE3316 Database Management Systems
Semester 2, 2001 Page under (re-)construction. Lecturer : Dr. David Dowe, School of CS SE, Faculty of IT, Monash University, Australia.
Recommended text

Date, C. J., An Introduction to Database Systems, 6th edn., Addison-Wesley (Addison-Wesley Longman), 1995. Other recommended texts

Elmasri, R. and S. B. Navathe, Fundamentals of Database Systems, 2nd edn., Benjamin-Cummings (Addison-Wesley Longman), 1994.

Ullman, J.D. and J. Widom, A First Course in Database Systems, Prentice Hall, 1997. Additional reading

Celko, J., SQL Puzzles and Answers, Morgan Kaufmann, San Francisco, Calif., U.S.A., 1997.

Hansen, G. W. and J. V. Hansen, Database management and design, 2nd ed., Prentice-Hall Inc., 1996.

McFadden, F. R. and J. A. Hoffer, Modern Database management, 4th ed., Menlo Park, Calif. : Addison Wesley, Benjamin/Cummings Pub. Co., 1994 (2nd ed. was c1988).

Zinky, M.A., J. W. Everett and L. J. Hawbaker, Introduction to INGRES, Boston : PWS-KENT Pub. Co., c1992 (although this course in 2000 uses Oracle).

Books by Harcourt Brace Dryden, or Morgan Kaufmann.

Prentice Hall Professional Technical Reference and A. Morrison and A. Rischert: ``Oracle SQL Interactive Workbook Companion Website''.

http://www.hoganbooks.com/freebook/webbooks.html.

http://www.monumental.com/boat/booksIndex.html. Hand-outs
Hand-outs are given out on a regular basis in lectures. All left-overs of hand-outs and assignments are given to the CSSE General Office on the ground floor of Bldg 63. Students who miss lecture hand-outs should obtain any such hand-outs from the CSSE General Office (and not from the lecturer, who will re-direct you to the CSSE General Office). In considerate fairness to the General Office admin. staff, these should ideally please be collected as soon as possible after the lecture, so that the admin. staff don't have to make any additional copies.
Newsgroup: monash.csse.cse2316-3316 .
SQL, Oracle, Ingres and other enviroment links :
SQL Standards, Standard SQL Publications and Articles.
Oracle8 Server SQL Reference, Release 8.0 and SELECT command.
(Outdated: Oracle7 Server SQL Reference Manual index, now slightly outdated.)
Oracle8 Product Documentation Library: text version and Java version.
Oracle8 Server, SQL*Plus Quick Reference, Release 8.0, SQL*Plus User's Guide and Reference and Understanding SQL*Plus, etc.
Operators, Functions, Expressions, Conditions (e.g., TO_DATE).
Oracle Corporation, POET Software and Sybase.
sqlcourse.com.
William Yuan's Ingres Database Reference Page - with questions and answers (although this course in 2000 uses Oracle).
Monash University's copy of Mini SQL (mSQL) no longer exists.
Logging in and accessing SQL (for Practice Exercises and for Assignment 1)
Log in to a Linux account (with SQL) at Monash, such as (but not necessarily): ra-clay.cc.monash.edu.au . (Your initial password will have been your CRUX password.)

Telnetting to ra-clay has been disabled due to its lack of security. Students must use ssh .
From UNIX, this looks like:
ssh username@ra-clay.cc.monash.edu.au

For Windows, students can get an ssh clone such as
http://www.chiark.greenend.org.uk/~sgtatham/putty/

A larger list of Windows clients is available at
http://www.freessh.org/windows.html
along with a lot of useful info about ssh from the home page, http://www.freessh.org/

Now that you've logged in to ra-clay.cc.monash.edu.au , the shell command below will add important SQL-related lines to the end of your ~/.login file. Only do it once:
orafix

Having typed in orafix, as the orafix script says, you should now log out and then log back in again.

sqlplus your_username@cse2316
(followed by your password)
lets you use the database called cse2316 .
Your username here will be "s" followed by your student_id, e.g. s12345678 .
If you don't yet know your password, rest assured that it was given to you.

Once connected, you can then change your database password as follows:
alter user `accountname' identified by `newpassword';

Then use SQL commands loosely like the one immediately below:
select blah1
from dld.tablename1, dld.tablename2 (or you could replace the text at left by: from tablename1, tablename2)
where tablename1.blah2 = tablename2.blah3
and another_condition;

Available tables in the database called cse2316 are:
dld.students, dld.staff, dld.courses and dld.enroll . (These pertain to the STUDENT database.)
dld.patient and dld.fillings . (These pertain to the DENTIST database.)
dld.customer, dld.product, dld.whouse, dld.stock, dld.ordhead and dld.ordline. (These tables pertain to the STOCKS database.)
SQL Practice exercises
sqlplus Practice exercises (hand-out #3) and Answers to these exercises.

``Light entertainment'' :-) : Pi = 3.141592653589793.... Assignment 1
Assignment 1 was handed out in the lecture of Thu 9 Aug 2001.
The submission specifications are spelled out in the assignment - please read them. Deadline: Fri 31st Aug 2001, 12 noon.
Spare copies should be available from the CSSE General Office in Bldg 63.
Andrew Langford's script clean facility. I can't vouch for whether or not this works, so back up your file and use the script clean facility - if you wish to - at your own risk.
Sample solutions to 2001's DBMS Assignment 1.

Assignment 2
Assignment 2 (hand-out #3) was handed out in the lecture of Thur 13 Sept 2001.
The detailed submission specifications are spelled out in the assignment - please read them.
Deadline: Mon 8th Oct 2001, 11:59a.m. (although submissions will be accepted from Mon 1st Oct 2001, and early submissions are encouraged).
Spare copies should be available from the CSSE General Office in Bldg 63.

Files for Assignment 2: hire.txt, video.txt, customer.txt, video.dat, SQL*Loader, (loadtable.pc,) assign2.pc, Makefile .
Note: video.dat creates the tables. When they're created, loadtable or SQL*Loader loads data in from the *.txt files. The assign2.pc program will struggle if it can't find the tables.
These above files have been moderately tested, so if you are having problems they are unlikely to be due to the files. Typical mistakes are cutting and pasting from a web browser, which can slightly alter text (such as losing the TABs in the Makefile), when you should be `saving' the file, and ordinary UNIX problems like incorrectly set paths so that make, proc, or even sqlplus are not found.

As specified in the assignment, use the Monash (FIT) Linux machines.
To run video.dat, type
sqlplus username@cse2316 @video.dat
followed by your sql password.
All the *.txt files should have their columns/fields delineated by TABs.

Use make to run the Makefile and `make' the programs.
It (pre-)compiles loadtable.pc to loadtable.c, and assign2.pc to assign2.c .
To run assign2, type ./assign2

video.dat will create tables. Of course, you can create tables without having to use video.dat . Also, if you want to create tables with a different number of fields, you first have to drop any existing table with the same name. You then have to either modify video.dat and then re-run video.dat, or CREATE TABLE.

Run loadtable on the files to put the data into the tables that were created by video.dat . This data will come from the specified *.txt files. As above, so that loadtable can work properly, make sure that all the *.txt files have their columns delineated by TABs.
assign2.pc is your main program file. You will eventually submit it. See the instructions in the Assignment.

Date field in HIRE table:
Note that SQL queries of the following form are permitted:
select dfield
from dfile
where (dfield + 14) < '25feb1989';
See also Answers to sqlplus Practice exercises or TO_DATE.
More on Oracle
J. D. Ullman's notes on the Oracle DBMS.
http://soi3.mmtel.ru/useoracle8/Default.htm.
Oracle downtime
ITS Interruption of Service - Oracle teaching services - occurred Friday 29-Sep-2000 08:00 hrs - 08:30 hrs. "The number of Oracle instance on oracle teaching server hits the limit and the server needs to be reconfigured." ITS apologises for any inconvenience caused by this action.
Consultation times and venue for exercises, discussion, assignment, etc. with Dr D. Dowe:
Tuesday 3:00 - 4:00pm; Thursday 12:00 - 1:00pm.
Clayton Bldg 26 (CS), Room 102 or Room 113.
For the exam period, Tue 23/10 2:00 - 4:00pm; Thu 25/10 11:00 - 1:00pm; Fri 26/10 2:00 - 3:00pm. Monash Library
Monash Library and catalogue. Monash Computer Centre and CSSE Computing Reference Information
Monash Computer Centre Reference Information and Information Sheets.
CS SE Practical and Tutorial Class Services (information possibly not available from this link)
Monash Uni. semester dates, vacations and holidays. Computer Science Club
Monash Uni. Computer Science Club. Language and Learning Services (LLS): Harriet Searcy, Harriet.Searcy@infotech.monash.edu.au . Job links (mainly unofficial - just some I happened to spot)
MONSEACS (official Monash University page)
Gradlink
Employment Opportunities Australia.
http://www.newsclassifieds.com.au/
First and Second Year Advanced Project Scheme: First and Second Year Advanced Project Scheme, 2001.
Summer Studentships: Summer Studentships, 2001-2002.
Dr David Dowe's 2002 Hons. projects.
Honours and postgraduate study - some options
"Data mining" using Minimum Message Length (MML) : CSC423 Learning and Prediction.
Honours, 2002 projects. Some of my Hons. projects and some of my research publications.
Postgraduate Course Information. More information on CSE2316 and CSE3316 Database Management Systems, Semester 2 2000; and DBMS, and here.
1999: Last year's CSE[23]316 and CSC[23]161 Database Management Systems, Semester 2 1999.
1998: CSC2161 and CSC3161 Database Management Systems, Semester 2 1998. Not directly related to DBMS (and rather unofficial): Rainforests and world hunger.