M359 - Relational databases: theory and practice

Week 2 : Block 1 - Databases in context (pages 68 - 100)

3. Database management system concepts

Let us consider a common abstraction that gives an

architectural structure to the components of a typical DBMS.

The ANSI/SPARC
Requirements Committee) (American National Standards Institute, Standards Planning And

1975 report proposed a standard set of terminology and a three-level architecture for database systems (it never became a standard, and is rarely the basis for vendor implementations of database systems, but it does offer a useful mechanism for considering DB functionality). The architecture attempts to separate user views of the data from the systems physical representation of the data.

3. Database management system concepts

The term schema is a commonly used term in the database

context it means a data definition that describes a DB.

There are various kinds of schema; however, in all cases

they will refer to a specification of the properties of data.

Each schema may have a distinct description because of the

different ways in which the data is represented to the system.

Each user process will still need some description of the data

it uses, but the definition of how data is stored in a file will be used only by the DBMS, rather than by any user process.
3

Three-schema architecture
To satisfy these needs for definitions of data in a way which provides data independence, we introduce what is called the three-schema architecture (a general architecture applicable to any class of DBMS, not just relational DBMSs).

Three-schema architecture
External schema(s)
mapping
Mapping
5

Logical schema

Storage schema

Three-schema architecture
A logical schema
The central component in this architecture Defines the logical properties of data in a database i.e.

Representation of the data Associated constraints (independent of how it is stored in files).

A database has just one logical schema, used by a DBMS to

manage all the data in the database.

For a relational database, the SQL Data Definition Language

(DDL) can be used to define this logical representation. Ex: Create Table Customers (ID char(6), Name char(40),)
6

Three-schema architecture
A storage schema
Defines how a database is stored in files and accessed

(using indexes to help in searching particular columns). Can specify how data is to be stored only if the data is already defined by an associated logical schema (i.e. storage schema definitions must relate to the definitions in a logical schema).
The connection between the logical and storage schema is

known as a mapping.
A logical schema determines what data is available in a

database, while a storage schema determines where and how data is stored in files.
7

Three-schema architecture
An external schema
Defines data for a user process or, more specifically,

defines data from a database, which the user process retrieves or updates by interaction with a DBMS. Is concerned with data as it is presented outside the DBMS to user processes.
External schema can specify how data is available to a user

process only if that data has already been defined by a logical schema.
External schema definitions must relate to those in a logical

schema, and each external schema must have a mapping from its associated logical schema.
8

Three-schema architecture
For a relational DBMS, an external schema is defined as

tables which contain the data required by a user process.

The external schema tables may differ from the tables in a

logical schema By including only some of the columns and some of the rows of the logical table By including data derived from the data described by the logical schema.
The external schema is independent of the storage schema

as it is defined by reference to the logical schema alone.

9

Three-schema architecture

10

Three-schema architecture
Exercise 3.1 (p. 71) Explain why an external schema should be considered as defining a logical representation of data.
An external schema defines what data is required by a user process, and is represented in the same way as a logical schema, so it should be considered as defining a logical representation of data.

Give one property of data defined as part of a logical

schema that is not included in an external schema.

A logical schema defines constraints, which are not included in an external schema because they are used just by the DBMS and not by user processes.
11

Three-schema architecture
Three-schema architecture provides data independence:
Logical data independence exists when a change to a

logical schema has no impact on a user process. This is possible because each user process is dependant only on an external schema.
Physical data independence exists when a change to a

storage schema has no impact on a user process. This is possible because such a change does not affect a logical schema, since the change can be taken into account by a change to the mapping for the storage schema.
12

Three-schema architecture
The three-schema architecture is a general model,

developed before relational DBMSs were in widespread use.

Many relational DBMSs have a fixed way of storing tables

and so a storage schema is NOT required this is a weakness of such implementations of relational technology.
However, most DBMSs permit indexes to be created and

added to table descriptions when required. An index in a DBMS can be used to speed searches for indexed values. Indexes are not directly concerned with how data is stored, only with how it is accessed.
13

Three-schema architecture
Where do indexes fit in the three-schema architecture? An index adds an extra description of the way in which data content is stored, but does not affect the way in which the data is physically stored. Thus, Indexes are sometimes considered not to be part of a storage schema.
Indexes are definitely not part of a logical schema because

they are not concerned with any logical property of the data itself. Consequently, indexes do not appear to fit anywhere in the three-schema architecture!!
14

Three-schema architecture
Logical schema is concerned with tables whose data is

stored in a database, also known as base tables. Thus, a logical schema in a relational DB is the collection of the descriptions of all the properties of the base tables.
In the relational model, you can also have a derived table

known as view. A view is a table without stored data whose values are defined by evaluating a defined query involving one or more base tables or other views.
In the context of the three-schema architecture, the view

could be seen as part of an external schema because it is describing data to be used by a user process.
15

The functions of a DBMS

We will consider the functionality of the DBMS by examining four main management task areas:

16

Data definition
This DBMS function is concerned with the definition of

properties of data and, more generally, with the creation and modification of a schema in which data definitions are kept.
A DBMS performs this function in response to a request from

a user process that gives users the capability to define data. Such a request includes a specification of some properties of data that a DBMS then incorporates within a schema. For example, include name & data type for columns. Create Table Customers (ID char(6), Name char(40),)
Relational DBMS allows new tables to be added at any time,

or existing tables to be altered by adding new columns.

17

Data manipulation
This DBMS function provides the means to manipulate

specific data in a database, which includes retrieving and updating : adding, deleting or changing data.
A DBMS performs this function in response to a request from

a user process, which provides a specification of what data is to be retrieved or updated. Insert into Customers (ID char(6), Name char(40),)
An example of one kind of manipulation, derived data such as

counts, totals and averages can be calculated by a DBMS for display by a user process.
18

Transaction support
A transaction specifies a sequence of updates to be executed

as a single unit of work which will succeed or fail as a complete unit.

Two standard SQL commands COMMIT and ROLLBACK can

be used to mark the end and the start of a transaction.

Example of transaction

Transfer of funds from one bank account to another: The transaction consists of two steps the first step removes the money from the source bank account; the second step places the money into the destination account.
19

Transaction support
The current transaction can be committed, that is, all the

data changes within it are made permanent, by issuing a COMMIT instruction at the end of the transaction.
The changes can be discarded by issuing a ROLLBACK

instruction, which has the effect of taking the database back to the state it was in before that transaction began.
A transaction is used to control sharing of data because

the result of a transaction execution is visible to other users of the system only at the end of the transaction.
20

Concurrency support
Concurrency needs to provide users with the perception

that they can access any data value whenever they want, without being affected by other users who want to access the same data at the same time.
If all users just want to retrieve that value, there is no

problem they all see the same value. For updating, there are potential problems when many users want to change the same data.

Concurrency control enables many user processes to

access a database at the same time in a way that prevents them interfering with each other.
21

Security management
A key responsibility for the DBMS is ensuring that the

data is both available and reliable.

Security management covers a range of key functions

including ensuring:
The integrity of the data The correctness of the processing of the data Only authorized users access the data

Not possible to lose data when a system crashes.

22

Constraint management
Constraints are properties of data. They are included in a

schema in the same way as described for data definition.

After a constraint has been defined, a DBMS must ensure

that data in a database never violates the constraint that is a constraint is enforced automatically.
Constraints are checked whenever a request to the DBMS

would result in changes to the data that might affect the constraint.
Many DBMSs allow constraint enforcement to be switched

OFF either temporarily or permanently (this control should be exercised with great caution).
23

Access control
Another security management controlling what users can do

and the data they can access.

Access control involves both:
the definition of users rights to access specific data the prevention of access to users without rights.

A DBMS automatically controls access according to defined

RIGHTS. These rights are included in a schema as part of the definition of a database. Example: The right to retrieve only a specific table or to update values in a specific column in specific table.
24

Backup and Recovery

If a hardware or software failure occurs, there needs to be a

way of reconstructing the database. Recovery is more than just using a backup copy of data from the night before.
ALL data up to the time of the failure has to be recovered!

Example: A bank will want to recover the effect of every transaction right up until the moment of the system failure.
An implicit part of this function is the continuous logging of all

changes, providing a copy of the updated data so that it can be used by the recovery process if needed (logging can often be switched on and off).
25

Requirement Change management

Here, we mean changes in the requirements (we are not

referring to changes in the data).

Changes may arise either form external influences (new

legislation) or from internal organization choice (improving the way a business operates).
There are two ways to react:

Restructuring Reorganization
26

Restructuring
Restructuring of the database occurs to meet changes in

requirements (if the definition of a table is changed to have an additional column, the storage of existing rows of that table must also be changed to allow for the new column).
This is performed by the restructure function of the DBMS,

with the new database structure being based on the original.

Restructuring will involve changes to the logical schema and

so will lead to changes in the storage schema, and may involve changes to the external schemas to make parts of the restructured database available to user processes.
27

Reorganization
Reorganization applies to the storage schema and

involves changes to the way in which the database is organized in files.

Reasons for performing reorganization
Operational maintenance: if the performance of the

database, and the user processes using the database, is important and falls below some threshold.
Porting and implementation maintenance: DBMS or

the underlying computer system undergoes changes that require the storage implementation to be revised.
28

Inside a DBMS
Normal users, designers and application developers should

NOT need to be aware of the internal components of the system; they should be concerned only with its functionality.
Knowledgeable users (responsible in achieving high

performance or specific security requirements), will need to know how to control the individual components.
There is usually a variety of tools available to database

administrators (DBAs) to enable them to interact effectively with the DBMS internal systems.
In the Sybase environment , the Sybase Central application

is the framework in which these administrative tools have been collected.

29

Internal DBMS data Viewing the system schema

Inside the DBMS there are a number of database tables that

store data related to the DBMS and the databases, users, security requirements and so on.
These are referred to collectively as the DBMS system

schema and commonly as the system tables.

These tables contain information relating to the specification

of the data managed by the DBMS. Every user database created within the DBMS will have its description stored in the DBMS schema in various system tables.
30

Activity 3.1 Examining the DBMS system tables

Launch the Sybase Central (database tool) Connect to the Walton database

(p. 78)

(m359 / m359 /ODBC Data Source name type/choose the name WaltonDSN) Explore and Enjoy

31

Expand this

32

33

Querying the system tables

Launch ISQL (Interactive SQL) Connect to the Walton database Write the following query:

34

Activity 3.2 Some wizards within Sybase Central (p. 79)

Connect to the Walton database using the following inf.:
Username : dba Password : lqs

35

36

37

38

39

40

41

One of the few places in Sybase Central where we can affect the internal physical storage schema (pages).
This option controls the effect of moving data between the hard disk and the main memory of a computer system as a series of pages.
42

43

44

45

46

Only one user

To create new user

47

Information system architectures

Usually, DBMS is separated from a user process. However,

Some personal computer packages will include a DBMS and a user process which together operate as a single unit. Such systems are examples of monolithic systems in which all the components are interdependent and cannot be separated.

Most modern computer systems are made from separate

distinguishable components which communicate with one another through clearly defined interfaces; (you were introduced to the Sybase DBMS and the end user tools ISQL and Sybase Central)
48

Information system architectures

The general term for a system in which interactions between

component processes are separated by a communication mechanism is a clientserver system.

Comparing this scenario with our case, a user process could

play the role of a client, with the DBMS playing the role of a server.
It is possible to have centralized clientserver systems in

which both the client and server exist in the same computer system. However, it is possible to distribute the client and server across a network (distributed system).
49

Information system architectures

Let us consider some of the common distributed data processing architectures and consider ways in which the data can be physically distributed when a network of computers is involved: ClientServer systems

Clientmultiserver systems
Distributed systems Replicated systems Mobile database systems

50

Client-Server systems
Client is usually responsible for the user interface and the

business application logic.

The server accepts and processes database requests and is

responsible for the DBMS functions.

This is commonly known as a two-tier clientserver system.

51

Client-Server systems
A three-tier system is one in which the client is reduced to

handling only the user interface (also known as a thin client).

The client interacts with an application server responsible for

implementing the business application logic (user process); this, in turn, interacts with a db server responsible for DB.

Three-tier architecture is common in web environments:

web browser (thin client) + web server offering app. services.

Multiple tiers are also possible!!
52

Three-tier client-server architecture

53

Properties of three-tier architecture

More flexible: processing can be done locally or centrally Ex: derived calculations, such as the total on a bill, can be done in the server or in the client

More scalable Delegating calculations to the clients allows:

more clients to be connected to the server faster response time since the network is usually slower than local processing

54

Clientmulti server systems

A single client requires access to several databases

which could be managed by different DBMSs. Here, the user process needs to serially connect to multiple database servers and combine the results in order to satisfy a particular requirement.
Suppose a universitys logical database is physically

split according to different geographic regions and there is a DBMS server in each regional office.
Each region physically stores data for only their

students and staff in that regions server.

55

Clientmulti server systems

A user might want to write a query that involves all

students in the university, such as a request to count all students on specific courses, or to change staff salaries globally;

56

Clientmulti server systems

We need a mechanism to make and break a connection

that also allows a client to communicate with different servers. Most database environments support this way of working by providing SQL statements for connection management.

57

Limitations of the client-multi server approach

The clientmultiserver approach fails to treat the data

collection as a single database.

It also requires quite a lot of extra work in managing the

connections to the various databases.

In addition, process users need to know the names of the

databases involved and which databases were responsible for which data items.
Distributed, replicated and mobile systems are designed

to relieve the user of these management and location awareness problems.

58

Distributed DBMS (DDBMS)

Here, there is no need for connection management within a user process.

Users and the processes they interact with do not have to know anything about where data is stored nor how it is processed. This is referred to as location independence. Distributed DBMS Global logical schema Distribution schema vs. vs. vs. Local DBMS Logical schema Storage schema

59

Distributed DBMS (DDBMS)

We want a DDBMS to take full advantage of the distributed nature of data, yet still have the general capabilities of a localized database, so we expect the following:
1. DDBMS should present a single view of all data - no worry

where the data is actually stored (location independence)

2. DDBMS should support transactions and maintain the

integrity of the distributed database. It must be able to recover from any failure (computer or network).
3. There must be a security process (unauthorized access)
4. DDBMS should work the same way whether it uses a PC or

a mainframe independently of the network characteristics.

60

Distributed DBMS (DDBMS)

A design consideration: Where to place the data in the different locations? Option1: Put some tables in one location & others in another Option2: Fragmentation (breaking a table into parts, while maintaining any necessary connection between the parts)
Horizontal : a subset of complete rows in one location 2. Vertical : some columns in one location 3. Combinations of horizontal and vertical fragmentation.
1.

Golden rule in Distributed DBMS: Place data so that most processing is performed locally
61

Distributed DBMS (DDBMS)

Transaction management in DDBMS A transaction that changes data in more than one database location will require coordination across the multiple servers. It is not sufficient for each server independently to be told to commit its work. We will need a two-phase commit. Query processing in DDBMS First : Finding where the data required is being managed using the distribution schema. Second: Determining how and where to transfer data between local databases. Since communication is the slowest step, the system might attempt to minimize the transfer of data when trying to optimize the distributed query.
62

Replicated systems
Replication provides copies of data that are distributed to

other database servers so that each user process can perform all processing locally.
Advantages:
Makes data available through local access Minimizes the use of remote connections Reduces dangers of remote system/connection failure

Replication can be applied to the entire contents of a DB or to

selected items within a database. Also, replicated items can be copied to every system within the replicated environment, or only to selected systems.
63

Replicated systems
A specialized replication service is required to ensure that copies

of replicated data are maintained in a suitable state of consistency

Ideally all changes to replicated data will be perfectly

synchronized within the system.

64

Replicated systems
A replication server, at each system, both sends and

receives data and so has two main roles: to receive or collect updates from its local database and pass copies on to other systems that require them to receive or collect updates from remote systems, via their replication servers, and apply them to the local database.
Who initiates a transfer of replicates?
Sender : Replicates are pushed (broadcast, subscript,..)
Receiver: Replicates must be pulled (demand-driven sys.)

65

Replicated systems
The replication server stores descriptions of any

replicated data and descriptions of how to reach other database servers.

Users may continue to work with local copies of data,

even though there may be a system failure at the remote location that holds the primary copy of the data
A database designer will decide the most appropriate

data items to replicate on each system. He will also determine which forms of replication service to use.
66

Mobile database systems

Portable devices (laptop, PDA, etc.) are now widely used Mobile DBMS systems are distributed or replicated systems

in which parts of a global database are fragmented and/or copied to the mobile devices in much the same way that the fixed networked distributed and replicated systems operate.
In a mobile DBMS the assumption is that for most of the time

these distributed and replicated data sets are not accessible to the global environment. Such systems depend on the mobile components reconnecting periodically in order to exchange data with the other systems.
67

Enhancing DBMS capabilities

DBMS vendors started to embed new capabilities directly into their DBMS products:
Java-enabled DBMS: Java code can be stored, manipulated

and executed within the DBMS.

Web-enabled DBMS: a web server interface is able to permit

web pages to directly interact with the content held in the DBMS without the need for an independent application service layer.
XML-enabled DBMS: these has built-in capabilities to store

and retrieve complex XML documents without the need for additional supporting software.
68

People and databases

We will consider different roles for people involved in a data management system:
Data administrators (DAs)
Database administrators (DBA or power-users) Database developers (DDs) Application developers (ADs) End users (users) Unwanted data users (abusers)

PS: Sometimes, different roles are performed by the same person.

69

Data administrators (DAs)

The DA is a senior manager whose remit is to oversee the

information strategy for the organization.

As a manager the DA sets policy and is NOT involved in the

technical management of the data or the data systems.

The role covers three major aspects of the organization's

data management:
What data is needed within the organization,

How that data gets used to the maximum benefit

What legal and ethical requirements the organization needs to

fulfill in order to store and manipulate that data.

70

Data administrators (DAs)

The DA will often act as a management interface between

the technical data management division and the business users of the data.
In such a role it is a requirement that the DA understands the

business domain and its data needs and is aware of the technical possibilities and limitations of the systems that are requested to meet these needs.
A typical DA will oversee the planning and development of

new data systems and the maintenance and control of existing systems.
71

Database administrators (DBAs)

Technical management of the data systems

Implementing the DAs policies and conceptual models

Leads the design & implementation of the physical database:
Definitions Constraints Security integrity controls

Management day-to-day running of the system:

performance tuning data backup management System monitoring to ensure that end user requirements are met.
72

Database developers (DDs)

Expert in the technical capabilities of the DBMS and

hardware systems.
Responsible for translating the logical database design into a

physical realization with a specific DBMS product

Aware of the different implementation options. He Chooses a

mechanism for implementation that satisfies performance as well as logical requirements.

Since DD is creating the physical DB, he will implement

constraints, security, backup and access mechanisms

Write the scripts, code, stored queries and procedures that

application developers and end users will often access.

73

Application developers (ADs)

Building & customizing user processes (programs & utilities,

report generators and query tools) to meet requirements of end-users.

Works under the direction of analysts who specify the

functionality required by each user or group of users.

Responsible for the coding, packaging and presentation of

data to and from the DBMS interfaces as well as handling error conditions.
The AD will treat the database and DBMS as a given, that is,

he wont be involved in its design or implementation.

74

End users (users)

Have the least interest in how the database has been

designed, implemented and managed.

They are only interested in the data. For example, a supermarket checkout operator scans

bar-codes through a bar-code reader and has no need to know what is happening to the data captured by the reader other than knowing it is a requirement if the customers bill and check-out receipt is to be accurate.
75

Unwanted data users (abusers)

These can range from those involved in:
industrial espionage hackers organized criminals (intent on fraud, blackmail, etc.) authorized users who overstep their permitted access

In the database environment it is common to find layers of

security and protection designed to prevent widespread damage if permitted access boundaries are breach.
Usually, threats in DB systems are to the large and valuable

data sets and the real world assets that they represent.
76

User interaction and database access

There are two types of user process: Application process
Software that involves a specific user view for just one database that

is used for some particular set of tasks (e.g. Walton app.) Can only be used for one purpose - the application for which it was specially designed and constructed; the user cannot change it.

Database tool
A general-purpose package that can be used with many different

databases, one at a time, by any authorized user, to access any data available to that user within a database (e.g. Sybase Central & ISQL). Provides different ways in which data can be processed & displayed. Should offer flexibility to the user
77

User interaction and database access

A range of capabilities are typical of database tools which are sometimes referred to by specialist names such:
Query tools: emphasis is on retrieval of stored data. End user tools: data retrieval, updates, constraint definition and

access control setting.

Reporting tools: facilities for the structured display and printing of

retrieved data.
Application development tools: allow the production of stand-alone

application processes without the need to use conventional programming languages directly.
Management tools: focus on the control and management of the

database through the DBMS.

78

SQL & interaction with the DBMS

Note the distinction between application processes and database tools. DBMS has also an SQL interface, showing that SQL can be used by any user process to interact with the DBMS.

Lets look in detail at the use of SQL within some types of user processes
79

Accessing the DBMS via an application process

Each DBMS uses a special language, known as a Data

Manipulation Language (DML).

An application process is written as a series of statements using a

conventional programming language (ex: Java) combined with other statements written using the DML specific to that DBMS.
The programming language being used is said to host the DML

(host language).Conversely, a DML statement is said to be embedded within an application process.

Example of Java code with an SQL statement embedded in it: rowSet.setUsername("m359"); rowSet.setPassword("m359"); rowSet.setCommand("SELECT title FROM book WHERE type = 'e-book' "); rowSet.execute(); ...

80

Activity 3.3 - Evaluating an SQL query

1. Run ISQL (which is a direct entry database tool)

2. Connect to Walton DB
3. Type the SQL DML statement:
SELECT title, author FROM book WHERE type = 'e-book

4. Execute the query (SQL statement submitted to DBMS)

ISQL is an example of a direct entry database tool (generalpurpose software, intended for use with many DBs and does not contain any fixed embedded DBMS).
81

Activity 3.4 - Using pre-prepared SQL queries

1. Run ISQL (which direct entry database tool)

2. Connect to Walton DB
3. Open the file showhighvaluestock.sql located in

..\M359\Activities\Block 1\Other Activities\

4. Execute it

The use of such a pre-prepared file is a convenience. This allows you to prepare SQL statements using any text editor and without having to connect to the DBMS until you want to evaluate your queries.
82

Activity 3.4 - Using pre-prepared SQL queries

83

Accessing the DBMS via a management DB tool

In Sybase Central the menu items, windows icons, data

entry and manipulation forms have database actions associated with them.
When a user selects a management function, that

request is sent to the DBMS typically in the form of SQL statements.

Users of such tools are NOT aware of DBMS requests

or the specification of the data manipulation for their activities. However, the database tool allows you to examine and modify the specification which is not possible in the application process.
84

Accessing the DBMS via a management DB tool

A database tool can store tailored actions and

procedures, such as forms, menu items, reports and so on, in a file.

Such a file is sometimes known as a library, since its

role is comparable to a program library that stores collections of conventional programs.

85

The SQL interface

The SQL interface represents some means of transferring an

SQL statement from a user process to a DBMS for execution

All the requests made by the various user processes can be

expressed using the database language, SQL: either embedded, directly entered by users, or stored and accessed in tool libraries.
An SQL statement has to be transferred to a DBMS for

execution. You may imagine this as a procedure call in which an SQL statement is just a parameter, as CALL execute_this_SQL (an SQL statement)
86

The SQL interface

The collection of procedures that are available for a given

DBMS forms the interface for interaction with the DBMS.

One major complication is that each software supplier

chooses to implement the details of each procedure call differently for their DBMS (different parameters and different formats..). Such an interface is said to be proprietary (or native) to that DBMS vendor.
An immediate problem with having different proprietary

DBMS interfaces is that any database tool which wants to interact with a DBMS via its interface has to express all interaction in terms of those proprietary procedures.
87

The SQL interface / ODBC

However, there is a solution to this problem. A set of general

procedures has been defined for common use, known as Open DataBase Connectivity (ODBC), because they provide an open(standard) interface to a DBMS

A database tool can use these ODBC procedures to express

its interaction with a DBMS in a single standardized way. Suppliers who want their DBMS to be accessible to database tools will supply an ODBC interface for their products. 88