AFM241 Study Guide - Final Guide: Microsoft Access, Conceptual Schema, Entity–Relationship Model

65 views2 pages

lilacmouse857

30 May 2018

School

University of Waterloo

Department

Accounting & Financial Management

Course

AFM241

Professor

Will Xiang

For unlimited access to Study Guides, a Grade+ subscription is required.

o This is not important - what matters is that intersection table must be created for

the proper implementation of a database

• Cardinality refers to the maximum and minimum number of occurrences that must be

included in a relation

o When we establish cardinalities, the direction of the association matters

o For example in a database that captures the relation between sales people

and customers, we have the following cardinalities:

1. Start looking at the relation as moving from the entity customer to the

entity salesperson in order to establish the cardinality of the entity

salesperson

a. ‘Start’ means to select customer record and try to trace it back to

salesperson

b. From business process standpoint, customer is assigned at least to

one salesperson and for a lot of companies at maximum to one sales

person – therefore the minimum cardinality for the salesperson entity

is 1 i.e. there is at least one salesperson that can be associated to a

customer and maximum cardinality for the salesperson entity is 1 i.e.

there is at most one salesperson that can be associated to a

customer

2. Start looking at the relation as moving from the entity salesperson to

entity customer in order to establish cardinality of the entity customer

a. We select the record of salesperson and try to trace back to one or

more customers

b. Business process standpoint and from a logical standpoint,

salesperson may not yet have been successful at placing an order

with a customer but if we happen to pick a seasoned s/he may have

hundreds of customers

c. Minimum cardinality for the customer entity is 0 i.e. the salesperson

does not have any customers yet and maximum cardinality for the

salesperson entity is many i.e. there are many customers that can be

traced back to this particular person

• Relations can be also classified as optional or mandatory and the entities themselves

that are related can be classified as weak or strong

• An optional relations exists when an entity on one side of a relation can be represented

with a corresponding occurrence but it is not required to

o E.g. a product from the product table can correspond to an entity in the order line

table but it does not need to as there are products that might never be sold

 Relationship is optional

• A mandatory relation occurs when occurrences on both sides of a relation must exist

o Example: relation between tOrder and tOrderline is mandatory because an

invoice must have line items for it to exist

• A weak entity generally meets two criteria:

1. Its existence is dependent on another entity – e.g. a line item cannot exist without

corresponding invoice

2. Its primary key is at least partially derived from the related entity’s primary key – e.g.

primary key of tOrderLine is a combination primary key and order number (primary

key of order table) is part of the combination

• If an entity meets both criteria, it is classified as a weak entity – else it is a strong entity

• A recursive relation occurs when an entity is related to itself

o Such an intra-entity relation we have seen in the employee table

o In this table, we have list of employees and reporting structure

o This allows us to see who is supervising who

5.3.5 Entity Relationship Diagram

Unlock document

This preview shows half of the first page of the document.
Unlock all 2 pages and 3 million more documents.

Already have an account? Log in

Document Summary

Its existence is dependent on another entity e. g. a line item cannot exist without corresponding invoice. Its primary key is at least partially derived from the related entity"s primary key e. g. primary key of torderline is a combination primary key and order number (primary key of order table) is part of the combination. In this table, we have list of employees and reporting structure. Sales module: name the entities, name the relations, relational schema: shows an erd entity"s relations by connecting foreign keys with their. Identify the maximum and minimum cardinalities respective primary keys: most prominent example of a relational schema is in the relationships view in microsoft. Order is optional to vendor: vendors are not limited in the number of products they supply, but this relation is not practical to implement, so the ord_line table is created to act as a bridge.

Related Questions

There are three ways to place an order at Robatelli's: in-store,via telephone, or online. The order processing systems areillustrated in

Here's how they work:

Robatelli's Order Processing Systems In-Store Orders

In-store orders are taken by restaurant staff serving patronsdining in one of Robatelli's restaurants or walking in to place anorder. Servers manually complete an order ticket at the tablesideand input the information into the company's point of sales systemthrough computer terminals located in the food preparationstations. Walk-in orders are typically entered directly into thesystem by staff using the computer terminal located at thecounter.

Telephone Orders

Approximately two-thirds of the company's business is processedvia thetelephone. Robatelli's phone ordering system is called aone-number system, referring to the convenience of ordering througha single phone number, regardless of the restaurant locationnearest the customer. Most phone orders are received by anoperator, who enters the order directly intoa computer terminalwhile speaking with the customer. Customer phone numbers are usedto present the customer with a choice of the nearest pizzeria forpreparation and carry-out or delivery. The software in the systemreferences a street database to verify that the street addressexists. Credit card numbers are obtained from customers paying bycredit card. The customer service representative verifies the orderand credit card number before ending the call. Upon confirmation ofan order, the order is sent directly to the restaurant, whereit isprocessed through the company's transaction processing system andprinted at the appropriate food preparation station(s). Credit cardnumbers are includedin the transmission, and all credit cardtransactions are processed at the restaurants at the time the orderis received.There are several advantages of handling phone ordersthrough a customerorder center rather than at individual restaurantlocations. Above all, the reduction in background noise improvesthe accuracy of the order-taking process. In addition, thecustomer's wait time is greatly reduced. Before Robatelli'simplemented its one-number system, customers' wait time at peakcould be up to 15 minutes. The order center's average wait timegoal is now one minute or less.

Internet Orders

To place an online order, a customer must be registered.Registration is a simple process that can occur anytime duringbusiness hours. Customers are asked to provide identifyinginformation that will be retained in the system. Repeat customerswill not have to go through the registration process again.Web-based ordering is tied to the company's one-number system. Whena customer enters an order online, customer information is pulledfrom the one-number system. Identifying data such as phone numberand address do not have to be entered. Menu offerings are presentedon the screen in various drop-down boxes so that any combination ofitems can be ordered with many different nchoices of toppings oraccompaniments. A customer must answer a series of questionsregarding the order, similar to the questions that would be askedif the customer had been speaking with a telephone operator. Onlineorders also require confirmation of the menu items and restaurantlocation before the call is ended. Credit card information may beentered online, and the transaction will be processed at therestaurant filling the order (as is done for telephone orders). Twofirewalls protect the security of customer information submittedonline.

Describe how each of the following types of ordersat

Robatelli's differs from the processesâ

a. In-store orders

b.Telephone orders

c.Internet orders

1) support or critique the idea that the Committee of Sponsoring Organization of the Treadway Commission (COSO) transition would strengthen internal controls over financial reporting in publicly traded companies

2) From the case study, determine the fundamental control deficiencies associated with the credit function. Next, propose at least one (1) control improvement that the credit department management could make in the process. Provide a rationale to support your response.

You have been assigned to review the internal controls of the credit department of a recently acquired subsidiary. The subsidiary imports several lines of personal computers and sells them to retail stores throughout the country. The credit department consists of a new credit manager, a clerk, and a part time secretary.

Sales are made by fifteen sales representatives: five are at company headquarters and handle large accounts with retail chains and ten are located throughout the country. Sales represesntatives visit current and prospective customers and, if a sale is made, either submit the order electronically or prepare a customer order from consisting of the original and three copies. One copy is retained by the customer, one by the sales representative, and one is sent to the warehouse; the original is sent to headquarters.

For new customers with orders of more than $100,000, a credit application is also completed and sent along with the order to headquarters. The credit application includes a bank reference and three credit references along with financial statements.

The sales order sent to headquarters goes first tot the credit department for approval. The credit department looks up the customer's credit on a computerized file that is maintained for customers with "good credit." If the customer is in the file, the clerk examines a monthly report listing all accounts that have not been paid in 60 days. If the customer is not listed in the report, the clerk initials the order as approved and sends it to accounting for recording and billing. The credit manager holds orders from new customers or from customers listed on the 60-day report for review. For transactions submitted electronically, the computer is programmed to identify all accounts that are over 60 days old and all sales that are over $100,000 to be sent to the credit manager for review.

For orders of more than $100,000 from new customers, the credit manager reviews the credit application along with the financial statements and calls at least one of the credit references. If the order is approved, the manager initials it and gives it to the secretary, who enters the approved data into the database.

If the order is denied, the manager adds the customer's name to a list of past rejected credit applications and canceled accounts. For new customers placing orders for less than the $100,000 limit, there is no further reveiw beyond the review of previously rejected credit applicants. If orders are not approved, the credit manager calls the warehouse to stop shipment. The order is marked "Credit Not Approved" and given to the secretary, who notifies the sales representative and the customer. The order and the credit application are then thrown away.

Once each quarter, the credit manager requests that the accounting department provide a list of all accounts more than 90 days old with supporting detail of account activitiy for the past 12 months. The credit manager reviews the information and determines whether action should be taken. Actions consists of the following:

1) The manager calls the sales representative and asks him or her to contact the client about payment

2) If payment is not made in three weeks, the credit manager calls the customer and requests payment. The customer is also put on the "no credit" list in the company's database.

3) If payment id not made within four additional weeks, the account is turned over to a collection agnecy.

When an account has been with a collection agency for two months without receiving payment, it is written off. The Credit manager prepares the necessary adjustitng entries.

ultramarinehippopotamus658

(ACCOUNTING INFORMATION SYSTEM)

Business scenario:

The model enterprise in this study is Euphoria Event Management Company (EEMC). As one of the major players in the event management industry, it provides a one-stop wedding service, organizes birthday parties, manages various kinds of celebrations and thus offers catering services based on the requirements of the customers. Due to this, the company handles multiple food inventory sales and purchases transactions and often slipped off the appropriate tracking and consumption of costs. Moreover, since it is classified as SME (small to medium-sized enterprise), it often fails to appreciate the importance of internal control in its daily operations.

The revenue cycle system at EEMC

The steps and procedures implemented within the revenue cycle of the company can generally be explained as follows:

1) Sales executive receives customer order by phone, fax or from a meeting.

2) She forwards the customer order to the kitchen department with details on quantities of food inventory needed and the date of delivery to customer.

3) The kitchen supervisor issues a Sales Order or sometimes a Packing Slip for the food order. Using this document, EEMC delivers the goods to customer.

4) Upon receiving the food, customer stamps and signs the Sales Order/Packing Slip.

5) One month after the delivery date, the accounts clerk at EEMC bills the food with Sales Invoice (stamp-duty receipt with authorized signature). The customer takes the original invoice while the company gets the copy of invoice and stamped receipt generated by the customer.

6) Two month after the delivery date, the accounts clerk makes a courtesy call to customer on the payment due.

7) Customer makes payment either using a cheque/clearing deposit slip or via a bank transfer.

8) As evidence of payment, the accounts clerk requests the original payment document (receipts or cheque stub) from

the customer or just check the transaction from the bank statements.

9) The accounts clerk records the sales transaction and the amount of sales in the general ledger. She also keeps the revenue cycle related documents as proof of the transaction.

The expenditure cycle system at EEMC

The steps and procedures implemented within the revenue cycle of the company can generally be explained as follows:

1) EEMC sets the budget for inventory purchase.

2) The Bill of Materials/Purchase Requisition document is created.

3) The procurement officer invites interested suppliers to quote their inventory price.

4) The procurement officer compares the quotation (price) of inventory among the various suppliers and appoints the

supplier who offers lowest price that commensurate with the desired quality of food. Appointment is usually conveyed by phone or fax.

5) When the food inventory arrives at the warehouse, the accounts clerk checks the quantity and the quality of inventory to confirm the order with the Bill of Materials.

6) Next, the supplierâs invoice (no Packing Slip from supplier, so the inventory is delivered with the invoice) is signed and the copy is taken and kept at the accounts department.

7) Between one to two weeks after the receipt of inventory, the accounts clerk confirms and pays for the purchases via a bank transfer. EEMC keeps the original receipt.

8) The accounts clerk records the purchase transaction and the amounts of obligation paid in the general ledger. She also keeps the expenditure cycle related documents as proof of the transaction.

Instructions:

1. Evaluate and comment on the internal control system related to the revenue cycle and expenditure cycle of the company. For each of the cycle, your answers must relate to the organizational control, documentation control and management practice control.

2. Using your own knowledge and creativity, suggest at least 3 ways to strengthen the documentation process and procedures using only the limited information given above.

3. Provide specific recommendations on how to overcome the discovered potential weaknesses on the organizational structure and authority of the company.

plumfish833

AFM241 Study Guide - Final Guide: Microsoft Access, Conceptual Schema, Entity–Relationship Model

Document Summary

Get access

Related Documents

AFM241 Chapter Notes - Chapter 5: Relational Database Management System, Database Theory, Unique Key

AFM241 Chapter Notes - Chapter 5: Database, Database Theory, Data Warehouse

AFM241 Study Guide - Final Guide: Accounts Receivable, Profit Margin, Database

Related Questions