Question

Rationale

The focus of the project is to develop your database programming skills. This project will help you get a fair idea of the sales and distribution system in any organization that has a chain of Carrying and Forwarding Agents (CFAs) or super stockists and stockists. You will be able to implement database programming concepts of ADO.NET in VB.NET and ASP.NET to create a real-life, web-based database application. (VB stands for Visual Basic.)

Scenario

Smooth Pen, Inc., a pen manufacturing organization, has a wide range of pens available in the market. It operates through a network of ten CFAs and more than five hundred stockists throughout the country. The final product reaches the customers after passing through CFAs, stockists, and retailers.

Smooth Pen, Inc. is experiencing difficulty in maintaining an inventory of stocks available with the CFAs and stockists. It does not have a proper sales and distribution system that can track the available stocks. As a result, the organization usually ends up having either excess or insufficient stock. This has led to a lowering of the organization's profit margins and competitive edge in the market.

The organization is also unable to gather details of the closing balance of stocks and accounts from the CFAs and stockists. This information is necessary for any manufacturing organization because it helps in future material planning. As the manufacturing capacity of Smooth Pen, Inc. is growing, it is gradually becoming more difficult to get the closing balance for a particular week or month at the right time and in the right form by using the existing mailing system. This, in turn, is affecting the planning of material requirement and financial management. This situation has led the organization to seriously think about providing the CFAs and stockists some sort of connectivity with its head office.

Smooth Pen, Inc. is looking for a centralized web database application, which can act as a repository for combining and storing vast amounts of master and transaction data from different sources, such as CFAs or stockists. CFAs should be able to log on to this application, check for the availability of stock, and if stock is available, raise invoices against orders created by their stockists. The stockists should also be able to log on and create orders for the CFAs. On the other hand, the head office should be able to use the same application to update or send any new data such as new product releases, stockists, and locations to CFAs, query daily data, and consolidate the transaction data of a particular day, week, or month on each CFA or stockist.

The organization believes that it will connect all the stockists with the CFAs and the CFAs with the head office by implementing this application, thus enabling quick information interchange and providing a scope for further planning.

Scope of the Project

The scope of this project is to understand the requirements of the system and develop an online web database application by using advanced database programming.

To create this application:

You may split the project into modules such as Masters and Order Entry.

You must develop these modules by using the ADO.NET framework.

You must design and implement the database by using MySQL.

You may then use ADO connectivity and apply concepts such as connecting to databases, DataAdapters, DataSets, DataTables, DataViews, DataLists, and DataGrids to develop the application.

You will have to create the following modules and generate reports for them using DataGrids and DataAdapters:

Masters: This will be a master module for products, CFAs, stockists, and locations.

Order Entry: This module will be used by a stockist to raise an order for a CFA.

Invoicing: This module will be used to generate an invoice against the orders entered. The CFA will then deliver the goods based on the invoice.

Collections: This module will be developed to track the account payables from the stockist.

Reports: This module will be used to generate individual or consolidated reports at the head office by using the above transaction data.

Platform

Based on the complexity of the requirements you will design and develop the application using VB.NET, which will use the features of ADO.NET framework.

You should:

Understand the problem statement before writing a program.

Design the normalized database schema and the application.

Use VB.NET to perform object-oriented database programming.

Design and implement connection requirements with connection pooling and caching.

Perform programming using both connected and disconnected environments with DataSets and DataGrids.

Test the project.

Deploy the project in ADO.NET.

Based on the project description, analyze the problem statement. Break up the problem into smaller tasks and prioritize the tasks that need to be performed.

Create a MySQL database, the required tables, and the associated keys. Design the structure of tables in such a way that the information is not repeated.

Use VB.NET to design the front end—the user interface.

Using ADO.NET, define and create different objects such as DataNameSpaces and DataProviders. Enhance the coding by using global functions and variables.

Define and create connection objects and then connect to the database through the connection objects.

Use all the connection parameters required including connection time-outs and securities.

Write codes to catch errors and exceptions that might occur while connecting to the databases. If necessary, use connection pooling and managing concepts.

The task prioritization table that lists the tasks, which should be performed to achieve the objectives of the project, in the order of their priority

The schema of the database that you have created

VB.NET screen grabs of all modules

The codes developed by you

A section describing the process of creating the application, including any problems you encountered

Perform coding for the forms to add, delete, update, and insert data in the Masters module.

Write code to retrieve the same data from the Order Entry and Invoicing modules.

Change the existing system into a web-based system by mapping VB.NET controls to web controls, and also modify the required code in ASP.NET to make it an online application.

Develop the Collections and Reports modules by using advanced database concepts, such as Complex Joins and Triggers, to optimize the performance and security of data retrieval.

Change your previously written simple queries to more complex queries or use stored procedures and rewrite the code accordingly.

Develop customized reports by using Data Filtering and Data Views.

Enhance your project by using ASP.NET Session Tracking to ensure security.

Configure the application so that it can be deployed in a web-based environment.

Submit the completed application as well as submit the following information in a Microsoft Word document:

Answer 1

The Leading C&F Agents provides following services

1. Warehousing before Transportation

2. Local Transportation

3. Container Arrangement

4.Reservation of Shipping Space

5.Selection of Mode of Transport

6. Packing, Marking and labelling

7. Completing Customs and Port formalities

8. Cargo Insurance

9. Advising Exporters on Trade Laws

10.Educating Exporters

11.Coordination with other Agencies

12.Procuring Documents

13.Warehousing facilities abroad

C&F Agents are greater emphasis on the flow of goods rather then their storage and they prefer to increase outsourcing of warehouse/distribution center activities. So, C&F Agents are also clients/customers for DCs.