Week 5 in class exercises

JustLee Books has become the exclusive distribuitor for a number of books. The company now needs to assign sales representatives to retail bookstores to handle the new distribution duties. For these assignments, create new tables to support the following:

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 wheather the sales representatives earns commission.)

CREATE TABLE store_reps

(rep_ID NUMBER(5),

Last VARCHAR(15),

First VARCHAR(10),

Comm CHAR(1));

Change the STORE_REPS table so that NULL values cant be entered in the name colimns (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:

Column Name	Datatype	Constraint Comments
Store_ID	NUMBER(8)	PRIMARY KEY column
Name	VARCHAR(30)	Should be unique and NOT NULL
Contact	VARCHAR(30)
Rep_ID	VARCHAR(5)

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 table were constraint? Make table modifications as needed so that you can add the required constraint.

Change the constraint created in task #6 so that associated rows of the BOOK_STORES table are deleted automatically if a new 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 constraint should be assigned to both the REP_ID and Store_ID columns.

Column Name	Datatype
Store_ID	NUMBER(8)
Name	NUMBER(5)
Quarter	CHAR(3)
Rep_ID	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.

Create two tables based on the E_R model shown below and the business rules in the following list for a work order tracking database. Include all the constraints in the CREATE TABLE statements. You should have only two CREATE TABLE statements and no ALTER TABLE statements. Name all constraints except NOT NULLs.

Dates

Will hold a Y or N

Will hold a name for the person assigned

Use your judgement for column datatypes and sizes

Proj# and Wo# are used to uniquely identify rows in these tables.

Each project added must be assigned a name, and no duplicate project names are allowed.

Each work order must be assigned a valid project when added and be assigned a description and number of hours.

Each work order added must have a different description.

The number of hours assigned to a work order should be greater than zero.

If data is provided for the Wo_complete column, only Y or N are acceptable values.