Create a MS Access database using the attached normalized tables, forms, validation rules, queries, reports, controls. Please read carefully the instructions attached.
Normalized Tables
| Table Name | Primary Key | Foreign Key | Other Attributes |
| PurchaseOrder | PONo | PreparedEmployeeNo, AuthorizedEmployeeNo, VendorNo | PODate |
| ReceiveOrder | ReceiptNo | EmployeeNo, VendorNo | ReceiptDate |
| MakePayment | PaymentNo | PreparedEmployeeNo, AuthorizedEmployeeNo, VendorNo, AccountNo | PaymentDate, PaymentAmount, |
| FlowerInventory | ItemNo | Description, QtyOnHand, ListPrice | |
| Cash | AccountNo | AccountType, Bank, AccountBalance | |
| Vendor | VendorNo | VendorName, VendorAddress, Balance | |
| Employee | EmployeeNo | EmployeeName, EmployeeAddress, DateStarted, JobTitle, Phone | |
| FI_PO | PONo, ItemNo | QtyOrdered, OrderPrice | |
| FI_RO | ItemNo, ReceiptNo | ItemCondition | |
| PO_RO | PONo, ReceiptNo | QtyReceived | |
| RO_MP | ReceiptNo, PaymentNo |
VALIDATION RULES AND VALIDATION TEXTS
| Table Name | Validation rule | Corresponding Validation Text | Why the role is implemented |
| FlowerInventory | >=50 And <=200
>=0 | Quantity on hand (i.e., QtyOnHand) should be between 50 and 200
Price cannot be a negative number | The QtyOnHand should never be less than the Reorder Point which we decided to be less than 50 or more than 200 flowers to keep the inventory under control - we don't want the flowers to spoil.
To ensure that the list price is a positive number since we cannot hold less than 0 of a product. |
| Cash | >=0 | The account balance cannot be negative number | To ensure that the bank account balance cannot go below 0 (Sign test which is to use appropriate arithmetic signs) |
| Employee | <=Now()
| The start date must be today’s date or earlier Validity test
Input Mask The employee phone number must be entered in a specific format | To ensure that a future date will not be entered since employees are entered into the system upon hiring, not prior and to reduce the potential of errors.
Specific format for the phone has 10 digits and must be entered in this format to be valid to ensure that incomplete or incorrect data is not unintentionally entered. |
| MakePayment | >0 | Payment amount must be greater than 0 | To make sure that the payment being sent is greater than zero to avoid blank checks being sent or loss of confidence in us by our vendors.
|
| PurchaseOrder | <="ReceiptDate" | Purchase date must be earlier or equal to receipt date | To ensure that the order was purchased before the delivery date. Ensures that dates are entered correctly and that flowers are not delivered prior to order being processed. |
| ReceiveOrder | <="PurchaseDate" | Receipt date must be greater than or equal to purchase date | To ensure that there's a purchase order earlier or equal to the receipt date because a purchase order must exist earlier to the date of the receipt of goods. This ensures proper protocols are followed. |
| Vendor | >0 | Vendor balance must be greater than 0 to pay. If there is a negative balance deduct from payment amount | To ensure that vendors are not overpaid; To ensure there is in fact a balance to pay that is greater than zero. Reduces accounting errors and reduces mismanagement of funds. |
| FL_PO | >0 | Order price must be greater than 0 | The order price must be greater than zero to ensure that all orders have valid amounts and that the product is not unintentionally being delivered without intention to receive funds. |
| FL_RO | "Excellent" Or "Good" Or "Bad" | Can only enter excellent, good or bad | Simple validity test confirms if input value is acceptable. Confirms if input value is acceptable. Easier to categorize and manage quality of orders and to track trends or issues when applicable. |
| PO_RO | >0 | Quantity received must be greater than 0 | To ensure that inventory is being received and properly accounted for. |
| RO_MP |
|
| This table only has primary keys. |
QUERIES
| Query Name | Table Used | Why the query is important for managers/decision makers. |
| Preparer or Authorizer | Employee | This query shows the names of employees who are permitted to prepare purchase orders and authorize the purchase orders. This query is important to see what roles/whos authorized for what type of tasks |
| Vendor Balance | Vendor | This query generates a report that shows each vendor’s total current balances in descending order. This query is important so that we can view with whom we have the largest balance (which vendor we owe the most money to.) This may influence which vendor we may want to pay first. |
| Inventory | FlowerInventory | This query generates a report that shows inventory items that are getting low and will need to be reordered soon . This is important to make sure that the inventory is not lower than 50, this checks which inventories are getting lower we’re getting closer to reorder point |
| February Purchases | Employee, PurchaseOrder, FL_PO | This query shows the details of purchase orders made in February 2020 and names of employees that prepared these purchase orders. This query is important for decision makers because they can run it to view the quantity and description of items ordered by employees in a specific period which can be helpful in viewing which flowers are more in demand. |
| Roses Purchases | Employee, PurchaseOrder, FL_PO, FlowerInventory | This query generates a report that shows the number of inventory items (roses) purchased in Feb 2021 and the total of order price. It verifies the number of roses purchased in Feb 2021. It’s helpful to know how many roses were purchased and to easily compare the purchase of roses during other seasons - particularly in months where roses are more difficult to acquire. |
| Vendor Payments | Vendor, Make Payment | This query generates a report to confirm whether vendors' balances were paid in full or not. This assists management in managing relationships with vendors, managing expenses, and balancing accounts (which accounts to pay first) when necessary. |
| Average Orders | Employee, PurchaseOrder, FlowerInventory, FL_PO | This query generates a report that shows the average number of orders placed by each employee during the year of 2021. This enables management to determine which types of flowers generate the most income, which employees are adept at selling which flowers, and assists in catching long term trends in sales. |
FORMS
| Form Name | Function | Who would use | Purpose |
| Purchase Order Form | To order inventory | Employee who prepares purchase orders | To record business transaction between the buyer and the seller (vendor) |
| Employee Entry Form | To enter employee’s data information | The manager/CEO of the company | This can be used when the employee is hired. To keep record of employees being employed by the company |
| Vendor Entry Form | To enter vendor data | Employee who prepares purchase orders | To keep vendors records |
|
|
|
|
|
|
|
|
|
|
REPORTS
| Report Name | What benefits the report provide | How often the report will be used. |
| Top Selling Employees | This report shows who is selling the most items per order giving management information on the performance level of each individual employee. | Monthly to determine who gets more hours, bonuses, raises etc. |
| Purchase Order Detail by Vendor | This report shows the purchase order details for the year listed by the vendor. It includes details such as the vendors balance, PO numbers placed and the detail for each PO placed by the vendor. | This report can be used to see what items your vendors are ordering and how often. This can be used to gain an understanding of which items are the most popular amongst your vendors. |
CONTROLS
| Control | Description |
| General Controls | Separation of duties - The preparer of purchase orders is different than who authorizes the purchase order to ensure separation of responsibilities (preventive control) |
| Input Controls | Range Control on FlowerInventory Table: Validation rule set to ensure that the values are always within our set limits (>=50 And <=200) Sign Test on FlowerInventory, Cash, Make Payment, Vendor, FL_PO, and PO_RO Tables: Set to ensure that the values are always greater than or equal to zero. Validity Test on Employee Table and FL_RO: Set to ensure that the date entered matches one of the acceptable values (which is that the start date should be today’s date or earlier.); set to ensure that the flower descriptions entered on FL_RO are only from the three acceptable values (Bad, Good and Excellent.) Field Test on Employee Table: Set to ensure that the phone number entered is 10-digits and in a specific format Valid Combination on PurchaseOrder and ReceiveOrder: Set to ensure that purchase date is earlier or equal to receipt date and receipt date is greater than equal to the date of the purchase. |
| Entity integrity control | Our primary keys: PONo; ReceiptNo; PaymentNo; ItemNo; AccountNo; VendorNo; EmployeeNo; PONo, ItemNo; ItemNo, ReceiptNo; PONo, ReceiptNo; ReceiptNo, PaymentNo are all unique and not null. |
| Referential integrity | EmployeeNo(preparer and authorizer) in PURCHASE-ORDER should exist in EMPLOYEE VendorNo in PURCHASE-ORDER should exist in VENDOR EmployeeNo in RECEIVEORDER should exist in EMPLOYEE VendorNo in RECEIVEORDER should exist in VENDOR EmployeeNo(preparer and authorizer) in MAKEPAYMENT should exist in EMPLOYEE VendorNo in MAKEPAYMENT should exist in VENDOR AccountNo in MAKEPAYMENT should exist in CASH |
| Queries | Vendor Payments Query: Verifies that the sum of the payments made to vendors equals the vendor balance |
