LAB 6:    Integrity Constraints

  1. Modify the constraints you have established on the Portfolio table to ensure that the relationship between Manager and Portfolio is optional;  the DELETE RULE for the foreign key should be NO ACTION.

  2. Enter the following commands to add rows to the PORTFOLIO table:

    3.     insert into portfolio values ('P021', null,25000);
        insert into portfolio values ('P022', 'M5',12000);

    What constraints (if any) are violated by the commands?  Would the result of the above inserts be different if the relationship between Manager and Portfolio was mandatory?

  3. Enter the following command:

    4.     update portfolio set managerno = 'M5' where managerno = 'M2';

    What constraint is violated by this command?

  4. Enter the command to delete manager number M2 from the manager table.  Why is the row not deleted?

  5. Enter the command to delete manager number M1 from the manager table.  What happens?  Why?

  6. Delete all the rows in the Holding table.

  7. Modify the Holding table as follows:

    8. Use ALTER TABLE to add a column: HOLDING_NO NUMBER(4) and establish it as the primary key.  Note that you will have to drop the current primary key first.
    Ensure that the relationships between PORTFOLIO and HOLDING and between STOCK and HOLDING are mandatory relationships (user ALTER TABLE to modify the columns).  For the portfolio number foreign key in HOLDING, set the DELETE RULE to CASCADE .  The DELETE RULE for the stock symbol foreign key in HOLDING should be NO ACTION.  Note:  you will have to user ALTER TABLE to drop the current foreign key and then ALTER TABLE to add the revised foreign key.

  8. Enter the following command to add a row to the HOLDING table:

    9.  insert into holding values (null, 'JDS','11-Oct-1999',200,21.80,1000);

    What constraint is violated by this command?

  9. Enter the following command to create a sequence called HOLDSEQ starting at 1001:

    10.     create sequence holdseq start with 1001;

  10. Modify the data in the script file holdingdata.sql to include an entry for HOLDING_NO which will be assigned the next value in the sequence HOLDSEQ.  The entry for HOLDING_NO in each row should contain:

    11.     holdseq.nextval

    Run the script file to load the holding table data.  Enter the command:

       select * from holding;

    to view the data in the Holding table. What values have been entered for HOLDING_NO?

  11. Enter the command to delete the stock for JDS from the Stock table.  What constraint is violated by this command?

  12. Enter the command to delete portfolio number P001 from the portfolio table.  What message is displayed?  Have any other rows been deleted?