You are a Database Administrator working for a small software solutions firm. You have been assigned to help develop a beverage imports management system. High Spirits is a small importing company that specializes in wines imported from Australia, Chile, United States and United Kingdom. The owner, Tim Tipple, has been considering expanding his inventory to include beer, liquor and liqueurs. Due to the increased complexity in record keeping, Tim wants to improve the Shipping/Receiving and Sales management system. Currently, each department completes its own paperwork and maintains its own records with its own software application. Departments communicate details by telephone or paper. Several costly mistakes have resulted in the loss of too many good customers. To improve customer service and avoid communication errors, High Spirits owner wants one system that provides employees with access to all administrative functions. The new application will provide an integrated interface, so Shipping/Receiving can manage inventory levels, and Sales Managers can easily invoice customers and obtain customer account information. Mr. Tipple wants access to all features of the new application. Naturally, built-in security features prevent unauthorized individuals from obtaining sensitive information. Mr. Tipples long term plans include company expansion into new markets. He would like to open new branches in the future. You are part of a team of IT professionals hired to implement a business solution for High Spirits administrative problem. Your team understands the importance of integrating current business practices, rules and documentation into any new system. General Requirements The High Spirits Imports Management System enables Shipping/Receiving and Sales Managers to effectively manage inventory and sales. The systems analyst on your team has produced a system chart that describes the proposed application based on interviews to discover Mr. Tipples requirements, company documents, and employee work habits and requirements. Figure 1: System flowchart for the High Spirits Imports Management System Sales Managers complete the initial paper-based sales contract, recording product particulars between the customer and High Spirits. They are responsible for inputting new customers and new sales into the system, and generating invoices. They maintain customer relationships, and deal with customer concerns or complaints. Shipper and receivers adjust inventory information, recording inventory particulars and quantities based on purchases and sales (transactions) of product. They are responsible for inputting new inventory into the system. Tim Tipple, the owner, can perform all functions including purchase new inventory. Database Usage The systems analyst has discovered the following facts about how the database will be used: Since the way the database is described is fairly normalized, joins will be frequently used in the HighSpirits application. Typically, a sales manager will search for a customer by phone number. They will want to know the customers first and last name initially, and may opt to do a search for other information later on. Searches in the Products table typically involve a Product number, and any or all of name, country, quantity, price and type. Most often the search involves Product number, quantity and price. Searches in the Transaction table typically involve a Transaction number, type, and date. Searches in the TransactionDetails table typically involve the Product or Transaction ID, price and quantity. The Country, Size, Product Types and Transaction Type tables are searched for each new transaction. Product stock information tends to change often. New customers and transactions are added continuously throughout the year. Retail prices are at least 110% of cost. Discounts or higher retail prices apply at Mr. Tipples discretion. Unit cost price includes any duties or taxes. Cost is 91% or less of retail price. Payment Procedures Payments entered into the database incorrectly cannot be deleted. Instead, they are voided. A voided payment has a payment value of $0.00. A reason should always be given for a voided payment. By voiding a payment instead of deleting it, HighSpirits ensures that it has an accurate record of all payments made, even if some of the entries were originally incorrect. Also, voiding payments prevents employee theft. Auditing System In order to keep track of amendments to transactions, Mr. Tipple would like an auditing system implemented. The auditing system should record whenever changes have been made to the Transactions table. Specifically, you should record when the changes were made, what fields were affected, and what their old and new values were. Based on the preliminary findings of the systems analyst, you will need to formalize the database design and use a DBMS (SQL Server, MySQL, etc) to build the database. You will NOT be required to build all the functionality as described above as part of this initial project. You will be responsible only for specific parts of the system and implementing specific features as described below. You should complete the following steps:

. 1. Using your DBMS, create an SQL script to create the High Spirits database. Name the database HSxxxxx,. Using your DBMS Help feature, explore the use of the SHOW command to display the contents of your database.