[Solved] Assignment 2

Assignment 2

2.1 Configure an Oracle user for yourself.

1. As you did last assignment, start the OracleXE server, start sqlplusas the system
2. Create a database user for yourself by saying

CREATE USER yourId IDENTIFIED BY yourPassword;

GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE MATERIALIZED VIEW, UNLIMITED TABLESPACE, CREATE SEQUENCE TO yourID;

1. You can now logout ( exit) and log back in as yourself ( sqlplus yourId/[email protected] ).

2.2 Study the DDL command file for the movies database and then try the following, noting what happens and why. The sql source code can be located in Blackboard

1. Try adding records to the movie relation that cause these intra-relation issues:
   1. a repeated primary keyvalue
   2. a NULL primary key value

• a violation of a CHECK constraint

1. a violation of an SQL datatype constraint
2. a negative score value

1. Try adding records that cause these inter-relation issues:
   1. a new record with a NULL value for a foreign key value
   2. a foreign keyvalue in a referencing (aka child) table that doesn’t match any key value in the parent tableTry this on the status field of a casting record, for which the schema disallows any values not explicitly listed in the StatusValue “legal values” table.

• a key value in a referenced(aka parent) table with no related records in the referencing table

1. Try deleting/modifying records as follows:
   1. Delete a referenced record that is referenced by a referencing record.
   2. Delete a referencing record that references a referenced record.

• Modify the ID of a movie record that is referenced by a casting record.

Redo these delete/modify test cases with ON DELETE CASCADE specified in the schema for the foreign key constraints in the casting table. Note that though the text discusses it, Oracle doesn’t support ON UPDATE CASCADE .

1. Can you add a constraint the requires that movies having a non-NULL score value implies that they have more than 1000 votes? If so, include theCHECKconstraint in your exercise 2 command file. If not, explain why not.

It would be wise to test the contents of your tables using simple DML commands.

2.3 Implement the ERD shown here as an Oracle database. Include a schema and 2-3 sample records for each table. Use the movies command file from lab 2 as a model for your implementation and be sure to set the constraints appropriately.

Be sure to include primary and foreign key constraints as appropriate.