================================================ SearchDatabase.com's Database Developer May 9, 2001 ================================================ Welcome to the searchDatabase.com Database Developer newsletter! Today's tip, "Multiple-table updateable views" can also be viewed online at: http://www.searchDatabase.com/tip/1,289483,sid13_gci551163,00.html ------------------------------------------------ LEARNING ZONE FEATURED BOOK OF THE WEEK ------------------------------------------------ "Oracle Forms Developer -- The Complete Video Course" By Benjamin Rosenzweig This integrated video-and-book package delivers outstanding start-to-finish Oracle Developer Forms instruction, direct from world-class Oracle instructors at Columbia University's Oracle Certificate Program. You get four hours of QuickTime video lectures, a complete interactive workbook in hard copy and e-book formats; plus an assessment module containing extensive review questions and exercises. Watch the lectures at your desktop, read the related chapters, do the hands-on labs, test your expertise with the interactive exercises. Everything works together to help you master Oracle Developer Forms -- fast. http://www.digitalguru.com/dgstore/product.asp?isbn=0130321249&ac_id=58 ************************************************ "Multiple-table updateable views" By Michael V. Mannino Have you ever wanted a joined table view to be updateable but all you get is read-only? This tip from Michael V. Mannino's "Database Application Development and Design" (McGraw-Hill) lays down some rules to follow to make sure your multiple-table views are updateable: It may be surprising but some multiple-table views are also updateable. A multiple-table view may correspond in a one-to-one manner with rows from more than one table if the view contains the primary key of each table. Because multiple-table views are more complex than single-table views, there is not wide agreement on updateability rules for multiple-table views. Some DBMSs may not support updateability for any multiple-table views. Other systems support updateability for a large number of multiple-table views. In the section below, the updateability rules of Microsoft Access are described as they support a wide range of multiple-table views. The rules for updateable join views in Oracle 8 are similar to Microsoft Access although Oracle is somewhat more restrictive on the allowable manipulation operations. In Access, for example, multiple-table queries known as 1-M queries are updateable. A 1-M query involves two or more tables with one table playing the role of the 1 table and another table playing the role of the M (many) table. To make a 1-M query updateable, follow these rules: Rules for multiple-table updateable queries: 1. The query includes the primary key of the M table. 2. The query contains all required fields (NOT NULL) of the M table. 3. The query does not include GROUP BY or DISTINCT. 4. The join field of the 1 table should be unique (either a primary key or a unique specification). 5. The query contains the foreign key column(s) of the M table. 6. The query includes the primary key and required fields of the 1 table as if the view supports insert operations on the 1 table. Update operations are supported n the 1 table even if the primary key is omitted in the view. Using these rules, Course_Offering_View1 (Example 1, below) and Faculty_Offering_View1 (Example 3) are updateable. Course_Offering_View2 (Example 2) is not updateable because Offering.CourseNo (the foreign key of the M table) is missing. In the SELECT statements, the join operator style (INNER JOIN keywords) is used because Microsoft Access requires it for updateable 1-M queries. Note: For Oracle and SQL2 the CREATE VIEW statement would be used with the SELECT in the below examples. For three examples based on these table creation statements, click here: http://www.searchDatabase.com/tip/1,289483,sid13_gci551163,00.html ------------------------------------------------ FOR MORE INFORMATION ------------------------------------------------ The Best Database Design Web Links: http://searchdatabase.techtarget.com/bestWebLinks/0,289521,sid13_tax283234,00.html Ask your database design questions--or help out your peers by answering them--in our live discussion forums: http://searchdatabase.techtarget.com/forums/0,289802,sid13_fid1,00.html Our database experts are waiting to answer your toughest database design questions in our new Ask the Expert section: http://searchdatabase.techtarget.com/ateExperts/0,289622,sid13,00.html Have a database design tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize! http://searchdatabase.techtarget.com/tipsContest/0,289488,sid13_prz520733_cts520732,00.html ================================================ SUBMIT A TECHNICAL TIP AND WIN A PRIZE! ================================================ Do you have a time-saving shortcut, trick, or script that you want to share with other database pros? The first fifty individuals who submit a tip will receive a free searchDatabase.com hat. The highest rated tips each month will win our "Tip of the Month" contest and receive a high-quality searchDatabase.com denim shirt AND a free book of your choice from Wrox Press. We're accepting short, focused tips or code snippets on topics of interest to DBA's and database developers, such as Oracle, DB2, SQL Server, database design, SQL, performance tuning, etc. Click here for more info and to submit your tip: http://searchdatabase.techtarget.com/tipsPrize/0,289492,sid13_prz520733_cts520732,00.html This will be a great way to share your knowledge, cement your status as an industry expert, and maybe win a prize. Send us your tip today! ******* Sponsored by Postmaster Direct ********* Get free offers from reputable merchants for products that you are interested in. Pick from over 50 categories of interest, modify your profile at any time to suit your needs, and receive only the email that interests you when you subscribe today. Just click on the link below and get your account up and running: http://searchdatabase.techtarget.com/postmasterDirect/1,289639,sid13,00.html ************************************************ To Remove your email address from the distribution list for this specific newsletter "Reply" to this message with REMOVE in the subject line. You will receive an email confirming that you have been removed. To Remove yourself from additional distribution lists or to update your preferences, go to the searchDatabase.com registration page at: http://searchDatabase.techtarget.com/register
