Homework 6

Due: December 3, 2019 by Dr. Fenner

To Submit

Written

Required
Graduate Students Only

NOTE: Exercise 6.6.1 is not a MySQL exercise. Use the flavor of SQL given in the book and submit your hand-written answer with the others at or before the beginning of class or else electronically before midnight. Each of your answers should be one or more SQL statements surrounded by high-level pseudocode. Be sure to group your SQL statements and queries into transactions as appropriate, as if there were others who may be querying/updating the database concurrently.

Reference

Exercise 5.3.1

Write each of Exercise 2.4.1 (reproduced below) in Datalog. You should use only safe rules, but you may wish to use several IDB predicates corresponding to subexpressions of complicated relational-algebra expressions.

Schema:

Product(maker, model, type)
PC(model, speed, ram, hd, price)
Laptop(model, speed, ram, hd, screen, price)
Printer(model, color, type, price)

Queries:

  1. What PC models have a speed of at least 3.00?
  2. Which manufacturers make laptops with a hard disk of at least 100GB?
  3. Find the model number and price of all products (of any type) made by manufacturer B.
  4. Find the model numbers of all color laser printers?
  5. Find those manufacturers that sell Laptops, but not PC’s.

Exercise 6.6.1

This exercise involves certain programs that operate on the two relations

Product(maker, model, type)
PC(model, speed, ram, hd, price)

from our running PC exercise. Sketch the following programs, including SQL statements and work done in a conventional language. Do not forget to issue BEGIN TRANSACTION, COMMIT, and ROLLBACK statements at the proper times and to tell the system your transactions are read-only if they are.

  1. Given a speed and amount of RAM (as arguments of the function), look up the PC’s with that speed and RAM, printing the model number and price of each.
  2. Given a model number, delete the tuple for that model from both PC and Product.
  3. Given a model number, decrease the price of that model PC by $100.
  4. Given a maker, model number, processor speed, RAM size, hard-disk size, and price, check that there is no product with that model. If there is such a model, print an error message for the user. If no such model existed in the database, enter the information about that model into the PC and Product tables.

Exercise 11.1.2

Suggest how typical data about banks and customers, as in Exercise 4.1.1, could be represented in the semistructured model.

Exercise 11.2.2

Show that any relation can be represented by an XML document. Hint: Create an element for each tuple with a subelement for each component of that tuple.

Exercise 11.3.1

Add to the document of Fig. 11.10 the following facts:

  1. Carrie Fisher and Mark Hamill also starred in The Empire Strikes Back (1980) and Return of the Jedi (1983)
  2. Harrison Ford also starred in Star Wars, in the two movies mentioned in (a), and the movie Firewall (2006)

Exercise 11.3.2

Suggest how typical data about banks and customers, as was described in Exercise 4.1.1, could be represented as a DTD.

Exercise 12.1.2

The document of Fig. 12.6 contains data similar to that used in our running battleships exercise. In this document, data about ships is nested within their class element, and information about battles appears inside each ship element. Write the following queries in XPath. What is the result of each?

  1. Find all the Class elements for classes with a displacement larger than 35000.
  2. Find the names of the ships that were sunk.

Exercise 12.2.1

Using the product data from Figs. 12.4 and 12.5, write the following in XQuery.

  1. Find the Printer elements with a price less than 100, and produce the sequence of these elements surrounded by a tag <CheapPrinters>.

Figures

Figure 11.10

Adding stars-in information to our XML document

Figure 12.4

XML document with product data — beginning

Figure 12.5

XML document with product data — end

Figure 12.6

XML document containing battleship data