List the data elements that are needed by the owner of Mobility Product Inc. to support the improved processes that you suggested in Assignment 1 for order processing, shipment and delivery tracking.If you did your own scenario in Assignment 1, then just read through the Mobility Inc. case and list the data items that are needed for efficient order processing, shipment tracking and customer service.You can use the form that is included in the case to get some ideas about the data elements that may be needed. Try to keep the list manageable (for example, do not try to list all possible features of the product, just a simple description, model number and color will be enough). (10 points)
Develop an ERD from the list of data elements identified above.Clearly show the entities, their attributes and the relationship between various entities along with the cardinalities and modalities.If you do your design correctly, then you should get have about 4-6 entities.(15 points)
Translate the ERD into complete database design.Make sure that you introduce/include the intermediate relations that may be needed to connect the many to many relationships in the design.Develop a database in MS-Access using your design and populate the tables with manufactured data.There is a site called fakenamegenerator.com that may be helpful in creating the ‘fake” data. (30 points)
Within the database you have created in Step 4, create the Queries necessary to support the information required by the company.Some examples of the queries are listed below. (30 points)
History of customer orders that will show customer details, products that they ordered along with order and delivery date and the sale price.
Shipping information for ordered products (product details, customer details along with the delivery address, date order was placed, date shipped, date to be delivered and the shipping company used).
The amount of revenue and profit generated by each customer and by each product model (two separate queries, one for the customer and another for the product).
A list of orders generated by each sales person for the last one month.This one should be interactive so that the information for each sales person can be easily found through a pop-up search box.
Discuss the business advantages for MPI in using the database and also resources needed by MPI to implement the database. (15 points)
Sample Solution
