SQL Clearly Explained- P9: You don’t need to be a database designer to use SQL successfully. However, you do need to know a bit about how relational databases are structured and how to manipulate those structures. | User-Defined Data Types and Typed Tables 413 creates a table with a column that stores a reference to an ingredient. The SCOPE clause specifies the table or view that is the source of the reference. To insert a row into a table with a REF column you must include a SELECT in the INSERT statement that locates the row whose reference is to be stored. As you would expect the object being referenced must exist in its own table before a reference to it can be generated. We must therefore first insert an ingredient into the ingredient table INSERT INTO ingredient VALUES Unbleached flour cups 25 Then we can insert a referencing row into ingredient_amount INSERT INTO ingredient_amount SELECT REF i FROM ingredient i WHERE Unbleached flour VALUES An application program that is using the recipe database as its Dereferencing for Data data store will need to use the reference stored in the ingredi- Access ent_amount table to locate the name of the ingredient. The DEREF function follows a reference back to the table being referenced and returns data from the appropriate row. A query to retrieve the name and amount of an ingredient used in a recipe instruction could therefore be written SELECT DEREF related_ingredient .ingredient_name amount FROM ingredient_amount WHERE DEREF related_instruction .recipe_name French toast Note that the DEREF function accesses an entire row in the referenced table. If you don t specify otherwise you will retrieve the values from every column in the referenced row. To Please purchase PDF Split-Merge on to remove this watermark. 414 Chapter 19 Object-Relational Support retrieve just the value of a single column we use dot notation. The first portion DEREF related_ingredient actually performs the dereference. The portion to the right of the dot specifies the column in the referenced row. Some DBMSs provide a dereference operator - that can be used in place of the DEREF function. The preceding query might be written