INVENTORY MANAGEMENT SYSTEM Introduction to Inventory Management System 1.1 Preface: Inventory Management is a best control method which allows to organizing and managing financial aspects. In a literal sense, inventory refers to stocks of anything necessary to do business. These stocks represent a large portion of the business investment and must be well managed in order to maximize profits. In fact, many small businesses cannot absorb the types of losses arising from poor inventory management. Unless inventories are controlled, they are unreliable, inefficient and costly. 1.2 Reasons for Keeping Inventory: There are three basic reasons for keeping an inventory: 1. Time - The time lags present in the supply chain, from supplier to user at every stage, requires that maintain certain amount of inventory to use in this "lead time". 2. Uncertainty - Inventories are maintained as buffers to meet uncertainties in demand, supply and movements of goods. 3. Economies of scale - Ideal condition of "one unit at a time at a place where user needs it, when he needs it" principle tends to incur lots of costs in terms of logistics. So bulk buying, movement and storing brings in economies of scale, thus inventory. 1.3 Purpose: The purpose of Inventory Management System software is to manage stock levels, record physical inventories, and track trends in item movement and location. Inventory control involves supervising the supply, storage, and accessibility of stock items - both to ensure an adequate supply and prevent a costly oversupply. At larger organizations, inventory control systems may integrate inventory control software with fixed or handheld devices that can read or print barcodes and RFID (Radio Frequency Identification) tags. 1.4 Limitations of Manual System: o Inherent delay in processing and retrieval of information o
Lack of accuracy and consistency in reporting
o
Loss of efforts and time in serving queries of repetitive nature
o
Duplication of work in different Directorates
o
Lack of transparency in working
o
Under utilization of information resources
o
Significant delay in traditional methods of communication
o
Lack of quality in planning and management of resources.
1.5 Scope: The Inventory Management discipline encompasses all system and data network elements from the mainframe to the server level throughout the enterprise. All mainframe and data network based hardware and software assets must be identified and entered into the Inventory System. Any changes to these environments must be reflected in the Inventory System. Financial and technical product information must be available through the Inventory System, as needed to support the functional responsibilities of personnel within the finance and contracts management departments. Asset criticality must be included with asset descriptive and financial information, so that the Recovery Management department is supplied with the information it requires. Recovery actions must be implemented to safeguard critical assets. Asset status must be included in the Inventory Management system, so that the component(s) can be serviced in adherence to legal, environmental, business, and industry requirements. This process should be used to drive the facilities management department via form routing when components change status from active to redeploy, donate, and terminate, of scrap. An audit trail of activities associated with equipment status changes and associated actions must be maintained to certify actions and eliminate legal and civil exposures. The Standards and Procedures Manual section relating to Inventory Management must be created and published. This section must describe the process by which assets are identified, entered into the Inventory Management System, tracked, and finally deleted. All information needed by personnel to perform Inventory Management functions must be clearly described within this S&P Manual section. 1.6 Mission: The mission of an Inventory System is to provide a Central Asset Repository of information used to define assets and relate the asset to its; owner, location, and relative importance. This information will provide personnel with data needed to support their job functions, for example: ďƒ˜ Facilities Management will be able to plan Heating, Ventilation and Air Conditioning (HVAC) requirements, as well as power and floor space needed to support equipment listed in Asset Repository for a specific location. To also perform the functions needed to adhere to legal, environmental, business, and regulatory requirements associated with equipment redeployment and termination.
ďƒ˜ Financial Services will be able to budget for asset procurement, depreciate assets over time, and complete tax documents. A report of equipment and their resale value can be
used to aid in planning equipment upgrades and to reduce the “Total Cost of Ownership” associated with equipment.
Contracts Management will be able to negotiate vendor discounts and enterprise agreements. Additional vendor agreements may be required to support transportation and warehousing, equipment service and reconfiguration requirements, data wipe services and products, buyers, and scrap dealers.
Contingency Planning personnel will be able to develop recovery plans for mainframe and office assets contained within the Inventory System, based on the assets relative importance (as stated within the Criticality field). Surplus equipment may be utilized to support recovery operations, if needed. Technical personnel will be able to resolve problems more quickly with the information contained within the Inventory System, because they will have a listing of the assets contained within a location.
The Inventory System should be integrated within the everyday functions performed by personnel associated with entering and maintaining asset information. The system will reduce the effort devoted to asset management, while supplying many personnel with the information they need to perform their functional responsibilities. 1.7 Objectives: The objective of Inventory Management is to manage the physical and logical properties of I/S resources and their relationship, while ensuring that service level commitments are achieved. This process will: Ensure efficient and timely identification of vital corporate assets.
Assist in managing the enterprise-wide inventory.
Provide a common repository for asset protection.
Plan and control the proliferation of assets across the enterprise. The objectives of Inventory Management are: To identify and track all data processing assets in an Inventory System Repository.
To define the process by which assets are identified and maintained in the Inventory System.
To provide Inventory System access to all necessary personnel (data entry, update and deletion).
To provide a full range of reports that will satisfy informational requirements.
To document the Inventory Management System within the Standards and Procedures Manual.
To provide training to personnel responsible for supporting the Inventory Management System. 1.8 Functional Areas: The functional areas that interface with an Inventory Management System are:
Figure 1: Overview of Inventory Management functional areas. All of the functional areas listed above can utilize the information contained within the Inventory Management System's Central Asset Repository of information. Additionally, the Recovery Management area could utilize inventory information to identify an assets criticality (especially when the asset's location and owner are identified within the Inventory Management System). Through the use of reports generated from the Inventory Management System's Repository, it would be possible to obtain a listing of all "Most Critical" resources, by location and group. This report would then serve as the basis of a Business Recovery Plan. 1.9 Integrated Inventory Management System: To successfully implement an Inventory Management System, it is necessary to integrate it within the everyday functions performed by company personnel. That is, when a user wants to order equipment or software, they would call up the Inventory Management System screen associated with Acquisition. The same types of processes should be available for Redeployment and Termination of assets. Should a user request the acquisition of a specific type of asset, then it could be possible for the inventory system to determine if the asset is already in surplus, or if it should be purchased under an existing Volume Purchase Agreement with a vendor.
Figure 2: Overview of an integrated Inventory Management System. The utilization of Inventory Management Systems to control the purchase and installation of assets can aid in the control of the business environment, while assisting in the assignment of personnel to perform asset related work functions. This methodology will result in a work-flow and asset management system. 1.10 Roles and Responsibilities: Inventory Manager: Responsible for maintaining the Inventory in a current and accurate state. Role is responsible for both mainframe and network resident devices and software components, interfaces with Systems Management disciplines and financial department. Inventory Clerks: Responsible for maintaining the Inventory Data Base Repository and for guarantying the information contained within the Repository is accurate and in a current state. Information
is data entered, or entered via automated tools. If automated tools are used, then clerks must be knowledgeable in program products used as a tool. 1.11 Maintenance Cost Tracking: Tracking maintenance costs is an important component. Therefore, the system shall provide time and cost tracking functionality. This will allow supervisors and managers to analyze current practices and to track and manage the costs of specific types of maintenance. The tracking shall include cost information for equipment, parts and materials, and personnel. 1.12 Inventory Management Benefits: • • • •
Reduces cost and provides detailed reports for reference or checking purposes. Increase Account Saturation and Maintenance. Provides flexibility to suit individual needs of customers. Strengthen the relationship with the customer by becoming an on-going partner in the customer’s profitability improvement and demonstrating that product price is only part of the cost of doing business with a supplier.
1.13 Inventory Management Software Features: • • • • • • • • • • •
Inventory Profitability Analysis. Support for Non-Serialized Items. Inventory Commitment. Barcode Printing. Lot Tracking. Price Protection. Up gradation of items. Support for Matrix Items and Item Images. Warehouse Management. Audit Trail for Inventory Adjustments. Stock/Inventory Transfer.
Introduction to Software development 2.1 What is Software Development? Software development is the set of activities that results in software products. Software development may include research, new development, modification, reuse, re-engineering, maintenance, or any other activities that result in software products. Especially the first phase in the software development process may involve many departments, including marketing, engineering, research and development and general management. The term software development may also refer to computer programming, the process of writing and maintaining the source code.
2.2 Overview of Software Development: There are several different approaches to software development, much like the various views of political parties toward governing a country. Some take a more structured, engineeringbased approach to developing business solutions, whereas others may take a more incremental approach, where software evolves as it is developed piece-by-piece. Most methodologies share some combination of the following stages of software development: • Market research • Gathering requirements for the proposed business solution • Analyzing the problem • Devising a plan or design for the software-based solution • Implementation (coding) of the software • Testing the software • Deployment • Maintenance and bug fixing 2.3 Software development methodology: A software development methodology is a framework that is used to structure, plan, and control the process of developing information systems. A wide variety of such frameworks have evolved over the years, each with its own recognized strengths and weaknesses. One system development methodology is not necessarily suitable for use by all projects. Each of the available methodologies is best suited to specific kinds of projects, based on various technical, organizational, project and team considerations. In other words, Software engineering is the practice of using selected process techniques to improve the quality of a software development effort. This is based on the assumption, subject to endless debate and supported by patient experience, that a methodical approach to software development results in fewer defects and, therefore, ultimately provides shorter delivery times and better value. The documented collection of policies, processes and procedures used by a development team or organization to practice software engineering is called its software development methodology (SDM) or system development life cycle (SDLC). 2.4 Methodologies as Risk Management: The challenge in selecting and following a methodology is to do it wisely to provide sufficient process disciplines to deliver the quality required for business success, while avoiding steps that waste time, squander productivity, demoralize developers, and create useless
administration. The best approach for applying a methodology is to consider it as a means to manage risk. We can identify risks by looking at past projects. If an organization has been plagued by problems resulting from poor requirements management, then a robust requirements management methodology would be well advised. Once this problem has been solved, through a repeatable process, the organization might then streamline its process, while ensuring that quality is maintained. Every step along the system development life cycle has its own risks and a number of available techniques to improve process discipline and resulting output quality. Moving through the development life cycle, we might encounter the following major steps: • • • • • • • • • • •
Project charter and business case Definition of the business process and business requirements Documentation of user, functional and system requirements Top level architecture, technical approach, and system design System decomposition into component and unit specifications and design Coding, unit test planning, and unit test Generation of test data for unit testing and system testing System integration and testing Implementation, delivery and cut-over Training and user support System upgrades and routine software maintenance
In addition, we might have support activities throughout the development effort such as: • • • • •
Configuration management (version identification, baseline management and change control) Requirements management and traceability Quality management (quality assurance, quality reviews, defect tracking) System engineering reviews (requirements review, prelim. and critical design reviews, etc.) Support environment (development tools, libraries, files management, data management)
Written guidance for all these steps would constitute the core of our methodology. We can see how it wouldn't take long to fill a number of big binders with development processes and procedures. Hence, the importance of selecting processes wisely to address known risks keeping the methodology streamlined and allowing for some discretion on the part of the project team. 2.5 Software Development Life Cycle: Those stages are often referred to collectively as the software development lifecycle, or SDLC. Different approaches to software development may carry out these stages in different orders, or devote more or less time to different stages. The level of detail of the documentation produced at each stage of software development may also vary. These stages may also be carried
out in turn (a “waterfall” based approach), or they may be repeated over various cycles or iterations (a more "extreme" approach). The more extreme approach usually involves less time spent on planning and documentation, and more time spent on coding and development of automated tests. More “extreme” approaches also promote continuous testing throughout the development lifecycle, as well as having a working (or bug-free) product at all times. More structured or “waterfall” based approaches attempt to assess the majority of risks and develop a detailed plan for the software before implementation (coding) begins, and avoid significant design changes and re-coding in later stages of the software development lifecycle. There are significant advantages and disadvantages to the various methodologies, and the best approach to solving a problem using software will often depend on the type of problem. If the problem is well understood and a solution can be effectively planned out ahead of time, the more "waterfall" based approach may work the best. If, on the other hand, the problem is unique (at least to the development team) and the structure of the software solution cannot be easily envisioned, then a more "extreme" incremental approach may work best. A software development process is a structure imposed on the development of a software product. Synonyms include software lifecycle and software process. There are several models for such processes, each describing approaches to a variety of tasks or activities that take place during the process. Software life cycle models describe phases of the software cycle and the order in which those phases are executed. There are tons of models, and many companies adopt their own, but all have very similar patterns. The widely used life-cycle models are: • •
Waterfall Model The V-Model
•
Prototyping
•
Incremental model
•
Rapid Application Development model (RAD)
•
Transformation
•
Spiral Model
The SDLC models are very important in software development. So we are going to give a shot at some well known or commonly used models. 2.5.1 Waterfall Model: This is the most common and classic of life cycle models, also referred to as a linear-sequential life cycle model. It is very simple to understand and use. In a waterfall model, each phase must
be completed in its entirety before the next phase can begin. At the end of each phase, a review takes place to determine if the project is on the right path and whether or not to continue or discard the project. Unlike what I mentioned in the general model, phases do not overlap in a waterfall model. Advantages: • • • •
Simple and easy to use. Easy to manage due to the rigidity of the model – each phase has specific deliverables and a review process. Phases are processed and completed one at a time. Works well for smaller projects where requirements are very well understood.
Disadvantages: • • • • • •
Adjusting scope during the life cycle can kill a project No working software is produced until late during the life cycle. High amounts of risk and uncertainty. Poor model for complex and object-oriented projects. Poor model for long and ongoing projects. Poor model where requirements are at a moderate to high risk of changing.
2.5.2 V-Shaped Model: Just like the waterfall model, the V-Shaped life cycle is a sequential path of execution of processes. Each phase must be completed before the next phase begins. Testing is emphasized in this model more so than the waterfall model though. The testing procedures are developed early in the life cycle before any coding is done, during each of the phases preceding implementation. Requirements begin the life cycle model just like the waterfall model. Before development is started, a system test plan is created. The test plan focuses on meeting the functionality specified in the requirements gathering. The high-level design phase focuses on system architecture and design. An integration test plan is created in this phase as well in order to test the pieces of the software systems ability to work together. The low-level design phase is where the actual software components are designed, and unit tests are created in this phase as well. The implementation phase is, again, where all coding takes place. Once coding is complete, the path of execution continues up the right side of the V where the test plans developed earlier are now putted to use.
Figure 2.5.2: V- Shaped Model Advantages: • • • •
Simple and easy to use. Each phase has specific deliverables. Higher chance of success over the waterfall model due to the development of test plans early on during the life cycle. Works well for small projects where requirements are easily understood.
Disadvantages: • • • •
Very rigid, like the waterfall model. Little flexibility and adjusting scope is difficult and expensive. Software is developed during the implementation phase, so no early prototypes of the software are produced. Model doesn’t provide a clear path for problems found during testing phases.
2.5.3 Spiral Model: The spiral model is similar to the incremental model, with more emphases placed on risk analysis. The spiral model has four phases: Planning, Risk Analysis, Engineering and Evaluation. A software project repeatedly passes through these phases in iterations (called Spirals in this model). The baseline spiral, starting in the planning phase, requirements is gathered and risk is assessed. Each subsequent spirals builds on the baseline spiral. Requirements are gathered during the planning phase. In the risk analysis phase, a process is undertaken to identify risk and alternate solutions. A prototype is produced at the end of the risk analysis phase.
Software is produced in the engineering phase, along with testing at the end of the phase. The evaluation phase allows the customer to evaluate the output of the project to date before the project continues to the next spiral. In the spiral model, the angular component represents progress, and the radius of the spiral represents cost.
Figure 2.5.3: Spiral Model Advantages: • • •
High amount of risk analysis Good for large and mission-critical projects. Software is produced early in the software life cycle.
Disadvantages: • • • •
Can be a costly model to use. Risk analysis requires highly specific expertise. Project’s success is highly dependent on the risk analysis phase. Doesn’t work well for smaller projects
2.5.4 Incremental Model:
The incremental model is an intuitive approach to the waterfall model. Multiple development cycles take place here, making the life cycle a “multi-waterfall” cycle. Cycles are divided up into smaller, more easily managed iterations. Each iteration passes through the requirements, design, implementation and testing phases. A working version of software is produced during the first iteration, so you have working software early on during the software life cycle. Subsequent iterations build on the initial software produced during the first iteration.
Figure 2.5.4: Incremental Life Cycle Model Advantages: • • • • •
Generates working software quickly and early during the software life cycle. More flexible – less costly to change scope and requirements. Easier to test and debug during a smaller iteration. Easier to manage risk because risky pieces are identified and handled during its iteration. Each iteration is an easily managed milestone.
Disadvantages: • •
Each phase of an iteration is rigid and do not overlap each other. Problems may arise pertaining to system architecture because not all requirements are gathered up front for the entire software life cycle.
2.6 Recent trends in software development sector: Given the rapid growth of this sector, several companies have started to use offshore development in China, India and other countries with a lower cost per developer model. Several new Web 2.0 platforms and sites are now developed offshore while management is located in Western countries. The advantages mostly revolve around better cost-control over the process, which means that there is lower cash-outflow (often the biggest struggle for startups). Furthermore, the time difference when working with India and China for the Western world allows work to be done round the clock adding a competitive advantage. Notable firms that are involved in development include Tata Consultancy Services, Infosys, Wipro, and Satyam.
In recent years the rapid growth of Software Development has also developed in Bangladesh. Many Software firms has risen and are doing well. Instead of buying software at a very high price from foreign or neighboring country like India, companies are now getting our own made software at lower price. 2.7 System Software: System software is closely related to, but distinct from Operating System software. It is any computer software that provides the infrastructure over which programs can operate, i.e. it manages and controls computer hardware so that application software can perform. Operating systems, such as GNU, Microsoft Windows, Mac OS X or Linux, are prominent examples of system software. System software is software that basically makes the computer work. Besides operating systems, other examples are anti-virus software, communication software and printer drivers. Without the system software the computer doesn't work. In contrast to system software, software that allows you to do things like create text documents, play games, listen to music, or surf the web is called application software. In general application software is programs that enable the end-user to perform specific, productive tasks, such as word processing or image manipulation. System software performs tasks like transferring data from memory to disk, or rendering text onto a display device. 2.8 Types of System Software: System Software can be classified as operating system, device drivers and utility software. An operating system creates an interface between user and the system hardware, while other system software will refine or allow greater interaction with the machine's hardware. System software helps run the computer hardware and computer system. It includes operating systems, device drivers, diagnostic tools, servers, windowing systems, utilities, language translator, data communication programs, data management programs and more. The purpose of systems software is to insulate the applications programmer as much as possible from the details of the particular computer complex being used, especially memory and other hardware features, and such accessory devices as communications, printers, readers, displays, keyboards, etc. Specific kinds of system software include: • • • • • •
Loading programs Operating systems (and their components, many of which are classified as system software) Device drivers Linkers Utility software Desktop environment / Graphical user interface/ Management Software
• • • •
Shell BIOS Hyper visors Boot loaders
If system software is stored on non-volatile memory such as integrated circuits, it is usually termed firmware. 2.9 Project Management Software: Project management software is a term covering many types of software, including scheduling, cost control and budget management, resource allocation, collaboration software, communication, quality management and documentation or administration systems, which are used to deal with the complexity of large projects. 2.9.1 Definition: Project Management Software are software programs that help with applying knowledge, skills, tools and techniques to plan and control resources, costs and schedules to meet the requirements of the particular project and include such integrated functions as calendars, charts, tracking of people and budgets, generating of reports and scheduling. 2.9.2 Why project management software? Any project in business, from the introduction of a new brand of soap to the design of a new Airbus needs managing. All the different elements: personnel, components, paperwork have to come together at the right times to produce a smooth flow and ensure deadlines are hit and money’s not lost. There are plenty of software tools that can help with this kind of management, from visual aids like Gantt charts and time sheet tracking, to collaborative controls, enabling whole teams to work better together. These are the key types of software within the Project Management sphere: Gantt Chart – a type of chart, which shows the relationships of the various elements in a project, over time. It can be used to monitor the progress of a project and to indicate which elements are crucial to meeting project deadlines. PERT Chart – PERT stands for Program Evaluation and Review Technique and offers similar functions to Gantt, including easy determination of the critical path within the project. Bug and Defect Tracking – provides assistance in tracking the handling of reported errors or faults in physical or software products. Time Sheet Tracking – when project members are dividing their time between multiple projects, time sheet tracking is used to record time spent on each task and issue time sheets for management approval each week, month etc.
Group Management – a general term covering aspects such as a specialist e-mail message center for those involved in a specific project or a discussion forum for project-related chat within an intranet. 2.10 Tasks or activities of project management software: Scheduling: One of the most common tasks is to schedule a series of events, and the complexity of this task can vary considerably depending on how the tool is used. Some common challenges include: Events which depend on one another in different ways or dependencies Scheduling people to work on and resources required by, the various tasks commonly termed resource scheduling. Dealing with uncertainties in the estimates of the duration of each task. Arranging tasks to meet various deadlines. Juggling multiple projects simultaneously to meet a variety of requirements. Calculating Critical Path: In many complex schedules, there will be a critical path, or series of events that depend on each other, and whose durations directly determine the length of the whole project (see also critical chain). Some software applications (for example, Dependency Structure Matrix solutions) can highlight these tasks, which are often a good candidate for any optimization effort. Providing information: Project planning software needs to provide a lot of information to various people, to justify the time spent using it. Typical requirements might include: • • • • • • •
Tasks lists for people, and allocation schedules for resources Overview information on how long tasks will take to complete Early warning of any risks to the project Information on workload, for planning holidays Evidence Historical information on how projects have progressed, and in particular, how actual and planned performance are related Optimum utilization of available resource
2.11 Approaches to project management software: Desktop: Project management software can be implemented as a program that runs on the desktop of each user. This typically gives the most responsive and graphically-intense style of interface. Desktop applications typically store their data in a file, although some have the ability to collaborate with other users (see below), or to store their data in a central database. Even a filebased project plan can be shared between users if it's on a networked drive and only one user accesses it at a time. Desktop applications can be written to run in a heterogeneous environment of multiple operating systems, although it's unusual. Web-based: Project management software can be implemented as a Web application, accessed through an intranet or extranet using a web browser. This has all the usual advantages and disadvantages of web applications: • • • • • • •
Can be accessed from any type of computer without installing software Ease of access-control Naturally multi-user Only one software version and installation to maintain Typically slower to respond than desktop applications Project information not available when the user (or server) is offline. Some packages do allow the user to "go-offline" .
Personal: A personal project management application is one used at home, typically to manage lifestyle or home projects. There is considerable overlap with single user systems, although personal project management software typically involves simpler interfaces. See also nonspecialized tools below. Single user: A single-user system is programmed with the assumption that only one person will ever need to edit the project plan at once. This may be used in small companies or ones where only a few people are involved in top-down project planning. Desktop applications generally fall into this category.
Collaborative: A collaborative system is designed to support multiple users modifying different sections of the plan at once, for example, updating the areas they personally are responsible for such that those estimates get integrated into the overall plan. Web-based tools, including extranets, generally fall into this category, but have the limitation that they can only be used when the user has live Internet access. To address this limitation, client-server-based software tools exist that provide a Rich Client that runs on users' desktop computer and replicate project and task information to other project team members through a central server when users connect periodically to the network and other tasks. Some tools allow team members to check out their schedules (and others' as read only) to work on them while not on the network. When reconnecting to the database, any changes are synchronized with the other schedules. Integrated: An integrated system combines project management or project planning, with many other aspects of company life. For example, projects can have bug tracking issues assigned to each project, the list of project customers becomes a customer relationship management module, and each person on the project plan has their own task lists, calendars, and messaging functionality associated with their projects. Similarly, specialized tools like Source Forge integrate project management software with source control (CVS) software and bug-tracking software, so that each piece of information can be integrated into the same system. 2.12 Software development activities: The activities of the software development process represented in the waterfall model are discussed here. There are several other models to represent this process. We use the waterfall model to develop the Inventory Management System Software.
Figure 2.12: Waterfall Model 2.12.1 Planning: The important task in creating a software product is extracting the requirements or requirements analysis. Customers typically have an abstract idea of what they want as an end result, but not what software should do. Incomplete, ambiguous, or even contradictory requirements are recognized by skilled and experienced software engineers at this point. Frequently demonstrating live code may help reduce the risk that the requirements are incorrect. Once the general requirements are gleaned from the client, an analysis of the scope of the development should be determined and clearly stated. This is often called a scope document. Certain functionality may be out of scope of the project as a function of cost or as a result of unclear requirements at the start of development. If the development is done externally, this document can be considered a legal document so that if there are ever disputes, any ambiguity of what was promised to the client can be clarified. 2.12.2 Design: Domain Analysis is often the first step in attempting to design a new piece of software, whether it is an addition to existing software, a new application, a new subsystem or a whole new system. Assuming that the developers (including the analysts) are not sufficiently knowledgeable in the subject area of the new software, the first task is to investigate the so-
called "domain" of the software. The more knowledgeable they are about the domain already, the less work required. Another objective of this work is to make the analysts, who will later try to elicit and gather the requirements from the area experts, speak with them in the domain's own terminology, facilitating a better understanding of what is being said by these experts. If the analyst does not use the proper terminology it is likely that they will not be taken seriously, thus this phase is an important prelude to extracting and gathering the requirements. If an analyst hasn't done the appropriate work confusion may ensue: "I know you believe you understood what you think I said, but I am not sure you realize what you heard is not what I meant.� 2.12.3 Specification: Specification is the task of precisely describing the software to be written, possibly in a rigorous way. In practice, most successful specifications are written to understand and fine-tune applications that were already well-developed, although safety-critical software systems are often carefully specified prior to application development. Specifications are most important for external interfaces that must remain stable. A good way to determine whether the specifications are sufficiently precise is to have a third party review the documents making sure that the requirements and Use Cases(A use case in software engineering and systems engineering is a description of a system’s behavior as it responds to a request that originates from outside of that system) are logically sound. 2.12.4 Architecture: The architecture of a software system or software architecture refers to an abstract representation of that system. Architecture is concerned with making sure the software system will meet the requirements of the product, as well as ensuring that future requirements can be addressed. The architecture step also addresses interfaces between the software system and other software products, as well as the underlying hardware or the host operating system. 2.12.5 Detailed Design The Detailed Design is kind of the translation of architecture, which is a concrete operational guide following architecture. It may cover programming specific designs, UI and validations, database structure, and also embed design pattern and best practice. 2.12.6 Implementation, testing and documenting: Implementation is the part of the process where software engineers actually program the code for the project. Software testing is an integral and important part of the software development process. This part of the process ensures that bugs are recognized as early as possible. Documenting the internal design of software for the purpose of future maintenance and enhancement is done throughout development. This may also include the authoring of an API, be it external or internal.
2.12.7 Deployment and maintenance: Deployment starts after the code is appropriately tested, is approved for release and sold or otherwise distributed into a production environment. Software Training and Support is important because a large percentage of software projects fail because the developers fail to realize that it doesn't matter how much time and planning a development team puts into creating software if nobody in an organization ends up using it. People are often resistant to change and avoid venturing into an unfamiliar area, so as a part of the deployment phase, it is very important to have training classes for new clients of your software. Maintenance and enhancing software to cope with newly discovered problems or new requirements can take far more time than the initial development of the software. It may be necessary to add code that does not fit the original design to correct an unforeseen problem or it may be that a customer is requesting more functionality and code can be added to accommodate their requests. It is during this phase that customer calls come in and you see whether your testing was extensive enough to uncover the problems before customers do. If the labor cost of the maintenance phase exceeds 25% of the prior-phases' labor cost, then it is likely that the overall quality, of at least one prior phase, is poor. In that case, management should consider the option of rebuilding the system (or portions) before maintenance cost is out of control. Bug Tracking System tools are often deployed at this stage of the process to allow development teams to interface with customer/field teams testing the software to identify any real or perceived issues. These software tools, both open source and commercially licensed, provide a customizable process to acquire, review, acknowledge and respond to reported issues. Tools & Technologies used 3.1 Back End: ORACLE 9i: ORACLE 9i can be used as back end. Using ORACLE 9i tablespaces can identify, create users, adding users, table creation, and inserting value in the table, modify table and many more works can perform. 3.1.1 Introduction to Tablespaces, Datafiles and Control Files: Oracle stores data logically in tablespaces and physically in datafiles associated with the corresponding tablespaces. Figure 3.1.1(a) illustrates this relationship.
Figure 3.1.1(a) Datafiles and Tablespaces Databases, tablespaces, and datafiles are closely related, but they have important differences: • •
•
An Oracle database consists of one or more logical storage units called tablespaces, which collectively store all of the database's data. Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that conform to the operating system in which Oracle is running. A database's data is collectively stored in the datafiles that constitute each tablespace of the database. For example, the simplest Oracle database would have one tablespace and one datafile. Another database can have three tablespaces, each consisting of two datafiles (for a total of six datafiles).
Oracle-Managed Files: Oracle-managed files eliminate the need for us, the DBA, to directly manage the operating system files comprising an Oracle database. We specify operations in terms of database objects rather than filenames. Oracle internally uses standard file System interfaces to create and delete files as needed for the following database structures: • Tablespaces • Online redo log files
•
Control files
Through initialization parameters, we specify the file system directory to be used for a particular type of file. Oracle then ensures that a unique file, an Oracle-managed file, is created and deleted when no longer needed. Allocate More Space for a Database: The size of a tablespace is the size of the datafiles that constitute the tablespace. The size of a database is the collective size of the tablespaces that constitute the database. We can enlarge a database in three ways: • • •
Add a datafile to a tablespace Add a new tablespace Increase the size of a datafile
When we add another datafile to an existing tablespace, we increase the amount of disk space allocated for the corresponding tablespace. Figure 3.1.1(b) illustrates this kind of space increase. When we add another datafile to an existing tablespace, we increase the amount of disk space allocated for the corresponding tablespace. Figure 3.1.1(b) illustrates this kind of space increase.
Figure 3.1.1(b) Enlarging a Database by Adding a Datafile to a Tablespace Alternatively, we can create a new tablespace (which contains at least one additional datafile) to increase the size of a database. Figure 3.1.1(c) illustrates this.
Figure 3.1.1(c) Enlarging a Database by Adding a New Tablespace The third option for enlarging a database is to change a datafiles size or let datafiles in existing tablespaces grow dynamically as more space is needed. We accomplish this by altering existing files or by adding files with dynamic extension properties. Figure 3.1.1(d) illustrates this.
Figure 3.1.1(d) Enlarging a Database by Dynamically Sizing Datafiles 3.1.2 Tablespace Overview:
A database is divided into one or more logical storage units called tablespaces. Tablespaces are divided into logical units of storage called segments, which are further divided into extents. Extents are a collection of contiguous blocks. Following topics related about tablespace: • •
The SYSTEM Tablespace Undo Tablespaces
•
Default Temporary Tablespace
•
Using Multiple Tablespaces
•
Managing Space in Tablespaces
•
Multiple Block Sizes
•
Online and Offline Tablespaces
•
Read-Only Tablespaces
•
Temporary Tablespaces for Sort Operations
•
Transport of Tablespaces Between Databases
The SYSTEM Tablespace: Every Oracle database contains a tablespace named SYSTEM, which Oracle creates automatically when the database is created. The SYSTEM tablespace is always online when the database is open. To take advantage of the benefits of locally managed tablespaces, we can create a locally managed SYSTEM tablespace, or we can migrate an existing dictionary managed SYSTEM tablespace to a locally managed format. In a database with a locally managed SYSTEM tablespace, dictionary tablespaces cannot be created. It is possible to plug in a dictionary managed tablespace using the transportable feature, but it cannot be made writable. The Data Dictionary: The SYSTEM tablespace always contains the data dictionary tables for the entire database. The data dictionary tables are stored in datafile 1. PL/SQL Program Units Description: All data stored on behalf of stored PL/SQL program units (that is, procedures, functions, packages, and triggers) resides in the SYSTEM tablespace. If the database contains many of
these program units, then the database administrator must provide the space the units need in the SYSTEM tablespace. 3.1.2 Users in ORACLE 9i: In Oracle terminology, a user is someone who can connect to a database (if granted enough privileges) and optionally (again, if granted the appropriate privileges) can own objects (such as tables) in the database.
The objects a user owns are collectively called schema. A schema, on its part, is always bound to exactly one user. Because there is obviously a 1 to 1 relationship between a user and a schema, these two terms are often used interchangeable.
In order to find out what users are created on the database, one can use dba_users (Has a record for each user in the database.) Types of Users: A user is either • • •
a local user, an external user, or a global user
Local users: A local user needs a password to log on to the database.
External users: An external user, unlike a local user, doesn't need a password to log on to the database; instead, an external service (such as the operating system) authenticates the user when (s) he logs on the database.
Global users: A global user, like an external user, doesn't need a password to log on to the database, instead, (s) he is authenticated by an enterprise directory service (such as X.509).
Database rights (Privileges): Users can be assigned rights what they're allowed to do in a database and what not. These rights are called privileges.
Privileges: A privilege is a right to execute an SQL statement or to access another user's object. In Oracle, there are two types of privileges: system privileges and object privileges. A privilege can be assigned to a user or a role. The set of privileges is fixed, that is, there is no SQL statement like create privilege xyz... Creation of users: Users are created with create user. Passwords: A user needs a password to create a session. The password is stated in the identified by password in the create user statement. In order to change the password, alter user SOME_USER identified by NEW_PASSWORD can be used. Alternatively, the password can also be changed with the SQL*Plus command password. It's possible to create password limits with profiles. Profiles: A user can be assigned a profile which limits the resources for a user or assigns password limits to a user. 3.2 Front End: Developer 6i
3.2.1 Forms:
3.2.1.1 What is a data entry form?
A primary task of commercial application developers is to design data-entry forms that look as close to the printed sheets of paper which are used in the manual system of data collection. Hence when a data entry operator switches from paper/pen to keyboard/VDU (Visual Display Unit) what is seen on the VDU looks completely familiar.
Oracle Forms is a software product for creating screens that interact with an Oracle database. It has a typical IDE including an object navigator, property sheet and code editor that uses PL/SQL.
Oracle Form Builder provides various tools which have powerful Graphical User Interfaces (GUI’s) to design such forms. All objects, properties, triggers required by a computerized form can be selected and manipulated by simply clicking on an appropriate icon. A Form Wizard is provided as part of the tool set.
The components of Forms Builder, Oracle’s GUI based forms creation tool are:
Forms Builder Forms Compiler Forms Runtime
3.2.1 Application Development in Forms:
3.2.1.1 Form Module:
The primary object created using Form Builder is a form. The Form module is nothing but a collection of objects such as blocks, canvases, frames, items and event based PL/SQL code blocks bound to triggers housed in an M.S. windows command window.
3.2.1.2 Menus: The menu module is a collection of objects such as menu items, sub menu items and event based PL/SQL code blocks that cause form-based work to be carried out.
3.2.1.3 PL/SQL Libraries:
The library module is a collection of PL/SQL functions and procedures stored in a single library file. This library file is then attached to a form/menu module. All other objects in the form or menu can now access and share the collection of PL/SQL function and procedures.
3.2.1.4 Object Libraries:
In a development environment, standards needs to be set and maintained. Standards can be maintained by developing common objects, which can be reused throughout a commercial application as well as across applications.
Object Libraries provides an easy method of creating and storing reusable objects and thus enforcing standards.
An Object Library can be created to:
Create, store, maintain, and distribute standard and reusable objects Rapidly create applications by dragging and dropping predefined objects on to form.
3.2.1.5 Database Objects: Oracle’s interactive tool (i.e. SQL*Plus) allows the creation of Database objects like stored procedures, Stored Functions and Database Triggers using appropriate SQL and PL/SQL syntax.
3.2.3 A Form Module:
Figure-3.2.3 A Form Module
A Form Module consists of the following components:
Blocks:
Blocks Items Frames Canvas views Window PL/SQL Code blocks
A Form contains one or more blocks. Blocks are logical containers and have no physical representation. Only the items contained in a block are visible in the form interface. A block can be conceptualized as a parent container object that holds a related group of child objects such as text items, radio buttons, and command buttons, check boxes and so on for capturing, displaying, and manipulating table data. Each block is bound to a set of properties that determine the behavior of the block. A block connected to a database object is called a Data aware block. A block not connected to any database is called control block. A block can be connected to a database object like a table, view or synonym. A block can also be connected to Stored Procedures. Each data block can be directly related to a (single) database table, view or synonym. A table, view or synonym connected to a block is known as a Base Table. Each column of the base table may have an associated block item bound to it. Items: Items are objects contained in blocks. At the most basic level, items serve as containers for data. The user can manipulate values in an item physically or via a PL/SQL code block bound to a trigger. The items in a block are usually bound to columns in a base table. Entering data into such items will cause the values entered to be stored in columns of the base table. Alternatively, items can be filled with data from the base table by performing an SQL query on the base table. Items need not always be bound to the columns of a base table. They can also hold calculated values, display related information from associated tables or accept operator input for processing later. Items not connected to a base table are called Control Items. Oracle Forms Builder supports several types of items that can be used to build a form. The items supported by Oracle Forms Builder are described below: Item Name
Item Icon
ActiveX control Bean area chart item check box display item Heirarchical item image item
tree
Description A custom control that simplifies the building and enhancing of user interfaces. An identification of a JavaBean that supplies a control for this item. A bordered rectangle of any size that can display a chart or other display generated by Graphics Builder. Operators cannot navigate to or manipulate chart items. A text label with a graphic state indicator that displays the current value as either checked or unchecked. Clicking on a check box toggles it to the opposite state. A read-only text box whose value must be fetched or assigned programmatically. Operators cannot navigate to a display item or edit the text it contains. An area which displays grouped data in the form of a standard navigator with expandable nodes. A bordered rectangle of any size that can display images fetched from the database or read in from the file system.
list item OLE container radio group sound item text item User area VBX control
A list of choices displayed as either a drop-down list, a list box, or a combo box. An area that stores and displays an OLE object that is created from an OLE server application. A group of radio buttons, one of which is always selected. Sound items allow the user to load, record, edit, play, and save sounds. A single- or multi-line text box that supports a variety of data types, format masks, and editing capabilities. An area that stores and displays a user-defined item. For use only in Microsoft Windows 3.1.
Frames: A frame is a graphic object that appears on a canvas. Frames are used to visually contain items that belong to a block. A frame provides programmers a convenient form object whose properties and events can be used to collectively control all the objects it contains. • A frame is an object with properties • Each frame can be associated with a block • When a frame is associated with a block, the items in the block are automatically arranged within the frame. • Frames can be sub-classed or included within an Object Library to enforce visual standards Canvas View: The canvas-view is the background on which Items, Text and Graphics or Frames are placed. The items in a block can be placed on different canvas-views. Each canvas-view can be displayed in different windows. The Oracle Forms, Block wizard allows items to be placed on different types of Canvas like Content and Tab. Window: Every new form is automatically held in a default window named WINDOW1. Additional windows can be created as needed by inserting them in the Object Navigator under the Windows node. The Object Navigator can also be used to rename Windows appropriately. For each window created, at least one canvas-view must be created. The canvas-view is the background on which Items are placed. The canvas-view is linked to a window by binding its name to the Window’s canvas view property. Window properties can be set at design or runtime. Programmatically setting window properties at run time determines the appearance and functionality of the window at runtime. PL/SQL Code Blocks:
Data manipulation can be done using the default form created by the Oracle Forms wizard. Forms created by the Forms Wizard can be customized to suit a commercial application or to map to the standards set for an application. The Oracle Forms tool provides suitable facilities to attach PL/SQL code blocks to Events built into all Form objects. This allows the form to map to the object oriented standard of Event driven form processing. Events can be conceptualized as PL/SQL code holders that automatically execute the PL/SQL code when they occur. Based on application requirements, an appropriate event must be selected and PL/SQL code bound to it. For example, if data needs to be validating to ensure input/output or business rules, PL/SQL code could be written in the WHEN-VALIDATE-ITEM event. PL/SQL code written in this event gets executed when form cursor navigation occurs. Every form contains at least one block, one window, one canvas and one or more items. Each item on the form has a set of attributes or characteristics, which determine how the item behaves at run time. Form items are named so that code in PL/SQL blocks used for form processing can reference them. Software Development Process and Activities of Inventory Management System Software 4.1 Project Planning and Oversight: Our first step of developing the software was planning the preliminary steps for the software development. The important task in creating a software product is extracting the requirements or requirements analysis. The whole overview of the current situation was recorded. 4.1.1 Requirement Analysis: Overview: Conceptually, requirements analysis includes three types of activity: •
• •
Eliciting requirements: the task of communicating with customers and users to determine what their requirements are. This is sometimes also called requirements gathering. Analyzing requirements: determining whether the stated requirements are unclear, incomplete, ambiguous, or contradictory, and then resolving these issues. Recording requirements: Requirements might be documented in various forms, such as natural-language documents, use cases, user stories, or process specifications.
Requirements analysis can be a long and arduous process during which many delicate psychological skills are involved. New systems change the environment and relationships between people, so it is important to identify all the stakeholders, take into account all their needs and ensure they understand the implications of the new systems. Analysts can employ several techniques to elicit the requirements from the customer. Historically, this has included such things as holding interviews, or holding focus groups (more aptly named in this context as
requirements workshops) and creating requirements lists. More modern techniques include prototyping, and use cases. Where necessary, the analyst will employ a combination of these methods to establish the exact requirements of the stakeholders, so that a system that meets the business needs is produced. 4.1.1.1 Stakeholder identification: Stakeholders (SH) are persons who will be affected by the system, either directly or indirectly. A major new emphasis in the 1990s was a focus on the identification of stakeholders. It is increasingly recognized that stakeholders are not limited to the organization employing the analyst. In our process obviously Standard Bank IT Department is the stake holder. 4.1.1.2 Stakeholder interviews: Stakeholder interviews are a common technique used in requirement analysis. These interviews may reveal requirements not previously envisaged as being within the scope of the project, and requirements may be contradictory. However, each stakeholder will have an idea of their expectation or will have visualized their requirements. We appointed several interviews with our stake holders. We had interviews with some spokes person. 4.1.1.3 Contract-style requirement lists: One traditional way of documenting requirements has been contract style requirement lists. In a complex system such requirements lists can run to hundreds of pages. An appropriate metaphor would be an extremely long shopping list. Such lists are very much out of favor in modern analysis; as they have proved spectacularly unsuccessful at achieving their aims; but they are still seen to this day. 4.1.2 Types of Requirements: Requirements are categorized in several ways. The following are common categorizations of requirements that relate to technical management: Customer Requirements: Statements of fact and assumptions that define the expectations of the system in terms of mission objectives, environment, constraints, and measures of effectiveness and suitability (MOE/MOS). The customers are those that perform the eight primary functions of systems engineering, with special emphasis on the operator as the key customer. Operational requirements will define the basic need and, at a minimum, answer the questions posed in the following listing: • Operational distribution or deployment: Where will the system be used?
• • • • • •
Mission profile or scenario: How will the system accomplish its mission objective? Performance and related parameters: What are the critical system parameters to accomplish the mission? Utilization environments: How are the various system components to be used? Effectiveness requirements: How effective or efficient must the system be in performing its mission? Operational life cycle: How long will the system be in use by the user? Environment: What environments will the system be expected to operate in an effective manner?
Functional Requirements: Functional requirements explain what has to be done by identifying the necessary task, action or activity that must be accomplished. Functional requirements analysis will be used as the top level functions for functional analysis. Non-functional Requirements: Non-functional requirements are requirements that specify criteria that can be used to judge the operation of a system, rather than specific behaviors. Performance Requirements The extent to which a mission or function must be executed; generally measured in terms of quantity, quality, coverage, timeliness or readiness. During requirements analysis, performance (how well does it have to be done) requirements will be interactively developed across all identified functions based on system life cycle factors; and characterized in terms of the degree of certainty in their estimate, the degree of criticality to system success, and their relationship to other requirements. Design Requirements: The “build to,” “code to,” and “buy to” requirements for products and “how to execute” requirements for processes expressed in technical data packages and technical manuals. Derived Requirements: Requirements that are implied or transformed from higher-level requirement. For example, a requirement for long range or high speed may result in a design requirement for low weight. Allocated Requirements: A requirement that is established by dividing or otherwise allocating a high-level requirement into multiple lower-level requirements.
4.1.3 Existing Inventory Management System in company: Most of the company maintains their inventory with Microsoft Excel till now. This process is quit old and they need a better management system. There is a problem of searching, as Microsoft Excel does not have any modern searching operation. There is also a problem of calculating some data. Our goal and challenge was to build user-friendly inventory management software for any company and provide them the best possible service. We have chosen Oracle 9i as our back end and developer 6i as front end and hope this latest frame work will satisfy our users. Figure 4.1.3: Sample diagram of existing inventory management system of a company
From above screen shot, it is clearly understood, the existing inventory management system is kind of old file cabinet system. For modern era it is quite impossible to maintain all of a company’s assets like this. The insertion and the retrievation of these data are extremely hard.
So we investigated holder’s needs and made a list of requirement. Our requirement analysis is as simple as that.
4.1.4 Required Features for Inventory Management Software: •
•
Data Insertion: Insert all data of a product like the service tag, product name, brand, model, unit price, warranty, depreciation period, vendor name, challan number, delivery date, bill number, bill date, remarks etc in database. Data Retrieve: Retrieve all inserted data by searching option anytime.
•
Searching Option: This is the most effective feature of the inventory management software. User can search by product category, by vendor, by brand, by serial number and also in a particular department.
•
Allocation: Allocating the products from the store to different department of a company.
•
Repairing: Sending non functional products to vendor for warranty claim.
4.2 Database Diagram for Inventory Management System: Figure 4.2: Database Diagram
4.2.1 Attributes of the Entities: PRODUCT: • PRO_ID(PK) • PRO_NAME • BRAND • Unit_Price • VENDOR_NAME(FK) • WARRANTY • REMARKS PURCHASE: • CHALLAN_NO (PK) • ITEM_NO (PK) • PUR_DATE • PRO_ID (FK) • QUANTITY • UNIT_PRICE • VENDOR_NAME (FK)
VENDOR: • VENDOR_NAME (PK) • ADDRESS • CONTACT_NO • STATUS [ SERVICE_TAG: • SERV_TAG (PK) • PRO_ID DEPARTMENT: • DEPT_ID (PK) • DEPT_NAME ISSUE: • • • • • • •
ISSUE_ID (PK) ITEM_NO (PK) ISSUE_DATE PRO_ID (FK) RECEIVED_BY SERV_TAG DEPT_ID (FK)
4.3 Project implementation: Back End: Oracle 9i: 4.3.1 Creating a DBA Login ID: Click on Start. Then select Programs Oracle-OraHome90 Manager Console to activate Enterprise Manager Console.
Enterprise
This will activate the Oracle Enterprise Manager Login dialog box. Select the option Launch standalone and click on the OK button. In the Oracle Enterprise Manager Console window, select and expand the second node belonging to the Global Database Name specified during installation. This will display the Database Connect Information dialog box as shown in diagram 4.3 .1(1.4). In this dialog box enter the username as system along with its appropriate Password and click on OK to continue.
Figure 4.3.1(1.1): Creating User When the node expands click on the plus sign besides the security node to expand it. Then expand the Users node by clicking on the plus sign besides it to get a list of user existing under oracle 9i for the current database refer to the figure 4.3.1(1.1). Right click on the User node and select the create option from the popup menu as shown in the Figure 4.3.1(1.1). This will open a new window.
Figure 4.3.1(1.2): Create User Enter DBA_SCT as the value of the Name field. Make appropriate entries for the Enter Password and the Confirm password fields. In the tablespaces frame, select the default tablespace as System. Click on the tab named Role to get a screen as shown in figure below. Select the Role named DBA and click on
to add to the role.
Figure 4.3.1(1.3): Role of the DBA After the new role is added, click on the cell below Admin Option to change the (cross) sign to the (check) sign refer to the above figure. Click on the tab mark named System privileges to get a screen as shown below:
Figure 4.3.1(1.3): System privileges of the DBA Select from the list of System privileges and click on to add to them. Include privileges required to perform basic SQL operations like: • Creating, modifying and deleting tables or views • Inserting, modifying and deleting data within self created tables • Commit or Rollback SQL operations • Grant privileges on owned objects Additionally, add the following privileges: • ALTER TABLESPACE • CREATE TABLESPACE • DROP TABLESPACE Then for each of the following privileges, click on the cell below Admin Option to change the (cross) sign to the (check) sign. Click on Create button to create the new user with DBA rights. Message indicating the successful user creation.
Figure 4.3.1(1.4) Successful user creation 4.3.2 Creating user named Inventory: The Database Administrator (DBA) can use the Enterprise Manager Console utility to create new users who will use Oracle 9i resources. Users created by the DBA will have limited privileges, being restricted to creating and managing tables or views within a single tablespace. The technique of creating a user, under Oracle 9i, is similar to creating the DBA. The difference is in the set of privileges assigned to a user. Click on Start. Then select Programs Oracle-OraHome90 Console to activate Enterprise Manager Console.
Enterprise Manager
This will activate the Oracle Enterprise Manager Login dialog box. Select the option Launch standalone and click on the OK button. In the Oracle Enterprise Manager Console window, select and expand the second node belonging to the Global Database Name specified during installation. In this dialog box enter the username as DBA along with its appropriate Password and click on OK to continue.
Figure 4.3.2(1.1): Database Connect Information
When the node expands click on the plus sign besides the security node to expand it. Then expand the Users node by clicking on the plus sign besides it to get a list of user existing under oracle 9i for the current database refer to the figure 4.3.2(1.1). Right click on the User node and select the create option from the popup menu as shown in the Figure 4.3.2(1.1). This will open a new window.
Figure 4.3.2(1.2): Creating User named Inventory1 Enter an appropriate user name value in the Name field. Then appropriate values in the Enter Password and the Confirm Password fields. In the Tablespaces frame, click on the default field and select P for the popup menu, refer to the figure 4.3.1(1.2). Click on the tab mark named Role. A screen as shown in the following figure appears.
Figure 4.3.2(1.3): Role of the user Click on the cell below Admin Option to change the sign refer to the above figure.
(cross) sign to the
Click on the tab mark named System privileges to get a screen as shown below:
(check)
Figure 4.3.2(1.4): System privileges of the Select from the list of system privileges and click on to add to them. Include privileges required to perform basic SQL operations like: • Creating, modifying and deleting tables or views • Inserting, modifying and deleting data within self created tables • Commit or Rollback SQL operations • Grant privileges on owned objects Click on Create button to create the new user. Message indicating the successful user creation. We have created user Inventory for our project.
Figure 4.3.2(1.5) Successful user creation
Figure 4.3.2(1.6): Users in Oracle 9i 4.3.3 Creating the script for tables: CREATE TABLE VENDOR( VENDOR_NAME VARCHAR(20) PRIMARY KEY, ADDRESS VARCHAR(20), CONTACT_NO VARCHAR(20), STATUS VARCHAR(20)
); CREATE TABLE PRODUCT( PRO_ID VARCHAR(20) PRIMARY KEY, PRO_NAME VARCHAR(20), BRAND VARCHAR(20), UNIT_PRICE NUMBER(20), VENDOR_NAME VARCHAR(20), WARRANTY NUMBER(10), REMARKS VARCHAR(20), FOREIGN KEY(VENDOR_NAME) REFERENCES VENDOR(VENDOR_NAME) ); CREATE TABLE PURCHASE( CHALLAN_NO VARCHAR(20)PRIMARY KEY, ITEM_NO VARCHAR(20)PRIMARY KEY, PUR_DATE DATE, PRO_ID VARCHAR(20), QUANTITY VARCHAR(20), UNIT_PRICE NUMBER(20), VENDOR_NAME VARCHAR(20), FOREIGN KEY(PRO_ID) REFERENCES PRODUCT(PRO_ID), FOREIGN KEY(VENDOR_NAME) REFERENCES VENDOR(VENDOR_NAME) ); CREATE TABLE DEPARTMENT( DEPT_ID VARCHAR(20)PRIMARY KEY, DEPT_NAME VARCHAR(20) ); CREATE TABLE ISSUE( ISSUE_ID VARCHAR(20) PRIMARY KEY, ITEM_NO VARCHAR(20) PRIMARY KEY, ISSUE_DATE DATE, PRO_ID VARCHAR(20), QUANTITY VARCHAR(20), RECEIVED_BY VARCHAR(20), SERV_TAG VARCHAR(20), DEPT_ID VARCHAR(20), FOREIGN KEY(PRO_ID)REFERENCES PRODUCT(PRO_ID), FOREIGN KEY(DEPT_ID)REFERENCES DEPARTMENT(DEPT_ID) ); CREATE TABLE SERVICE_TAG(
SERV_TAG VARCHAR(20)PRIMARY KEY, PRO_ID VARCHAR(20), FOREIGN KEY(PRO_ID)REFERENCES PRODUCT(PRO_ID) );
We have created tables by executing SQL command in the SQL *PLUS.
At first we have logged in SQL* PLUS by entering username as inventory and appropriate password as shown in figure below:
Figure 4.3.2(1.7): Connecting SQL* PLUS 4.4 Project implementation: Front End: Developer 6i: 4.4.1 New Product Purchase: When new products come to hand by purchase they must be added into the inventory list to keep further record about those products. The input page enables users to enter new product
information. It contains all the necessary fields like product id, product name, brand, model no, price, warranty, depreciation period, remark about the product, vendor name from which the product has been bought, purchase date, challan no etc. Fig. 4.4.1(1.1) shows the purchase form for entering new product.
Figure 4.4.1(1.1): New product purchase form 4.4.2 Product & Vendor Information: If we want to get information about a specific product, we can get that from this form. We can also insert new product here. It is also possible to get vendor information like address or contact number etc. we can get from here.
Figure 4.4.1(1.2): Product & Vendor information form 4.4.3 Allocation:
What products are issuing in which department of a company, quantity, who is taking etc. can be known from this form. We can also get a report of this information using report builder.
Figure 4.4.1(1.3): Product issuing form 4.4.4 Search the stock: To allocate a product or a number of products to a department, first we have to know what products are currently available in the stock.
Figure 4.4.1(1.3): Stock form 4.4.5 Auto completion: An attractive feature of this application is the auto completion of texts in the brand and model field. When a user starts typing something, it automatically comes with some suggestion similar to the input text that was entered earlier. Users don’t need to type the whole text, just scroll and select whatever user wants.
4.4.6 Easy selection of vendor name: When entering the vendor name, user can unfortunately type a wrong name which can result to a great problem for further searching. The drop down list enables user to select a vendor name, which has been previously used. 4.4.7 Saving data into the database: Once user is sure about what s/he entered s/he can now save the data permanently to the database. The save button allows to save the data that are being showed in the temporary list. 4.4.8 Search by serial or tag no. of a specific product: In this section of searching, the products or inventory items are searched by a serial or tag no. to find information about a specific product. The result shows the item which has the serial no. Conclusion &Future Work 5.1 Conclusion: As we started developing this project we went through some common problems like where to start, who will start and how. But gradually we have learnt to overcome these problems. First of all we tried to implement our limited knowledge of SDLC on this project. We are glad to say that it worked well. Maybe it was not fully followed but we did our best as some final year students. The systematic sequence of our work helped the project to go on or not ending in some dead loop. Our team spirit and cooperation also went well. We cannot also deny the constant support from our departmental teachers, advisor and our Head of the Department. Saying about the environment of the Standard Bank Limited, it was wonderful as everyone we met was extremely cooperative and friendly. Since every system has some limitations, so the proposed system is also not untouchable in this regard. Although it includes many features but still it would not be sufficient as the user requirements are not always same. The change in the requirements will need some changes in the system to fulfill the requirements. The security of the system will be one of the prime concerns. 5.2 Future work: As the time was limited for our project, it was not possible to fulfill all requirements. Further work can be done on this project like search by different categories, making easy report, security of information etc. References:
Book Referred:
ďƒ˜ Commercial Application Development Using Oracle Developer 2000 Forms 6i by Ivan Bayross.
Fundamental of Software Engineering—Rajib Mall (PHI). System Analysis and Design—Elias M. Awad
Sites Referred:
http://www.google.com
http://en.wikipedia.org/wiki/Inventory
http://download.oracle.com/docs/cd/B10500_01/server.920/a96524/c04spac e.htm#967
http://www.adp-gmbh.ch/ora/concepts/users.html
http://en.wikipedia.org/wiki/Oracle_Forms