Transcript Lecture 5

Lecture 5
Some solutions on the written
examination:
• Primary key: Unique identifier for each row in a table. Not allowed to
be null.
• Cascade update: A constraint for a relationship that allows updates of
a primary key. If you change the primary key – all the foreign keys on
the other side of a relationship is updated automatically.
• Null: is not the same as 0 because 0 is a value. Null value means
”nothing at all” or ”not applicable”.
• View: Its a virtual table based on an sql-question to serve as
tailormade information for an application or user. Good to present
views (belongs to the external layer) because of changes in the logic
layer). Could also act as a security mechanism to hide some colums or
so.
• Normalization: Is a technique used to validate your database design
from a couple of normalization rules. The most usual normalization
rules is 1NF, 2NF, 3NF and BCNF. The rules are used to check that no
redundant information or inconsistent information exists in the
database. This means that you can do updates, insertions and deltete
operations without any problem.
Objectives of Three-Level Architecture
• All users should be able to access same data.
• A user’s view is immune to changes made in other
views.
• Users should not need to know physical database
storage details.
• DBA should be able to change database storage
structures without affecting the users’ views.
• Internal structure of database should be unaffected by
changes to physical aspects of storage.
• DBA should be able to change conceptual structure of
database without affecting all users.
3
ANSI-SPARC Three-Level Architecture
4
ANSI-SPARC Three-Level Architecture
• External Level
• Users’ view of the database.
• Describes that part of database that is relevant to a
particular user.
• Conceptual Level
• Community view of the database.
• Describes what data is stored in database and relationships
among the data.
• Internal Level
• Physical representation of the database on the computer.
• Describes how the data is stored in the database.
5
Differences between Three Levels of
ANSI-SPARC Architecture
6
Data Independence
• Logical Data Independence
• Refers to immunity of external schemas to changes in conceptual
schema.
• Conceptual schema changes (e.g. addition/removal of entities).
• Should not require changes to external schema or rewrites of
application programs.
• Physical Data Independence
• Refers to immunity of conceptual schema to changes in the
internal schema.
• Internal schema changes (e.g. using different file organizations,
storage structures/devices).
• Should not require change to conceptual or external schemas.
7
Data Independence and the ANSISPARC Three-Level Architecture
8
Database Languages
• Data Definition Language (DDL)
• Allows the DBA or user to describe and name entities,
attributes, and relationships required for the application
• plus any associated integrity and security constraints.
• Data Manipulation Language (DML)
• Provides basic data manipulation operations on data held in
the database.
• Procedural DML
• allows user to tell system exactly how to manipulate data.
• Non-Procedural DML
• allows user to state what data is needed rather than how it
is to be retrieved.
• Fourth Generation Languages (4GLs)
9
Constraint on relationships
• Cascade delete
• The relation below try to describe the structure of an
order. Can an orderhead exist without a row?
• If you delete a certain row in orderhead…what happens?
• Cascade update
• If you update oredernr in orderhead…what happens?
Orderhead
Ordernr
Date
custnr
1
*
Orderrow
Ordernr
Partnr
Quantity
orderprice
What is a stored procedure?
• How its working?
• SQL server has a language called transact SQL and its
used to create modules of logic database actions.
• It is stored in database with a name to call when you
need it.
• An example of executing a stored procedure to create a
new customer:
EXEC sp_newcustomer (’23’,’Jesper’, ’Hakeröd’,…)
• More practise on this in laboratory work 2.
Example of a stored procedure
-- =============================================
-- Author:
<Jesper>
-- Create date: <2009-10-10>
-- Description:
<Insert a new customer>
-- =============================================
CREATE PROCEDURE [dbo].[uspNewCustomer] (@CustomerID as bigint, @firstname as
nvarchar(50), @surename as nvarchar(50), @address as nvarchar(50), @cellular as
nvarchar(50), @email as nvarchar(50), @zipcode as nvarchar(10))
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insertion of the customer
INSERT INTO CUSTOMER (CustomerID, firstname, surename, address, cellular, email, zipcode)
VALUES (@CustomerID, @firstname, @surename, @address, @cellular, @email, @zipcode);
END
Exemple: Security in SQL Server
Exemple: Security in SQL Server
Database Security
• Data is a valuable resource that must be strictly
controlled and managed, as with any corporate
resource.
• Part or all of the corporate data may have strategic
importance and therefore needs to be kept secure and
confidential.
• Mechanisms that protect the database against
intentional or accidental threats.
• Security considerations do not only apply to the data
held in a database. Breaches of security may affect
other parts of the system, which may in turn affect the
database.
16
Database Security
• Involves measures to avoid:
– Theft and fraud
– Loss of confidentiality (secrecy)
– Loss of privacy
– Loss of integrity
– Loss of availability
17
Database Security
• Threat
– Any situation or event, whether intentional or
unintentional, that will adversely affect a system
and consequently an organization.
19
Summary of Threats to Computer
Systems
20
Typical Multi-user Computer
Environment
21
Countermeasures – Computer-Based
Controls
• Concerned with physical controls to administrative
procedures and includes:
– Authorization
– Access controls
– Views
– Backup and recovery
– Integrity
– Encryption
– RAID technology
22
Transaction Support
Transaction
Action, or series of actions, carried out by user or
application, which reads or updates contents of
database.
• Logical unit of work on the database.
• Application program is series of transactions with nondatabase processing in between.
• Transforms database from one consistent state to another,
although consistency may be violated during transaction.
23
Example Transaction
24
Transaction Support
• Can have one of two outcomes:
– Success - transaction commits and database reaches a
new consistent state.
– Failure - transaction aborts, and database must be
restored to consistent state before it started.
– Such a transaction is rolled back or undone.
• Committed transaction cannot be aborted.
• Aborted transaction that is rolled back can
be restarted later.
25
State Transition Diagram for Transaction
26
Properties of Transactions
•Four basic (ACID) properties of a transaction are:
Atomicity
Consistency
Isolation
Durability
27
‘All or nothing’ property.
Must transform database from one consistent
state to another.
Partial effects of incomplete transactions should
not be visible to other transactions.
Effects of a committed transaction are
permanent and must not be lost because of
later failure.
Concurrency Control
Process of managing simultaneous
operations on the database without having
them interfere with one another.
• Prevents interference when two or more users are
accessing database simultaneously and at least one is
updating data.
• Although two transactions may be correct in
themselves, interleaving of operations may produce
an incorrect result.
28
Need for Concurrency Control
• Three examples of potential problems
caused by concurrency:
– Lost update problem.
– Uncommitted dependency problem.
– Inconsistent analysis problem.
29
Lost Update Problem
• Successfully completed update is overridden
by another user.
• T1 withdrawing £10 from an account with
balx, initially £100.
• T2 depositing £100 into same account.
• Serially, final balance would be £190.
30
Lost Update Problem
• Loss of T2’s update avoided by preventing
T1 from reading balx until after update.
31
Uncommitted Dependency Problem
• Occurs when one transaction can see
intermediate results of another transaction
before it has committed.
• T4 updates balx to £200 but it aborts, so balx
should be back at original value of £100.
• T3 has read new value of balx (£200) and
uses value as basis of £10 reduction, giving a
new balance of £190, instead of £90.
32
Uncommitted Dependency Problem
• Problem avoided by preventing T3 from
reading balx until after T4 commits or aborts.
33
Inconsistent Analysis Problem
• Occurs when transaction reads several values
but second transaction updates some of them
during execution of first.
• Sometimes referred to as dirty read or
unrepeatable read.
• T6 is totaling balances of account x (£100),
account y (£50), and account z (£25).
• Meantime, T5 has transferred £10 from balx to
balz, so T6 now has wrong result (£10 too high).
34
Inconsistent Analysis Problem
• Problem avoided by preventing T6 from reading
balx and balz until after T5 completed updates.
35
Serializability
• Objective of a concurrency control protocol is
to schedule transactions in such a way as to
avoid any interference.
• Could run transactions serially, but this limits
degree of concurrency or parallelism in system.
• Serializability identifies those executions of
transactions guaranteed to ensure consistency.
36
Concurrency Control Techniques
• Two basic concurrency control techniques:
– Locking,
– Timestamping.
• Both are conservative approaches: delay
transactions in case they conflict with other
transactions.
• Optimistic methods assume conflict is rare
and only check for conflicts at commit.
37
Locking
Transaction uses locks to deny access to other
transactions and so prevent incorrect updates.
• Most widely used approach to ensure
serializability.
• Generally, a transaction must claim a shared
(read) or exclusive (write) lock on a data item
before read or write.
• Lock prevents another transaction from
modifying item or even reading it, in the case of
a write lock.
38
Locking - Basic Rules
• If transaction has shared lock on item, can
read but not update item.
• If transaction has exclusive lock on item, can
both read and update item.
• Reads cannot conflict, so more than one
transaction
can
hold
shared
locks
simultaneously on same item.
• Exclusive lock gives transaction exclusive
access to that item.
39
Two-Phase Locking (2PL)
Transaction follows 2PL protocol if all
locking operations precede first unlock
operation in the transaction.
• Two phases for transaction:
– Growing phase - acquires all locks but cannot
release any locks.
– Shrinking phase - releases locks but cannot
acquire any new locks.
40
Preventing Lost Update Problem using 2PL
41
Pearson Education © 2009
Preventing Uncommitted Dependency Problem
using 2PL
42
Preventing Inconsistent Analysis Problem using 2PL
43
Deadlock
An impasse that may result when two (or
more) transactions are each waiting for locks
held by the other to be released.
44
Deadlock
• Only one way to break deadlock: abort one or
more of the transactions.
• Deadlock should be transparent to user, so
DBMS should restart transaction(s).
• Three general techniques for handling
deadlock:
– Timeouts.
– Deadlock prevention.
– Deadlock detection and recovery.
45
Timeouts
• Transaction that requests lock will only wait
for a system-defined period of time.
• If lock has not been granted within this
period, lock request times out.
• In this case, DBMS assumes transaction may
be deadlocked, even though it may not be,
and it aborts and automatically restarts the
transaction.
46
Deadlock Prevention
• DBMS looks ahead to see if transaction would
cause deadlock and never allows deadlock to
occur.
• Could order transactions using transaction
timestamps:
– Wait-Die - only an older transaction can wait for
younger one, otherwise transaction is aborted
(dies) and restarted with same timestamp.
47
Deadlock Prevention
– Wound-Wait - only a younger transaction can
wait for an older one. If older transaction
requests lock held by younger one, younger one
is aborted (wounded).
48