Transaction analysis, file organisation and security measures

Published: Last Edited:

This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.

a) Transaction analysis

Although care has been taken in the design and optimisation of the database queries and transaction, initial design was carried out in a high level form. (Elmasr R, 2007).

For the Extreme sports database I will be utilising the InnoDB engine since although MySQL has several storage engines built within the DBMS, InnoDB and BDB storage are the only engines to support Atomic, Consistent, Isolation, and Durable (ACID) transactions with commit, rollback, and crash recovery capabilities. With InnoDB being the only engine to supports row-level locks with queries running as non-locking consistent reads by default.

Initially I have undertaken identification of any critical transactions. Critical transactions being those transactions that have high volume or which require high performance.

I have identified customer bookings as a critical transaction and have broken down the steps for each critical transaction, enabling identification of utilised system resources:-

For the customer bookings Transaction the following steps are required:-

* Identification of the logical I/Os.

Ø For each of the logical I/O a determination of each physical I/Os are made.

Ø Project traffic volume to obtain the number of physical I/Os per second.

Ø Estimate the actual number of physical I/Os per second. With consideration of system architecture such as:

I/Os fulfilled by disk cache. This could well significantly reduce the physical reads.

* Increased number of disks writes for raid mirroring.

* Data writes to provide audit trails

* Locking and unlocking records.

* Identification of System Bottlenecks.

Undertake a comparison of the systems hardware configuration capabilities with respect to the transaction loads expected and resulting resource utilization of resources. Once this analysis has been undertake a proposal to remedy any system bottlenecks needs to be made.

These are likely to include items such as:-

· Revision of the design of the database.

· Removal of any resource intensive functionality that is of limited business benefit.

· Possible rescheduling of some functionality to quiet-hours.

· Upgrade the hardware configuration.

b) File organisation

Since it is imperative to choose a specific storage structures and access paths as part of the database design process to achieve optimal performance from MySQL DBMS this was built in at an early stage of design. Selection of the InnoDB storage engine which supports the notion of a table space, and the MySQL Server, and offers the ability to partition the data. This allows for the distribution of sections of the tables across the file system, giving the ability to effectively, store sections of a table as a completely separate table in different physical locations. The partition function (user-selected rule) responsible for the data may be modulus in MySQL, matching against a set of ranges or value lists. (, 2008). Therefore, in consultation with the Extreme Sports Company we have taken the decision to partition the following table, since they are business critical and likely to grow at an extremely fast rate. We can easily partition the tables on dates of occurrence since all of the table listed hold date references.

* Occurrence table

* Customer Bookings

* Quals table

* Customer Quals

Partitioning will give the following benefits for Extreme sports.

Data considered of no further use to business purposes can be easily removed from the table by dropping the partition containing only that data. Conversely, the same can apply to the process of adding new data.

Queries can be greatly optimized by ensuring data addressed by specific WHERE clauses access only the required partitions.

Since partitions can be altered after a partitioned table has been created. Data may be readily reorganized to enhance frequent used queries. (, 2008)

c) Indexes

Whilst developing the database a decision had to be made weather to increase query performance by using indexes, since queries are primarily dependant on indexes to expedite the processing of selected joins (Elmasr R, 2007) and suffer the overhead of queries in terms of insert, delete and update operations. I have initially decided to index those factors that will assist in query performance regarding key business queries:

Customer registered on a course: -

Generally in MySQL Index theory, states that only one index is used per table for each MySQL query. However, there are a few exceptions. One such exception is a concatenated index which uses multiple columns, we could therefore place a concatenated index to speed the retrieval of Customers first name, last name and address from the customer table.

We could then place a simple Column index on the occurrence_idoccurence column within the customer booking table to sped retrievals of course booking details.

I would then add further Column indexes on idemp within the employee table .idequip in equipment table and the idoccurence within the occurrence table to speed query's for the other business critical queries.

However after implementation further investigation of the index size, level of improvement, and the number of additional reads required for the business critical queries.

d) User Views

A view is a named table, derived from other tables. The definition of this view then becomes a persistent component of the database. Views can be made of tables already contained within the database or from the table presentation of other views.

In the case of the Extreme sports company there is a need to limit the data each class of user can view and provide additional security through the use of views I.E by hiding rows in a table.

I propose three views:-

One for the customer who would be able to view the following:-

* Booking details for themselves only bearing in mind the data protection act and so prevent customers from viewing other customer's details.

* Also to enable them to making bookings against an activities occurrence.

* Course occurrence giving details of the sport, dates and level and location of course.

A view for instructors and administrators:-

* Showing access to qualifications held, Occurrences of sports again detailing level dates equipment allocated and the course instructor.

Finally A view for the office Manager:-

* Enabling a view of all data tables on the database for the insertion of new courses, employees, equipment Sports types and level.

Efficient implementation of views for querying is rather complex .However , whilst designing views implementation within the Extreme sports Database every effort has been made to ensure al views conform to Edgar F. Codd Rule 6: The view updating rule: All views that are theoretically updatable must be updatable by the system. (Codd's_12_rules, 2009)

Lastly as with all database processes the use of views by a DBMS such as MySQL involves balancing space saved against the time taken to perform the underlying query responsible for creating the View. With MySQL it is advisable to perform all queries of a view at one time as MySQL is intelligent enough to cache a views data that is used regularly so that it only has to be accessed once. This can be a substantial saving on space and was a prime factor in implementing views for the above company as their IT infrastructure is very limited.

e) Security Measures

As mention in uder user views acess to rows and columns can be limited in this way however just because auser is unable to see the data does not mean that data is safe from attack.

This is where the MySQL privilege system is used to to authenticate a user who connects from a given host and to associate that user with privileges on a database such as SELECT, INSERT, UPDATE, and DELETE. (, 2005)