Motivation for Building a Business Logic Layer Using PL/SQL - Thesis Example

Name Tutor Course Date Motivation for Building a Business Logic Layer Using PL/SQL Building a business logic layer using PL/SQL is very important and every organization or business should be motivated by the benefits of doing so since they outdo the cost of implementing business logic layer using PL/SQL. For instance, less time is consumed when entering data since the information is coded and coding he or she does not have to determine the code for every item that is being entered (Scott, 45). In addition, it would prevent errors such as selling a product twice which is a common error in the tourism industry. There are occasions where a company sells a room and ends up reselling it due to poor business logic layers where products are not clearly defined. This leads to frustration on the part of the customer and such inconveniencies can make the company loss a lot of customers (Scott, 53). The use of PL/SQL in creating a complete business logic layer will ensure that multimedia data storage into the database is properly done. This would include the storage of photos, sight descriptions and maps. Since PL/SQL would ensure efficiency in doing this, every business should seek to implement it (Scott, 34). Large scale businesses are likely to adopt this while the small scale businesses are likely to be left behind. However, they are all important in the economic development of any country and should be assisted to prevent them from being left aside from the mainstream. This is the role of the government and the large scale businesses. Graphical presentation are easily enhanced when business logic layer is implemented using PL/SQL. This will attract more customers and result to increased profit margins. The business logic layer can be designed in such a way that it addresses different challenges such as difference in language and currency. This is made possible by the use of codes. Europe experiences such problems but the tourism industry can be sustained by building the business logic layer usinf PL/SQL. The solution is simple, universal and easily manageable. The solution does not involve storing Multilanguage translations in any form of files. Instead, the translations are stored in the database. The customer is in a position to access the information in his or her preferred language (Kevin, 56). If one wants to include a new language to the existing application, he/she would only be required to add to the translation table new translations. These are examples of translation tables. Table1. ID of the message message 1 greetings 2 First question Table2. Translations id of message Id of language Translation 1 English Hello! 1 French Comment cava`! 1 Croatian Bok! 1 Deutsch Hallo! The other benefit of a business logic layer is that a file system is not needed since all information and business logic is stored in the database in the form of PL/SQL. The procedures are grouped in packages making accessibility and modification easier. Implementing this package will definitely be a motivation since companies do not have to worry about the technological problems that are involved with application of new technologies and would result to the transition of any country into a networked economy when applied appropriately. The system is also easily expandable, compact and open. The well defined SQL/PL procedures make it modular and flexible thus enabling implementation of new features and upgrading of the system is easy. Building Complete Business Logic Layer by Using PL/SQL PL/SQL is an abbreviation for Procedural Language /Structural Query Language which is one of the programming languages. It is used in Oracle Database together wit SQL and Java. It is an expansion of SQL. This means it came as a result of addition of SQL. It has been availed in different versions. These versions come along as a result of some changes which are made by the software developers. The current versions are more advanced (Mishra and Beaulieu, 504). They are responsive to clients and programmers for they have been developed in such a way that a learner can learn very easily. In deed, currently, learners take little time, and they incur little cost. This language supports variables, loops, exceptions, conditions and even arrays. Variables carry message from the program to database. They are generally various kinds of these variables. The first one is used for database columns. The other is generic which deals with numbers. The third one is a variable that is declared. Loops are constructs in this programming language. They are very imperative though they are complex. They are greatly used when you want to run a program where operations are recurring. Exceptions are set packages. Conditions are statements used in outlining limits that a program cannot go beyond. It has codes which are implanted to the database directly. The units can be retrieved from the database for later use. From the above information, we learn that PL/SQL came about as a result of inefficiency from SQL. To start with SQL can be used with few statements unlike in PL/SQL which has more statements. This is like the loop statements. When executing the statement, only one can be executed in SQL unlike in PL/SQL where several units are executed. In PL/SQL one can be assisted on how carry out commands in n orderly way whereas, in SQL one is shown what to execute. PL/SQL not complicated to study or to apply. It is indeed effective in management and efficient in executing complex operations. PL/SQL use has assisted greatly in cost reduction which was being spent in SQL. PL/SQL is a language that is rated as relatively simple for anyone to learn. It has been integrated with SQL. In fact, it has been strengthened by SQL. Studying PL/SQL is simple because of the use of commands which are in English words. The commands are many in number thus it is to run a long program as you incorporate them all. It has proved effective in the management of database operations in large scale and it is efficient in performing complex operations. The use of loops, conditions and arrays has facilitated its performance. As one uses it, many statements can be sent to be processed. The network load is well utilized with some degree of or no traffic at all. The time spent in transmission of all statements is also minimized. This technology has been developed from SQL Forms which in the past involved spending an extra amount of money to use PL/SQL. Over several decades now, performing activities using oracle has developed from one step to another with PL/SQL evolving into a highly functional, robust and mature database language. According to reasonable and logical expectations, any language that is robust and simple to learn e.g. PL/SQL would automatically be a grantee of efficient, effective, maintainable and readable but this is not the case with PL/SQL. For instance, in Oracle development, among the program developed in a span of recent 20 years, the worst of the have been written with the application of PL/SQL. These mistakes are only identified when major crisis occur. In addition, PL/SQL applications are transferable and protected. In fact, you can use whichever operating system. There is only one demand: that the databases to be used together with the language should be able to run in the system. The security comes as a result of the use of codes. In code writing, limits are put to ensure data is not availed to any one. Information is allowed to be viewed by authorized clients to reduce risks of plagiarism and alterations. Engineering a better PL/SQL would be the most effective solutions to the current crisis in the market that can result to great losses. The manual methods have been found to have many short comings. As a result, this paper will aim at improving the development process of PL/SQL hence solving some major problems that have been experienced in practice. The purpose of doing so is to reduce losses resulting from defective developments. For instance, $59.5 was spent in US in a span of one year (2002) owing to inadequate testing and development of inferior software. The current estimated number of workers in the IT sector in US is 2.5 million. Among these employees, those involved in writing and designing codes are 50% of the total. This means that the PL/SQL developer bug costs are almost $48,000 (Richard, 78). Though there are best practice rules that have been set in programming, these practices have failed in addressing several issues such as effectiveness, measurability, consistency and reliability. This calls for a better procedure in the implementation of business rules that will overcome the challenges stated above. PL/SQL groupings The groupings in PL/SQL include LRH (loose root hierarchy) package through which loose root hierarchy is implemented. Excluding the top-level node, every node can actually have more than one parent node. The storage package changes and creates data on sections and stores among others (Scott, 132). The implementation every store and presentation of every section is inform of LRH. The next package is the product list package. The information included in this package includes identity of the product, its quantity and notes to the customer. The customer baskets and orders are implemented in the basket package and the order package. The product package is used to manage all products and relates them the section on store. Lastly, the Multilanguage package is implemented to manage message translations and also for translating part of the database while the accounting package manages translations. Business logic layer It is a domain layer. It is one of the tiers that is used to separate database tier from client interface. In case their changes or replacements are being made in the other tiers, it can withstand any alterations. Data being fetched or entered in other tiers does not affect this tier. This tier makes it simple for modifications and updates to be made easily without any effect to previous developments that were made. The figure below shows a representation of Business logic layer and how it acts as an intermediary between the two tiers(Kathy, 345). Fig 1: Business logic layer representation. It acts as a representation of the regulations and actions of a business domain. It also manipulates the raw facts and makes sure that they are stable and up to date i.e. they are not invalid. It is also a locality for the logic of a service. Validity is always needed to ensure that when dispensation of raw facts is being carried out, there will be a response. The ability to respond is determined by how valid the data is. Validity also affects arithmetic calculation and performance. Some data may be prepared to be presented; their ability to present is also determined by their validity(Trezzo, 99). PL/SQL as part of a business logic layer In an architecture made of several layers, in this case consider a three- layer application; PL/SQL can be used as a business logic layer. The other layers will be raw facts layer and Presentation layer. In real life situation, we can consider a client who wants a general insurance for his motorbike. The motorist has some facts about the insurance cover but doesn’t know how to go about it. In the process an agent of X Insurance Company approaches the motorist to market out the company’s products and services and explains to him how to acquire the cover. He will then direct the motorist to the company and assist to carry out the transactions. The motorist can be presented in the data layer where facts are kept. The agent acts as a business logic unit whereas the company can be seen as presentation layer. In a data layer, raw facts are stored and due to information passage from one layer to the other, some of it can be kept in this layer for latter use. The work of business logic layer is to process all the facts and synchronize all the activities being conducted between data layer and presentation layer. Once presentation layer receives all the processes required for a specific activity, it gives the desired outcome that is send back to the data layer through the business logic layer. Fig 2: presentation of a three-layered architecture Triggers These are blocks of PL/SQL codes that are performed when an activity is done in the location area of data. There are three basic triggers that normally used. The first one is called include trigger. The others are renew and delete triggers. They are used to modify during PL/SQL programming (Turau, 109) because changes are normally made and in case there mistakes that have been done during programming, they can be corrected. For instance, when some facts have not been fed to the program, you can insert it. The include trigger will be used to facilitate its manipulation. The general procedure used when using triggers is as follows (Anderson and Wendelken, 624): MAKE (OR CHANGE) TRIGGER trigger_name PRIOR TO (or SUBSEQUENT TO) INCLUDE or RENEW [of columns] or DELETE ON table name {DESIGNED FOR EVERY ROW [WHILE (condition)]} START ... FINISH; Stored procedures in PL/SQL When carrying out programming in PL/SQL it is good to note that programs are designed in orderly way. This enables one to be in a position to use codes in the right way such that at the end of the program, there is no defect or fault that can cause the program not be run and be executed in the expected manner. Once this program is prepared it is kept in the database and is known as stored procedure. Actually, the stored procedure in the future can be retrieved to be used in collaboration to other programs (Odewahn, 256). These stored procedures help in maintaining facts’ authenticity and enhance high production. This is done as they facilitate data availability. Data security is always needed in each and every company, to ensure that there are no data alterations, piracy or plagiarism you can protect during PL/SQL programming. This can be done easily by using permissions. This permission gives limit to the number of users who can retrieve stored data. This enabled by allowing some unique and well draw outlined schedule to be followed. To ensure no data delay that can cause minimized performance, statements are not used. Blocks are thus used to replace the statements (Feuerstein and Pribly, 1200). The blocks are usually as follows: DECLARE Variable_declarations START Practical_cipher EXCLUSION Fault_handling FINISH; Declare: This is where a variable is stated. The stating is done depending on the nature of program being prepared. All the variables are stated here. Start: The program is prepared in this section using all forms of statements and conditions applicable. Exclusions: In this portion all errors are managed from here in order to avoid mistakes which can cause the program not to run well. Finish: This used to note the end of a program. Functions in PL/SQL They are unique types of actions that have the ability to call back a value. They are usually used as shown below: MAKE FUNCTION function_name (FUNCTION_PARAMS) RETURN return_type is START FUNCTION_BODY CALL BACK something_of_return_type; FINISH; The make command is used to mean create. Function command gives one a chance to name the kind of function to use. Start shows where the program will begin to run as execution process is being carried out. Function body is the area where all statements are declared. It is the chief body of the program being developed. As a program is being run, some values are likely to be special depending on the nature of the program. Call back command allows such value to be returned and they are usually the end products. Once the return value is achieved the program comes to an immediate end, hence the finish command. For instance, to use the functions in subtracting two number which are integers (int) x and y, you can work it out as follows(Ayers et al, 23): MAKE FUNCTION SUB_TWO (Int X, Int Y) CALL BACK INT IS START CALL BACK (A - B); FINISH; Business rules These are the dealings, declarations and constraints a company needs to apply in order to realize its objectives within the set time. These assist the company to carry out its transactions in quick and faster manner. They can be seen as the company’s undertakings showing the real implementations. In programming, the rules can easily be put into use by use of the various languages we have such as the PL/SQL. In fact these business rules have many advantages to the programmers. To start with, they make people program in a faster way and as a result productivity is high and of good quality. They really make the users to identify the tools to use in each and every program they are about to start. Due to this rules, they make programs very stable and indeed a bit flexible. This can be realized in the case where one can retrieve data from a database made using PL/SQL and link with another program. It is through these rules one can set the programs in a way which is secure and limit the clients who can access the already store facts. If these rules were not set, it would be hard to share data since some clients can decide to make some changes or else distort the whole program (Rosenzweig and Silvestrova, 456). Business logics can be customized by users who are not specialized in programming. This result into assisting them to get the programs they want without necessarily incurring some expenses of creating their own programs. They comprise a regulation Authoring Device which gives the rules to be followed by all users. Secondly, a Regulations engine which is quick and effective is also needed to be involved in the rules setting. Finally, there is SDK which is used to guarantee rules creation by convention applications. These applications are the ones used to amend the regulations(Greenwald et al, 382). These rules are also very essential for they enable processes to be explained in a language all members in a company can understand. During training resources are well utilized. This may be counted in terms of time spent and money used. There is no description of processes in the same way. If duplication is noted, it can be noticed easily and then corrected. Implementing Atomic Use Cases Using Oracle Store Models ought to be precise for them to be subjected to test for disapproval or approval. Precision calls for determination of a programmer as he designs a business logic layer. Atomic use case concept is very important and has proved effective in enhancing the effectiveness and precision of software. This idea can be applied in information systems that are project oriented. In the business, employee’s information such as ID, phone number and name are unique to each individual. The atomic use case can be applied in entering details in the database. Since each employee is unique, the ID can be implemented in such a way it is easy to access full information regarding them. This is important for instance when considering whom to offer a promotion. When a new employee joins the organization, his or her profile is created in the database. If a new employee is to be added, his or her id is entered. The systems portrays an error message if such an id already exists thus preventing duplication and confusion that cost the organization a lot of time and resources. The operation is terminated and a unique id has to be identified. The system automatically creates a new employee when a unique id, name and phone number are entered and the information is saved in the system. Each employee id is very unique. In the first procedure, the name of the person is entered and the system checks if such a person exists, if not, the second transition is carried out where the name and the phone number are entered. The first has no any precondition attached to it. The post condition in this transition determines in which set the created employee with the input data is stored. Transition T3 does not take place if such an id exists hence an error message is sent. The use case overall flow is presented by the graphical part while the happenings are presented in the inscription part. Since PL/SQL can be applied in identifying and automatically creating an ID, the work is made simpler and exemption case is avoided. This is used to retrieve the employees incase their profile need adjustments or needs to be accessed (Kinh and Tharam, 7). The atomic use case can also be used in accessing student’s information from the database using their ids. The system is in such a way that it can maintain its integrity. In this case, it protects itself from invalid requests and has the ability to respond to any request be it valid or invalid. The implemented functional core though the atomic use case in n-tier architecture language acts as a business logic layer. It is very effective since it retrieves information very fast, presents users with from which selection can be done very fast and validation is timely and fast. According to a research by Oracle consulting, there is a lot of ambiguity in the definition of business rules and many errors occur during recording of the rules (Oracle consulting, 1). Codes are duplicated where the implementation framework for the business is missing. This kind of a code gives one difficulties in maintaining it and also jeopardizes the robustness of the server. It also leads to data loss or accessibility by wring clients. Most of the times, if a client knows about he may run away from the designer. In this internet error, having a framework for implementation of business rules is necessary since the technology is changing fast and this demands for flexibility and increased speed in the implementation of business rules. Failure to do this would result to employees that lack motivation, increased risk of errors and loss of business opportunities. According to Oracle cooperation this issues are addresses by the implementation of business rules using CDM Rule frame (Harumi, 103). CDM RuleFrame supports logical three-tier internet applications development. The business logic tier is implemented by this frame while the data tier is created by the Orackle and the presentation tier by the development tools. The CDM RuleFrame includes a combination of productivity boosting utilities, architecture of sophisticated software, a process and concepts. Using the CDM RuleFrame, there are several processes that are involved in the building of the business logic layer(McLaughlin, 302) in the Use Atomic case as shown in the diagram below. Fig 3.  Business Logic Layer (Ullman and Widom, 528) Rule Description The rules are elaborated in details. This makes it easy to comprehend them. They are later kept in business function. They are formed to ensure that as users are programming an error can easily be detected. In other words, violation of the rules is restricted due to description done and then storage is done. Rule drawing Once the rules have been defined, they are design. Drawing involves giving conditions to triggers as the first step. For example, the designer can state what extremes can cause rule defilement. Designing will show how data is used and the kind of messages can be shown when errors occur. Rule types and codes are also developed in the rule drawing process. Rule Frame This basically involves the implementation of the drawn rules in the business logic layer. Actually, this is where the layer can be used in the in databases. Systems that are aimed at being implemented in the internet need to be designed in such a way that they can be accessed from different ends. This includes a front end for internal power users which should be sophisticated and another one for other users which should be simple and easy to use. These systems should be flexible and implementation should be at a given speed hence making it easy to extend and maintain business logic. This is owed to the fact that requirements of the business are continuously changing as new challenges and technologies arise. CDM RuleFrame was found to be effective in the maintenance and implementation of business logic (Oracle Consulting, 1). In application development, the most difficult task is maintaining, implementing, designing and analyzing business rules (Ivan, 443). There are several issues that are faced during business logic implementation. The issues include ambiguous phrasing of business d functions and existence of hidden business rule, difficulties I the estimation of the design phase resulting from undefined needs to be implemented, partiality in the implementation of complex rules when involving multiple entities. Loss of rules when adding others where the implementation of the rules is only done in the front end server robustness and lack of a mechanism for tracking analysis documents that have already been approved, makes verification impossible. Implementation of changes is costly and causes the organization to re-advertise it. This leads to a waste of time and resources (Ritter, 105). The Oracle consulting encourages the use of CDM RuleFrame to solve the above problems. It provides implementation, design and analysis framework for business logic. In Oracle Designer, description of recording the business rules in such a way that it is a separate element is clear. The fact that the relations between presentation layer and data layer are clear simplifies impact analysis. The automatic generation of validation codes for certain rules by the CDM framework saves time and hustles of doing so manually. After the business rules are recorded, they are transformed into design definitions of the business rule by the CDM RuleFrame. This process is similar to transformation into tables of entities and business rules into modules by the Oracle Designer (Rosenzweig and Motivala, 601). For rules that are not automatically transformed to generate the validation code, CDM RuleFrame rule coding is made easier through the provided standard services hence reducing code number lines considerably. The business logic layer deployment packages are generated after coding all the business rules. Complete business logic layer generation or regeneration is possible since the meta data for business rules resides in the Repository of the Oracle Designer. CDM RuleFrame is based on a proven and mature technology and its deployment in the Oracle8i server uses PL/SQL. This has advantages such as availability, scalability and security. Java based technology is allowed and this results to the CDM RuleFrame power being leveraged. This means that CDM RuleFrame can be used with as many front ends as created using the Oracle Deployment tools. These tools include PL/SQL, JDeveloper, WebDB, and Oracle (Rosenweig and Silvestrova, 688). This has several advantages. The framework is known to help in making business rules rapid and simple to execute. Execution is so fast and also uncomplicated. This is attained through the use of a utility which changes the rule breakdown into plan fundamentals. It gives quality services; in deed it ensures that coding is valid. The utilities used by the frame work generate the rules which check code validity and produce deployment packages. The packages are produced for each table. Secondly, rule preservation is made to be quick and undemanding. In the Oracle designer, every fundamental form is positioned in a specified location. There is always a utility that is used to make preservation of rules easy. In addition, the rules are recorded in distinguished fundamental. This is usually done as the rules are being analyzed and designed. The fundamentals can be coupled to each other (Feuerstein, 23). This enables traceability of rules. The other merit of this frame work is the ability to enforce transaction intensity rank. The enforcement guarantees some one to lay down rules into a server where formerly it was impossible. The laying of the rules was only done in the client side. Rule usage is made to be flexible than before due to the high intensity enforcement. This enforcement allows error checking to be done at once. After the check, they are transmitted to the client for correction at ago thus reducing traffic in the network. User response is optimized. The frame work creates an opportunity for all business logics to be used again. This is facilitated due to the use of a front ends which are autonomous. The frame work has also allowed users to make alterations of primary data supply. This is done carefully not to change the front end. Conclusion One can say that PL/SQL is a good programming language. It is easy to learn and it can be incorporated very well into Oracle Database. May operation can be carried out in this program. It is a cost effective language, since one incurs limit expenses to implement it(Gennick, 592). Building a business logic layer using PL/SQL is also easy to do since it all entails use of English words. In fact, a client will be in a position to spent limited time, thus it is time saving. Work Cited Anderson C. & Wendelken D. Oracle PL/SQL by Example. Addison-Wesley Professional, 1996. Ayers D. et al. Professional Java Server Programming. Wrox Press, 1999. Feuerstein S & Pribly B. Oracle PL/SQL Programming. 4th Ed. O’Reilly media, Inc, 2005. Feuerstein S. Oracle PL/SQL Best Practices. 2nd Edition. Shroff/o'reilly, 2008. Feuerstein S. Oracle PL/SQL Programming. O’Reilly and Associates, 1995. Gennick J. Oracle SQL*Plus: The Definitive Guide. 2nd Edition. Shroff/o'reilly ,2005. Harumi K, et al. Conversations + Interfaces = Business Logic. Heidelberg: Springer Berlin, 2001. Ivan B. SQL,PL/SQL-The Programming Language of oracle. Bpb, 2004. Kathy B. Middleware isolates business logic. New York: SIGS Publications, Inc, 1997. http://portal.acm.org/citation.cfm?id=284112.284140.  Kevin, L. & Marlene, T. Oracle 8i DBA handbook. Osborne/McGraw-Hill Company, October 1999. Kinh, N. and Tharam, D. (2005). Atomic Use Case: A Concept for Precise Modelling of Object- Oriented Information Systems. Melbourne: Department of Computer Science and Computer Engineering La Trobe University. McLaughlin B. "Business Logic, Part 1". O'Reilly and Associates, 2002. http://www.onjava.com/pub/a/onjava/excerpt/bldgjavaent_8/index1.html. . Mishra S. & Beaulieu A. Mastering Oracle SQL, 2nd Edition Shroff/o'reilly, 2004 . Greenwald R et al. Oracle Essentials, 2nd Edition. Shroff/o'reilly 2001. Odewahn, A. Oracle Web Applications: PL/SQL Developer's Introduction. O'Reilly Media, 1999. Oracle consulting. CDM RuleFrame: the business rule implementation framework that saves you the work. New Yolk: Oracle Corporation, 2001. Richard J. Oracle Performance Tuning. Oracle Press, 1999. Ritter D. Java in the Database. Intelligent Enterprise, 1999. Rosenweig B. & Silvestrova E. Oracle PL/SQL Interactive Workbook. Prentice Hall Ptr, 2002. Rosenzweig B. & Motivala B. Oracle Forms Developer -- The Complete Video Course [VHS]. Prentice Hall, 2000. Rosenzweig B. & Silvestrova E. Oracle Pl/sql By Example.4th Ed. Singapore : Pearson Education Pte Ltd, 2009. Scott, U. Oracle 8i advancwd PL/SQL programming . Osborne/McGraw-Hill Company, April 2000. Trezzo C. Oracle PL/SQL Tips and Techniques. Osborne McGraw-Hill, 1999. Turau V. 2002. "A framework for automatic generation of web-based data entry applications based on XML". ACM Press, 2002. http://portal.acm.org/citation.cfm?id=509011.  Ullman D. & Widom D. First Course in Database Systems. Goal Series Hardcover. 2nd edition. Prentice Hall, 2001.528 Read More