Further Consolidation
This presentation is the property of its rightful owner.
Sponsored Links
1 / 20

Further Consolidation PowerPoint PPT Presentation


  • 63 Views
  • Uploaded on
  • Presentation posted in: General

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. The ‘Customer Orders’ Database.

Download Presentation

Further Consolidation

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Further consolidation

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.


The customer orders database

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.


Further consolidation

‘Product’ Relation

Candidate Key

What additional integrity constraints would be worth adding ?


Customer relation

‘Customer’ Relation

Candidate Key

What additional integrity constraints would be worth adding ?

  • Check house numbers are reasonable.

  • Check postcodes. (?)


Query check on customer

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 AndHouseNo <= 0 ]

Gives all data for ‘duff’ cases; makes it easier to correct.

SQL is :SELECT * FROMCustomerWHERE HouseNo IS NOT NULLAnd HouseNo <= 0 ;


Correct customer relation

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 );


Further consolidation

‘Order’ Relation

Candidate Key

What additional integrity constraints would be worth adding ?

  • Check dates are reasonable.

  • Check customer exists.


Query check on order

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 isSELECT * FROM OrderWHERE Date < ( SYSDATE - 6 ) Or Date > SYSDATE ;


Correct order relation dates

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) );


Query check on order1

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


Correct order relation

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 ) ;


Further consolidation

‘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.


Query check on orderline

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


Correct orderline relation

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 ) ;


Orderline

‘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.


Adding an order

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).


Adding an orderline

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).


Add an order with orderlines

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 ) ;


Deleting an order

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’.


Delete an order with orderlines

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’ ;


  • Login