Run Script to generate Sku_Data and populate it. CREATE TABLE SKU_DATA ( SKU Int
ID: 3740370 • Letter: R
Question
Run Script to generate Sku_Data and populate it.
CREATE TABLE SKU_DATA (
SKU Int NOT NULL,
SKU_Description Char(35) NOT NULL,
Department Char(30) NOT NULL,
Buyer Char(35) NULL,
CONSTRAINT SKU_DATAPK PRIMARY KEY (SKU));
-- SKU_DATA Data
INSERT INTO SKU_DATA VALUES (
100100, 'Std. Scuba Tank, Yellow', 'Water Sports', 'Pete Hansen');
INSERT INTO SKU_DATA VALUES (
100200, 'Std. Scuba Tank, Magenta', 'Water Sports', 'Pete Hansen');
INSERT INTO SKU_DATA VALUES (
101100, 'Dive Mask, Small Clear', 'Water Sports', 'Nancy Meyers');
INSERT INTO SKU_DATA VALUES (
101200, 'Dive Mask, Med Clear', 'Water Sports', 'Nancy Meyers');
INSERT INTO SKU_DATA VALUES (
201000, 'Half-dome Tent', 'Camping', 'Cindy Lo');
INSERT INTO SKU_DATA VALUES (
202000, 'Half-dome Tent Vestibule', 'Camping', 'Cindy Lo');
INSERT INTO SKU_DATA VALUES (
301000, 'Light Fly Climbing Harness', 'Climbing', 'Jerry Martin');
INSERT INTO SKU_DATA VALUES (
302000, 'Locking Carabiner, Oval', 'Climbing', 'Jerry Martin');
Look for functional dependencies.
Create new table.
Populate new table.
Drop Column Department from Sku_Data.
Add Foreign Key constraint to Sku_Data (Buyer).
Write code to test referential integrity.
Use EXCEPT and Use Subquery with a JOIN
Explanation / Answer
If you have any doubts, please give me comment...
CREATE TABLE BUYER(
BuyerName VARCHAR(35) NOT NULL PRIMARY KEY
);
INSERT INTO BUYER VALUES('Pete Hansen');
INSERT INTO BUYER VALUES('Nancy Meyers');
INSERT INTO BUYER VALUES('CIndy Lo');
INSERT INTO BUYER VALUES('jerry Martin');
ALTER TABLE SKU_DATA DROP COLUMN Department;
ALTER TABLE SKU_DATA ADD CONSTRAINT FK_1 FOREIGN KEY(Buyer) REFRENCES Buyer(Buyer);
UPDATE SKU_DATA SET Buyer='CHEGG' WHERE Buyer = 'Peter Hansen';
SELECT * FROM SKU_DATA S JOIN Buyer B ON S.Buyer = B.Buyer;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.