INFO1408 Database Design Concepts

1. INFO1408Database Design Concepts Week 16: Introduction to Database Management Systems Continued

2. Last week we talked about database features • This week we are going to look in more detail at three of these features: • Improving security • Privileges • Views • Recovery From Failure • Commit and Rollback • Concurrency Control (dealing with many users) • Locking • Deadlock.

3. 1. Improving Security

4. Database access security • Prevent unauthorised access to the database • This means making sure users can only access the data that they need • This is done by granting privileges - users are given the rights to add, amend, delete etc if they need to do this - otherwise they cannot carry out the operation • The other main method of restricting access to information is the use of views.

5. Definition of a view • A view is the result of a query • This is displayed to the user as if it is a table, and can be used like a table • The view restricts the columns and/or rows of the original table that the user has access to • A view can be made up of several tables (by including a join in the query). This can reduce the complexity for the user.

6. Reasons for using a view • Hiding columns and/or rows that the user does not need (or should not be allowed to see). If someone does not know the data is there they will not be tempted to access it • Hiding complex database operations from the user. (This also makes it more difficult for users to work out the structure of the database) • May improve the performance (speed) of a query that the user carries out on the view.

7. Example • A user in the database department wishes to allocate staff to jobs/projects • This needs the employee table. We do not want the user to see the employees’ salaries.

8. Employee Table

9. We can create a view so that the employees’ salaries are not shown to the user CREATE VIEW Emp AS SELECT Employee-number, employee-name, skills, department FROM employee; • Note that Salary is not included in the query.

10. The Resulting View (Emp View)

11. If we decide the user can only look at employees in their own department we add a WHERE clause to the SELECT statement to limit the number of rows: SELECT Employee-number, employee-name, skills, department FROM employee WHERE department=’Database’;

12. Emp View (version 2)

13. Views can also make things simpler for the user • In the last example the user may also need to inform the employee’s manager • Details of departmental managers are held in another table (called Manager) • We can create a view which presents all the information for the user to complete this task, by joining the two tables in a query.

14. Manager Table

15. Emp View (version 3)

16. 2. Recovery from Failure

17. Recovering from Failure • All systems will fail at some time • Organisations need procedures to make it easy to get the system up and working again with minimal disruption • This is dealt with using back-up and recovery procedures – just as you do with your work!!

18. Transaction Logs • Large databases keep transaction logs • These logs record all the work which has been carried out since the last back-up. The log can be used to redo all this work if necessary.

19. Transactions • A transaction is defined as a unit of work. The important thing to remember is we do all or none of it • This could be quite simple such as changing the name of a customer.

20. Complex Transactions • A transaction can be complex involving many updates to different tables – the user triggers the transaction once and works through it to the end • It is these complex transactions that make life difficult. If there is a system failure, how do we know which parts of the transaction have been completed? • In other words where to start again?

21. An Example • A user is carrying out a transaction which involves inputting a new product • This involves: 1. Adding the product details (product number, description, quantity in stock, who the supplier is, price they will pay the supplier for the product, and the price they will sell it for) 2. Updating the supplier’s record so that they can be paid for what they have supplied 3. Calculating a discount (as it is a new product) 4. Updating the website with the new product’s details.

22. The system fails when the user is part-way through stage 4 • This means: • the new product has been entered into the system • the supplier’s record shows that the supplier is owed an amount of money • the discount price has been calculated and is on the database • but the product has not yet been added to the website • BUT the user does not know exactly when the failure occurred. The user has three options….

23. 1.Assume they have finished the transaction • This means that customers will not know about the product OR 2. Start the transaction again from the beginning • This means that the supplier gets paid twice and the discount is recalculated reducing the price by even more, but customers will know about the product OR 3. The user guesses where to start again • This gives a combination of problems.

24. This situation is obviously not OK • We need to ensure the integrity of our database (so that we can trust the information the database supplies) • We achieve this by using a feature usually included as part of the DBMS.

25. Commit • The transaction log records when each transaction starts and what changes it makes to the database • When the transaction is completed a command called COMMIT is issued which makes all the changes permanent, and records the transaction as complete in the log.

26. Rollback • When there is a system failure the DBMS looks through the transaction log and finds transactions that have been started but do not have a COMMIT • These transactions are undone using the transaction log to put the database back how it was before the failure – this process is known as ROLLBACK.

27. 3. Dealing with Many Users

28. Dealing with many users • We have covered some of the things organisations do to protect their databases from malicious and accidental damage • They do this is to ensure the integrity of the database (i.e. that it is available and reliable) • There is one more possible problem with multi-user systems • Consider this situation…….

29. Two users wish to enter an order for the same item (pencils) at the same time. There are 4,000 in stock • User A’s order is for 200 pencils • User B’s order is for 250 pencils • User A is given a value of 4000 pencils in stock. This value is amended to 3800 and committed to the database • Before user A has committed their transaction user B has also been given the value of 4000 which they amend to 3750 • This transaction is also committed and overwrites the value in the attribute (setting it to 3750) • The database is left with wrong information - it should say 3550.

30. How does the DBMS stop this problem? • This is dealt with by locking part of the database • The DBMS does not allow other users into that area until the lock is removed • There are different levels of locking. The person responsible for the database must decide when to apply which level • There are several alternatives……..

31. Database locking • If any user is accessing the database no one else can. Rarely used – too disruptive • Table locking • If a user is accessing a table other users are locked out from the table. Again this may be too inconvenient if there are many users • Block or Page locking • An operating system puts blocks or pages of data into memory. If a user has accessed a part of the database the rest of the data read into memory at the same time will not be available to other users.

32. Record or row locking • Lock users from accessing a record if someone else is using it. The most common as it minimises disruption to users without taking too much effort to manage • Column or attribute level locking • Lock users from accessing an attribute if someone else is using it. Sounds good in theory but very difficult to manage - slows the system down - deadlocks are more common (explained later).

33. Using our previous example - assuming record level locking • User A requests the record for pencils which gives the user the value of 4000 for quantity in stock of 4000. The record is locked • User B requests the same record which is locked. They have to wait • User A completes their transaction, Quantity in stock is amended to 3800, the transaction is committed and the lock removed • User B is given the updated record and amends the quantity in stock to 3550. It is locked from other users until the COMMIT command is received.

34. Deadlock • There is one potential problem with locking called a deadlock • This is when two users cannot complete their transactions (and release the lock) because they are each waiting for the other to release the lock on another record - it sounds complicated so we will use an example.

35. Assuming column or attribute level locking • User A works in the purchasing section of an organisation. The transaction involves updating the quantity in stock then updating the price of the item • User B works in the finance section and is processing a customer’s order. This involves finding out the price of an item and then checking the quantity in stock.

36. Select and update quantity in stock (lock attribute) Database user A D A T A B A S E Select and update price of item (must wait due to user B’s lock) Select price of item (lock attribute) Database user B Select and update quantity in stock (must wait due to user A’s lock)

37. In this situation both users could wait forever for the other to release the lock • This fortunately is dealt with by the DBMS. The DBMS looks for deadlock situations and usually terminates one of the transactions involved and rolls back the transaction.

38. Summary • You should now be able to describe the following terms: • Security privileges and views • COMMIT and ROLLBACK commands to recover from failure • Explain locking • Explain what is meant by deadlock.