Modify the following SQL command so that the Rep$\_$ID column is the PRIMARY KEY for the table and the default value of Y is assigned to the Comm column. $($The Comm

column indicates whether the sales representative earns commission.$)$

CREATE TABLE store$\_$reps

$($rep$\_$ID NUMBER$(5),$

last VARCHAR$2(15),$

first VARCHAR$2(10),$

comm CHAR$(1))$;

Change the STORE$\_$REPS table so that NULL values can't be entered in the name columns $($First and Last$).$

Change the STORE$\_$REPS table so that only a $Y$ or $N$ can be entered in the Comm column.

Add a column named Base$\_$salary with a datatype of NUMBER $(7,2)$ to the STORE$\_$REPS table. Ensure that the amount entered is above zero.

Create a table named BOOK$\_$STORES to include the columns listed in the following chart.

BOOK$\_$STORES TABLE

Column Name

Store$\_$ID

Name

Contact

Rep$\_$ID

Datatype

NUMBER$(8)$

VARCHAR$2(30)$

VARCHAR$2(30)$

VARCHAR$2(5)$

Constraint Comments

PRIMARY KEY column

Should be UNIQUE and NOT NULL

Add a constraint to make sure the Rep$\_$ID value entered in the BOOK$\_$STORES table is a valid value contained in the STORE$\_$REPS table. The Rep$\_$ID columns of both tables

were initially created as different datatypes. Does this cause an error when adding the constraint? Make table modifications as needed so that you can add the required

constraint.

Change the constraint created in Assignment #$6$ so that associated rows of the BOOK$\_$STORES table are deleted automatically if a row in the STORE$\_$REPS table is deleted.

Create a table named REP$\_$CONTRACTS containing the columns listed in the following chart. A composite PRIMARY KEY constraint including the Rep$\_$ID$,$ Store$\_$ID$,$ and

Quarter columns should be assigned. In addition, FOREIGN KEY constraints should be assigned to both the Rep$\_$ID and Store$\_$ID columns.

REP$\_$CONTRACTS TABLE

Column Name

Datatype

Store$\_$ID

Name

Quarter

Rep$\_$ID

NUMBER$(8)$

VARCHAR$2(5)$

CHAR$(3)$

NUMBER$(5)$

Produce a list of information about all existing constraints on the STORE$\_$REPS table.

Issue the commands to disable and then enable the CHECK constraint on the Base$\_$salary column.

Please make sure you submit the SQL $($queries$)$ and the results of the queries. Write the query, run it in Oracle Live, and show the results.

DATABASE PREPARATION

This chapter assumes you have created the initial JustLee Books database as instructed in Chapter $2.$