Database Modeling & Design Fourth Edition- P49: Database technology has evolved rapidly in the three decades since the rise and eventual dominance of relational database systems. While many specialized database systems (object-oriented, spatial, multimedia, etc.) have found substantial user communities in the science and engineering fields, relational systems remain the dominant database technology for business enterprises. | Data Manipulation Language DML 227 insert into customer values 010 klingon rogueShip 4 This command deletes all customers with credit levels less than 2 delete from customer where credit_level 2 This command modifies the credit level of any customer with level 6 to level 7 update customer set credit_level 7 where credit_level 6 Referential Integrity The following update to the item table resets the value of item_num for a particular item but because item_num is a foreign key in the order table SQL must maintain referential integrity by triggering the execution sequence named by the foreign key constraint on update cascade in the definition of the order table see Section A2 . This means that in addition to updating a row in the item table SQL will search the order table for values of item_num equal to 368 and reset each item_num value to 370. update item set item_num 370 where item_num 368 If this update had been a delete instead such as the following delete from item where item_num 368 then the referential integrity trigger would have caused the additional execution of the foreign key constraint on delete set default in order as defined in Section which finds every row in order with item_num equal to 368 and takes the action set up in the default. A typical action for this type of database might be to set item_num to either null or a predefined literal value to denote that the particular item has been 228 Appendix The Basics of SQL deleted this would then be a signal to the system that the customer needs to be contacted to change the order. Of course the system would have to be set up in advance to check for these values periodically. SQL Views A view in SQL is a named derived virtual table that derives its data from base tables the actual tables defined by the create table command. View definitions can be stored in the database but the views derived tables themselves are not stored they are derived at execution time when the view is invoked as
