I need help completing the coding of integrity constraints IC1 ... IC5. SPOOL ddl.txt SET ECHO ON - - Author: <<< YOUR NAME GOES HERE >>> - - IMPORTANT: use the names IC-1, IC-2, etc. as given below. - -------------------------------------------------------------------- /*The following DROP command is inserted for convenience so that if you need to recompile your code, it will drop the table (if it already exists). */ DROP TABLE Orders CASCADE CONSTRAINTS; DROP TABLE OrderLine CASCADE CONSTRAINTS; -- CREATE TABLE Orders ( orderNum INTEGER PRIMARY KEY, priority CHAR(10) NOT NULL, cost INTEGER NOT NULL, /* IC1: The priority is one of: high, medium, or low */ <<< YOUR SQL CODE GOES HERE >>> /* IC2: The cost of a high priority order is above 2000. */ <<< YOUR SQL CODE GOES HERE >>> /* IC3: The cost of a medium priority order is between 800 and 2200 (inclusive). */ <<< YOUR SQL CODE GOES HERE >>> /* IC4: The cost of a low priority order is less than 1000. */ <<< YOUR SQL CODE GOES HERE >>> ); - - CREATE TABLE OrderLine ( orderNum INTEGER, lineNum INTEGER, item CHAR (10) NOT NULL, quantity INTEGER, PRIMARY KEY (orderNum, lineNum), /* IC5: Every order line must belong to an order in the Order table. Also: if an order is deleted then all its order lines must be deleted. IMPORTANT: DO NOT declare this IC as DEFERRABLE. */ <<< YOUR SQL CODE GOES HERE >>> ); - - ---------------------------------------------------------------- - TESTING THE SCHEMA - ---------------------------------------------------------------- INSERT INTO Orders VALUES (10, 'high', 2400); INSERT INTO Orders VALUES (20, 'high', 1900); INSERT INTO Orders VALUES (30, 'high', 2100); INSERT INTO Orders VALUES (40, 'medium', 700); INSERT INTO Orders VALUES (50, 'low', 1100); INSERT INTO Orders VALUES (60, 'low', 900); SELECT * from Orders; -- ---------------------------------------------------------------- INSERT INTO OrderLine VALUES (10, 1, 'AAA', 200); INSERT INTO OrderLine VALUES (10, 2, 'BBB', 300); INSERT INTO OrderLine VALUES (60, 1, 'CCC', 5); INSERT INTO OrderLine VALUES (15, 1, 'AAA', 7); SELECT * FROM OrderLine; -- DELETE FROM Orders WHERE orderNum = 10; SELECT * From Orders; SELECT * FROM OrderLine; -- SET ECHO OFF SPOOL OFF
I need help completing the coding of integrity constraints IC1 ... IC5.
SPOOL ddl.txt
SET ECHO ON
- -
- - Author: <<< YOUR NAME GOES HERE >>>
- -
- - IMPORTANT: use the names IC-1, IC-2, etc. as given below.
- - --------------------------------------------------------------------
/*The following DROP command is inserted for convenience so that if you need to recompile your code, it will drop the table (if it already exists).
*/
DROP TABLE Orders CASCADE CONSTRAINTS;
DROP TABLE OrderLine CASCADE CONSTRAINTS;
--
CREATE TABLE Orders
(
orderNum INTEGER PRIMARY KEY,
priority CHAR(10) NOT NULL,
cost INTEGER NOT NULL,
/*
IC1: The priority is one of: high, medium, or low
*/
<<< YOUR SQL CODE GOES HERE >>>
/*
IC2: The cost of a high priority order is above 2000.
*/
<<< YOUR SQL CODE GOES HERE >>>
/*
IC3: The cost of a medium priority order is between 800 and 2200 (inclusive).
*/
<<< YOUR SQL CODE GOES HERE >>>
/*
IC4: The cost of a low priority order is less than 1000.
*/
<<< YOUR SQL CODE GOES HERE >>>
);
- -
- -
CREATE TABLE OrderLine
(
orderNum INTEGER,
lineNum INTEGER,
item CHAR (10) NOT NULL,
quantity INTEGER,
PRIMARY KEY (orderNum, lineNum),
/*
IC5: Every order line must belong to an order in the Order table.
Also: if an order is deleted then all its order lines must be deleted.
IMPORTANT: DO NOT declare this IC as DEFERRABLE.
*/
<<< YOUR SQL CODE GOES HERE >>>
);
- -
- - ----------------------------------------------------------------
- - TESTING THE SCHEMA
- - ----------------------------------------------------------------
INSERT INTO Orders VALUES (10, 'high', 2400);
INSERT INTO Orders VALUES (20, 'high', 1900);
INSERT INTO Orders VALUES (30, 'high', 2100);
INSERT INTO Orders VALUES (40, 'medium', 700);
INSERT INTO Orders VALUES (50, 'low', 1100);
INSERT INTO Orders VALUES (60, 'low', 900);
SELECT * from Orders;
-- ----------------------------------------------------------------
INSERT INTO OrderLine VALUES (10, 1, 'AAA', 200);
INSERT INTO OrderLine VALUES (10, 2, 'BBB', 300);
INSERT INTO OrderLine VALUES (60, 1, 'CCC', 5);
INSERT INTO OrderLine VALUES (15, 1, 'AAA', 7);
SELECT * FROM OrderLine;
--
DELETE FROM Orders WHERE orderNum = 10;
SELECT * From Orders;
SELECT * FROM OrderLine;
--
SET ECHO OFF
SPOOL OFF
Step by step
Solved in 2 steps
