Group Project $$ Part III

Kim$$s Flower Inc. Purchasing $($Expenditure$)$ Cycle

Requirements:

The final part of the project requires your team to implement the logical data model in MS Access. Please

read carefully the instructions below before you start working on your project. You should submit your

database file related to the question $1$&$2$&$3$ and a separate word document with answers$/$explanations to the

question $4$ as requested below.

$1.$ Create tables in the MS Access file by using the provided solution of $$Group Project $-$ Part II with

Solution$($i$.$e$.,$ the normalized tables$).$

$$ In each table, define the primary key. Primary keys cannot be null.

$$ For each attribute, define the data type, provide a field description, and define the field

properties if necessary

$2.$ Establish relationships between all the tables you created. You may take a look at the video $$How to add

relationship between tables in Microsoft Access$$ on Canvas to add relationships between tables.

$$ Make sure that you enforce the referential integrity in all your relationships. Referential integrity

rule: Foreign key which link rows in one table to rows in another table must have values that

correspond to the value of a primary key in another table.

$3.$ Populate all your tables with fictitious data entries

$$ You should sufficiently populate your tables so that you can demonstrate your design $($about $20$

rows for events and $10$ for agents and resources$).$

$4.$ Create queries and explain why these queries are important for managers$/$decision makers.

$$ You should have at least three multiple table queries in your design.

$$ At least one of your queries should perform calculation. You can create a query using SQL

language or Query design.

$5.$ Document the existing internal control structure based on your current design.

$$ You may refer to some controls we discussed in Chapter $13.$ You need to specify at least TWO

threats and explain related controls your team can provide to mitigate each of them.

$$ For example, the threat to the database could be entering an invalid supplier when an employee

enters the purchase order. To control the threat, we can apply the validity check by comparing

the supplier ID which the employee enters with the purchase order with the supplier ID in the

supplier mater file to verify that the account exists. $($Please do not use this example in your

submission$)$

Deliverables:

Create the tables in $$TeamNumber$-$Part$3.$accdb.$$

Complete the document $($i$.$e$.,$ TeamNumber$-$Part$3.$docx$).$

Submit two files. Be sure to change the file name using your team number. For example, my MS Access file

would be $$Team$10-$Part$3.$accdb$$ and my MS Word file would be $$Team$10-$Part$3.$docx.$.$

Only one team member needs to submit these required files.

Normalized Tables

$\backslash$table$[[$Table Name,Primary Key,Foreign Key,Other Attributes$],[$OrderInventory$,$PurchaseOrderNo,$\backslash$table$[[$PreparedEmployeeNo$,],[$AuthorizedEmployeeNo$,],[$VendorNo$]],$PODate$],[$ReceiveOrder$,$ReceiptNo,$\backslash$table$[[$EmployeeNo$,$ VendorNo,$],[$PaymentNo$,],[$PurchaseOrderNo$]],$ReceiptDate$],[$MakePayment$,$PaymentNo,$\backslash$table$[[$PreparedEmployeeNo$,],[$AuthorizedEmployeeNo$,],[$VendorNo$,$ AccountNo,$]],$PaymentDate, PaymentAmount,$],[$FlowerInventory$,$ProductNo,,Description, QtyOnHand, ListPrice$],[$Cash$,$AccountNo,,AccountType, Bank, AccountBalance$],[$Vendor$,$VendorNo,,$\backslash$table$[[$VendorName$,$ VendorAddress,$],[$Balance$]]],[$Employee$,$EmployeeNo,,$\backslash$table$[[$EmployeeName$,$ EmployeeAddress,$],[$DateStarted$,$ JobTitle, Phone$]]],[\backslash$table$[[$FlowerInventory$-],[$OrderInventory$]],\backslash$table$[[$PurchaseOrderNo$,],[$ProductNo$]],,$QtyOrdered, OrderPrice$],[\backslash$table$[[$FlowerInventory$-],[$ReceiveOrder$]],\backslash$table$[[$ProductNo$,],[$ReceiptNo$]],,$ProductCondition$]]$

Note:

For OrderInventory table, we do not need to have ProductNo as a foreign key to link FlowerInventory table. Instead, FlowerInventory$-$OrderInvetory table will link the OrderInventory table and FlowerInventory table together.

For ReceiveOrder table, we do not need to have ProductNo as a foreign key to link FlowerInventory table. Instead, FlowerInventory$-$ReceiveOrder table will link the ReceiveOrder table and FlowerInventory table together.