Hands-on Assignments JustLee Books has exclusive distributor for a number of boo

Question

Hands-on Assignments JustLee Books has exclusive distributor for a number of books. The company now needs to sales representatives to retail bookstores to handle the new distribution duties. For these assignments, create new tables to support the following: dify the following SQL command so that the Rep ID column is the PRIMARY KEY for 1. table and the d he 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));

Explanation / Answer

1)

CREATE TABLE store_reps

(rep_ID NUMBER(5) PRIMARY KEY,

last VARCHAR2(15),

first VARCHAR2(10),

comm CHAR(1) DEFAULT 'Y');

2)

CREATE TABLE store_reps

(rep_ID NUMBER(5) PRIMARY KEY,

last VARCHAR2(15) NOT NULL,

first VARCHAR2(10) NOT NULL,

comm CHAR(1) DEFAULT 'Y');

3)

CREATE TABLE store_reps

(rep_ID NUMBER(5) PRIMARY KEY,

last VARCHAR2(15) NOT NULL,

first VARCHAR2(10) NOT NULL,

comm CHAR(1) DEFAULT 'Y' CHECK (comm IN ('Y','N'));

4)

ALTER TABLE store_reps

add (Base_salary Number(7,2))

add constraint store_reps_b_s_ck check (Base_salary > 0).

5)

CREATE TABLE Book_stores

(Store_ID Number(8) CONSTRAINT b_s_sid_pk Primary Key,

Name varchar2(30) CONSTRAINT b_s_name_uk Unique,

contact varchar2(30),

Rep_ID varchar2(5)

);

6)

ALTER TABLE book_stores

modify (Rep_ID number(5))

add constraint b_s_rid_fk foreign key(Rep_ID)

References Store_Reps(Rep_ID);

7)

ALTER TABLE book_stores

add constraint b_s_rid_fk foreign key(Rep_ID)

references Store_Reps(Rep_ID) ON DELETE CASCADE;

8)

CREATE TABLE Rep_Contracts

(Store_ID number(8) REFERENCES Book_stores(Store_ID),

Name number(5),

Quarter Char(3),

Rep_ID Number(5) REFERENCES Store_Reps(Rep_ID),

PRIMARY KEY(Store_ID,Quater,Rep_ID),

);

9)

SELECT * FROM user_constraints WHERE table_name='Store_Reps'

10)

alter table store_reps

disable constraint store_reps_b_s_ck;

alter table store_reps

enable constraint store_reps_b_s_ck;

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.