1. 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 VARCHAR2(15), first VARCHAR2(10), comm CHAR(1));
2. Change the STORE_REPS table so that NULL values can’t be entered in the name columns (First and Last).
3. Change the STORE_REPS table so that only a Y or N can be entered in the Comm column.
4. 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.
5. Create a table named BOOK_STORES to include the columns listed in the following chart.
6. 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.
7. 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.
8. 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.
Business Rules
• Use your judgment 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 to 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.
Create and execute the SQL statements needed to enforce the data relationships among these tables.
