Discussion items:

https://landing.athabascau.ca/pg/forum/topic/127809/apartment-rental-db-security-and-fault-management/

https://landing.athabascau.ca/pg/forum/topic/127407/apartment-rental-db-performance/

https://landing.athabascau.ca/pg/forum/topic/127683/db-capacity-planning/

Reading about ACID rules refreshed my memory on the basic principles of maintaining data integrity in databases. I got the opportunity to investigate the various isolation levels presented in ANSI/ISO SQL Standard. I am most interested in isolation property then the other three properties: Atomicity, Consistency and Durability. This is because this is the only property that the SQL code implementer has complete control over. The other three properties are more or less handled by modern DBMS with little or no human intervention.

ACID is just one of the many aspects of insuring data integrity. This week I also got a chance to read about data backup, clustering, transactions, performance and finally, security. Data backup is relatively easy to execute but painful in planning. Depending on the business scenario, a local database or a distributed database, resource availability, etc to determine the best backup strategy. This affects the frequency in schedule planning, resource planning and disaster recovery process. Performance is important to the usability of a particular system, clustering a few database servers together is one way to improve performance among all other solutions that deal with increasing hardware capacities. However, I have seen some remarkable performance improvement using query optimization and indexing using tools available in modern DBMS to identify performance bottleneck and to analysis the execution path of query. Finding out the weak spots to target for indexing can avoid table scan that is costly to overall execution time. Security is important to segregate data segments by roles or users. In my case I used roles as a way to group common permissions to related group of users. Granting only the necessary access rights (select, update, delete, DLL, etc) to the right objects (tables, views, procs, functions, etc) for a well-defined role can safeguard against inadvertent data corruption as well as malicious attacks. Another way to minimize malicious attacks is through the use stored procedures for DML instead of dynamic queries to avoid SQL injection attacks. Also, database login credential information must be well secured and protected.

Dickson