Lab 5: XML databases and XQuery

Task 1: consider two database designs

A relational database for bank data

Imagine that a bank is using a relational database for data about its customers and their accounts.

Customer(number CHAR(8) PRIMARY KEY, name VARCHAR(20), address VARCHAR(30));
Account(number CHAR(10) PRIMARY KEY, branch VARCHAR(20), balance FLOAT);
Owns(account_number CHAR(10), customer_number CHAR(8),
    PRIMARY KEY (account_number, customer_number),
    FOREIGN KEY (account_number) REFERENCES Account,
    FOREIGN KEY (customer_number) REFERENCES Customer
);

1. What is the purpose of the Owns table? Would it be possible to eliminate this table and include its data in one of the other two tables?

An XML database for bank data

We could also represent the bank’s data in XML, which would make it easier to exchange data among applications and to integrate it with data from other sources. (There would also be some disadvantages, including the fact that storing data in XML is typically less efficient – both in the amount of storage used and in the speed with which queries can be executed.)

You can see a small instance of the XML version of this database here:
sample bank database in XML

Note that it includes elements for each type of entity in the database:

• account elements for the data found in the Account table

• customer elements for the data found in the Customer table.

It also uses nested child elements for some of the information about accounts (e.g., balance) and customers (e.g., name and address).

2. How are the primary keys of Customer and Account captured?

3. The XML version of the database does not include separate elements for the relationships in the Owns table. How are those relationships captured?

Task 2: write XQuery queries

For the rest of this lab, we’ll write queries for the XML version of the bank database discussed above.

1. Recall that XPath models an XML document as a tree in which the nodes represent elements and attributes, and that it allows us to access collections of nodes from the tree.

   Write an XPath expression to obtain the account numbers of all accounts with a balance that is greater than 400.

2. Write an XQuery FLWOR expression to obtain the same account numbers as the XPath expression from the previous problem.

3. Write an XQuery FLWOR expression to produce the join of the account and customer elements. The result of the query should consist of new elements of type cust_acct, each of which contains a nested customer element followed by an associated nested account element. Order the results by customer number.

4. Write an XQuery FLWOR expression to create new elements of type customer that include:

   • a name child element for the name of the customer
   • new child elements for each account that the customer owns. These new account elements should have as their value the location of the account’s branch, followed by a hyphen, followed by the account’s balance.

   For example:

   <customer>
     <name>Jose Delgado</name>
     <account>Burlington, MA - 7000</account>
     <account>Burlington, MA - 300</account>
   </customer>
   

5. Write an XQuery FLWOR expression to create new branch elements for each branch of the bank. These branch elements should have nested child elements for the location of the branch and the total balance of all of the accounts at that branch. For example:

   <branch>
     <location>Burlington, MA</location>
     <total_balance>8300</total_balance>
   </branch>
   

   Hint: There is an aggregate function called sum() that can be used to add up a set of numeric element or attribute values.

6. Write an XQuery FLWOR expression to create elements of type customer_summary that include, for each customer, child elements for the name of the customer, the address of the customer, and the number of accounts that the customer owns. For example:

   <customer_summary>
     <name>Jose Delgado</name>
     <address>Zero Longhorn Ave., Belmont, MA</address>
     <num_accounts>2</num_accounts>
   </customer_summary>
   

If time permits

If you have extra time, feel free to work on PS 2: Part II and ask the TAs for help.

Last updated on October 9, 2024.