Ms. Marcia Wilson owns and operates Marcia’s Dry Cleaning, which is an upscale dry clean...

Ms. Marcia Wilson owns and operates Marcia’s Dry Cleaning, which is an upscale dry cleaner in a well-to-do suburban neighborhood. Marcia makes her business stand out from the competition by providing superior customer service. She wants to keep track of each of her customers and their orders. Ultimately, she wants to notify them that their clothes are ready via email.

Assume that Marcia has hired you as a database consultant to develop an operational database named MDC that has the following four tables:

CUSTOMER (CustomerID, FirstName, LastName, Phone, Email)

INVOICE (InvoiceNumber, CustomerID, DateIn, DateOut, Subtotal, Tax,

TotalAmount)

INVOICE_ITEM (InvoiceNumber, ItemNumber, ServiceID, Quantity, UnitPrice,

ExtendedPrice)

SERVICE (ServiceID, ServiceDescription, UnitPrice)

A Microsoft Access 2013 version of the MDC database and SQL scripts to create and populate the MDC database are available for Microsoft SQL Server 2013, Oracle Database Express Edition 11g Release 2, and MySQL 5.6 Community Server at the Database Concepts Web site at www.pearsonhighered.com/kroenke. Sample data for the CUSTOMER table are shown in Figure 7-32, for the SERVICE table in Figure 7-33, for the INVOICE table in Figure 7-34, and for the INVOICE_ITEM table in Figure 7-35.

A. Create a database in your DBMS named MDC, and use the MDC SQL scripts for your DBMS to create and populate the database tables. Create a user named MDC-User with the password MDC-User+password. Assign this user to database roles so that the user can read, insert, delete, and modify data.

B. If you haven’t completed exercise 7.50, do it now.

C. Add a new folder to the DBC Web site named MDC. Create a Web page for Marcia’s Dry Cleaning in this folder—using the file name index.html. Link this page. D. Create an appropriate ODBC data source for your database.

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

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

G. Code a PHP page to display CustomerInvoiceView. Using your sample database, demonstrate that your page works.

H. 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. Using your sample database, demonstrate that your pages work.

I. 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.