Lab Assignment 2

Assignment 2 - LIS 558

Due Date: Mar 2, 2000. Please submit the following exercises on a 3.5" diskette. You MUST have a backup copy of your completed assignment (in case the submitted version is damaged). Compact the database prior to copying to diskette for submission.

ABSOLUTELY NO COLLABORATION WITH ANYONE. You are welcome (encouraged) to use any published materials as well as the Help feature of MS Access 97.

1. Upon graduation, you have decided to open a mail order bookstore, "Wealth of Knowledge". After creating an entity-relationship database model, you have determined that the following relational tables are required. Create the database, using appropriate data types. Be sure to use the Lookup Wizard to facilitate data entry into foreign key fields. Edit relationships, ensuring referential integrity. Use the caption property where necessary to create clear labels.

BOOKS (BookID, Title, AuthorFName, AuthorLName, PublicationYear, Price, Fiction?, CategoryID)

CUSTOMERS (CustomerID, FirstName, LastName, Address, City, Province, PostalCode, PhoneNum)

ORDERS (OrderID, CustomerID, OrderDate, Paid?)

ORDERDETAILS (OrderDetailsID, OderID, BookID, Quantity)

CATEGORIES (CategoryID, CategoryName) [this validation table exists only to simplify data entry]

2. Create the following forms and use at least four formatting features (e.g., colour, picture, lines, fonts, etc.) to make each one attractive and easy to use (adjust spacing, etc).

Customer Form

Categories Form

Books Form

Order form with Order Details subform (datasheet) and a Customer subform (columnar). Use tab controls for the subforms)

Add the following records using your forms:

You've decided to initially carry books in the following categories: Novel, Sports, History, Database and Cooking.

Your first shipment of books has arrived and contains:

Wilderness Tips, Margaret Atwood, 1984, $39.99, Novel, Fiction

The Robber Bride, Margaret Atwood, 1992, $29.99, Novel, Fiction

Database Processing, David Kroenke, 1999, $99.00, Database, Non-fiction

Larry's Party, Carol Shields, 1995, $9.99, Novel, Fiction

Civil War, Charles Plato, 1950, $12.00, History, Non-fiction

The Wars, Timothy Findley, 1989, $10.00, Novel, Fiction

Your first four customers are:

Fred Flintstone, 22 Quarry Lane, Bedrock, Ontario, N6C 2S3

Lisa Simpson, 188 Saxophone Lane, Springfield, Ontario, M8R 4J8

Betty Rubble, 12 Stone Road, Bedrock, Ontario, N6G 2W6

Dianne Haskett, 299 Church Street, London, Ontario, N8Y 4R3

The first few orders are as follows:

On Nov 3, 1999, Lisa orders one copy of the Robber Bride and two copies of Civil War.

On Nov 15, 1999, Betty orders 30 copies of The Wars (she's a school teacher).

On Nov 20, 1999, Lisa calls back to order 3 more copies of Civil War, one copy of Wilderness Tips and one copy of Larry's Party.

On Jan 5, 2000, Fred orders a copy of Wilderness Tips.

3. Create the following queries. Name them with the letter prefix, followed by an appropriately descriptive name.

Which books do you carry that have the word "war" in the title?

Which books do you carry by authors other than Plato, Rob and Shields?

Which books written since 1990 have customers from Bedrock ordered?

Create a query that allows the user to input a subject each time the query is run so that the appropriate books are displayed.

How many books of each category do we carry within the $10 to $50 range?

How many books of each category has each customer ordered in November 1999?

What is the average price of fiction books by each author?

What would each book cost if you were to increase the price by 5%?

When were the orders for Civil Wars received?

Who has ordered history books?

4. Generate the following reports. All reports (as well as forms) should have a title.

Create a sales summary for November 1999. For each city grouping, show each book that was ordered, its price, the total number that were ordered and gross revenue from that book. Show the gross revenue for each city as well as a gross revenue for all cities. Show the current date and number the pages.

Create a professional-looking invoice for each order. Show the invoice number (OrderID), date of the order, customer's mailing information, the name, price and quantity of books ordered. Show the extended price (price multiplied by quantity) for each line. Include a subtotal as well as the total with taxes (7% GST) at the bottom. This report should generate one invoice for each order.

Create a bar graph that shows the number of books sold each month.

Create mailing labels for customers who live in Bedrock and Springfield.

LIS 558