Introduction
There are issues that are seen and evident with the database that are used by the tour operator. One issue is that there is repeating data in the database. This is an issue that should be looked into when proposing a new data structure. There should not be any repeating values in the database. This paper will focus on procedures on how to refine the data structure for the tour operator’s database.
The tables that are there in the database are the following:
1st Normal form
The table has repeating values that should be altered to eradicate redundancy. If one customer chooses more than one tour, the name of that customer will appear many times. There is a need to include the tours selected in the Tour_table. There will be no repeating values of the database. The tables will now look like the data below:
2nd Normal form
The data model that has been presented still has some issues though it is in 1NF. One problem that the data still has is deletion and insertion anomalies. If, one season has one client, and that client is deleted, all the information about that client will be deleted. Also, of one tour type has only one client, the deletion of that client will cause the loss information about this type of tour. This requires that two other tables be created that will hold the type of tour and the season type.
Stored procedure rationale
The use of stored procedures in this database is to the benefit of the processes that will be done at the database. There is a need to ensure that the database performs optimally for good results. Stored procedures are lighter and do not need a lot of resources to undertake. Compared to queries, they are lighter when they are processed by the database. The database processing will be optimized because it will reduce the network traffic. This is because with the use of stored procedures, only the name of the stored procedure will be sent to the database for processing. This is not the same with the use of queries where the whole text will be sent to the database. Security optimization will also be enhanced with the use of stored procedures. The data objects that are found in the database will be secured with the procedures that will be processed in the database.
Optimization will be achieved with the use of stored procedures because of the Oracle System Global Area (SGA). It makes the loading of the stored procedures faster and efficient. The stored procedures will be stored in the SGA until they are removed from the page-out memory. The procedure that items are paged out will follow an algorithm where the items that were least used recently are wiped out. Stored procedures are stored on the RAM of the computer and that is why they will execute faster than queries. This makes the procedures and transactions of the database perform optimally. This is an important step that should be taken in order to have faster transaction processing for the database.
Another rationale is that the stored procedures will be checked by the SQL Server Profiler to know if it has been recompiled too often. This makes optimization of the database easily achieved. It is important to understand the requirements for this database. The event will be traced in the category of Stored Procedures. They are used to check if one stored procedure has been recompiled too often. There is also another provision which can be used to check the point at which the stored procedure has been recompiled. With the knowledge of the points of recompilation, the database administrator will be able to reduce or eradicate excessive recompilations. The use of WITH RECOMPILE option will enable the database administrator to change the instances where the stored procedure will be used. This will eradicate the need for the server to reuse the stored procedure several times.
Works Cited
Caffrey, Melanie. "Modeling and Accessing Relational Data." Oracle Magazine25.6 (2011): 71-74.
Halpin, Terry, and Tony Morgan. Information modeling and relational databases. Morgan Kaufmann, 2010.
Teorey, Toby J., Sam S. Lightstone, Tom Nadeau, and H. V. Jagadish. Database modeling and design: Logical design. Elsevier, 2011.
