IMS1907 Database Systems

Week 5

Database Systems Architecture

Systems Architecture

The ‘blueprints’ of the system Focus on the individual ‘building blocks’ of the system and how they are put together – hardware, software, network, database – encourages independence between components Often considers logical and physical views of the system and it’s components Match ‘user needs’ to architecture ‘Art’ vs ‘Engineering’? Monash University 2004 2

Database Systems Architecture

You have considered several different ‘views’ of databases – enterprise view, ER models, tables, datasheets, forms, reports, queries – different aspects of the logical and physical views A schema – a representation, model or specification of a view of a database Database systems are based on the ANSI/SPARC standard three-schema architecture Monash University 2004 3

Three-schema Architecture

The ANSI/SPARC standard for describing the structure of data (1978) consists of three schema – external schema • user views – conceptual schema • single, coherent definition of enterprise data – internal schema • physical storage structures Monash University 2004 4

Three-schema Architecture

User View n User View 2 User View 1 Physical Schema 1 Logical Schema 1 DB 1 External Schema Conceptual Schema Internal Schema PS n LS n DB n Enterprise Data Model Physical Schema 2 Logical Schema 2 Monash University 2004 DB 2 5

External Schema

Combination of the enterprise data model (top-down) and a collection of detailed (bottom-up) user views User view is a logical description of some portion of the database required to perform a task – guided by user requirements Represent data access and authorisation at the individual users’ level Conceptually a relation, but not actually stored in DBMS – records in a view are computed as needed Monash University 2004 6

Conceptual Schema

Detailed specification of the overall structure of organisational data Complete logical view – independent of any DBMS technology Usually depicted graphically – ER, OO modelling Schema specifications stored as metadata Scope is entire organisation or major business area Monash University 2004 7

Conceptual Schema

Includes all entity types and subtypes All relationships are documented All attributes are documented – keys specified Data types, formats, domains, and business rules ar4e specified and stored in repository Ideally data model is fully normalised – acceptable and common to normalise in the logical schema Monash University 2004 8

Internal Schema

Physical storage structure details Representation of the conceptual schema as it is physically stored on particular DBMS technologies A conceptual schema can have many internal schemas Consists of a logical and physical schema Good design relies on understanding of how data is accessed and used Monash University 2004 9

Internal Schema

Logical schema – representation of data for particular DBMS • relational, OO, dimensional – tables, data types, formats, keys, … – derived by transforming elements of conceptual schema to DBMS structures Monash University 2004 10

Internal Schema

Physical schema – set of specifications describing how data from a logical schema are stored in a computer’s secondary memory for a specific DBMS – ideally one physical schema for each logical schema – describes organisation of physical records, file organisations, access paths to data, usage of indexes, clusters, … Monash University 2004 11

Database System Development

The conceptual schema and external schema are typically developed iteratively until both are fully defined Logical schema is developed by transforming conceptual schema (or parts of it) to implementation model constructs Associated physical schema is specified taking into account the software, hardware and network characteristics along with users’ performance expectations Inconsistencies discovered in physical schema may require iteration back to design of conceptual schema Monash University 2004 12

Data Independence

Ability to change the schema at one level of a DB without having to change the schema at the next higher level Logical data independence – the capacity to change the conceptual schema without having to change external schema or application Physical data independence – the capacity to change the internal schema without having to change conceptual or external schema Only mappings between levels should change Monash University 2004 13

Data Independence

This means that application programs are insulated from – changes in the way the data is structured – logical • changes in the way data is defined should not affect what the user sees – changes in the way the data is stored – physical • changes in the data storage method should not affect what the user sees, nor the conceptual view of the data Monash University 2004 14

Database Systems Network Architecture

A major decision in database systems design relates to where the data is physically stored and processed Many different types of database systems in use in enterprises – need to balance organisational, technical and usage issues – data for a given IS may reside in multiple locations on many machines – different types of processing occur at different locations Monash University 2004 15

Client/server Architecture

Significant factors in the growth of client/server architecture – increasing systems complexity – the proliferation of web-enabled systems Need for an application level of ‘servers’ that handle transactions from ‘client’ machines against some back-end database Data can exist on many different types of server – database server, application server, client server, web server Monash University 2004 16

Client/server Architecture

We commonly consider the following three architectural layers or tiers – Client tier – Application or Web server tier – Enterprise or Data Services server tier Sometimes this view is limited to the client tier and a general server tier Monash University 2004 17

Client/server Architecture

Client tier – sometimes called the presentation tier – desktop PCs, workstations, laptops, devices – managing user interfaces – may be some localised data – web scripting tasks may be executed – concept of a ‘thin client’ Monash University 2004 18

Client/server Architecture

Application or Web server tier – sometimes called process services tier – houses applications • A/P, A/R, Orders, Sales, Inventory, … – houses web services • processes HTTP protocols, scripting tasks, dynamic web pages, session management, calculations – provides data access • access and connectivity to DBMS Monash University 2004 19

Client/server Architecture

Enterprise server tier – sometimes called the data services or database tier – sometimes stored on a mainframe or minicomputer – transaction databases containing all organisational data, summarised data on departmental databases – performs sophisticated calculations – manages merging of data from multiple sources – manages multiple requests for data from multiple sources Monash University 2004 20

Database Systems Architecture

In a client/server architecture – DBMS software on a server (database server or database engine)performs database commands sent to it directly from client workstations or via application servers – client concentrates mainly on user interface functions – application servers concentrate on application-related processing functions – allows distribution of database across all types of user groups and one central server, as a single distributed database or as a set of physically related databases Monash University 2004 21

Database Systems Architecture

Implications for database development – ease of separation of development of database and modules that maintain it, from the IS applications that access and present database contents to users – many programming languages provide easy-to-use GUIs • Powerbuilder, Java, VB.NET, … – middleware greatly facilitates application access to data across large, complex systems – opportunities for reuse of software components Monash University 2004 22

References

Elmasri, R. and Navathe, S.B., (2000),

Fundamentals of Database Systems

, (3 rd edn.), Addison-Wesley, Reading, Massachusetts, USA.

Hoffer, J.A., Prescott, M.B. and McFadden, F.R., (2005),

Modern Database Management

, (7 th edn.), Pearson Education Inc., Upper Saddle River, NJ, USA.

Kroenke, D.M., (2004),

Database Processing: Fundamentals, Design and Implementation

, (9 th edn.), Pearson Education Inc., Upper Saddle River, NJ, USA.

Ramakrishnan, R. and Gehrke, J., (2003),

Database Management Systems,

(3 rd edn.), McGraw-Hill, Boston, USA.

Monash University 2004 23