Further Consolidation

1. Further Consolidation Objectives of the Lecture : • To use simple queries to check out a DB. • To use insertions, deletions and amendments to maintain a DB. • To review the need for integrity constraints to ensure a DB is maintained correctly.

2. The ‘Customer Orders’ Database • DB consists of 4 relations : • Product - holds data about products sold. • Customer - holds data about customers. • Order - holds general data about customer orders. • OrderLine - holds data about individual products ordered by customers Infrequent changes. Small regular changes. Continually changed. For this exercise, the relations have no Referential Integrity or Ad Hoc constraints.

3. ‘Product’ Relation Candidate Key What additional integrity constraints would be worth adding ?

4. ‘Customer’ Relation Candidate Key What additional integrity constraints would be worth adding ? • Check house numbers are reasonable. • Check postcodes. (?)

5. Query Check on ‘Customer’ House Numbers Could retrieve all house numbersCustomerProject[ HouseNo ]and ‘eyeball’ them as check.Not helpful if there are many customers. Pick out tuples with ‘duff’ house numbers.CustomerRestrict[ HouseNo NOT NULL And HouseNo <= 0 ] Gives all data for ‘duff’ cases; makes it easier to correct. SQL is :SELECT * FROMCustomerWHERE HouseNo IS NOT NULLAnd HouseNo <= 0 ;

6. Correct ‘Customer’ Relation Need to : • Amend data in relation to correct it; • Add an integrity constraintto prevent the error recurring. Cannotaddan integrity constraintuntil the relevant data in the relation is valid. Amend Relation SQL is :UPDATECustomerSET HouseNo = 698WHERE CustNo = ‘C765’ ; And so on if there were other errors. Add Ad Hoc Integrity Constraint SQL is : ALTER TABLE CustomerADD CONSTRAINT Valid_HouseNoCHECK( HouseNo > 0 Or HouseNo IS NULL );

7. ‘Order’ Relation Candidate Key What additional integrity constraints would be worth adding ? • Check dates are reasonable. • Check customer exists.

8. Query Check on ‘Order’ Dates What is a reasonable date ? Can’t be future, or too far in past. Assume a “week up to and including Today” is acceptable. Pick out tuples with ‘duff’ order dates.OrderRestrict[ Date < ( Today - 6) Or Date > Today] SQL is SELECT * FROM Order WHERE Date < ( SYSDATE - 6 ) Or Date > SYSDATE ;

9. Correct ‘Order’ Relation : Dates Need to : • Amend data in relation to correct it; • Add an integrity constraint to prevent the error recurring. Amend Relation SQL is :UPDATEOrderSET Date = SYSDATEWHERE Date < ( SYSDATE - 6 ) Or Date > SYSDATE ; Or set Date to another date if appropriate. Add Ad Hoc Integrity Constraint SQL is : ALTER TABLE Order ADD CONSTRAINT Valid_Date CHECK( Not (Date < ( SYSDATE - 6 ) Or Date > SYSDATE) );

10. Ord2 01-Dec-2007 C755 Query Check on ‘Order’ Customers Exist Check for customers not in Customer relation. OrderRestrict[ CustNo Not In ( ................................... ) ] Need to get all the ‘CustNos’ in Customer. CustomerProject[ CustNo ] CustNos inCustomer. Gives data to put in previous query. To accomplish in SQL, write : SELECT CustNo FROMCustomer ; SELECT * FROMOrderWHERE CustNo Not In (‘C123’, ‘C453’, ‘C765’, ‘C109’, ‘C125’, ‘C025 ); gives

11. Correct ‘Order’ Relation : Customers Exist Amend Relation SQL is :UPDATEOrderSET CustNo = ‘C765’WHERE CustNo = ‘C755’ ; Or as appropriate. Add Referential Integrity Constraint SQL is : ALTER TABLE Order ADD CONSTRAINT Valid_CustNo FOREIGN KEY( CustNo ) REFERENCES Customer( CustNo ) ;

12. ‘OrderLine’ Relation Candidate Key What additional integrity constraints would be worth adding ? • Check order exists. • Check product exists. • Check quantities are positive. • Check prices are positive.

13. Query Check on ‘OrderLine’ Orders Exist Check for orders not in OrderLine relation. OrderLineRestrict[ OrdNum Not In ( ........................... ) ] Need to get all the ‘OrdNums’ in Order. OrderProject[ OrdNum ] OrdNums in Order. Gives data to put in previous query. Thus to accomplish in SQL, write : SELECT OrdNum FROMOrder ; SELECT * FROMOrderLineWHERE OrdNum Not In (‘Ord1’, ‘Ord2’ ); gives

14. Correct ‘OrderLine’ Relation : Orders Exist Fortunately all the orders appearing in OrderLine are also in Order.So no amendments are necessary to correctOrderLine. Still need to add a Referential Integrity Constraint. SQL is : ALTER TABLE OrderLine ADD CONSTRAINT Valid_OrdNum FOREIGN KEY( OrdNum ) REFERENCES Order( OrdNum ) ;

15. ‘OrderLine’ : Remaining Checks & Corrections • Check that the ‘ProdNos’ all exist in Product.Correct if necessary.Regardless of any corrections, add a Referential Integrity Constraint to prevent it from occurring. • Check that ‘Quantity’ is always positive.Correct if necessary.Regardless of any corrections, add an Ad Hoc Integrity Constraint to prevent it from occurring. • Check that ‘TotalPrice’ is always positive.Correct if necessary.Regardless of any corrections, add an Ad Hoc Integrity Constraint to prevent it from occurring.

16. OrdNum Date CustNo Ord1 23-Nov-2003 C123 Ord2 01-Dec-2007 C765 Adding an Order INSERT INTO Order VALUES (‘Ord2’, ’30-Nov-2007’, ‘C453’) ; ‘Today’ is02-Dec-2007. This will fail due to a duplicate candidate key. (‘Date’ and ‘CustNo’ are valid).

17. Adding an OrderLine INSERT INTO OrderLine VALUES (‘Ord3’, 1, ‘Prod5’, 2, 64.50 ) ; This will fail due to a referential integrity constraint - there is no ‘Ord3’ in Order. (All other values are valid).

18. Add an Order with OrderLines The following insertions would be OK. INSERT INTO Order VALUES (‘Ord3’, ’30-Nov-2007’, ‘C453’) ; INSERT INTO OrderLine VALUES (‘Ord3’, 1, ‘Prod5’, 2, 64.50 ) ; ‘Today’ is 02-Dec-2007. Another line of the order could also be added as follows ;- INSERT INTO OrderLine VALUES (‘Ord3’, 2, ‘Prod4’, 5, 54.49 ) ;

19. OrdNum Date CustNo Ord1 23-Nov-2003 C123 Ord2 01-Dec-2007 C765 Ord3 30-Nov-2007 C455 Deleting an Order DELETE FROM Order WHERE OrdNum = ‘Ord3’ ; This will fail due to a referential integrity constraint. There is still an order line(s) for ‘Ord3’.

20. Delete an Order with OrderLines The following deletion(s) would be OK :- DELETE FROM OrderLine WHERE OrdNum = ‘Ord3’ ; This would remove all lines of the ‘Ord3’ order, however many there are. ‘Ord3’ can now be deleted from Order :- DELETE FROM Order WHERE OrdNum = ‘Ord3’ ;