Lecture 9 Term 2

1. Lecture 9 Term 2 6/3/12

2. Feedback on Database Security Report • Report structure – make use of headings and sub-headings • Managers may only read the headlines • Leverage tables and diagrams – remember ‘a picture paints a thousand words’ • Paraphrase statements leverage from external sources otherwise cite the author (s) in an appropriate manner • Use a widely accepted referencing standard APA, Harvard Referencing • Write in the third person • Be complete and inclusive in your choice of attributes for inclusion • Ensure that you refer back to the case/topic/research objective posed • Conclusions should provide an overview and ALSO indicate what happens next…..

3. Indexes • An index can be created in a table to find data more quickly and efficiently. • Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book. • The users cannot see the indexes, they are just used to speed up searches/queries. • Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So you should only create indexes on columns (and tables) that will be frequently searched against. • SQL CREATE INDEX Syntax • Creates an index on a table. Duplicate values are allowed: CREATE INDEX index_nameON table_name (column_name)

4. CREATE INDEX emp_index ON EMPLOYEE (EMP_NO, EMP_NAME);

5. When should indexes be avoided? • Although indexes are intended to enhance a database's performance, there are times when they should be avoided. The following guidelines indicate when the use of an index should be reconsidered: • Indexes should not be used on small tables. • Tables that have frequent, large batch update or insert operations. • Indexes should not be used on columns that contain a high number of NULL values. • Columns that are frequently manipulated should not be indexed.

6. Other SQL Statements DROP INDEX index_name • The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. • ALTER TABLE table_nameADD column_namedatatype • ALTER TABLE table_nameDROP COLUMN column_name • ALTER TABLE table_nameMODIFY column_namedatatype

7. Problems with transactions • Computer could fail • May be a lack of crucial information • Failure in communication link

8. Database Transaction Management • Managing Commits in Transactions • Committing (and rolling back) transactions is slow because of the disk I/O and potentially the number of network round trips required. • Commit • Signals the successful end of a series of updates • Rollback • Aborts the current transaction

9. Database Transaction Management • Isolation Levels • represents a particular locking strategy used in the database system to improve data integrity. • Local Transactions • is a transaction that accesses and updates data on only one database. • Distributed Transactions • access or update data on multiple networked databases.

10. Continued… • For example a payroll payment system may need to update these tables when an employee is paid: • Employee record Table - holds a record for each employee. • Accounts Table - holds payment details by department • Auditing Table - holds all payment details for auditing purposes • The idea is when the employee is paid that his record is updated with the paid amounts, a record is added to the accounts table and one to the auditing table. • That way the company can account for the salary payment. It all adds up and is consistent. • The problem arises if something goes wrong while writing to these three tables. Maybe a hardware failure or a program bug, say a duplicate data row added or a crash due to hardware. If you work with databases, you will experience a failure at some time or other. You are most likely to get problems during development if a SQL query goes wrong. Say it takes too long to run and you have to kill the process.

11. ACID Properties • The ACID model is one of the oldest and most important concepts of database theory. • It sets forward four goals that every database management system must strive to achieve: atomicity, consistency, isolation and durability.

12. ACID Properties • Atomicity states that database modifications must follow an “all or nothing” rule. Each transaction is said to be “atomic.” • Consistency states that only valid data will be written to the database. • Isolation requires that multiple transactions occurring at the same time not impact each other’s execution. • Durability ensures that any transaction committed to the database will not be lost.

13. Concurrent Transactions • Database accessed simultaneously by two or more transactions • Problems of concurrency • Lost update • Uncommitted dependency problem • Inconsistent analysis problem

14. Serialisation of Transactions • When two or more transactions are executed concurrently on a database, their effect should be the same as if they had executed serially, with one completing before the other starts

15. Shared Update • Two users making updates at the same time. • Single vs. Multiuser environment. • Solutions • Avoidance: • Prohibit shared update • Allow access retrieval only • Record updates in transaction file and update database periodically using a batch program

16. Shared Update 2. Locking • Lock table/record/field from access by other users. • Types of Lock • Exclusive • Read only • Lock Time-out • Optimistic/pessimistic locking • Other variables • Lock granularity • Deadlock

17. Dealing with Deadlocks • Prevention • Detection

18. Recovery • Back-ups or saves(normal backup of db files) • Journaling/Audit trail/Audit file • keep a log or journal of the activity which updates the database • Recovery involves: copying the backup over database and running a special program to update the backup version of the database with the transaction log.

19. Security • Restriction of access to authorised users only. • Password • Encryption • Views • Authorisation Levels • Read only • Edit • Delete • create

20. Data Integrity • DBMS provides a mechanism to enforce specific rules. Example • Customer numbers must be numeric. • Programmers must also develop their own • Example • Credit limits must be 300, 500 or 1000 only. • The sales rep for a given customer must exist. • No customer may be deleted if he/she currently has an order on file.

21. Data Independence • Organisations are rarely single site /single entity. • Flows of data transcend the boundaries of organisations – so do information systems. • Data communciation must be implemented. • Databases can be used to support the distribution of information resources.

22. Integration of Applications • Organisational data sources are varied • All applications must be integrated to save time (i.e data exchange) • Databases may be used to enable theis integration. • Portability/compatibility is paramount.

23. Utilities • Compact data files • Index/re-index data files • Repair database (crash) • Import/export data from and to other sources. • Enforce standards (e.g. integrity relationships) • Associated data dictionary • Access to remote computers (login, emulation)

24. Some Drawbacks… • Complexity • A DBMS is a complex piece of software all users must fully understand it to make use of its functionalities • Cost of DBMS • The cost varies significantly depending on the environment and the functionality provided. Must take into consideration recurrent annual maintenance costs

25. Continued.. • Cost of Conversion • Cost of converting existing applications to run on the new DBMS and hardware. (additional training costs) • Performance • DBMS is written for applications in general which means that some applications may run slower than before • Higher Impact of Failure • Centralization of resources increases vulnerability of the system

26. Database Administrator • Oversee a staff of database specialists • Final recommendations for DB design • Load and maintain DB • Establish security controls • Perform backup and recovery

27. Data Administration • The role of managing the organisation’s data resources, concerned with information policy, data planning, maintenance of data dictionaries, and data quality standards. • http://www.youtube.com/watch?v=Jos6dRLzi80

28. Data Administration Data Administrator Database technology And management Database Management System Data planning and modelling technology Users

29. NOSQL • NoSQL encompasses a wide variety of different database technologies and were developed in response to a rise in the volume of data stored about users, objects and products, the frequency in which this data is accessed, and performance and processing needs. • Relational databases, on the other hand, were not designed to cope with the scale and agility challenges that face modern applications, nor were they built to take advantage of the cheap storage and processing power available • Document databases • Key-value stores • Graph stores • Wide-column stores • http://www.mongodb.com/learn/nosql

30. PostgreSQL • Open source object-relational database system • It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness • Runs on all major operating systems • http://db-engines.com/en/system/Oracle%3BPostgreSQL

31. For consideration • Consider Date’s 12 objectives for distributed databases, include in your answer a discussion of the problems that may be occurred in complying with these. • Explain what is meant by transaction management in database management systems. • Explain the purpose of locking in database processing, describe a number of locking techniques. Show how the use of locks in database applications can cause deadlocks.

32. Threats to the Database • User Errors • Software errors • Hardware failures • Malicious damage • Breach of confidentiality • Concurrency errors

33. Data Integrity • Entity Integrity • Referential Integrity

34. Data Validation • Defence against erroneous data entry to the database • Example • You can define rules to validate certain fields against specific values, a range of values, patterns, and length. You can define multiple rules, which are listed in the ruleList element. Each rule you define is contained within a rule element, and each rule element only defines one rule (though the rule can be complex and include conditional rules and operators).

35. Type Checking • Range of acceptable input values • Admissible operations on the data • How the data is handled on output

36. Validation Techniques • Constraints definable with the DBMS • Field level validation • Input mask • Validation rule/text • required • Record level validation • Control validation • Form-level validation • SQL constraints

37. More validation… • Assertions • General controls on content of the database • Triggers • General validation mechanism enabling execution of units of code when certain related events occur