1. Many companies try to manage their business processing using Excel spreadsheets and file sharing systems. This may be sufficient when the company is small, but as companies grow these methods exhibit some major shortcomings as information management tools. What are some of these deficiencies and how do they affect the business?
Some of the major deficiencies are Data storage, Integration, and Validation. If your business is small and just starting, you can use Excel spreadsheets to make lists of your customers and add few details like one address and telephone number. Once your business starts growing, you may need to store more details about your customers like shipping and mailing addresses. Telephone and fax numbers and more information about contact person and their contact number. In this case, Excel may not be able to fulfill your need and you have to look for better alternatives. For the growing small business, Access can fulfill their needs as it allows customer to control and organize these data by creating multiple tables. Also in Excel, finding just a row or column of data that you need from multiple tables is troublesome.
2. Download the Stoves.accdb Access database in Doc Sharing; it has the following tables filled with data.
CUSTOMER (CustomerSK, Name, Phone, EmailAddress)
STOVE (SerialNumber, Type, Version, DateofManufacture)
REGISTRATION (CustomerSK, SerialNumber, Date)
STOVE_REPAIR (RepairInvoiceNumber, SerialNumber, Date, Description, Cost, CustomerSK)
Given these tables, write the SQL code to accomplish the following requests.
1. Show all the data in each of the four tables.
2. List the names and e-mail addresses of all customers who have had a stove repair that cost more than $50.
3. List all versions of stoves of type "Maytag" that have no record of being repaired.
You can view the Microsoft tutorial, Create Queries for a New Database, found in the lecture to learn how to test your queries in Stoves.accdb. Don't...