Unformatted Attachment Preview
Kim Flowers Procurement Cycle
The third stage of the project requires students to implement their logical data model in
MS-Access 2013. Read carefully the instructions below before you start working on your
project.
1. Create the tables from “project stage 2” in Microsoft Access. In each table, define
the primary key and foreign keys. For each attribute, define the data type, provide
a field description, and define the field properties. For each table, there should be
at least one validation rule and a corresponding validation text. I would also like
to see some default value entrees and captions in each table. Make sure that you
follow the naming notation (tbl rpt) as it is covered in the Access book.
2. Populate 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).
3. Establish relationships between all the tables you created (based on your design).
Make sure that you enforce the referential integrity in all your relationships.
4. Create some queries and explain why these queries are important for
managers/decision makers. You should have at least four multiple-table queries in
your design, and three other queries. At least one of your queries should perform
calculation using “Expressions” (as described in chapter 4 of the access book).
Your queries will be evaluated based on the above requirements.
5. Create at least three forms; one of which should be a multiple table form.
Describe the function of the form, who would use it and its purpose.
6. Create at least three reports for management/decision makers; describe what
benefits these reports provide for decision makers, and how often they will be
used. One of your reports should display a summary value, and a calculated field
using the expression builder (as described in chapter 5).
7. Document the existing internal control structure for your company (based on your
current design), and recommend additional desirable controls. ( Control examples
are provided in the “Sample Controls” table in in chapter 10)
8. Describe any additional (for extra credit) work that you did.
9. Summarize what benefits did your company gain from adopting the database
approach (for that purpose you can assume that prior to your implementation,
your company had a computerized/manual accounting system)
Extra credit will be assessed for any additional features that were not required. The
overall grade for the project will be given based on the compliance with the above
instruction, and the overall effort given to the design and implementation of the
database. The project should be submitted via email. The subject of your email should
follow the required convention.
1
2
Floral Inv (ItemNo, Description, QOH, ListPrice)
PurchaseOrders (PONo, Date, EmployeeNo* (preparer), EmployeeNo2* (Authorizer),
VendorNo*)
PurchaseOrderDetail (PONo*, ItemNo*, Qty, Price)
Employee (EmployeeNo EmployeeName, DateStarted, Address, Phone#, Category,
Grade Salary, commission)
The above table contains all the internal agent (i.e employees of the company)
•
APClerk
•
Kim
•
FloralDesigner
•
PurchaseAgent
Vendor (VendorNO, VendorName, Address, Phone#, Balance, )
CashAccounts (AccountNo, Bank, Type, DateStarted, Balance)
Floral Receipt ( ReceiptNo, Date, EmployeeNo* (receiver), VendorNo*)
ReceiptDetail/ReceiptsApplied – This table takes care of the M:M relationships between
Receipts and POs and Receipts and Items; because there is relationship between items
and POs, only one table is needed. If there had been no relationship between items and
POs, two separate tables would have been appropriate
ReceiptFlowersPO (ReceiptNo*, ItemNo*, PONo*, Qty, Condition)
Payment (VoucherNO, EmployeeNo* (preparer), EmployeeNo2* (Authorizer),
VendorNo *, AccountNo*,Date, Amount)
##CashPaymentsApplied (VoucherNo*, ReceiptNO*)
FlowerDisposal (DisposalNO, EmployeeNo* (authorizer) Date)
FlowerDisplosalDetail ( DisposalNo*, ItemNo*, QtyDisposed, Reason)
3
1
Running head: KIM’S FLOWER EXPENDITURE CYCLE
Kim’s Flower Expenditure Cycle
Name
Yasser bakraa
Saud alarifi
Mohammed alsumali
Tutor
Institution
Date
2
KIM’S FLOWER EXPENDITURE CYCLE
2NF
Inventory (Inventory#, Description, ListPrice, BeginningQOH)
(1 NF)(2NF)
Purchase Order (PurchaseOrder#, Date, Amount, Vendor#*, Employee#*)
(1 NF)(2NF)
Floral Receipt (Receipt#, Date, Amount, Vendor#*, Employee#*)
2 NF Employee (Employee#, EmployeeName, Phone#, DateStarted, Position)
2 NF Vendor (Vendor#, VendorName, Address, Phone#, AccountBalance)
2 NF Cash (Check#, AccountType, BalanceBeginningCurrentPeriod)
(1 NF)(2NF)
Cash Payments (Payment#, Date, Amount, Supplier#*, Employee#*, Check#*)
(1 NF)(2NF)
Inventory-Purchase Order (Inventory#*, PurchaseOrder#*, QuantityOrdered)
(1 NF)(2NF)
Inventory-Floral Receipts (Inventory#*, Receipt#*, QuantityReceived)
(1 NF)(2NF)
Purchase Order-Floral Receipts (PurchaseOrder#*, Receipt#*)
(1 NF)(2NF)
Floral Receipts-Cash Payments (Receipt#*, Payment#*,
AmountAppliedtoInvoice)
3
KIM’S FLOWER EXPENDITURE CYCLE
REA DIAGRAM OF KIM’S FLOWERS EXPENDITURE CYCLE
I.
IDENTIFY EVENTS
“The first stage is to classify all important events or transactions using the simple ‘give – to - get
the exchange.”
i.
Enter Purchase Order
ii.
Prints buying orders & send to vendors recorded as “Floral Receipts.”
Pay for end month amounts charged, call. “Cash Payments” (Cheggy Study, 2018).
II.
IDENTIFY AGENTS AND RESOURCES
“Then, we have to identify the properties involved with these events.” We determined that there
are more than one resources entangled with these events:
i.
Flowers
ii.
Cash
“Next, come up with the agents or parties needed to make these events occur.”
(Note: There are usually not less than two agents involved in each event)
The following are the agents included in the Purchase Order event:
i.
Buying Agent
ii.
Kim
iii.
Vendor
Floral Receipts event includes the following agents:
4
KIM’S FLOWER EXPENDITURE CYCLE
a.
Vendor
b.
Floral Designer
The following are the agents included in the Cash Payments events:
i.
Accounts Payable Clerk
ii.
Kim
iii.
Vendor
III. DEFINE AND SHOW CARDINALITIES OF RELATIONSHIPS
1.
Identify event cardinality – agent relationships.
2.
Identify event cardinality – resource relationships.
3.
Identify event cardinality – event relationships.
Step 1: Define the cardinalities of event agent relationships
When conversing with Kim, we comprehend that an acquiring specialist arranges the
blossoms from the sellers. One and just a single acquiring operator can submit a particular request
in the PC framework. In any case, they can arrange more than one kind of blossom with one seller
without a moment's delay. Kim is the person who supports these requests. Since they are not
difficult to track, one botanical fashioner records the receipt of blooms from a merchant on a
different screen in her bookkeeping framework. Just a single A/P Clerk makes the checks for
sellers, and just Kim approves them and sends them out. In this manner, the most extreme
cardinality on the operator side of the connections among occasions and inward specialists is
dependably 1 (point, 2018). A representative should dependably take part in each much whether it
be putting in a request, accepting it, or dispensing checks. In this manner, the base cardinality on
the specialist side of the connections among occasions and inside operators is one also (Cheggy
Study, 2018).
5
KIM’S FLOWER EXPENDITURE CYCLE
Buy orders, flower receipts, and money installments all include merchants. Every occasion
must be connected to a specific merchant: A buy arrange must recognize and be sent to a seller.
Every occasion must be connected to one merchant because each request is set with a particular
seller, every conveyance originates from a particular seller, and every installment is made to a
particular seller. In this way, the base and most extreme cardinalities on the specialist side of the
connections among occasions and outside operators is 1 (point, 2018).
In the PC framework, Kim realizes that while data about representatives and sellers can be
put away inconclusively, data about occasions are put away for a monetary cycle. Accordingly,
toward the start of the period, no inside or outer operators would be connected to any action.
Thus, the base cardinality on the occasion side of the event - agent relationship is 0. It is likewise
conceivable that an occasion can't happen all the while between all outer and inside specialists,
additionally making the cardinality 0. Be that as it may, operators may take an interest in an
occasion on different occasions. For instance, the acquiring specialist can put in various requests
in the PC and Kim can approve different requests. Sellers can likewise get numerous buy requests
and money installments. While the A/P Clerk can create numerous checks and Kim can sign and
convey various checks. Subsequently, the most extreme cardinality on the even side of the
occasion -agent relationship is many (Cheggy Study, 2018).
Stage 2: Identify cardinalities of occasion -resource relationships
For Kim's Flower Shop, each buying request and botanical receipt must include something
like one blossom stock thing. However, it can likewise have more than one stock thing.
Subsequently, on the stock side of the stock -event relationship, the base cardinality is 1, and the
most extreme cardinality is many (point, 2018).
6
KIM’S FLOWER EXPENDITURE CYCLE
Since stock data is put away inconclusively and occasion data is put away for the financial period,
there might be where no stock being requested or gotten. Be that as it may, during the year a
specific stock thing might be requested or gotten on numerous occasions. Subsequently, the base
cardinality is 0, and the greatest cardinality is numerous on the occasion side of the considerable
number of connections between the blossom stock and the occasions associated with it.
While there can be a wide range of general record money accounts that an installment
originate from, every particular installment should just originate from one particular record. In
this manner, Kim's organization will have a base and a most extreme cardinality of one on the
Cash side of the Cash--Cash Payment relationship. Since money installments is an occasion, the
data on it is held for one financial cycle. Be that as it may, one money record can have many
money installments from it. In this way on the Cash Payment side of the Cash - Cash Payment
relationship has a base cardinality of 0 and the greatest cardinality of many.
Stage 3: Identify cardinalities of event - event connections
In Kim's Flower Shop, arrange to happen before they are conveyed, and a few requests can
incorporate things that are out of stock. This way, a portion of the blooms may precede those that
are out of stock come. In this way on the Floral Receipt side of the Purchase (Cheggy Study,
2018) Order - Floral Receipt relationship, the base cardinality is 0, and the most extreme
cardinality is many. It is Kim’s business rehearse that everything on a botanical receipt might be
from the other buying arrangement. Be that as it may, each bloom stock got must originate from a
buy arrange. Accordingly, the base cardinality on the Purchase Order side of the Purchase Order Floral (Cheggy Study, 2018).
Receipt relationship is zero, and the most extreme cardinality is many. It is feasible for
Kim's organization to get stock and not pay for it immediately. Likewise, a separate receipt will
7
KIM’S FLOWER EXPENDITURE CYCLE
dependably be paid for utilizing a similar check. In this way in the event- resource connection
between Floral Receipts and Cash Payment, on the Cash Payment side, the base cardinality is 0,
and the greatest cardinality is 1. Kim's organization permits that each check may pay for a few
receipts. Each money installment must have a botanical receipt to run with it. Subsequently, on
the Floral Receipt t side of the event - event relationship, the base cardinality is 1, and the most
extreme cardinality is many (Cheggy Study, 2018).
8
KIM’S FLOWER EXPENDITURE CYCLE
Reference
Cheggy Study. (2018). REA Diagram. Retrieved from Cheggy Study:
https://www.chegg.com/homework-help/questions-and-answers/q-prepare-rea-diagrambackground-kim-s-flower-inc-operated-kim-burke-chief-executive-offic-q19771112
point, T. (2018). Database - First Normal Form (1NF). Retrieved from Tutorials point:
https://www.tutorialspoint.com/sql/first-normal-form.htm