Read the case study given below:

The Import Food Market (IFM) in Austin, Texas, sells food from Asian countries such as China, Thailand, and Korea. The owners of the IFM are Kim and Joe Lee. Because you are a good friend of their son, they have asked you to design and create a database that allows them to keep track of their customers more efficiently.

The Lees explain that all customers are very valuable to them and that they pride themselves on customer service. For example, customers who call in orders have the groceries delivered directly to their homes for no additional charge. Timely delivery is so important to the Lees that they record the time each order was taken and when it was delivered. Kim and Joe want a good way to keep track of customers, where they live, and how to get in touch with them, either by phone or by e-mail. A typical customer order includes items such as sriracha, bamboo shoots, and rice. All money for grocery orders is collected upon delivery on a cash-only basis. Joe Lee tells you to ignore payment information for the database project.

You are currently taking a class on databases in which you have learned about their design and implementation with Microsoft Access. You plan to create a simple database for the Lees’ business using Access. Then, after a trial run, you plan to replace the database with a larger one that can be made available on the Internet and accessed by multiple users simultaneously.

For now, you need to concentrate on designing the database. You have already been told about the customer information that is needed. Kim explains that all products and their prices will also have to be tracked. Order information must be recorded as well. You will need to keep track of all order dates and the time each order was received and delivered.

After you create and populate the database tables, you must think about how the market can best use the information in them. First, you need to create a form that lets the Lees easily record all incoming orders. When the company grows, the form can be made available on the market’s Web site so customers can order quickly and easily.

The database should be able to answer several questions, so the next step is to create sample queries to show the Lees how the system works. For example, because customers often call and ask for recommendations about different sauces that the market sells, you can create a query that lists all the sauce products in the store’s inventory.

Another issue involves your friend, the Lees’ son, who makes all late-night deliveries. The Lees often wonder whether the market has enough late-night orders to justify paying their son for a full night’s work. You will create a query that lists all deliveries after 11 p.m. on a specified day to support your friend’s argument that he deserves a full night’s pay.

Another useful query would list the products that are most popular on a specified day. Also, the Lees say that customers often call and ask for the least expensive products, so you want to create a query to list all products that cost less than a specified price. In addition, the market often offers discounted prices at certain times of the year, so you will set up a query that allows the Lees to decrease prices for products in their inventory.

Finally, you need to create an attractive sales report that lists all customers, what they ordered, and how much money they owe for a specified day’s orders. This information will be important for tracking revenue and tax records.

Create database tables in Access and populate them with data.

• Create at least nine customer records and add yourself as the tenth customer.
• Create 10 different types of products to sell.
• Create at least 10 orders on a single day. Make sure that most customers order more than one product and that they order multiple quantities of some products.

Answer rating: 100% (QA)

The Import Food Market IFM Database Design Tables and Relationships Customers ID ... View the full answer