The Web Server Environment - Database Processing: Fundamentals, Design, and Implementation - page 531

C. Add a new column Status to the INVOICE table. Assume that Status can have the
values ['Waiting', 'In-process', 'Finished', 'Pending'].

D. Create a view called CustomerInvoiceView that has the columns LastName, FirstName,
Phone, InvoiceNumber, DateIn, DateOut, Total, and Status.

E. Code a PHP page to display CustomerInvoiceView. Add a hyperlink to the MDC Web page
to access the page. Using your sample database, demonstrate that your page works.

F. Code two HTML/PHP pages to receive a date value AsOfDate and to display rows of
CustomerInvoiceView for orders having DateIn greater than or equal to AsOfDate. Add
a hyperlink to the MDC Web page to access the HTML page. Using your sample data-
base, demonstrate that your pages work.

G. Code two HTML/PHP pages to receive customer Phone, LastName, and FirstName
and to display rows for customers having that Phone, LastName, and FirstName. Using
your sample database, demonstrate that your pages work.

h. Write a stored procedure that receives values for InvoiceNumber and NewStatus and that
sets the value of Status to NewStatus for the row having the given value of InvoiceNumber.
Generate an error message if no row has the given value of InvoiceNumber. Using your
sample database, demonstrate that your stored procedure works.

I. Code two HTML/PHP pages to invoke the stored procedure created in part H. Add a
hyperlink to the MDC Web page to access the HTML page. Using your sample data-
base, demonstrate that your page works.

J. Create an XML Schema Document for a row of the CUSTOMER table. Use only simple
elements, and use Figure 11-52 as an example.

K. Create an XML Schema document for a join of CUSTOMER and INVOICE data.
Assume that the document has one customer and from zero to many orders for that
customer. Use Figure 11-58 as an example.

L. Write an SQL statement with FOR XML that will produce the document you created in
part K.

M. Create an XML Schema document that has all of the data for a given customer. How
many multivalued paths does this schema have?

N. Explain how the XML Schema document you created in part M can be used to advan-
tage by Marcia's Dry Cleaning.

If you have not already done so, answer the questions for The Queen Anne Curiosity
Shop (QACS) at the end of Chapter 7 (pages 353-359), and for the DBMS you are using
as described in:

Chapter 10A for Microsoft SQL Server 2012 ●

Chapter 10B for Oracle Database 11 g Release 2 ●

Chapter 10C Oracle MySQL 5.6 ●

A. Add a new folder to the DBP Web site named QACS. Create a Web page for The Queen
Anne Curiosity Shop in this folder, using the file name index.html. Link this page to the
DBP Web page.

B. Create an appropriate ODBC data source for your database.

C. Code a PHP page to display the data in the ITEM table. Using your sample database,
demonstrate that your page works.

D. Create a view called CustomerPurchasesView that has the columns CustomerID,
LastName, FirstName, SaleID, SaleDate, SaleItemID, ItemID, ItemDescription, and
ItemPrice.