CSE2316 and CSE3316 Database Management Systems
Semester 2, 2002 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, 7th edn., Addison-Wesley (Addison-Wesley Longman), 2000. Other recommended texts
  • Elmasri, R. and S. B. Navathe, Fundamentals of Database Systems, 2nd edn., Benjamin-Cummings (Addison-Wesley Longman), 1994.
  • Garcia-Molina, H., Ullman, J.D. and J. Widom, Database Systems - The Complete Book, Prentice Hall, New Jersey, U.S.A., 2002.
  • Lewis, P.M., A. Bernstein and M. Kifer, Databases and Transaction Processing - An Application-Oriented Approach, Addison-Wesley, 2002.
  • Ullman, J.D. and J. Widom, A First Course in Database Systems, Prentice Hall, 1997. (See Garcia-Molina, Ullman and Widom above.) 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 2002 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., DATE Datatype, TO_DATE and SYSDATE; or possibly search for 'date' or 'sysdate' here).
    Oracle Corporation, POET Software and Sybase.
    sqlcourse.com.
    William Yuan's Ingres Database Reference Page - with questions and answers (although this course in 2002 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 had been disabled due to its lack of security. Students must use ssh . (See below for remote login instructions.)
    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.)
    Instructions for remotely logging in (care of an anonymous DBMS student from 2002)
    1. I suggest to download "putty.exe" from www.google.com Type "putty.exe" into the search bar.
    2. Download putty.
    3. Open putty.
    4. Type in "ra-clay.cc.monash.edu.au" as the computer to connect to.
    5. Click SSH
    6. Connect
    7. Type in your username and password for ra-clay ( This will be the same as your Novell username and password IF YOU HAVE SET ALL OF YOUR PASSWORDS TO BE THE SAME ON ALL MACHINES - I think that this can be done through the Monash Portal ).
    8. Type in your password.
    9. Type "sqlplus sYOURSTUDENTNUMBER @cse2316"
    10. Type in the password - You know what this is.
    Good luck. If you are experiencing problems with login, go to ITS.
    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 Tue 20 Aug 2002.
    The submission specifications are spelled out in the assignment - please read them. Deadline: Thu 12th Sep 2002, 2:00p.m.
    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.

    Assignment 2
    Assignment 2 (hand-out #4) was handed out in the lecture of Tue 17 Sept 2002.
    The detailed submission specifications are spelled out in the assignment - please read them.
    Deadline: Thu 10th Oct 2002, 11:59a.m.
    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.

    Recall that logging in to the database requires username, database name, and password. For sqlplus you give the first two as username@databasename . For assign2 and loadtable, however, the database name has been hardcoded into the program, so you just give your username, which is s{IDnum}.

    The sqlplus password given in lectures is just an initial password, by the way - and it is almost certainly not your AuthCate+ password. You can, of course, change your sqlplus password with the `password' command in sqlplus.

    To run video.dat, type
    sqlplus username@cse2316 @video.dat
    followed by your sql password.
    (You might want to cut and paste "sqlplus username@cse2316 @video.dat" to get it right with white space in exactly the right places.)
    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';

    or

    select * from fillings
    where fillings.fdate > sysdate;

    See also Answers to sqlplus Practice exercises, or DATE Datatype or TO_DATE or SYSDATE or possibly search for 'date' or 'sysdate' here.
    More on Oracle
    J. D. Ullman's notes on the Oracle DBMS.
    http://soi3.mmtel.ru/useoracle8/Default.htm.
    spool: Storing, Printing and Formatting Oracle Query Results.
    Oracle8 Error Messages.
    Submitting assignments using "submit"
    Submitting assignments using submit: The submit program is available as /cs/cc/bin/submit on ITS systems and /usr/local/bin/submit on CSSE systems.
    To be safe and sure, run the submit program from Ra-Clay. If you try to submit from elsewhere (e.g., the PC which you boot into Linux), then success is not guaranteed.
    The submit program is fairly self-explanatory - students just run "submit". If they get stuck, they can look at the submit man page under Linux by running
    "man -M /cs/cc/usr/man submit". Students can look in /cs/cc - they'll see a README file which gives them instructions on how to view man pages. The /cs/cc/bin directory is where the submit program is.
    Oracle downtime
    No problems at this stage.
    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.
    Dr D. Dowe is on leave and unavailable from Mon. 30/9/2002 to Fri. 4/10/2002 inclusive. 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 . Australian UNIX and Open Systems User Group (AUUG) 2002 Conference Student Day
    AUUG 2002 Conference Student Day. 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, 2002-2003 (deadline Mon 14th October 2002).
    Dr David Dowe's 2002 Hons. projects.
    Honours and postgraduate study - some options
    "Data mining" using Minimum Message Length (MML) : CSE455 Learning and Prediction II: MML Data Mining.
    Honours, 2002 projects. Some of my Hons. projects and some of my research publications.
    Postgraduate Course Information. More information on
    2001: CSE2316 and CSE3316 Database Management Systems, semester 2, 2001,
    2000: CSE2316 and CSE3316 Database Management Systems, Semester 2 2000; and DBMS, and here.
    1999: 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, world hunger, give water, breast cancer, animal rescue and other do-goody links.