Scenario
La Trobe Retail Store (LRS) required an information management system to enable retail stores to manage their day-to-day operations and integrate their business into a single centralized unit leading to increased operational efficiency. The application domain for this assignment is a part of a simplified information management system for LRS. Stores are to be provided the facility to create and manage a customer database including details such as customer information, and product information etc. The Retail Store management has decided to complete this project in two phases: data modelling and implementation. You are required to complete and submit the data modelling phase for this assignment.
After the above dates will incur penalties in accordance with La Trobe business School Policies.
Background to the Project
The LRS is a place where there are several things present, such as goods and products, customers and their needs, retail executives, etc. Such things can only be well handled by a person known as the retail store manager. The obligations of a store manager mostly depend upon the size of the store. The principal responsibility of a store manager is handling and overseeing all the day-to-day processes that are carried out in the store. He/She manages the product inventory, salespersons, goods for display, sales, etc.
Apart from product and customer management, He/she also has to take care of the interests of the members in his sales team, as they are the ones who are in direct contact with the customers. Salesperson duties include taking inventory of existing products and keeping tabs on what has been ordered and purchased.
The LRS consists of the sale of products from a fixed location for direct consumption by the customer. Each store is recorded as store ID, store name and location. Each salesperson in the store has staff identification number, name, and contact details. The LRS records each product as product code, name and price. Different stores may supply different price for same product. The proposed LRS information system should also maintain customer's information by recording their name, address, and phone number.
The store also records invoice number and issue date before sending an invoice to a customer. Items of each invoice which describe the products bought by the customer should be recorded. Additionally, the system should record the quantity of each product.
The following explanation may assist you to understand the ERD:
- more than one stores in a city, one store is a supervise store.
- a customer/shopper can have many invoices.
TASK 1: - Interviewing the Client/prospective customer at LRS
Adopt design thinking approach to interview prospective customers/shoppers at LRS to determine the requirements for information management system at LRS. Document the method used in the design thinking approach
TASK 2: Work-Centred Analysis Framework
Map the requirements identified in task 1 to the six system elements of LRS case study based on work-centred analysis framework.
TASK 3: Performance Analysis - Business Value and Customer Value
Conduct a Performance Analysis of Business Process and Product/Output Components identified in Task 2 to determine business value and customer value gains which can be obtained through design and deployment of an information system as sub-system of the work system.
TASK 4: Architecture of the Information Component
A. Develop an Entity Relationship Diagram (ERD) to represent the data identified in the information component of work-centred analysis framework for LRS information management system. The diagram of your ERD model must:
- show all necessary entities, attributes and relationships
- show unique identifiers and nature of interaction between entities..
B. Map the ERD into a set of relations in at least Third Normal Form (3NF). You should demonstrate that each relation satisfies the rules for first normal form, second normal form, and third normal form.
For example:
PROPRIETOR (ProprietorID, FamilyName, GivenName, PhoneNbr) This relation is in 1NF because it does not contain any repeating groups.Using DFD techniques draw the context diagram of Business Process Architecture of LRS information Management System. Also draw level 0 data flow diagram showing the major sub-processes, data stores and flow of data between various sub-processes or steps in the business process component.
TASKS 1
The submitted work will therefore comprise: A Microsoft Word document containing:
1) Application of Design thinking approach as an interview methodology for determining the requirements of LRS information management systems
2) Mapping of requirements identified through the design thinking approach on to the 6 system elements of work-centred analysis framework.
3) Performance Analysis of business process and product/output components of LRS information management system
4) A list of brief descriptions for each entity and attribute that you have identified in your case study.
5) A copy of your entity-relationship model(s).
6) A copy of relational tables in 3rd normal form
7) A list of any assumptions that you made (include any clarifications from your tutor).
Relational Database Implementation
You must implement the normalized entity relationship model developed in TASK 4.
The Managers of La Trobe Retail Store (LRS) information system require reporting features to help with identifying customers, products, staffs and invoice details. Some of such reports include: staffs details, finding specific age groups or genders of customers and grouping products with stores, printing a list of staff etc.
This will be achieved by creating a MS ACCESS 2007 database making use of supporting features like: forms, queries and reports. A user manual is essential to this project as the managers are novice computer users and will require assistance for using the database.
TASK 6:
In your report, you must include a data dictionary based on given model solution (i.e., normalised ERD). You should briefly describe all entities and attributes are included, descriptions are clear, reasonable field formats, etc.
TASK 7:- LRS Database Implementation The recommended tool for this assignment is Microsoft Access 2007.
A. Implement your data dictionary to create a relational database in Microsoft Access 2007.
B. Populate the database with sample data. Sufficient sample data (at least 10 records per table) must be included to demonstrate that the queries, view(s), and report(s) work correctly.
C. Create view objects ("SQL queries" in MS Access 2007) to obtain tables that demonstrate following reports. (Note: it is strongly recommended that you do not use the Access query builder (QBE) - you should type the SQL queries manually.)
Finally, create the following forms and reports, requiring a separate query to satisfy each of the following information needs:
1. A form to enable simple and easy entry, delete and update of customer and product information.
2. Create a Store form in Design View and add a subform into it to view product details in each store.
3. A report that prints the customers' details. 5 to a page.
So, La Trobe Retail Store can email them required information electronically.
4. A report showing invoice details of last 30 days. Order the list so that the recent invoice appear first.
5. A report that shows the details of customers who visited retail stores more than once in the last month
6. A report that shows the age group (e.g., 18-30, 30-50, 50 and above) who did most of the shopping in the last 30 days.
7. A report that shows the group of products available for each store.
8. A switchboard or startup form, which includes all required forms and reports in the LRS database system.
Save the database structure, test data and query/report designs to the CD. However, printout the result of your forms and reports and include these in the hard copy submission.
TASK 8: User Manual
Create a user manual for your La Trobe Retail Store Information System. This manual should explain all the features of your database and how to do each task. The manual should also include screen shots of what you are explaining. Screen shots can be taken by pressing Alt+PrintScreen, and then pasting it into your document