Use Of Triggers In Our Database example essay topic

2,218 words
As the database team, it was our job to provide a storage facility for the other teams. We had to come up with a design that was flexible enough to handle the various types of data and yet robust enough to make searching and record lookup efficient. Since Oracle is available in the lab and is a very powerful database management system, we decided to leverage our assets and create the database on perhaps the best database server available. But before we could slap together some SQL commands we had to plan some things out first.

Schema The process of building our database consisted of many phases of analysis before the actual design. We had to create an outline, and distribute it to the groups for feedback. Upon receiving the feedback and suggestions, we constructed the Entity-Relationship Diagram. Then we transformed the ERD into a prototype of the tables in the database and asked other team members for approval.

Then we made final layouts of the tables, which consisted of the actual names of the tables and their corresponding attributes. These layouts were distributed to the other teams so that they can incorporate our design into their servlets. Constructing an ER diagram is very important because the ER diagram is most often used as a tool for communication between database designers and end users during the analysis phase of database development. The process of designing the ERD consists of defining the types of entities: strong entities, weak entities, and the important attributes associate with each entity. Each entity has many attributes associated with it. Examples of data attributes for the customer table are customerid, customer name, etc.

We composed the data into six entities: customer, order, booksordered, musicordered, books, music, and tracklist. The symbols at the end of each relationship line, on the ER diagram, represent relationship cardinality and modality. For example, a customer might submit many orders or not submit anything at all, because it's not mandatory for a customer to place an order. In our ER diagrams (figure 1, page 5), majority of our entities are composed of many attributes. For example, shipping information is included in the customer table.

One of the advantages of composing many attributes in one table is that we don't have to do joint queries between different tables. After constructing the ERD, we transformed it into tables. Each table contains one or more primary keys. These keys uniquely identify individual instances of an entity type. An example is that each customer is uniquely defined by a customerid. Some tables contain referential keys which are a referenced from primary keys of other tables.

For example, the customerid from the order table is a referential key from customerid of customer table (figure 2, page 6). Because of the way items are recorded in an order, it would be a simple matter to additional merchandise to the database. It would simply require us to create an inventory table for that type of item and thatItemordered to keep track of the users that order that item. The triggers would have to be updated but no changes would be required to any of the other tables. Script Files Our group chose to write SQL files to create the tables and to populate them with data. It is possible to create and populate tables by typing in SQL statements at the SQL prompt.

However, this makes it very cumbersome to alter the tables when mistakes are made. So, our group decided to create files using a text editor, which contained SQL commands to create our tables and triggers, and then saved it with a '. SQL' extension. Once the file is created, the 'Start' command, followed by the file name, was used to run and execute the file. When a 'Start' command is executed, each statement in the file is executed in order. Errors occurring in the file are displayed, and the file continues to run.

By creating files, our group could easily incorporate changes. All we had to do was edit the commands in our files, and run the files again. For example, if we wanted to add another attribute to our books table, then we would only add in the name of that column into our 'create table' command rather than creating the table all over again. However, we would have to make sure there is an entry for that column for each book. But since the books are stored in a file, we only have to add in the entry to the existing information of each book, instead of entering in the books all over again.

The files also serve as a backup. For example, if the tables got destroyed, all we would have to do is run our files again, instead of creating the tables all over, and then populate them with information. Also, if another group was testing whether they could write into our tables, and the information they wrote destroyed our table, we don't have to panic about creating the table over. Instead it would take us less than a minute to fix the problem.

If we need to move the tables into another account, we can just run the files in that account. The files help save a lot of time. The files also helped us in populating the tables. We wrote one insert command, which contained the right order of attributes for the books and music.

Then we made multiple copies of that line and modified the information according to each book or music item. This method saved time because otherwise we would have the same command many times, and also worry about entering the information in the correct order. If the information was inserted in an incorrect order, then the same command would have to be entered all over again. Triggers After completing the tedious task of populating our database, we then had to write triggers.

Triggers are procedures that execute implicitly whenever a table is modified, or when some user actions or database system actions occur and it does not accept arguments. Oracle executes the triggers when an INSERT, UPDATE, or DELETE command is issued against the associated table. The timing can be BEFORE or AFTER and action. You can associate up to 12 database triggers with a given table.

Database triggers fire with the privileges of the owner, not the current user. So, the owner must have appropriate access to all objects referenced by the trigger action. Triggers supplement the standard capabilities of Oracle to provide a highly customized database management system (figure 3, page 6). A trigger stored in the database can include SQL and PL / SQL statements to execute as a unit and can invoke stored procedures.

Procedures and triggers differ in the way that they are invoked. A user, application, or trigger specifically executes a procedure. Triggers are basically fired by Oracle when a triggering event occurs, no matter which user is connected or which application is being used. A trigger can also call out to a C procedure, which is useful for computationally intensive operations. The body of the trigger is a conventional PL / SQL statement. In principle, this body may perform any action allowed in a PL / SQL statement, but in practice there are limits, do to Oracle's conservative approach to avoiding a situation where one trigger performs an action that triggers a second trigger, which triggers a third, and so on.

Roughly, the body should not modify the relation on which the trigger is placed, or any relation linked to it by a constraint such as a foreign key constraint. The triggers that we have written adds or removes books from the order and updates the inventory of books and music whenever customers place or cancel orders. The triggers only run upon the actions occurring in the order, booksordered, and musicordered tables. If any activity occurs elsewhere, the triggers will not fire. In the books table, if a deletion occurs, then the trigger runs. In booksordered and musicordered, the triggers only run when an insertion or deletion occurs.

We wrote three triggers that keep track of the ordered items and books / music inventory. If a customer decides to cancel an order, then the ORDER UPDATE trigger removes the item (s) from the booksordered and musicordered tables. It does not do anything until the something is deleted from the orders table. The other two triggers BOOKSINVUPDATE and MUSICINVUPDATE deal with the book and music inventory. They only run if someone is inserting or deleting into the booksordered or musicordered tables. If a book or music item is removed from the order, then the trigger adds the removed item back into the book or music inventory.

If an item is added to the order, then the trigger subtracts the item from the book or music inventory. Although triggers are useful for customizing database, use them only when necessary. Excessive use of triggers can result in complex interdependencies, which may be difficult to maintain in a large application. For example, when a trigger fires, a SQL statement within its trigger action potentially can fire other triggers, resulting in cascading triggers (figure 4, page 7). Therefore we limited the use of triggers in our database and created them so that they are not too complex. Along with our three triggers, we considered writing a trigger for calculating the order total and subtotal, but decided it would be much more convenient to let the customer team calculate this since they need to present this information to the user before inserting it into the database.

By providing the three triggers, the logic is significantly simplified for the customer team since they do not have to adjust the inventory recorded in the books and music tables. Accessibility JDBC make database access in Java very easy. JDBC drivers exist for many database managers as well as having a JDBC to ODBC Bridge, which allows access to almost any database using simple SQL statements. Users can write SQL statements to the database and as long as there exists a JDBC (or ODBC using the JDBC to ODBC bridge) driver for the database, SQL statements will function normally. These drivers exist for nearly every type of database known to man. In order to maintain a certain level of abstraction, we developed a class that connects the user to the database using JDBC.

This allows the other teams to instantiate an object of our class to connect to the database and it hides the lower level details from them. Team members can pass SQL statements through the connection classes' execute method and it will return back the result set. This will allow us the option, if it should come about, to change the database's location and or type without the database users having to make changes to their code. Due to the specific details that are demanded by the search team we didn't develop prepared statements to return items from the database. Instead the search team defined their own set of methods with the help of some sample SQL statements we supplied, this gives them the freedom to change how they search and return data without us having to change our code. They have the ability to control how they search for items if by titles, author, ect. and how they sort those returns as well.

If we developed our own methods then it would confine them to using methods that may not allow them the types of returns that they would desire and it would require changes to be made in two places every time a new search needed to be added. In the future, we would like to develop prepared statements for the customer team's database interactions because their interactions are fairly static and less likely to change over the development cycle, unlike the search team. An in-depth test and collaboration with the customer team would be needed to develop those methods in order to determine the necessary statements. In the future we would like our connection class to be a servlet or separate persistent entity that interacted with both the customers and search teams own Servlets. That way our connection class would become more abstract and would make it so that other teams don't have to import our code into theirs. Another feature in having our code in a separate entity would allow us to setup different accounts to the database to enhance security.

This would also allow us to change our code more easily. Currently, if we needed to change our code, we would have to send our new class file to the other teams and have them.