information system, project, process modeling

There are 2 parts to this project. The content for both parts are unrelated, although the concepts are related.

All written deliverables should be professionally prepared. This is included implicitly in the requirements (especially since we are business students). While deliverables are not graded for grammatical and typographical errors (this is not an English or Literature course), the language should be professional, coherent, and comprehensible. If need be, please feel free to utilise the writing centre (please refer to the syllabus) prior to submission.

Part 1 Data Management (10 points)

Software skills: Database conceptualisation and design.

Business skills: How databases support functional area(s)

Select any one functional area in a business organisation. Think about the data involved in the processes. Based on your selection, use an Entity Relationship Diagram (ERD) to depict the data model of the functional area[footnoteRef:0]. The materials covered in class (on databases and general MIS) may be useful in helping you select a functional area for analysis. Please be sure to select something that is manageable[footnoteRef:1]. Please also have at least 4 entities (2 points per entity) in your ERD with appropriate attributes. Please do not use the ERD examples covered in class because we’re supposed to think. There is no need to have all possible attributes. But please have relevant/suitable ones for each entity. Some attributes can be pointless, depending on the design of your database. And we don’t want to have unnecessary ones. Please also include an explanation[footnoteRef:2] of the functional area/process you are modeling, and detailed justification for the entities and relationships, as this will determine how you want your database to work in support of the function you selected (2 points). You can refer to Chapter 1 for functional area information systems (FAIS) and how they function within an organisation. [0: Recall Module 1 (Chapter 1).] [1: A possible idea could be a warehouse that has entities like inventory, manager, order, store; or a bank that has entities like branch, customer, transaction, relationship manager.] [2: This is to ensure we think this through and avoid introducing entities just for the sake of introducing them]

Your ERD must be constructed using Visio (or equivalent[footnoteRef:3]). Paste the ERD in a word document as a picture for submission. Seattle’s virtual desktop should have Visio installed. If you really have issues accessing Visio, you can conceptualise it and use the computer in the lab. This will minimise the amount of time you need using the software itself. Please do not draw it manually, as this is an MIS class after all. We are expected to use technology tools to solve problems. 😊 [3: You can try Gliffy in Goole Docs or on the browser. There are others too.]

Grading rubric

The grading rubric is given below. 10% of the points are given to truly exceptional work.

Part 2 Data Queries (10 points)

Software skills: Database design and querying

Business skills: Inventory management

In this exercise, you will use a database software to manage the inventory of a small business. Each product is given by its Item Identifier. Wisteria, located in New York, sells groceries. Currently, Wisteria purchases supplies from 5 suppliers, and sells the products in 10 locations. Using the information (data) found in the tables given in the small desktop database (grocery.accdb), help manage information about Wisteria’s suppliers and products.

The description of each field in the database can be found for each table (Table Design view). If preferred, they are also available in the accompanying word document (Supermarket_sales_documentation v2.docx) in alphabetical order.

Perform the following.

1. To learn more about the products that are expensive, along with their markup, execute a query that identifies the five most expensive dairy products only (for customers). The query should list the products in descending order from most expensive to least expensive (by Selling Price). Include the Item Identifier, inventory for each, and the markup percentage for each. Save this query as Q1_Five_Most_Expensive. Hint: Profit Mark Up = (Selling Price – Cost Price)/Cost Price. (2.5 points)

2. To learn more about the products that require reordering and from whom to reorder, execute a query that lists each supplier’s name, its products (Item identifier), the corresponding inventory, and associated reorder levels. Sort in ascending order for each supplier. Within each supplier, the products should be sorted alphabetically too. Include a criterion for products that have inventory that is more than 1900. Hint: This requires hierarchical sorting and a numerical filter. Save this query as Q2_Supplier_Product (2.5 points)

3. To learn more about the sales of specific products in supermarkets only, execute a query listing only the products (Item identifier) starting with “FD” and end with “8” (for eg, “FDABC8”), along with their corresponding item type, cost price, outlet type, and outlet sales. Save this query as Q3_BR_outlet (2.5 points).

4. To enhance the database, we need a table of supplier information. Create a table called Supplier_Info that has supplier telephone numbers. For full credit here, include an appropriate input mask. Add the appropriate fields and populate the table with the suppliers to demonstrate our ability to enter data to a database and to check that it works. Be sure to enter all suppliers to your table (there should be 5). Make up the telephone numbers. This requires some thinking on what field(s) to include in the new table. Recall that databases should be normalized, and we should join apples to apples, oranges to oranges; and we want to avoid redundancies. For purposes of this assignment, create the table accordingly. There’s no need to join other tables. Save this table as Supplier_Info (2.5 points).

Your work will be evaluated on the accuracy of the results, quality of your answers, and ability to apply the concepts. Submit your work on Canvas. There should be 2 documents: (1) a single word document (or pdf) for part 1, and (2) the database file (Access file) for part 2 by the due date. Late submissions will result in a 5% penalty for each day late.

Page 4 of 6