Sys Fish

Sys Fish is a family-owned chain of seafood stores. They have expanded from their humble beginnings in New York, to a $30,000,000 business with stores in 44 cities throughout the United States. They originally specialized in live Maine lobster, but they now provide customers with over 50 types of fresh fish.

This expansion has progressed relatively smoothly, but Sy is getting older, and he does not want to spend 15 hours each day managing the businesses. Sy thinks that a computer system may make his work more efficient, and you have been hired to help the firm develop the project.

During the first few days you interviewed not only Sy, but also many of the employees who have years of experience and many good ideas. You have summarized the results of these conversations as follows:

Each store can carry all types of seafood, but they usually specialize in local favorites. These fish are purchased directly from local fishers and are then marked up outrageously. Because of Sys international reputation, customers are willing to pay almost anything for fresh fish with his name on it. All stores are very successful.

Customers (usually small restaurants) are allowed to buy on credit, and all pay on the last day of the month. It is rumored that Sy has family connections in Brooklyn, so no customer would consider returning fish or defaulting on a sale: no one wants to sleep with the fishes.

Normal business proceeds as follows. An employee decides approximately how much fish is needed each day to service his/her customers. They purchase that amount in the early morning, clean all of it, and deliver it by 3:00 p.m. This minimizes the on-hand inventory of fish from day to day. Each fisher supplies fish to only the local store; fish is never transported between locations. Employees always use company trucks to visit fishers and customers.

Sy handles all of the hiring at each store. Each employee can perform many functions such as purchasing, selling, and cleaning fish. These employees earn a wage based on their hourly rate and the number of hours worked. The hours worked are recorded on weekly time cards which are identified by Time Card Numbers which are unique throughout the company. When employees sum their hours at the end of the week, they also note the % of their hours dedicated to cleaning fish. Although employees are usually assigned to a particular store, they may be transferred to another if Sy sees a need.

Sys also utilizes a fleet of trucks. They are used to bring the fish from the wharf to the store and to deliver the fish to major customers such as restaurants. The use of a particular truck on a purchase or sale is tracked, as is the time of the employee/driver. Each truck is uniquely numbered, and may be transferred between the stores. Company trucks are leased on yearly contracts from truck vendors. Each contract supplies one truck for one year on a negotiated fee. Lease payments against each contract are made monthly by Sy who will send a truck vendor just one check to cover all existing contracts with that supplier.

Sys uses five checking accounts for all of his disbursements and receipts. As a result, these accounts are used to pay fishers for their fish and each employee for his/her service. One check (unique number) is cut for each fish purchase submitted properly (it will have a unique purchase invoice number) and each time card. Customer payments are assigned a unique remittance advance number and they are deposited in just one account.

You may assume that only Sy has access to the cash accounts, therefore he is always the person who acts for the company on cash transactions. You may also assume that Sy is the agent for the company on the acquisition of employee service and the lease of trucks. Do not model Sy as an employee or as a separate entity in this problem.

You have also kept a list of data elements that you want to include in Sys system. They are as follows:

Location Code # $ amount of sale

Dollar amount of purchase Employee Soc. Sec. No. (SS#)

Employee Name Fisherman/Fisherwoman Name

Quantity of each seafood item purchased cash account balance

Quantity of each seafood item sold Store telephone no.

Present store assignment for employee Wage rate

Bank account no. Truck mileage for this sale

Fisher reputation Truck mileage for purchase of fish

Time spent (in minutes) of purchasing employee fish description

Present store assignment for truck Customer A/R

Check # $ amount of cust. remittance

Time card # Driver time for this sale

Individual A/P for vendor Purchase invoice #

Check amount in dollars Total QOH of each item

% of time cleaning fish for this period employee Remittance advance # (RA#)

Total hrs worked this period, employee Customer #

Location quantity of each seafood item Sales invoice #

Item selling price (standard across US) Truck engine #

Date of sale Fish product #

Date of purchase Vendor #

Lease # Lease fee

Truck model year Vendor for this lease

Fisher # Seafood shelf life (long or short)

Lease monthly payment Store address

Vendor name customer name

At this point, you realize that it is important to begin the systems analysis, and you need to document the system you plan on implementing. You want to convince Sy that a system will be valuable to the company, just as his intuition predicted. You plan on implementing a pilot system using ACCESS. The interviews that you have completed will provide an excellent base for this prototype.

You have identified the following steps to guide your implementation. Use the information you gathered in your interviews. With the exception noted above, if you feel you can increase the value of the system by adding additional features, attributes, etc., feel free to do so (BUT DOCUMENT THEIR NEED PLEASE AND BE AWARE THAT THE PROBLEM DOES NOT NEED TO BE EXPANDED).

Develop an REA diagram for Sys Fish. Create table structures for the entities and relationships. In determining minimum cardinalities, you may assume that agents and resources are entered into the database before any relationships with other entities are established (unless you think that evidence would indicate otherwise).

(Can be done in Access, to qualify for Bonus, or do manually.) Enter at least five instances (full set of new values of extensional features) in each table. Make sure that the foreign key values are consistent with the other tables so that you can link tables, but do not worry that the data in the tables is completely consistent. Minor non-correspondences are not a problem. I would recommend strongly that you start with an index card for each table and fill it out in pencil.

(Can be done in Access, to qualify for Bonus, or do manually). Compose the following queries using your own tables and data:

Fisher Item listing: show which fishers supply each product to Sys Fish.

Cash Receipts: List cash receipts for each customer and also identify the sale which is being paid.

Summary Cash Disbursements for Employee: List each employee and the total amount that has been distributed to him or her. NOTE: This is not asking for merely the Month-To-Date wages for the employees, but requires a sum of the checks distributed to employees. Since an events-based system keeps data for more than one year or month, the query would show the total of all of the checks the employee has received. Make sure that at least one employee has gotten more than one payroll check so that the amount of the sum is greater than the value of one check (and it does not have to be the amount of the MTD wages).

A minimum of 3-4 other queries. Do not provide more than 10-12 queries.

Construct a value chain (as a series of GIVE-GET Dualities) for Sys Fish and explain why/how he is successful (you may assume that Sy provided all of the initial funding himself and that administrative expenses not described in the problem are minimal). Does his success look sustainable and what critical factors need to be managed in this company? What cycles have not been included in your enterprise database and what extra data linkages might be needed to assess the full effect of all give-and-take exchanges? You may assume that all financing comes from loans that are kept off the books (in other words, dont worry about the financing cycle; just assume that you start and end with some money.)

Package all of your results with a written summary. This summary should include any assumptions you have made about the company that have affected your REA diagram. It should look professional (i.e. be typed, no spelling errors, etc.). Additionally your report must include a discussion of how this system will help the accounting function in Sys Fish.

Bonus Requirements: (15 marks: Maximum possible score for the project is 115; grade will be the score divided by 100)

Use the REA diagram and table structures to create a relational database with ACCESS.

Create all of the tables (intentional features). Additionally, put in as many of the relationships in ACCESS as you feel comfortable with (the more, the better).

Requirements 2 and 3 from above can be done with Access, that way a manual linking will not be necessary.

Good Luck!

This exercise is adapted from materials provided by Professor William McCarthy of Michigan State University. I am grateful to him for allowing me to use it in this course.