ist 309

ITA#2:

Retail Company-- ERD Development

Marylyn Inc. has experienced tremendous growth over the past one year and top management thinks keeping data in a spreadsheet format has many drawbacks. These include minimizing data redundancy, data inconsistency, and data isolation and maximizing data integrity, security, and independence. Top management has requested you to help the company expand its data capture and reporting capabilities by developing a relational database as a more functional structure.

Requirements:

1. Revisit the Customer Relations Spreadsheet you used in Assignment #1 and determine which tables need to be created.

2. Create the required tables.

3. Transfer the tables to a Microsoft Access Database.

4. Create an Entity Relationship Model using the MS Access Database Management System using the following rules.

• A customer can place zero or more orders, but an order can be placed by one and only one customer.

A product can be requested by one or more orders, and an order can request one or more products.

A Sales Representative can serve one or more Customers, but a customer must be served by one and only one Sales Representative.

• When you have a one to many relationship; place the primary key of the one side of the relationship into the many side of the relationship.
• When you have a many-to-many relationship; create a bridge table whose composite key has one primary key from each of participating tables.

Due Date : 08 /14/2019 at the end of the class demonstration

ITA#3 : Database Development

Based on the ER Diagram you developed in ITA#2, you are requested to complete the following assignment:

• Create a Main Form and Sub-Form for each one to many relationship.

Create a query that lists the total purchases by each customer.

Create a query that lists the total sales of each Sales Representative.

Create a report for the list of total purchases by each customer.

Create a report for the list of total sales by each Sales Representative.

Export the two reports generated in d. & e. above to an Excel Spreadsheet.

Use the knowledge you have gained in class to produce a graph and/or dashboard to present your report to top management. Write your observations on the spreadsheet.

Due Date: 08/21/2019 at the end of the class demonstration.