This lab focuses on the SQL DDL.

Configuring an Oracle User

Do this…

Configure an Oracle user for yourself.

  1. As you did last week, start the OracleXE server, move to your network drive and start sqlplus as the system user.

  2. Create a database user for yourself by saying

    SQL> CREATE USER yourId IDENTIFIED BY yourPassword;
SQL> GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE MATERIALIZED VIEW, UNLIMITED TABLESPACE, CREATE SEQUENCE TO yourID;

  3. You can now logout ( exit ) and log back in as yourself ( sqlplus yourId/yourPassword ).

Working with Oracle Integrity Constraints

You now will consider a sample command file that defines a simple movies database with some sample data. Pull this (and future) sample code from this (public) repo: https://github.com/cs342/code. Study the movies database code in 02modeling/movies.sql so that you know how command file works. In particular, consider the purpose of the StatusValue table.

Exercise 2.1

Try the following, noting what happens and why. Store your DDL/DML commands along with explanations of how/why they work in lab02_1.sql.

  1. Try adding records to the movie relation that cause these intra-relation issues:

    1. a repeated primary key value
    2. a NULL primary key value
    3. a violation of a CHECK constraint
    4. a violation of an SQL datatype constraint
    5. a negative score value

  2. 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 key value in a referencing (aka child) table that doesn’t match any key value in the referenced (aka parent) table
    3. a key value in a referenced table with no related records in the referencing table

  3. 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.
    3. Modify the ID of a movie record that is referenced by a casting record.

    Note that though the text discusses it, Oracle doesn’t support ON UPDATE CASCADE. Would supporting such a feature be a good idea? See Ask Tom’s discussion of this.

Save your command file for submission.

We now address two more challenging questions.

Exercise 2.2

Give answers to these more challenging questions in lab02_2.txt.

  1. The original IMDB Movie table included fields for both score and votes. Can you add a constraint (using the mechanisms we’ve studied) the requires that all movies having a non-NULL score value must also have more than 1000 votes? If so, explain what constraint you’d specify; if not, include an explanation of why it’s not possible.

  2. Database systems, including Oracle, allow DBAs to create separate constraints that are set on given tables, which allows DBAs to turn constraints off and on during database operations, e.g.:

    ALTER TABLE tableName
    ADD CONSTRAINT constraintName
    someConstraint;

    Can you imagine when, if ever, you might want to separate a constraint from the table definition it modifies? If so, describe the circumstances; if not, explain why not.

Save your text file for submission.

Note also that Oracle treats empty strings (i.e., '' ) as NULL values while other DBMSs, e.g., SQL Server, distinguish these two values.

Adding Additional Integrity Contraints

If time allows, complete the following exercises. They are optional for the lab but required for the homework.

The movies database hard-codes enumerated types for the performer status using a CHECK constraint.

Exercise 2.3 [Homework]

Can you modify the movies database to support the enumerated status type using the relational model itself rather than hard-coding the values in a CHECK constraint? If so, show how and explain the benefits and/or costs of doing it this way. If not, explain why not.

Store your modified version of the movies database or your explanation in lab02_3.sql.

DBMSs provide automated support for generating sequences of unique Id numbers; see Oracle’s Overview of Sequences.

Exercise 2.4 [Homework]

Build and use a sequence for the primary key values of the Movie relation, then consider these questions.

  1. Would you need an additional sequence for the performer relation primary key values? Why or why not?
  2. Do you see any problems with using sequences in a DDL command file to construct the full movies database?

Store your modified command file and your answers to the questions in lab02_4.sql.

Remember to stop the OracleXE service as described in lab 1.

Checking in

We will grade your work according to the following criteria:

Exercises 2.3–4 are graded with the homework.