Employing XML, XQUERY, and SQL Queries Techniques - Assignment Example

EMPLOYING XML, XQUERY ANF SQL QUERIES TECHNIQUES Customer inserts His/Her Name Customer inserts Name of Institution 17.03.2014 Sample SQL Statements and their XQuery equivalents 1. Display some details of all employees and their privilege levels SQL Command: SELECT Employees.ID, Employees.Last Name, Employees.First Name, Employees.E-mail Address, Employees.Job Title, Employees.Business Phone, Employees.Address, Employees.City, Employee Privileges.Privilege ID FROM Employees, Employee Privileges WHERE Employees.ID = Employee Privileges.Employee ID; XQuery: for $Employees in //Employees let $Employee Privileges := //Employee Privileges [Employee ID = $Employees/ID] return {$Employees/(ID | Last Name| First Name| E-mail Address | Job Title | Business Phone )} {$Employee Privileges/Privilege ID} Report: This is a simple query that simply picks records from two tables with no much filtration. In the case of SQL, a simple SQL command is given, which simply selects the specified fields from the two tables. For the case of XQuery, if only one field was to be shown, a single XPath would be sufficient to implement the whole action. A typical example would be: XPath: //Employees[ID = //Employees Privileges/Employee ID]/Last Name However, since data is to be selected from more than one node, the need for XQuery arises. The exact XQuery statement that can accomplish the above task would be: for $Employees in //Employees let $Employee Privileges in //Employee Privileges Where $Employee Privileges/Employee ID = $Employees/ID return {$Employees/(ID | Last Name| First Name| E-mail Address | Job Title | Business Phone )} {$Employee Privileges/Privilege ID} This is simplified to the sub-selection example like the one shown above. All the examples that follow involve complex inner joins, hence the approach will be to use sub-selections that are related to the above example. 2. Display some details of orders whose unit price is less than $20 SQL Command: SELECT Orders.Order ID, Orders.Employee ID, Orders.Customer ID, Orders.Order Date, Order Details.Unit Price FROM Orders, Order Details WHERE Orders.Order ID = Order Details.Order ID and Order Details.Unit Price < 20; XQuery: for $Orders in //Orders let $Order Details := //Order Details [Order ID = $Orders/ID][Unit Price 20 and Order Details.Unit Price 20][Unit Price 50; XQuery: for $Orders in //Orders let $Order Details := //Order Details [Order ID = $Orders/ID][Unit Price>50] return {$Orders/(Order ID | Employee ID| Customer ID| Order Date | } {$Order Details/Unit Price} Report: Here, the SQL command is selecting data from two tables, Orders and Order details. At the same time, it is checking in the order details table for orders whose unit prices are greater than 50. The comparison in both SQL and XQuery are similar as the < sign is used in both cases. Only one field is displayed from the Order Details table, hence no need for enclosing it into brackets, in the case of XQuery. 5. Display some details of orders shipped to New York SQL Command: SELECT Orders.Order ID, Orders.Employee ID, Orders.Customer ID, Orders.Order Date, Orders.Shipped Date, Orders.Ship Name, Orders.Ship City, Order Status.Status Name FROM Orders, Order Status WHERE Order Status.Status ID = Orders.Status ID and Orders.Ship City = “New York”; XQuery: for $Orders in //Orders let $Order Status := //Order Status [Status ID = $Orders/Status ID] [Ship City= ‘New York’] return {$Orders/(Order ID | Employee ID| Customer ID| Order Date | Shipped Date | Ship City} {$Order Status/Status Name} Report: The tables from which information is drawn are “Orders” and “Order Status”. In SQL, the condition is declared as Orders.Ship City = “New York” while for the case of XML, the expression [Ship City= ‘New York’] is used. 6. Display some details of orders shipped to Cities starting with letter M SQL Command: SELECT Orders.Order ID, Orders.Employee ID, Orders.Customer ID, Orders.Order Date, Orders.Shipped Date, Orders.Ship Name, Orders.Ship City, Order Status.Status Name FROM Orders, Order Status WHERE Order Status.Status ID = Orders.Status ID and Orders.Ship City like “M%”; XQuery: for $Orders in //Orders let $Order Status := //Order Status [Status ID = $Orders/Status ID] [starts-with(Ship City, “M”)] return {$Orders/(Order ID | Employee ID| Customer ID| Order Date | Shipped Date | Ship City} {$Order Status/Status Name} Report: The tables from which information is drawn are “Orders” and “Order Status”. In SQL, the condition is declared as Orders.Ship City like “M%” using the ‘like’ key word while for the case of XML, the expression [starts-with(Ship City, “M”)] is used depicting the use of ‘starts-with’ key word. 7. Display some details of orders and cities whose ship names contain “an” SQL Command: SELECT Orders.Order ID, Orders.Employee ID, Orders.Customer ID, Orders.Order Date, Orders.Shipped Date, Orders.Ship Name, Orders.Ship City, Order Status.Status Name FROM Orders, Order Status WHERE Order Status.Status ID = Orders.Status ID and Orders.Ship City like “%an%”; XQuery: for $Orders in //Orders let $Order Status := //Order Status [Status ID = $Orders/Status ID] [contains(Ship Name, “an”)] return {$Orders/(Order ID | Employee ID| Customer ID| Order Date | Shipped Date | Ship City} {$Order Status/Status Name} Report: The tables from which information is drawn are “Orders” and “Order Status”. In SQL, the condition is declared as Orders.Ship City like “%an%” using the ‘like’ key word while for the case of XML, the expression [contains(Ship Name, “an”)] is used depicting the use of ‘contains’ key word. 8. Display purchases that were conducted by sales managers SQL Command: SELECT Purchase Orders.Purchase Order ID, Purchase Orders.Supplier ID, Purchase orders.Created By, Purchase Orders.Submitted Date, Purchase Orders.Creation Date, Suppliers.Last Name, Suppliers.Job Title FROM Purchase Orders, Suppliers WHERE Purchase Orders.Supplier ID = Suppliers.ID and Suppliers.Job Title = “Sales Manager”; XQuery: for $Purchase Orders in //Purchase Orders let $Suppliers := //Suppliers [ID = $Purchase Orders/Supplier ID][Job Title= ‘Sales Manager’] return {$Purchase Orders/(Purchase Order ID | Supplier ID| Created By| Submitted Date | Creation Date)} {$Suppliers/(Last Name | Job Title)} Report: The tables from which information is drawn are “Purchase Orders” and “Suppliers”. In SQL, the condition is declared as Suppliers.Job Title = “Sales Manager” while for the case of XML, the expression [Job Title= ‘Sales Manager’] is used. 9. Display the Orders of Customers whose job title is that of a Purchasing Manager SQL Command: SELECT Customers.ID, Customers.Company, Customers.Last Name, Customers.First Name, Customers.Job Title, Orders.Order ID, Orders.Employee ID, Order Details.Order ID FROM Customers, Orders, Order Details WHERE Customers.ID = Orders. Customer ID and Orders.Order ID = Order Details.ID and Customers.Job Title = “Purchasing Manager”; XQuery: for $Customers in //Customers [Job Title= ‘Purchasing Manager’] let $Orders := //Orders [Customer ID = $Customers/ID] let $Order Details := //Order Details [ID = $Orders/Order ID] return {$Customers/( ID | Company | Last Name | First Name | Job Title |)} {$Orders/(Order ID | Employee ID)} {$Order Details / Order ID} Report: Here, in formation is extracted from three tables, namely, “Customers”, “Orders” and “Order Details”. In SQL, the condition is declared as Suppliers.Job Title = “Purchasing Manager” while for the case of XML, the expression [Job Title= ‘Purchasing Manager’] is used. 10. Display Products that were transacted on 3/24/2006 SQL Command: SELECT Products.ID, Products.Product Code, Products.Product Name, Product.Description, Inventory Transactions.Transaction Created Date FROM Products, Inventory Transactions WHERE Inventory Transactions. Product ID = Products.ID and Inventory Transactions.Transaction Created Date = “3/24/2006”; XQuery: for $Products in //Products let $Inventory Transactions := //Inventory Transactions [Product ID = $Products / ID][ Transaction Created Date = ‘3/24/2006’] return {$Products/( ID | Product Code | Product Name | Description | Job Title |)} {$Inventory Transaction / Transaction Created Date} Report: The tables from which information is drawn are “Products” and “Inventory Transaction”. In SQL, the condition is declared as Transactions.Transaction Created Date = “3/24/2006” while for the case of XML, the expression [ Transaction Created Date = ‘3/24/2006’] is used. 11. Display Employees who have a shipped status SQL Command: SELECT Employees.ID, Employees.Last Name, Employees.First Name, Employees.Job Title, Purchase Orders.Status FROM Employees, Purchase Orders, Purchase Order Status WHERE Purchase orders.Created By = Employees.ID and Purchase orders.Status ID = Purchase Order Status.Status ID and Purchase Orders.Status ID = 2; XQuery: for $Employees in //Employees let $Purchase Orders := //Purchase Orders [Created By = $Employees/ID][Status ID = 2] let $Purchase Order Status := //Purchase Order Status [Status ID = $Orders/Status ID] return {$Employees/( ID | Last Name | First Name | Job Title |)} {$Purchase Orders/Status} Report: Here, as opposed to the previous examples, information is drawn from three tables (“Employees”, “Purchase Orders” and “Purchase Order Status”), but only two are visible. Also, the status ID is used as the identifier, but it is not displayed in the selection. In SQL, the condition is declared as Purchase Orders.Status ID = 2 while for the case of XML, the expression [Status ID = 2] is used. 12. Display Employees who have an invoiced status SQL Command: SELECT Employees.ID, Employees.Last Name, Employees.First Name, Employees.Job Title, Purchase Orders.Status FROM Employees, Purchase Orders, Purchase Order Status WHERE Purchase orders.Created By = Employees.ID and Purchase orders.Status ID = Purchase Order Status.Status ID and Purchase Orders.Status ID = 1; XQuery: for $Employees in //Employees let $Purchase Orders := //Purchase Orders [Created By = $Employees/ID][Status ID = 1] let $Purchase Order Status := //Purchase Order Status [Status ID = $Orders/Status ID] return {$Employees/( ID | Last Name | First Name | Job Title |)} {$Purchase Orders/Status} Report: Just like in the above example, information is drawn from three tables (“Employees”, “Purchase Orders” and “Purchase Order Status”), but only two are visible. Also, the status ID is used as the identifier, but it is not displayed in the selection. In SQL, the condition is declared as Purchase Orders.Status ID = 1 while for the case of XML, the expression [Status ID = 1] is used. 13. Display some details of orders shipped to Cities ending with letter “l” SQL Command: SELECT Orders.Order ID, Orders.Employee ID, Orders.Customer ID, Orders.Order Date, Orders.Shipped Date, Orders.Ship Name, Orders.Ship City, Order Status.Status Name FROM Orders, Order Status WHERE Order Status.Status ID = Orders.Status ID and Orders.Ship City like “%l”; XQuery: for $Orders in //Orders let $Order Status := //Order Status [Status ID = $Orders/Status ID] [endss-with(Ship City, “l”)] return {$Orders/(Order ID | Employee ID| Customer ID| Order Date | Shipped Date | Ship City} {$Order Status/Status Name} Report: The tables from which information is drawn are “Orders” and “Order Status”. In SQL, the condition is declared as Orders.Ship City like “%l” using the ‘like’ key word while for the case of XML, the expression [endss-with(Ship City, “M”)] is used depicting the use of ‘ends-with’ key word. 14. Display the Orders of Customers whose job title is that of “Purchasing Manager” or “Owner” SQL Command: SELECT Customers.ID, Customers.Company, Customers.Last Name, Customers.First Name, Customers.Job Title, Orders.Order ID, Orders.Employee ID, Order Details.Order ID FROM Customers, Orders, Order Details WHERE Customers.ID = Orders. Customer ID and Orders.Order ID = Order Details.ID and Customers.Job Title in (“Purchasing Manager”, “Owner”); XQuery: for $Customers in //Customers [Job Title= (“Purchasing Manager”, “Owner”)] let $Orders := //Orders [Customer ID = $Customers/ID] let $Order Details := //Order Details [ID = $Orders/Order ID] return {$Customers/( ID | Company | Last Name | First Name | Job Title |)} {$Orders/(Order ID | Employee ID)} {$Order Details / Order ID} Report: This selection makes use of the “in” operator. For the above case, the in operator is used to display records from three tables, “Customers”, “Orders” and “Order Details” of which the job title of the employee is designated either “Purchasing Manager” or “Owner”. The declaration of this condition is different for SQL and XQuery. In SQL, it is used as Customers.Job Title in (“Purchasing Manager”, “Owner”), while in XQuery it is used as [Job Title= (“Purchasing Manager”, “Owner”)] 15. Display the Orders of Customers whose job title is neither “Purchasing Manager” nor “Owner” SQL Command: SELECT Customers.ID, Customers.Company, Customers.Last Name, Customers.First Name, Customers.Job Title, Orders.Order ID, Orders.Employee ID, Order Details.Order ID FROM Customers, Orders, Order Details WHERE Customers.ID = Orders. Customer ID and Orders.Order ID = Order Details.ID and Customers.Job Title not in (“Purchasing Manager”, “Owner”); XQuery: for $Customers in //Customers [Job Title != (“Purchasing Manager”, “Owner”)] let $Orders := //Orders [Customer ID = $Customers/ID] let $Order Details := //Order Details [ID = $Orders/Order ID] return {$Customers / ( ID | Company | Last Name | First Name | Job Title |)} {$Orders / (Order ID | Employee ID)} {$Order Details / Order ID} Report: This selection is the opposite of the above example because it uses the “not in” operator. For the above case, the in operator is used to display records from three tables, “Customers”, “Orders” and “Order Details” of which the job title of the employee is neither designated “Purchasing Manager” nor “Owner”. The declaration of this condition is different for SQL and XQuery. In SQL, it is used as Customers.Job Title not in (“Purchasing Manager”, “Owner”), while in XQuery it is used as [Job Title != (“Purchasing Manager”, “Owner”)] Comparative Analysis of SQL and XQuery From the above discussion, it is evident that both SQL and XQuery are seemingly able to perform the selection tasks efficiently. However, one remains to wonder what the need of another language is when the other one can still perform the same task. This boils down to the motivation of the W3C to come up with the XQuery language in addition to the already existent SQL language. For a number of years, there was a lot of uncertainty about the sufficiency of XML in comparison to other data formats to have a special query language. SQL, on the other hand, has been existent for quite some time, and it is renowned for its ability to retrieve complex information from relational databases. However, it was discovered, later on, that a totally new language would be necessary to handle XML data. As opposed to relational data, which is flat, in the sense that it is organized in terms of rows and columns, XML data is nested, meaning complications may arise, especially when the nesting is irregular. Though using foreign keys may be a good way to search data in relational databases, this may prove tricky in cases where the nesting level of object structures is indefinite. XML, however, allows one to search objects very easily, however irregular their position is in a document. Other reasons prompted the W3C workgroup to consider developing the new XML Query language was the need to have an effective semantic language as opposed to having an extension of the relational language. This was to provide an interfacing tool that would strike a compromise between the working of relational databases and nested databases. This needed to clearly indicate performance and scalability, especially when it comes to the volume of data and the complexness that may arise with the different types of queries. Bibliography BRUNDAGE, M. (2004). XQuery the XML query language. Boston, Addison-Wesley. http://proquest.safaribooksonline.com/0321165810. CHAMBERLIN, D., & KATZ, H. (2003). XQuery from the experts: a guide to the W3C XML query language. Boston, Mass. [u.a.], Addison-Wesley. FAWCETT, J., AYERS, D., & QUIN, L. R. E. (2012). Beginning XML, 5th Edition. New York, Wiley. http://www.books24x7.com/marc.asp?bookid=46607. HOPPE, A. (2008). Materialized view matching and compensation for SQL/XML and Xquery. Thesis (M.Sc.)--York University, 2008. HUNTER, D. (2007). Beginning XML. Indianapolis, IN, Wrox/Wiley Pub. KLEIN, S. (2006). Professional SQL Server 2005 XML. Indianapolis, Wiley Pub. MCGOVERN, J. (2004). XQuery kick start. Indianapolis, Ind, Sams. MELTON, J., & BUXTON, S. (2006). Querying XML XQuery, XPath, and SQL/XML in context. San Francisco, Calif, Morgan Kaufmann. http://site.ebrary.com/id/10229489. PAPAKONSTANTINOU, Y., & MANOLESCU, I. (2006). Querying XML with XQuery. New York ;London, Springer. RAY, E. T. (2003). Learning XML. Beijing, O'Reilly. http://proquest.safaribooksonline.com/0596004206#######. Appendix Below is the relationship diagram of the Northwind database on which the SQL and XQuery statements are to be based Read More