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
Here we have given queries for the above mentioned task with integrity constrains. You can find the solution in step 2.
Step by step
Solved in 2 steps
