1. Create a table named book_stores to include the column listed in the followin
ID: 3905392 • Letter: 1
Question
1. Create a table named book_stores to include the column listed in the following chart. 2. 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 modification as needed so that you can add the required constraint. 3. Charge 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. 4. 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, FOREGION KEY constraint should be assigned to both the Rep_ID and Store_ID columns. Columns Name datatype Store_ID NUMBER(8) Name NUMBER(5) Quarter CHAR(3) Rep_ID NUMBER 5. Produce a list of information about all existing constraint on the Store_REPS table. 6. Issue the commands to disable and then enable the CHECK constraint on the base_salery column.
Explanation / Answer
If you have any doubts, please give me comment...
-- 1 & 2)
CREATE TABLE Book_Stores(
REP_ID VARCHAR(5),
FOREIGN KEY(REP_ID) REFERENCES STORE_REPS(REP_ID)
);
-- assumed datatype RepID in STORE_REPS is INTEGER
-- In this case foregin key constraint fails, They both must have same datatype
CREATE TABLE Book_Stores(
REP_ID NUMBER(5),
FOREIGN KEY(REP_ID) REFERENCES STORE_REPS(REP_ID)
);
--3)
CREATE TABLE Book_Stores(
Store_ID NUMBER(8),
Name NUMBER(5),
Quarter CHAR(3),
Rep_ID NUMBER(5),
FOREIGN KEY(REP_ID) REFERENCES STORE_REPS(REP_ID) ON DELETE CASCADE
);
-- 4)
CREATE TABLE REP_CONTACTS(
REP_ID NUMBER(5),
store_id NUMBER(8),
quarter INTEGER,
PRIMARY KEY(REP_ID, store_id, quarter),
FOREIGN KEY(REP_ID) REFERENCES STORE_REP(REP_ID),
FOREIGN KEY(store_id) REFERENCES Book_Stores(Store_ID)
);
--5)
DESC Store_REPS;
--6)
ALTER TABLE STORE_REPS ADD Base_salary NUMBER(1,2) CHECK Base_salary>0;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.