mysql code 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, which

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.

$$ Payments 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 ID$$s composed of a single digit integer

Australia $(1)$ Chile $(2),$ UK $(3)$ and US $(4)$

$2020$ Page $|5$

The systems analyst has already begun analyzing the data requirements of this

database.

The four main tables include:

$$ Customers

$$ Products

$$ Transactions

$$ TransactionDetails

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 Code

$$ Phone Number

The following information needs to be recorded about Products:

$$ Product number

$$ Name

$$ Type

$$ Size

$$ Country of origin

$$ Supplier name

$$ Quantity in stock

$$ Cost per unit

$2020$ Page $|6$

The following information needs to be recorded about Transactions:

$$ Transaction ID

$$Type of transaction

$$ Date of the transaction

$$ Customer or supplier

$$ Invoice number

$$ Payment due date

The following information needs to be recorded about TransactionDetails:

$$ TransactionID

$$ ProductID

$$ Quantity of product bought or sold

$$ Price of product bought or sold

The following information needs to be recorded about payments. A payment

is applied to a transaction rather than to a customer:

$$ Date

$$ Amount

$$ Invoice number

$$ Transaction ID

The following information needs to be recorded about audits:

$$ Date of change

$$ The Changed field

$$ The old value for each field

$$ The new value for each field

$$ Transaction ID

The Changed field is a true or false field. It should hold a $0$ if the field$$s data did

not change and a $1$ if it did. For example, if only the first payment date changed, a

PaymentDueDateChanged field requires a $1$ value but all other fields in the record

require a $0$ to indicate they did not change.

$2020$ Page $|7$

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 customer$$s 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$.$ Tipple$$s discretion.

$$ Unit cost price includes any duties or taxes. Cost is $91\%$ or