Advanced Accounting Information System - Case Study Example

Running head: Advanced Accounting information system Advanced Accounting information system Name Course Tutor Date Part I-Existing System With all of Trigon Corporation success, it is difficult to identify their weakness. Their weakness lies in their product seasonality. Trigon’s customers have a huge influence in their operations. Trigon Corporation spends a lot of money and resource to collect and keep track of their customers buying pattern and preferences. They also shared this data with their suppliers to come up with the best strategies to serve their customers. Trigon Corporation is operating in a very intense industry where they have a lot of competition. Purchases of computer parts discussion with customer on requirements Production check on quality make delivery Flow chart of the process Preparation Send to delivery department Send to quality Control department The company has a specific corporate mission. This mission has to remain clear to all those in management and especially all employees asking the questions what is our business? Who is our customer? What is value to customer? What will our business be? What should our business be? Are questions that the company has raised in creating the strategic plan. Trigon Corporation is the world biggest retailer because of their strength. It is hard for any of their competitors to duplicates some of this strength. In the US, Mexico and Canada, Trigon Corporation was able to do whatever they want in terms of choosing the locations for their stores, suppliers and consumers link network, and set their merchandise at the lowest price. Unfortunately, this strength can not easily be implemented in their international operations. Even though they still make a profit in their overall international operations, they are struggling and even incur a lost of hundreds of millions in the market. The problem with Trigon Corporation is that they can not use the same success factors that work so well in the United States and use it to their international operations. Another benefit of focusing on just a few countries rather than fragmented all over the place is that it will increase their purchasing power. In their international operations, Trigon Corporation do not have this influences over their suppliers because of the amount they purchase does not nearly resemble the amount of merchandise purchase from the United States’ suppliers. This will increase their influences over their suppliers, and they can start making demands similar to their United States system. Trigon Corporation needs to be in control of all aspect of the operations for their system to work efficiently. Having a close relationship with their suppliers has always been one of Trigon Corporation’s success factors. That is why it is so crucial for them to take control and have their suppliers on the same field. The procedure of dealing with new customers Not only has Trigon Corporation’s strategic placement of their stores and distribution centers given them a competitive advantage, their strategic operations and inventory management has played a vital role in their success. Trigon Corporation’s inventory is kept at a minimum, allowing them to achieve maximum efficiency of their store floor space. This strategy helps Trigon Corporation lower inventory cost, which allows them the ability to meet the demands of local consumers, and the ability to avoid shortages. Thus, in return giving them a higher operational efficiency and keeping their prices significantly lower that other discount retail stores. The company’s has successfully implemented a business model that is built on elements helping the company to achieve cost leadership, strong market position, distribution and manufacturing efficiencies. Within the company there is a strong presence of a business culture that is shared amongst various business functions and associates of the company and has helped the company to retain its position in the market and impose greater challenges for other companies. The company’s strategy has been the binding force that has allowed the company to face tougher market conditions and competitive forces over the years and it is likely to play an important role in shaping up the industry in the US and other countries. Trigon Corporation is one of the largest corporations that stand for the small town community values that are well served through efficiencies in its operation management. Trigon Corporation is well known for their size and vast amount of different products and services they offer. So size is an important factor for them. This makes it very difficult for them to implement the same success system in places like all countries. Even though land in this place is very expensive, but this is not a problem for Trigon Corporation because they can afford it. So for Trigon Corporation to implement their strength in places like this is almost impossible. It could be inferred that the changing market conditions continue to challenge retail business that are already facing difficulties in retaining good business prospects. Companies like Trigon Corporation need to understand the changing dynamics of the industry that is very much based on the changing behaviors and trends. It is therefore suggested that retailers need to understand the implications from the changes in the buyers market and adapt ways to efficient manage customer relationships. Part II- The new information system Entity – Relationship Diagram (E – R Diagram) In the Entity – Relationship diagram for this problem, there are three entities and there are only two types of relationships between the entities, they are, many-to-many type of relationship and one-to-one type of relationship. The entities are Items_In_Stock entity, Supplier entity and Order_Details entity. The Items_In_Stock entity has a many-to-many relationship with the Supplier entity because one supplier can supply many items and many suppliers may supply one item itself. The Items_In_Stock entity also has a many-to-many relationship with the Order_Details entity because many items may be ordered in one order given to a supplier and one item itself may be part of many orders made to different suppliers. There is a one-to-one relationship between the Supplier entity and Order_Details entity because only one order is given to one supplier and one supplier supplies items only for one order. After normalizing the data we get three tables for the three entities and their attributes are listed as columns in each table with primary to identify each item in the table and foreign keys to cross-reference one table with another. A double-edged arrow indicates a many-to-many relationship and a straight-line indicates a one-to-one relationship in the following diagram: Query Transactions For example, let us assume for the first entity Item_In_Stock the following field lengths: Item Code (field type: alphanumeric, length: 8), Item_Description (field type: character, length: 30), Price (field type: numeric, length: 5), Quantity_In_Store (field type: numeric, length: 5), Re_Order_Level (field type: numeric, length: 5), Re_Order_Quantity (field type: numeric, length: 5). For the second entity Supplier_Details, Supplier_ID (field type: alphanumeric, length: 8), Item _Code (field type: alphanumeric, length: 8), Supplier_Name (field type: character, length: 30), Address (field type: alphanumeric, length: 30), City (field type: character, length: 20), Phone Number (field type: numeric, length: 5) For the Order_Details entity we group the following sets of related data, Order_ID (field type: alphanumeric, length: 8), Supplier_ID (field type: alphanumeric, length: 8), Item _Code (field type: alphanumeric, length: 8), Item Description (field type: character, length: 30), Quantity Ordered (field type: numeric, length: 5), Order Date (field type: date, length: 8), Total Amount (field type: numeric, length: 6). A company may have a number of views and dividing its incoming business. The most common approaches used by the companies is to segment their customers by their turnovers and classifying the customers as large, medium, small and tiny businesses. This approach has its own limitations in the sense that each division will be handling repeated vertical areas. However the advantage is that the large and not-so-large customers can be easily dealt with in different ways. One another common approach adopted by organizations is to divide the customer by their nature of business. In this case specialization of products provided and competence tends to get accumulated in the division and hence dealing with new client by domain specialists ensures a very positive impression of having domain knowledge to the customer. This helps Trigon to make quick positive impressions on the customers and help close sales faster and reduce sales cycles. Among the alternates cited above it is highly recommended that the customers be classified by the nature of their businesses. This will ensure that domain expertise gets developed and accumulated within a specific business division creating a pool of expertise. This domain expertise will ensure that the customers have more confidence in the solutions being proposed by the company. Focus on smaller customers Smaller customers have ensured higher profitability. Smaller customers mean quicker decisions. Smaller customers also mean limited IT budgets and smaller business orders. So while it is attractive to work with smaller customers, it is vital to have a mix of a few large customers and much higher number of small customers. There are two possible alternate solutions to each, creating one sales team to deal with different customer segments and the other to deal with different geographies. Each has its set of pros and cons. Since the long term vision of the organization is also to be present in multiple geographies it is highly recommended that there be different teams that target different geographies and each be customized to suit the culture and buying behavior of that specific geography. Part III-accounting software Screen Design Of The Actual Database The name of the database is inventory_control and there are three tables in this database, they are, item_in_stock table, supplier_details, order_details as shown in the following screen. The item_in_stock table has item_code as the primary key and the supplier_details table has combination of supplier_code and item_code as primary keys. The order_details table has a combination of order_id, item_code and supplier_code as primary keys. All these tables are linked to each other foreign reference keys shown in the database normalization diagrams in the previous sections. Queries 1. List all items in stock: Query: select * from item_in_stock; 2. List all of the items in each category. Query: select * from item_in_stock where item_code=’dvdsn001’. 3. List all of the items not in stock. Query: select * from item_in_stock where quantity_in_stock=0; 4. List all of the items not in stock in each category of item. Query: select * from item_in_stock where item_code=’wash001’ and quantity_in_stock=0; 5. List all of the suppliers who supply a particular item. Query: select * from supplier_details where item_code=’tvsn001’; 6. List all of the suppliers from a particular city. Query: select * from supplier_details where city=’middlesex’; 7. List all the transactions made between particular periods. Query: select * from order_details where order_date between ‘2010-08-12’ and ‘2010-08-15’; 8. List all of the transactions made by a particular staff. Query: select * from order_details where staff_name=’Jonathan Stone’; 9. List all the orders generated during a particular period. Query: select * from order_details where order_date between ‘2010-08-12’ and ‘2010-08-15’ and order_status=’Generated’; Entity – Relationship Diagram (E – R Diagram) In the Entity – Relationship diagram for this problem, there are six entities and there are only three types of relationships between the entities, they are, many-to-many type of relationship, one-to-many and one-to-one type of relationship. The entities are Items_In_Stock entity, Supplier entity and Order_Details entity, client_details entity, client_order_details_entity and the returns_inwards entity. The Items_In_Stock entity has a many-to-many relationship with the Supplier entity because one supplier can supply many items and many suppliers may supply one item itself. The Items_In_Stock entity also has a many-to-many relationship with the Order_Details entity because many items may be ordered in one order given to a supplier and one item itself may be part of many orders made to different suppliers. There is a one-to-one relationship between the Supplier entity and Order_Details entity because only one order is given to one supplier and one supplier supplies items only for one order. Similarly, there is a many-to-many relationship between items_in_stock and client_order_details entity, one-to-many relationship between customer_order_details and customer_details entities, and a one-to-many relationship between customer_order_details and returns_inwards entities. After normalizing the data we get six tables for the six entities. The attributes of the six tables are listed as columns in each of these tables. Each table has a primary key to identify each item in the table and a foreign key to cross-reference one table with another. A double-edged arrow indicates a many-to-many relationship and a straight-line indicates a one-to-one relationship. A simple arrow indicates a one-to-many relationship in the following diagram: Verify that a design meets user requirements and provide justification of the database design. The E-R diagram above follows the top-down approach and it is follows a logical schema of designing a database. By using database normalization I have verified that the design meets user requirements. E-R diagrams are widely used in software engineering to determine the most appropriate design for database in a relational database system. Database normalization ensures that there are no duplicate fields in the database and only the relevant details are stored in a database. Part 3 Query Transactions 1. List all the items ordered Query: Select * from customer_order_details 2. List all orders for a particular item Query: select * from customer_order_details where item_code=’DVDth001’; 3. List all items for a particular order Query: select * from customer_order_details where client_order_id=’IBM0001’; 4. List all customers Query: select * from cust_details List all items returned Query: select * from returns_inwards 5. List all items returned by a particular order Query: select * from returns_inwards where return_id=’ret0010’; Documentation 1. The documentation provided above supports the implementation of the database. Through the process of normalization, the verification of data has been carried out and only relevant fields are incorporated in the tables, there are no duplication of fields in the database and no duplicate keys. The validation process has been taken care of through restriction on the type of data entered. For example, in customer name only characters can be entered when you specify the field type as character. To prevent the field from showing null entries specify the command ‘NOT NULL’ at the end of a query for adding fields to a table. A screen shot of the validation procedure is shown below: 2. Evaluation of testing techniques and application of one of the testing techniques to database design. Many testing techniques can be applied on a database design to uncover errors if any. The different testing techniques are unit testing, black-box testing, database re-factoring etc. In this assignment an evaluation of the unit testing and database re-factoring technique is done. Unit testing technique: This testing technique is used in database design and provides a structured and automated way of testing each part of a database system. Mostly developers who develop a component do the unit tests and would write the test cases for the component. The testing of these components in an isolated manner to ensure component behavior before integrating with the main system is known as unit testing. Unit tests can be run on database stored procedures, functions and triggers. The different types of unit tests are: Feature tests: Testing the core features of the database from the database user’s perspective is known as a feature test. Schema test: This test is used to test the database schema. Testing a view to see if it returns the expected results etc are examples of a schema test. Security test: Testing the security of the database is done in this phase. Here you test to see if unauthorized users gain entry to the database by testing the database using negative tests. Stock-Data test: Data that is used infrequently is known as stock data and many databases have these seed data. This test ensures that your seed data exists in your database The unit testing technique is a very good technique to test your database design. This testing technique completely tests all the components of a database and is more advantageous than manual testing. Database Re-factoring technique: This is a technique in which a simple change to a schema improves the database design but retains the behavior and semantics of a database. The purpose of database re-factoring is to: Safely fix existing legacy databases: Use database re-factoring to improve data and the quality of data in legacy databases using this technique. This is the only technique that can be used to improve legacy databases. Support evolutionary development: Most of today’s development processes support evolutionary programming and therefore database design also must evolve. Database re-factoring techniques help database designs to evolve. I applied the unit testing technique on my database design, and the following screens show how I did that and whether my database design passed the test. First, add the test in the unit testing application: Name the test as: Add a row count condition Set the number of rows to test to 15. Run the test And the inventory_control database passed the test. LIST OF REFERENCES Whitten, Jeffrey L.; Lonnie D. Bentley, Kevin C. Dittman. (2004). Systems Analysis and Design Methods. 6th edition. Read More