CSc 4710/6710 Homework Assignment 3
Spring 2001; Due: February 15, 2002
-----------------------------------

(1) Solve problems 2.3 and 2.6 on pages 88-91 of the Oracle8 Primer
    textbook.

(2) Generate an ER design for the GradeBook and the MailOrder databases
    whose relational schema (in SQL) is available in the Oracle 8 
    Primer textbook.


Remarks:

  - Data for the database will be supplied later by me. For now, please
    use your own data; however final submission should be done with
    my data.

  - This homework (problem 1) will be electronically submitted. 
    You need to perform a screen capture of the SQL session. 
    Instructions on how to do the screen capture is given below.

  - For problem 2.6, you must treat each update problem as operating
    on the same database, the one I give you. So, prior to each update,
    you should flush the database and repopulate with original data.

  - You must create 7 different .sql files called 3.sql, 6a.sql, 6b.sql,
    6c.sql, 6d.sql, 6e.sql, and 6f.sql. 
    The 3.sql file should contain all queries one after the other.

    The 6a.sql, 6b.sql etc files should contain your solution to the 
    corresponding update problem. Each update statement should be 
    preceded with a query or queries showing that part of the database state
    being affected by the update, and followed by a query or queries showing 
    that part of the database state which the update affected. 

  - You need to submit all 7 files using the handin command:

      $ handin6710 3 3.sql 6a.sql ...

    You must also submit a printed copy of the screen captured files.
    Please attach a cover sheet with identifying information.

---

How to capture the screen of an SQL session

set echo on
-- (1) list all agents
select * 
from   agents;
-- (2) List agent names of agents who have supplied 
--     part "p01"
select aname
from agents
where aid in (select aid
              from orders
              where agents.aid = orders.aid and
                    pid = 'p01');
-- (3) list all customers
select * 
from   customers;
set echo off
exit;

$script hw3.dat
$sqlplus user/password
SQL>start hw3
$<CTRL>-D (to logout from shell)