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, the United States, and the 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, Mr$.$ Tipple 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$.$ Tipple$$s 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.

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$.$ Tipple$$s requirements, company documents, and employee work habits and requirements. 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. Data Requirements This section describes which databases are required and what information must be stored in each. The High Spirits Imports Management System consists of the following two databases: $$ HS$\_$Employee database $$ HighSpirits database Currently, the HS$\_$Employee database is a Microsoft Excel spreadsheet that stores employee information including loginIDs and passwords. There are no tables defined within this database. For future scalability and access from different regions possibly by the Internet, the systems analyst suggests that the HighSpirits application use MySQL or SQL Server as the backend database instead of Microsoft Access. The HighSpirits database will be used for storing information about products, customers, and billing. Eventually, if Mr$.$ Tipple opens new branches, the database will need to accommodate locations. The systems analyst suggests that, for additional security, the employee data in the existing Excel spreadsheet be moved to its own SQL Server database. He has provided you with a copy of the Excel spreadsheet $($HS$\_$Employees.xls$),$ which is available on your project work disk. Both the HighSpirits database and the HS employees database can, for now, exist on one server. Data Rules Mr$.$ Tipple wants you to include the following business rules in the HighSpirits database: $$ Product types are beer $(1),$ liqueur $(2),$ liquor $(3)$ and wine $(4).$ The most popular Product Type is wine. $$ The most popular Size is $750$ml$.$ Common sizes are $375$ml $(1),750$ml $(2),$ and $1$ litre $(3),$ and $12-$case $(4).$ Transaction numbers start at $100$ and increment by $5.$ Transaction types are purchase $(1)$ and sale $(2).$ Most Transactions are sales. $$ Payment due dates are $30$ days from transaction date. $$ The minimum transaction quantity is $5.$ Inventory levels are kept to at least $50$ of each product. $$ Countries are identified by IDs composed of a single digit integer Australia $(1)$ Chile $(2),$ UK $(3)$ and US $(4).$ The systems analyst has already begun analyzing the data requirements of this database.The four main tables include: $$ Customers $$ Products $$ Transactions $$ Transaction Details The database includes various lookup tables: $$ Country $$ Size $$ Product Types $$ Transaction Types The following information needs to be recorded about Customers: $$ Company Name $$ Address $$ City $$ Province $$ Postal