Overall DBMS-

Architecture
• Database Users:
• Users are differentiated by the way they expect to interact
with the system:
• Application programmers:
– Application programmers are computer professionals
who write application programs. Application
programmers can choose from many tools to develop
user interfaces.
– Rapid application development (RAD) tools are tools that
enable an application programmer to construct forms
and reports without writing a program.
• Sophisticated users:
– Sophisticated users interact with the system without
writing programs. Instead, they form their requests in a
database query language.
– They submit each such query to a query processor,
whose function is to break down DML statements into
instructions that the storage manager understands.
• Specialized users :
– Specialized users are sophisticated users who write
specialized database applications that do not fit into the
traditional data-processing framework.
– Among these applications are computer-aided design
systems, knowledge base and expert systems, systems
that store data with complex data types (for example,
graphics data and audio data)
• Naïve users :
– Naive users are unsophisticated users who interact with
the system by invoking one of the application programs
that have been written previously.
– For example, a bank teller who needs to transfer $50
from account A to account B invokes a program called
transfer. This program asks the teller for the amount of
money to be transferred, the account from which the
money is to be transferred, and the account to which the
money is to be transferred.
• Database Administrator:
• Coordinates all the activities of the database system.
The database administrator has a good
understanding of the enterprise’s information
resources and needs.

• Database administrator's duties include:

– Schema definition: The DBA creates the original
database schema by executing a set of data definition
statements in the DDL.
– Storage structure and access method definition.
– Schema and physical organization modification: The
DBA carries out changes to the schema and physical
organization to reflect the changing needs of the
organization, or to alter the physical organization to
improve performance.
– Granting user authority to access the database: By
granting different types of authorization, the database
administrator can regulate which parts of the database
various users can access.
– Specifying integrity constraints.
❖ Query Processor:
• The query processor will accept query from user and solves
it by accessing the database.
Parts of Query processor:
• DDL interpreter
• This will interprets DDL statements and fetch the definitions
in the data dictionary.
• DML compiler
• a. This will translates DML statements in a query language
into low level instructions that the query evaluation engine
understands.
• b. A query can usually be translated into any of a number of
alternative evaluation plans for same query result. DML
compiler will select best plan for query optimization.
• Query evaluation engine
• This engine will execute low-level instructions generated by
the DML compiler on DBMS.
• Storage Manager/Storage Management:
• A storage manager is a program module which acts like
interface between the data stored in a database and the
application programs and queries submitted to the system.
• Thus, the storage manager is responsible for storing,
retrieving and updating data in the database.
• The storage manager components include:
– Authorization and integrity manager: Checks for integrity
constraints and authority of users to access data.
– Transaction manager: Ensures that the database remains in a
consistent state although there are system failures.
– File manager: Manages the allocation of space on disk storage
and the data structures used to represent information stored on
disk.
– Buffer manager: It is responsible for retrieving data from disk
storage into main memory. It enables the database to handle
data sizes that are much larger than the size of main memory.
– Data structures implemented by storage manager.
– Data files: Stored in the database itself.
– Data dictionary: Stores metadata about the structure of the
database.
– Indices: Provide fast access to data items.
❖ Prime and Non Prime Attributes in DBMS with
Example
❑ Prime Attributes –
Attribute set that belongs to any candidate key are called
Prime Attributes.
(union of all the candidate key attribute)
{CK1 ∪ CK2 ∪ CK3 ∪ ……}
If Prime attribute determined by other attribute set, then
more than one candidate key is possible.

For example,
If A is Candidate Key, and X→A, then, X is also Candidate
Key

❑ Non Prime Attribute – Attribute set does not belongs

to any candidate key are called Non Prime Attributes.
 Modelling Temporal Data
• Temporal database stores data relating to time
instances. It offers temporal data types and stores
information relating to past, present and future time.
• Temporal databases provide a uniform and
systematic way of dealing with historical data.
• More specifically the temporal aspects usually
include valid time and transaction time. These
attributes can be combined to form bitemporal data.
✔ Valid time is the time period during which a fact is
true in the real world.(Historical database)
✔ Transaction time is the time period during which a
fact stored in the database was known.(Rollback
database)
✔ Bitemporal data combines both Valid and
Transaction Time.
• Example:-Imagine that we come up with
a temporal database storing data about
the 18th century. The valid time of these
facts is somewhere between 1700 and
1799, where as the transaction time starts
when we insert the facts into the
database, for example, January 21, 1998.

• Consider data about employees with

respect to the real world is stored in table.

• Then, the following table could result:

Emp Name Departme Salar ValidTimeSta ValidTimeEn
ID nt y rt d
10 John Research 11000 1985 1990
10 John Sales 11000 1990 1993
10 John Sales 12000 1993 INF
11 Paul Research 10000 1988 1995
Georg
12 Research 10500 1991 INF
e
• The above valid-time table stores the history of the
employees with respect to the real world. The
attributes ValidTimeStart and ValidTimeEnd
actually represent a time interval which is closed at
its lower and open at its upper bound.

• Thus, we see that during the time period [1985 - 1990),

employee John was working in the research
department, having a salary of 11000.

• Then he changed to the sales department, still earning

11000. In 1993, he got a salary raise to 12000.

• The upper bound INF denotes that the tuple is valid

until further notice. It is now possible to store
information about past states.

• We see that Paul was employed from 1988 until 1995.

In the corresponding non-temporal table, this
 Different Forms of Temporal Databases

• The two different notions of time - valid time and

transaction time - allow the distinction of different forms
of temporal databases.
• A historical database stores data with respect to valid
time, a rollback database stores data with respect to
transaction time.
• A bitemporal database stores data with respect to both
valid time and transaction time.

• Commercial DBMS are said to store only a single state of

the real world, usually the most recent state. Such
databases usually are called snapshot databases.

• A snapshot database in the context of valid time and

• On the other hand, a bitemporal DBMS such as
TimeDB stores the history of data with respect to
both valid time and transaction time.

• The history of when data was stored in the database

(transaction time) is limited to past and present
database states, since it is managed by the system
directly which does not know anything about future
states.

• A table in the bitemporal relational DBMS TimeDB

may either be a snapshot table (storing only current
data), a valid-time table (storing when the data is
valid wrt. the real world), a transaction-time table
(storing when the data was recorded in the
database) or a bitemporal table (storing both valid
• An extended version of SQL allows to specify
which kind of table is needed when the table
is created.

• Existing tables may also be altered (schema

versioning). Additionally, it supports
temporal queries, temporal modification
statements and temporal constraints.

• The states stored in a bitemporal database are

shown in fig.

• Of course, a temporal DBMS such as TimeDB

does not store each database state separately
as depicted in the figure, it stores valid time
and/or transaction time for each tuple.
Canonical Cover
 Canonical Cover - 1
• Given a relation R with a set of FDs F
• 4 steps to find a canonical cover for F:

• Step 1: Decompose all FDs in standard form

• Replace each FD X → A1A2…Ak in F with
X→A1, X→A2, …, X→Ak

20
 Canonical Cover - 2
• Step 2: Eliminate unnecessary attributes
from LHS
• For every FD X → A in F, check if the closure
of a subset of X determines A. If so, remove
redundant attribute(s) from X

21
 Canonical Cover - 3
• Step 3: Remove redundant FD(s)
• For every FD X → A in G
– Remove X → A from G, and call the result G’
– Compute X+ under G’
– If A ∈ X+, then X → A is redundant. Hence, we
remove the FD X → A from G (That is, we rename
G’ to G)

22
 Canonical Cover - 4
• Step 4: Make LHS of FDs unique
• Replace X→A1, X→A2, …, X→Ak with X →
A1A2…Ak

23
END