Transcript Database Management System

Database Management
System
Module 5
DeSiaMore
www.desiamore.com/ifm
1
Boyce-Codd Normal Form

BCNF was designed to cover anomalies
that arise when there is more than one
candidate key in some set of data
requirements.
DeSiaMore
www.desiamore.com/ifm
2
Example BCNF

Suppose we have introduced a scheme
of majors into degree scheme at a
unveristy. The business rules relevant
to that prt of this domain covering
majors are:


Each student may major several areas
A student has one tutor for each area
DeSiaMore
www.desiamore.com/ifm
3
Example BCNF



Each area has several tutors but a tutor
advise in only one area.
Each tutor advises several students in an
area.
On the grounds of these rules, a
schema produced in third normal form
represented below
Majors(studentNo, area, staffNo)
DeSiaMore
www.desiamore.com/ifm
4
Example BCNF

A set of sample data is provided in the
table below.
DeSiaMore
www.desiamore.com/ifm
5
Example BCNF

The schema has the following
anomalies:

Suppose student 123456 changes one of
his or her majors from computer science to
information systems. Doing this means that
we lose information about staffNo 234
tutoring on computer science. This is an
update anomaly.
DeSiaMore
www.desiamore.com/ifm
6
Example BCNF


Suppose we wish to insert a new row to
establish the fact that staffNo 789 tutors
on computer science. We cannot do this
until at least one student takes this area as
his or her major. This is an insertion
anomaly
These anomalies occur because there
are two overlapping candidate keys.
R.F. Boyce and E.F. Codd came with the
solution.
DeSiaMore
www.desiamore.com/ifm
7
BCNF

A relation is in BCNF if every
determinant is a candidate key. The
schema above can be converted into
BCNF in one of two ways:
Schema 1:
StudentTutorial(studentNo, staffNo)
TutorAreas(staffNo, area)
 Schema 2:
StudentTutorials(studentNo, area)
TutorAreas(staffNo,
area)
DeSiaMore
www.desiamore.com/ifm

8
Concurrency control and
recovery
DeSiaMore
www.desiamore.com/ifm
9
Transactions
In multi-user database system the
procedures that cause changes to a
database or retrieve data from the
database are called transactions.
 A transaction may be defined as a
logical unit of work.
 Include activities performed by an
individual, group, organisation or
software process
on database.
DeSiaMore
www.desiamore.com/ifm

10
ACID

To remain an accurate reflection of its
real-world domain, any transaction such
as the one above should demonstrate
the properties of atomicity, consistency,
isolation and durability (ACID)
DeSiaMore
www.desiamore.com/ifm
11


Atomicity – Since a transaction consists of a
collection of actions, the DBMS should ensure
that either all the transaction is performed or
none of is performed.
Consistence – all transactions must preserve
the consistency and integrity of the database.
Operations performed by an updating
transaction, for instance, should not leave the
database in an inconsistent state
DeSiaMore
www.desiamore.com/ifm
12

Isolation – While a transaction is
updating shared data, that data may be
temporarily inconsistent. Such data not
be made available to other transactions
until the transaction in question has
finished using it.
DeSiaMore
www.desiamore.com/ifm
13

Durability – When a transaction
completes, the changes made by the
transaction should be durable. That is,
they should preserved in the case of
hardware or software failure.
DeSiaMore
www.desiamore.com/ifm
14
Transaction in SQL-Based
System.


In most SQL-Based products, a
transaction is simply a sequence of
SQL-statement that is packaged as a
single entity.
Relational DBMS maintain consistency
by ensuring either that all the SQL
statements in a transaction complete
successifully or that none do.
DeSiaMore
www.desiamore.com/ifm
15
Transaction in SQL-Based
System



In SQL the statements COMMIT and
ROLLBACK are used to delineate
transactions.
COMMIT makes permanent changes to
a database.
ROLLBACK undoes all changes made in
an unsuccessful transaction.
DeSiaMore
www.desiamore.com/ifm
16
Example SQL-Transaction
INSERT INTO registration(studentNo,
moduleName) VALUES (34698,
‘Relational Database Systems’)
UPDATE Modules
SET roll = roll + 1
COMMIT
DeSiaMore
www.desiamore.com/ifm
17
CONCURRENCY

Data in the database systems can be
accessed by many users; multiple data
sharing.



E.g. Airline reservation system
There must be mechanism of controlling
shared or concurrent access.
When there are several transactions executed
on parallel on a shared database, their
execution must be synchronised.
DeSiaMore
www.desiamore.com/ifm
18
Concurrency Control


The primary objective of concurrency
control is to prevent interference
between transactions to occur.
A major control strategy used for
concurrency control is locking.
DeSiaMore
www.desiamore.com/ifm
19
Locking
It is the most approach in contemporary
DB.
 When a transaction locks a data-item it
is saying to other transactions ‘Hey I
am doing something with this dataitem, wait until I have completed may
task’.
 Importance of this to serialise access to
limited resources.
DeSiaMore
www.desiamore.com/ifm
20

Two types of Locks

Read locks – A read lock gives only read
access to data and prevents any other
transaction from updating the locked
data.


A transaction applies this type of lock when
it wishes to query a file but does not want
to change it.
Also it does not want other transactions to
change it while it is looking at it.
DeSiaMore
www.desiamore.com/ifm
21
Two types of Locks

Write locks; this gives both read and
write access to a data-item. It also
prevents any other transaction from
reading from or writing to a data.
DeSiaMore
www.desiamore.com/ifm
22
Transaction Manager and
Transaction Log


Transaction manager is a module within
the kernel of a DBMS that handles the
throughput of transactions against a
database.
It ensures that transaction either
completes successfully or fails.
DeSiaMore
www.desiamore.com/ifm
23
Transaction Log


The transaction log is a file that
contains data about the updates
affecting a database.
Such data may comprise transaction
records.
DeSiaMore
www.desiamore.com/ifm
24
Recovery



Recovery is the process of ensuring that the
database can achieve a consistent state in
the event of failure.
Failure can occur either when writing the data
to buffers or while flushing the buffers.
Flushing is the process of transferring data
from buffers in main memory to secondary
memory.
DeSiaMore
www.desiamore.com/ifm
25
Recovery


If a transaction issued a commit but
failure occurred before flushing
completes, then the changes to the
database will not be made permanent.
In this case the recovery module will
need to redo the transaction.
DeSiaMore
www.desiamore.com/ifm
26
Distributed Database System

A distributed database system is a
database system which is fragmented
or replicated on the various
configurations of hardware and
software, located usually at different
geographical sites within an
organisation.
DeSiaMore
www.desiamore.com/ifm
27
Distributed Database System



Distribution is solely said of
fragmentation and replication of data.
A data fragment constitutes some
subset of the original database.
A data replicate is constitutes of some
copy of the whole or part of the original
database.
DeSiaMore
www.desiamore.com/ifm
28
Distributing Data
DeSiaMore
www.desiamore.com/ifm
29
Distributing Data
DeSiaMore
www.desiamore.com/ifm
30
Distributed Database System

An example of distributed database system
DeSiaMore
www.desiamore.com/ifm
31
Advantage
DeSiaMore
www.desiamore.com/ifm
32
Advantage
DeSiaMore
www.desiamore.com/ifm
33